how do i implement custom variable in my execution task?

Question

Post reply

how do i implement custom variable in my execution task?

maya potiyevskaya

SSCrazy

Points: 2091
More actions
November 7, 2007 at 12:06 pm

#153942

Hi Experts,
every month i run ssis that appends prior month data and updates the date stamp with the last business day of the month for each appended record. there is a user-defined function that calculates it and my update statement looks something like this: Update myTable set as_of_Date = dbo.MyCalcDate() where as_of_Date is null.as you can see, it takes a lot of time to run this statement because we need to retrieve this value for each record. i would like to retrieve it only once, put it into custom variable and change my statement to:
Update myTable set as_of_Date = @@my_stamp where as_of_Date is null.
Can i please do it in SSIS? Is it hard?
Many thanks!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Tommy Bollhofer SSChampion Points: 14940 More actions · Answer 1

Use an execute SQL task. See "Usage Examples" here: http://www.sqlis.com/58.aspx

Tommy

Follow @sqlscribe

maya potiyevskaya SSCrazy Points: 2091 More actions · Answer 2

it's pretty good, thank you.

i did define custom variable called last_prior_month_date as DateTime under User namespace.

now, how and where do i actually assign a value to it that is coming from a user-defined function that resides in my database; the same connection as one used for execute task.

thank you.

Tommy Bollhofer SSChampion Points: 14940 More actions · Answer 3

NP - so for example. Let's say you have a datetime variable called "MyDate". Create a new execute SQL task. Change the result set to "single row". The SQL statement will look something like:

select dbo.MyFunction(SomeParameter) AS SomeValue

Select the result set tab within the execute SQL task. Assign the result set name as "SomeValue". Select the variable "MyDate"

That's it - hope this helps 🙂

Tommy

Follow @sqlscribe

maya potiyevskaya SSCrazy Points: 2091 More actions · Answer 4

this is pretty good :-), i got this done with your help, Tommy, thank you.

now, how do i pass this value of my custom variable MyDate to my sql statement in my next execution task.

i did something like this, but the syntax is invalid:

Update myTable set as_of_Date = @@MyDate

where as_of_Date is null

thank you so much for your help.

Tommy Bollhofer SSChampion Points: 14940 More actions · Answer 5

NP 🙂

Create another execute SQL task, select the parameter mapping tab. Under variable name, select User::MyDate. The direction will be Input, the data type will be DATE, and the parameter name will be 0.

Define your UPDATE statement in the general tab (SQL statement) as follows:

Update myTable set as_of_Date = ?

where as_of_Date is null

Tommy

Follow @sqlscribe