January 26, 2010 at 5:08 am
Can anyone give me a simple script for reading a variable and then writing to another variable in a script task?
I'm trying to dynamically set a file system task destination file name, where we need to insert a string to start of a file name?
Any help would be hugely appreciated
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
January 26, 2010 at 8:51 am
Have you tried Expressions? Seems like the scripting/variable approach would be a little overkill but I'm not fully up onthe requirement.
Steve.
January 26, 2010 at 9:04 am
That was my initial thought.
I tried using an expression on the destination file path, but SSIS was adding in a \ which I wasn't expecting,so I've had to go down the route of writing a variable using a script task.
I managed to find a few code snippets that i've put together to give me the required result.
Is there a way to define other variable values using expressions then?
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
January 26, 2010 at 9:10 am
http://www.simple-talk.com/sql/ssis/passing-variables-to-and-from-an-ssis-task/
usually lock and unlock them
Dts.VariableDispenser.LockOneForWrite
vars.Unlock()
sort of thing
January 26, 2010 at 9:18 am
I tried using an expression on the destination file path, but SSIS was adding in a \ which I wasn't expecting,so I've had to go down the route of writing a variable using a script task.
I managed to find a few code snippets that i've put together to give me the required result.
Is there a way to define other variable values using expressions then?
Where was it adding in the backslash? ie could you simply modify your expression to anticipate the additional \ added by SSIS? Are you able to post a sample of what you had (indicating which property it was on), would make it infinitely easier to try and provide you some hints.
And to answer your question, yes, you can modify variable values using expressions.
Steve.
January 27, 2010 at 1:47 am
I've changed my package now a little too much to revert back to where I was having issues to give an exact example, basically my steps were:
1. Assign the fully qualified file name and path to a variable for the each loop enumorator.
2. the second step was to rename the file using a file system task as I needed to strip off the .txt extension and prefex the file name with "GRAINT." so effectively I was trying to acheive something like taking C:\MyPath\GR123.txt and renaming it as file name C:\MyPath\GRAINT.GR123
I done this using an expression for the file system task: Destination variable. Setting the FST: IsDestinationPathVariable to False.
what it was generating was in the end was something like: C:\MyPath\GRAINT.\GR123, and I couldn't see how I could get rid of the slash in GRAINT.\GR123.
Could you give me an example though, of how I can set a variable value, using expressions. I think that'll be quite handy to know.
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
January 28, 2010 at 11:20 am
I think this is what you were asking for. I think the script tasks are easier to follow than the expressions.
Dim ExportFileName As String, ArchiveExportFileName As String
ExportFileName = Dts.Variables("User::ExportFileFolder").Value.ToString + Dts.Variables("User::ExportFileName").Value.ToString
ArchiveExportFileName = Dts.Variables("User::ExportArchiveFileFolder").Value.ToString + CStr(Format(Now(), "yyyyMMddhhmmss_")) + Dts.Variables("User::ExportFileName").Value.ToString
Dts.Variables("User::ExportArchiveFileName").Value = ArchiveExportFileName
January 29, 2010 at 2:30 am
Thanks Steve / Charles.
I think it's somthing that comes down to preference. Personally, for the clarity of being able to see what your setting in the variable to be ( plus I'm not that experienced at vb) I think expressions are easier, looking at Jamies blog.
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
January 29, 2010 at 6:15 am
I'm partial to them too, but like putting equations on Precedence Constraints, it's not something that gets done 'all the time' . I often create packages that are maintained by other people and find that the visual indicators (the little purple corner for expressions and little function symbol for the constraints) aren't 'loud' enough to let people know that these settings are there.
Steve.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy