I have been looking all over for some info about other people having this problem, but haven't found anything.
I have a package that needs to download a dated file from an ftp site. I am using a couple script objects to set variables, and one of them is the filename based on the date. I use an expression to get the date:
@.[User::varFileName] = (DT_WSTR,4) Year( GetDate()) + (DT_WSTR,2) Month( GetDate()) + Substring((DT_WSTR, 29) GETDATE(), 9, 2)
Everything works really well when I am debugging it locally. However once it is on the server or even once I come back to it in a day or two, I am still seeing the old date. I thought it might be because my variable needed to be set to evaluateexpression = true, however once I did this it hung me and prevented me from debugging and I had to end bus dev studio. Not sure if its because it is being evaluated in two places (as a global and then in a script) but when I took it out of my script it hung again. Its strange in order to get it to work when I am debugging it locally I have to go to each process and evaluate the expressions in there, then it seems to work. thanks!
Doriss wrote:
I have been looking all over for some info about other people having this problem, but haven't found anything.
I have a package that needs to download a dated file from an ftp site. I am using a couple script objects to set variables, and one of them is the filename based on the date. I use an expression to get the date:
@.[User::varFileName] = (DT_WSTR,4) Year( GetDate()) + (DT_WSTR,2) Month( GetDate()) + Substring((DT_WSTR, 29) GETDATE(), 9, 2)
Everything works really well when I am debugging it locally. However once it is on the server or even once I come back to it in a day or two, I am still seeing the old date. I thought it might be because my variable needed to be set to evaluateexpression = true, however once I did this it hung me and prevented me from debugging and I had to end bus dev studio. Not sure if its because it is being evaluated in two places (as a global and then in a script) but when I took it out of my script it hung again. Its strange in order to get it to work when I am debugging it locally I have to go to each process and evaluate the expressions in there, then it seems to work. thanks!
Not quite sure of the whole picture here but when you say its being evaluated in two places I start to worry. So, two questions:
Where is the expression (is it on a variable or elsewhere)?
What are you attempting to do in your script task?
-Jamie
|||
The script portion might just be my inexperience with ssis, but I read online somewhere that using script objects to set your variables was good practice and I was originally having problems getting my variables to be read at all, and this solved the problem. So basically they are just empty script objects where I am using the expressions for those objects to set variables that will be used throughout the process. I used two of them because at the time I couldn't figure out how to use them to set just a plain variable without using one of the properties (ex: readonlyvariable). So I use the readonlyvariable and the readwrite variable in each object to set my expression. I thought for a moment just the other day that I could actually take this out and set the variables globally, but as I mentioned, that is when I had problems with the app crashing when I tried to set the expression to be evaluated at a global level. I actually tried changing my program today to also retrieve the date from the database by running a query that would return it to a variable and it still did not work. It seems as if the variables get set at some point in time and then they are not re-evaluated.
|||The script portion might just be my inexperience with ssis, but I read online somewhere that using script objects to set your variables was good practice and I was originally having problems getting my variables to be read at all, and this solved the problem. So basically they are just empty script objects where I am using the expressions for those objects to set variables that will be used throughout the process. I used two of them because at the time I couldn't figure out how to use them to set just a plain variable without using one of the properties (ex: readonlyvariable). So I use the readonlyvariable and the readwrite variable in each object to set my expression. I thought for a moment just the other day that I could actually take this out and set the variables globally, but as I mentioned, that is when I had problems with the app crashing when I tried to set the expression to be evaluated at a global level. I actually tried changing my program today to also retrieve the date from the database by running a query that would return it to a variable and it still did not work. It seems as if the variables get set at some point in time and then they are not re-evaluated.
Woah. That's alot of information.
I wouldn't agree that using script tasks to set a variable is considered better practice than using an expressoin on the variable. In fact I would argue to the contrary:
Variables evaluated by an expression are more reusable|||
Wanted to post that I found the resolution to this. I did end up getting rid of the script object stuff. I think I suffered from too much information on the web that sent me in the wrong direction. I decided to use global variables, and got rid of my script objects. But what really made it work was removing the reference to the variable name. So this:
@.[User::varFileName] = (DT_WSTR,4) Year( GetDate()) + (DT_WSTR,2) Month( GetDate()) + Substring((DT_WSTR, 29) GETDATE(), 9, 2)
Now becomes:
(DT_WSTR,4) Year( GetDate()) + (DT_WSTR,2) Month( GetDate()) + Substring((DT_WSTR, 29) GETDATE(), 9, 2)
When I did it the other way it was locking my variables (which was why it was crashing though it actually wasn't - it was just taking a really long time to tell me what the problem was). I also changed some of my other variables that were referencing this variable, which I just read about.
For the record, I think the documentation on variables is a bit lacking. It seems some important things to know from my experience is to make everything global (work with it in the variables window and set it's properties). Make sure to set the evaluateexpression to True if thats what you want (in the properties window). Do not reference another variable in your expression and do not set the variable equal as mentioned above (if you are doing this in the properties expression window). Other things to know that it took me forever to figure out is that you should save the package as a server package (you have to use the copy as) - this is the easiest way to port over your package. You need to give the user account that this is running under user mapping to msdb (SQLAgentOperatorRole, SQLAgentReaderRole, SQLAgentUserRole) as well as create a proxy account. Best info for that is here:http://www.codeproject.com/useritems/Schedule__Run__SSIS__DTS.asp
Phew - it only took me forever to actually get this to work!
No comments:
Post a Comment