Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

When does a variable get evaluated Expand / Collapse
Author
Message
Posted Monday, February 18, 2013 6:52 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 25, 2014 2:48 AM
Points: 386, Visits: 623
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
Posted Monday, February 18, 2013 7:29 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 6:08 AM
Points: 4,982, Visits: 11,673
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1421217
Posted Monday, February 18, 2013 7:44 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:40 PM
Points: 7,084, Visits: 12,576
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
Post #1421223
Posted Monday, February 18, 2013 10:18 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 25, 2014 2:48 AM
Points: 386, Visits: 623
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
Posted Monday, February 18, 2013 10:32 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:40 PM
Points: 7,084, Visits: 12,576
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
Post #1421431
Posted Tuesday, February 19, 2013 6:42 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 6:08 AM
Points: 4,982, Visits: 11,673
opc.three (2/18/2013)
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.


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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1421613
Posted Tuesday, February 19, 2013 8:13 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 25, 2014 2:48 AM
Points: 386, Visits: 623
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
Posted Tuesday, February 19, 2013 9:12 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:40 PM
Points: 7,084, Visits: 12,576
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 Obfuscate though? 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


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1421710
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse