Scipt Task to Write a variable

Question

Scipt Task to Write a variable

dave-dj

SSChampion

Points: 11297
More actions
January 26, 2010 at 5:08 am

#216082

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)

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 1

Have you tried Expressions? Seems like the scripting/variable approach would be a little overkill but I'm not fully up onthe requirement.

Steve.

dave-dj SSChampion Points: 11297 More actions · Answer 2

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)

darth vodka SSCrazy Points: 2367 More actions · Answer 3

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

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 4

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.

dave-dj SSChampion Points: 11297 More actions · Answer 5

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)

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 6

Jamie Thompson gives a pretty good example on his blog, here.

HTH,

Steve.

Chuck Hottle SSCarpal Tunnel Points: 4777 More actions · Answer 7

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

dave-dj SSChampion Points: 11297 More actions · Answer 8

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)

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 9

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.