Using variables in package configuration

Question

Using variables in package configuration

johnmcp

SSCommitted

Points: 1969
More actions
June 8, 2009 at 5:32 am

#132736

I'm trying to create a dynamic file name for my logging file in SSIS, but, I also want the connection string to be configurable through package configurations so that when I'm deploying the package I can specify a particular folder. Or to put it another way, I want the file name to change dynamically and the folder path to be configurable when I'm deploying the package.
I thought I could create a variable which would hold the dynamically changing file name, then append this variable to the end of my connection string in the configuration file, but this doesn't seem to be possible (or maybe I'm just not doing it correctly?). Can anyone suggest an alternative way to achieve this?

Viewing 4 posts - 1 through 3 (of 3 total)

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

Ells SSCrazy Eights Points: 9901 More actions · Answer 1

Couple of options. Why not use System variable PackageName. I use a package level variable LogFolder that contains a string value such as "c:\logging\". Then the connection can be set to use the file name based on the folder and package name.

First step is to use the package variables and get that working. Then use package configurations if required to offer more flexibility.

Hope that helps.

Mark

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 2

Have a look at these two links. They show you different options on how you can achieve this.

http://www.sqlis.com/post/Easy-Package-Configuration.aspx

http://www.sqlis.com/post/Looping-over-files-with-the-Foreach-Loop.aspx

[font="Verdana"]Markus Bohse[/font]

johnmcp SSCommitted Points: 1969 More actions · Answer 3

Ok I was thinking about this in the wrong way. All I had to do was create a variable for my folder name, then use this variable in an expression to define the connection string for my log file, and finally add my variable to the package configuration.

So if anyone else is wondering, my connection string looks like this now:

@[User::LogFilePath] + "\\Log" + (DT_STR,4,1252)DATEPART( "yyyy" , @[System::StartTime] ) + RIGHT("0" + (DT_STR,4,1252)DATEPART( "mm" , @[System::StartTime] ), 2) +

RIGHT("0" + (DT_STR,4,1252)DATEPART( "dd" , @[System::StartTime] ), 2) + ".txt"

creating a new file everyday, which is exactly what I need.

Thanks for the help guys.