SSIS – Connection Manager – Dynamic Connection string – Connection String with date and time

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!

1. Create a new Connection (Create a new Flat File Connection with sample file [FlatFile1])
2. Go to Properties of Flat File and Edit Expressions
SSIS-ConnectionManager-Properties-Expressions

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.
-- 

SSIS-ConnectionManagerProperty-DelayValidation

4. Select Delay Validation to True to make sure that the connection string will be built during the task execution
SSIS-ExpressionEditor

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.

-Vijred

Advertisements
This entry was posted in Productivity, SQL, SSIS and tagged , , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s