This Blog explains how to dynamically generate Connection string!
I had a requirement to push the data to a file in specific folder but the file name should contain Date and Time. To address the requirement, I had to generate the connection staring dynamically based on package execution time. In this example, I am creating Connection string of a Flat file dynamically!
3. Select Connection String Property and edit the expression. Following is the sample expression which creates Connection string in this format C:\vijred_YYMMDD-HHMMSS.txt
a. Check below code for sample string!
"C:\\vijred_"+RIGHT((DT_STR,4,1252)YEAR(GETDATE()),2) +RIGHT("0" + (DT_STR,2,1252)MONTH(GETDATE()),2) +RIGHT("0" + (DT_STR,2,1252)DAY(GETDATE()),2)+"-"+RIGHT("0" + (DT_WSTR,2)DATEPART("hh", GETDATE()), 2) + RIGHT("0" + (DT_WSTR,2)DATEPART("mi", GETDATE()), 2) + RIGHT("0" + (DT_WSTR,2)DATEPART("ss", GETDATE()), 2)+(DT_WSTR,4)".txt" -- NOTE: If edit the connection string, make sure " character is correct and replace \ with \\ when you replace folder path. --
Note: Variables can be used while generating the connection string and variable values can be updated by preceding tasks during Package execution. Using this approach, we can also pull some values from database to build connection string.