February 18, 2013 at 6:52 am
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.
February 18, 2013 at 7:29 am
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.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
February 18, 2013 at 7:44 am
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
February 18, 2013 at 10:18 am
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.
February 18, 2013 at 10:32 pm
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
February 19, 2013 at 6:42 am
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?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
February 19, 2013 at 8:13 am
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.
February 19, 2013 at 9:12 am
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? :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 😉
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply