LearnItFirst User Forum

Welcome Guest

Search | New Posts | Members | Log In | Register

SQL Server Forum - LearnItFirst.com » SQL Server Database Administration » Integration Services (SSIS) and DTS » Script Replace part of Variable

Script Replace part of Variable

Options

Previous Topic · Next Topic

pvong

Posted: Tuesday, August 12, 2008 3:12:46 PM

Rank: Newbie

Joined: 8/8/2008
Posts: 1
Points: 3
Where do you live?: Baltimore

FYI - The wink face is actually Close Parentheses with a simi colon.

I'm a newbie. This is my first script and I'm not much of a programmer.

Here's my scenerio.
I have a Package Variable called "TradeDateString" and it's a string and it's value is being pulled from a SQL Task and the format is always in YYYY-MM-DD like 2008-08-06. I've tested this over and over and it works just fine.

I have another Package Variable called "SqlCommand" and it's also a string. The following is the Full Statement:
WITH MaxMoxyFill(OrderID, ExecTime) AS (SELECT OrderID, MAX(CONVERT(VarChar, FillDate, 114)) AS FillDate FROM MoxyFill GROUP BY OrderID) SELECT MoxyOrders.OrderID, DATEADD(DAY, 0, DATEDIFF(DAY, 0, MoxyOrders.OrderDate)) AS TradeDate, MoxyTranType.TranType AS Side, MoxyTradeSummary.TotalPlaceQty AS NumShares, UPPER(MoxyOrders.Symbol) AS Ticker, CASE Limit WHEN 'MKT' THEN 'M' ELSE 'L' END AS MktLimit, CASE Limit WHEN 'MKT' THEN '' ELSE Limit END AS LimitPx, CASE CONVERT(VarChar, GoodThroughDate, 23) WHEN '1900-01-02' THEN 'DAY' ELSE 'GTC' END AS DayGTC, MoxyOrders.OrderBrokerID AS Broker, MoxyTradeSummary.TotalFillQty AS ExecNumShares, MoxyTradeSummary.TotalFillCost / MoxyTradeSummary.TotalFillQty AS ExecPX, MoxyTradeSummary.TotalBillComm / MoxyTradeSummary.TotalFillQty AS Comm, MoxyOrders.UserDef2 AS Notes, CONVERT(Char(5), MoxyOrders.OrderDate, 108) AS OrderTime, MaxMoxyFill_1.ExecTime, MoxyOrders.UserDef1 AS CurrPX, MoxyOrders.OrderUserDef1 AS PM FROM MoxyOrders INNER JOIN MoxyTranType ON MoxyOrders.TranCode = MoxyTranType.TranCode AND MoxyOrders.TranCode = MoxyTranType.TranCode INNER JOIN MoxyTradeSummary ON MoxyOrders.OrderID = MoxyTradeSummary.OrderID INNER JOIN MaxMoxyFill AS MaxMoxyFill_1 ON MoxyOrders.OrderID = MaxMoxyFill_1.OrderID WHERE (DATEADD(DAY, 0, DATEDIFF(DAY, 0, MoxyOrders.OrderDate)) = CONVERT(DATETIME, '2999-12-31 00:00:00', 102))

All I want to do is for the Script task to look for 2999-12-31 and replace it with my TradeDateString variable.

In the ScripTask, I have TradeDateString as a READONLY variable and SqlCommand as a READWRITE variable.

This is what I tried and these this is the err msgs I'm getting.

Public Sub Main()
Dts.Variables("SqlCommand" Wink

.Value = Replace("2999-12-31", CStr(Dts.Variables("TradeDateString" Wink

.Value), CStr(Dts.Variables("SQLCommand" Wink

.Value))
Dts.TaskResult = Dts.Results.Success
End Sub

I get the error you see in err1.jpg.

What do I have to type to get the SqlCommand string to replace 2999-12-31 with my TradeDateString?

Thanks!
Phil

Scott Whigham

Posted: Tuesday, August 19, 2008 7:08:00 PM

Rank: Super Mod

Joined: 3/20/2006
Posts: 476
Points: 1,053
Where do you live?: Dallas, TX

Hi there -

I see these message typically in two scenarios: (1) mistyping the variable name (it is case sensitive), or (2) not listing the variable in the Script Task's list of ReadOnly/ReadWrite variables.

Users browsing this topic

Guest

Forum Jump

You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.