Script Replace part of Variable

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Script Replace part of Variable

Author

Topic

pvong
Yak Posting Veteran

58 Posts

Posted - 2008-08-13 : 09:36:57

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

------------------------------
Using VS2008 / Learning in VB.Net 2.0 / Win2003 / SQL 2005 w SP2
Be kind to the newbies because you were once there.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-13 : 10:10:14

Didnt understand need of script task here. could you directly use the TradeDateString variable value in the expression evaluation for getting value of sqlcommand? you just need to set evaluate as expression property for sqlcommand variable to yes for this.

pvong
Yak Posting Veteran

58 Posts

Posted - 2008-08-13 : 11:58:18

Thanks for the advice. I'm very new at this and I was wondering if you can help me through the steps.

How do I "evaluate as expression property for sqlcommand variable to yes". I couldn't find this option. I did find expression in the Script Task and this is what I tried.

I got rid of my Script task and started a new one to make sure I'm starting fresh. In the ScripTask, I have TradeDateString as a READONLY variable and SqlCommand as a READWRITE variable. In the Expression section, I chose the ReadWriteVariable as the property and chose this expression.
REPLACE( @[User::SqlCommand] , "2999-12-31", @[User::TradeDateString] )
When I click Evalutate Expression, I see that it gives me exactly what I need. When I go back to see the ReadWriteVariable, my SqlCommand is now gone and it's replaced with my new Select Statement with the correct Replace. Of course when I rung this, I get nothing. I'm assuming it's because it's running my original Select with the 2999-12-31 because ReadWriteVariable did not list my SqlCommand.

Am I even close on doing this correctly? If so, how do I set the SqlCommand to = the Select Statement with the new Replaced date? I thought that had to be set in the ReadWriteVariable?

Thanks!

------------------------------
Using VS2008 / Learning in VB.Net 2.0 / Win2003 / SQL 2005 w SP2
Be kind to the newbies because you were once there.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-13 : 12:18:44

first create the variable TradeDateString in variable window
then create the sqlcommand variable set evaluate as expression property on property tab (select variable and click property icon on top) to true and set expression value as:-

'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)) = '+ (DB_WSTR)(DB_DATE)[user::@TradeDateString]

click evaluate expression and see that its valid.

pvong
Yak Posting Veteran

58 Posts

Posted - 2008-08-13 : 15:29:04

This is definitely the way I want to do it. I was wondering if you could help me out with some of the problems. I'm very new at this.

To help me understand this, I just decided to start a whole new package for test.
1) I created a project DataSource. no problems
2) I created a String Package Variable called "TradeDateString" and I set the value to '2008-08-05'.
3) I created a Package Variable called "SqlCommand"
4) In the properties, I set the EvaluateAsExpression = TRUE
5) In the Expression Builder, I have the following just for testing:

SELECT CONVERT(VarChar, OrderDate, 23) AS TradeDate
FROM MoxyOrders
WHERE (CONVERT(VarChar, OrderDate, 23) = + @[User::TradeDateString] )

6) The error message I get is:

TITLE: Expression Builder
------------------------------
Expression cannot be evaluated.

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.762&EvtSrc=Microsoft.DataTransformationServices.Controls.TaskUIFramework.TaskUIFrameworkSR&EvtID=FailToEvaluateExpression&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:

Attempt to parse the expression "SELECT CONVERT(VarChar, OrderDate, 23) AS TradeDate FROM MoxyOrders WHERE (CONVERT(VarChar, OrderDate, 23) = + @[User::TradeDateString] )" failed. The expression might contain an invalid token, an incomplete token, or an invalid element. It might not be well-formed, or might be missing part of a required element such as a parenthesis.
(Microsoft.DataTransformationServices.Controls)
------------------------------
BUTTONS:
OK
------------------------------

7) If I test this query with a real value in any SSMS, I get my results with no problem.
SELECT CONVERT(VarChar, OrderDate, 23) AS TradeDate
FROM MoxyOrders
WHERE (CONVERT(VarChar, OrderDate, 23) = '2008-07-29')

Questions, how does the variable know to use my Project Datasource? Almost everything I do ask for a Datasource and using the expression in the variable does not ask me this. I did create a project Datasource so I assume the variable will auto use this source?

Thanks!

------------------------------
Using VS2008 / Learning in VB.Net 2.0 / Win2003 / SQL 2005 w SP2
Be kind to the newbies because you were once there.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-14 : 14:33:51

the variable doesnt want a source. All expression builder does is to see if expression is valid. it wont execute. its executed only when you call this variable in any of your task using connection manager set for it.
Now the expression

'SELECT CONVERT(VarChar(length), OrderDate, 23) AS TradeDate
FROM MoxyOrders
WHERE CONVERT(VarChar(length), OrderDate, 23) =' + @[User::TradeDateString]

try like this and see if it works

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources