When does a variable get evaluated

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Integration Services » When does a variable get evaluated

When does a variable get evaluated

Rate Topic

Display Mode

Topic Options

Author

Message

aaron.reese

Posted Monday, February 18, 2013 6:52 AM

SSC Veteran

Group: General Forum Members
Last Login: Monday, May 13, 2013 7:33 AM
Points: 234, Visits: 441

Guys,

I have a package scoped variable which gets its value from an expression:

replace((DT_WSTR, 30)(DT_DBDATE)GETDATE() ,"-","")

I use this variable to pass the date (as a string) to a stored procedure.

The package is surrounded by a for-loop container so that it is always running (I use Konesans file watcher to trigger the package processing) It appears that the value being passed to the procedure is the date the package was started, not the current date. I had made the assumption that this would get validated at point of use: if I wanted to evaluate based on the package start date I would have used the relevant system variable.

Was my original assumption wrong, or do I need to look elsewhere. If it was wrong, then how do I get it to evaluate when it is called (I could put the calcuation into the execute sproc command but it gets used in loads of places, which is why the variable was a better choice)

BTW I can't use scripts to set variable values, I don't have a full copy of VS on my development machine.

Post #1421190

Phil Parkin

Posted Monday, February 18, 2013 7:29 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 5:12 AM
Points: 4,226, Visits: 9,458

Your ExecuteSQL task will have associated with it a system variable called ContainerStartTime, scoped only at the task level. Maybe that is worth a try.

____________________________________________________________________________________________

Help us to help you. For better, quicker and more focused answers to your questions, consider following the advice in this link:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.

Post #1421217

opc.three

Posted Monday, February 18, 2013 7:44 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 7:22 AM
Points: 6,693, Visits: 11,707

I can tell you that variables based on expressions do change when they are used but the "used" part is a little murky. The loop coupled with the waiting that the file watcher task does may not give you what you expect. Phil's idea is definitely worth a shot. I was also going to suggest changing the variable scope to be at the Execute SQL Task level and see if that helps defer the evaluation to be closer to when you are wanting it to be evaluated.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1421223

aaron.reese

Posted Monday, February 18, 2013 10:18 AM

SSC Veteran

Group: General Forum Members
Last Login: Monday, May 13, 2013 7:33 AM
Points: 234, Visits: 441

Thanks to both..

The sproc gets fired up to 18 times, but by different tasks, which is why I wanted to use a package level variable.

I didn't want to use the container start time as I would have to configure the variable 18 times or use in-line conversions in the expression which makes it difficult to maintain.

In the end, I have added an Execute SQL task immediately after the file watcher which does a


SELECT cast(year(getdate())as nvarchar(4)) + right('0' + cast(month(getdate()) as nvarchar(2) ),2) + right('0' + cast(day(getdate()) as nvarchar(2)),2) as 'FormatDate'

and saves the recordset value to the variable. This way I know it gets re-evaluated each time the file watcher fires.

Post #1421289

opc.three

Posted Monday, February 18, 2013 10:32 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 7:22 AM
Points: 6,693, Visits: 11,707

Good to know you have a way forward but it seems like a Script Task would be a better choice here to save you a round-trip to the database server just to get a formatted date. I didn't get what you meant by "full copy of VS." That is not a requirement to develop Script Tasks in BIDS.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1421431

Phil Parkin

Posted Tuesday, February 19, 2013 6:42 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 5:12 AM
Points: 4,226, Visits: 9,458

opc.three (2/18/2013)

Yes, I was going to follow up on this too. What happens if you add a script task and click on the Edit Script button?

____________________________________________________________________________________________

Help us to help you. For better, quicker and more focused answers to your questions, consider following the advice in this link:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.

Post #1421613

aaron.reese

Posted Tuesday, February 19, 2013 8:13 AM

SSC Veteran

Group: General Forum Members
Last Login: Monday, May 13, 2013 7:33 AM
Points: 234, Visits: 441

Nothing. I don't get the script editor window, which is why I assumed that I needed the full copy of VS to use scripts.

Add a Script task to the design surface

Double click to get the script task dialog box

move to Script in the left pane

Click on Design Script button

The dialog box closes and returns me to the design surface with the script task having focus with its drag handles.

FWIW I don't like script tasks as I think that they obfuscate what is happening (and VB.net scares me!) . I would prefer to use an alternative where possible, even if it is not so efficient.

Post #1421665

opc.three

Posted Tuesday, February 19, 2013 9:12 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 7:22 AM
Points: 6,693, Visits: 11,707

aaron.reese (2/19/2013)

FWIW I don't like script tasks as I think that they obfuscate what is happening (and VB.net scares me!) . I would prefer to use an alternative where possible, even if it is not so efficient.

Scared I guess I can understand but there is no reason to be Smile

Obfuscate though? Ermm

Moreso than using an Execute SQL Task that pushes a result into a Variable?

Personally I prefer C# but VB.net can get the job done too. Unfortanately C# is not an option for you until you move to SSIS 2008 or above. I can see your concern about learning a new language but know that Script Tasks will help you bridge the gap between what SSIS provides out of the box and what you will actually need to produce for solutions using SSIS as the requirements get more complex. I would just say to keep an open mind Wink

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1421710

« Prev Topic | Next Topic »

Permissions