June 20, 2010 at 11:16 pm
Hi!
I hope someone can help me with an SSIS problem…….
I have created an SSIS package and used the Expressions ConnectionString Parameter to generate a timestamped flat file, and this works fine. Now I want to include this connection string into a config file. However, when I do this, it generates a static copy of the file name.
Eg, this is what I have in my Expressions Connection String:
"\\\\server\\d$\\ssis\\FILE_NAME"+ (DT_WSTR, 30) (DT_DBDATE) GETDATE() + replace((DT_WSTR, 30) (DT_DBTIME) GETDATE(),":", "" )+".csv"
and this is what is generated in the config file:
[ConnectionString]" ValueType="String">
<ConfiguredValue>\\server\d$\ssis\FILE_NAME2010-06-21130604.csv</ConfiguredValue>
Is there any way to make this file name dynamic within the config file?
Thanks 😀
June 21, 2010 at 7:09 am
Computers are dumb things. They do only exactly what you tell them to do, and nothing more.
You gave an expression, so SSIS evaluated it and stored it in the config file.
So, I don't think you can put a dynamic file name in a config file. Maybe you can try putting quotes around it, but I'm afraid SSIS will look it as text and will not evaluate it the next time.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 21, 2010 at 7:15 am
Do you want to update the config file prior to running the package, or do you want to compute the filename in the package and then save it back to the config file at runtime?
Just curious... if you can dynamically compose the file name within the package, why do you need to store the value in a config file?
June 21, 2010 at 9:17 am
Hi,
I've done pretty much what you need by using variables that are set in the config file. So I have server name in the config file, which maps to a variable, when I use this variable within the expression.
Expression (variable @[Config::fileLogs] is the one I set in the config file)
@[Config::fileLogs] + @[System::PackageName] +
(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) +
RIGHT("0" + (DT_STR,4,1252)DATEPART( "hh" , @[System::StartTime] ), 2) +
RIGHT("0" + (DT_STR,4,1252)DATEPART( "mi" , @[System::StartTime] ), 2) +
RIGHT("0" + (DT_STR,4,1252)DATEPART( "ss" , @[System::StartTime] ), 2) +
".csv"
Variable set in config file:
.config data...<Configuration ConfiguredType="Property" Path="\Package.Variables[Config::fileLogs].Properties[Value]" ValueType="String"><ConfiguredValue>D:\Microsoft SQL Server\MSSQL\PackageLogs\....rest of config data
Hope this makes sense 😉
Jinx.
June 21, 2010 at 4:02 pm
The reason I need to change the file name in the config file, is that the company I am sending it to will have a different file location, as well as different servers, usernames/passwords etc, than mine. I only want them to change the config file. They shouldn't need to go into the package at all (I am not sure they even know how to use SSIS). They then need to run the package and send the output files to me. The timestamp portion of the file name is the problem, not the file name itself.
Is there any other way I can add a timestamp to a file name within the config file? I have tried putting quotes around it, doesn't work 🙁
June 21, 2010 at 4:06 pm
Thanks Jinx....I'll try that 🙂
June 23, 2010 at 8:02 am
I think the package configuration data can only be saved at design-time. I do not think there is an SSIS runtime method that will update the config file based upon the new value of a variable.
Include only the static part of the file path in your package config and append the timestamp during runtime using an expression, just as you have already done. For example, create MyServer, MyFilePath, MyFileName, MyFileExt variables and save these values in your config. Then concatenate these values plus your timestamp in your connection string expression . Your client would update these variable values as necessary in the xml config file.
June 23, 2010 at 11:47 am
You are correct, the package will not update the file using any out-of-the-box methods.
You could write a Script Task to do it, but I am unsure if the file would be locked for writes by the package while it is running..
However, I completely disagree with repeatedly changing the file, it is too complex to work well. You are better served by using the file to point at a server that contains a config table and reads the values you want, they can EASILY be changed at runtime without all the hassles we are talking about..
CEWII
June 23, 2010 at 12:09 pm
All you can do to make it work is declare different variables for servername, path etc and then construct the dynamic file name by appending the variable names.
In the package configuration DO NOT give the connectionstring as config value, instead give the variables as configurable value.
It will solve ur problem.
I recommend http://www.expertmsbitraining.com
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply