sql server data tools dynamic file name for flat file source

Question

I've got a ssis package that does a simple enough data import from a text file into a database table. The import file name needs to be configured as a dynamic source since the file name will change. So my file name will have following pattern: bookNames_Shopping_05_02_2016.txt --> bookNames_Shopping_ will be constant but the date stamp will change.

I set up a variable called filename, and in the connection manager's properties window, I set the ConnectionString property to @[User::filename]. How do I set the filename variable to read the file name that is in the folder that the connection manager is pointed to? P.S. I know it can be done inside a For Each loop but since I don't have multiple files but a single file that needs to be processed, I **don'**t want to use the for each loop.

Full path for the file: \XYZYUC3312B6\SHARE\Bound\bookNames_Shopping_05_02_2016.txt

Answer 1

An alternative to applying a loop is using an expression. Assuming the single text file being read contains in its name the same date in which the SSIS package will process it, you can do the following:

1. Create a User variable called "FileName" of type String.
2. Create a new Connection Manager called "bookNames_Shopping" pointing to the current full path "\XYZYUC3312B6\SHARE\Bound\bookNames_Shopping_05_02_2016.txt".
3. Select the new Connection Manager just created and open Solution Explorer for it.
4. Select Expressions. When the Property Expressions Editor opens, go to the Property column and select Connection String from the drop-down. In the next column under Expression on the same line, select the browse button. This will open the Expression Builder window.
5. In the text box just below "Expression:", enter the following:

`"\XYZYUC3312B6\SHARE\Bound\" + @[User::FileName] + "_" +
RIGHT("0" + (DT_STR, 2, 1252) DATEPART("mm" , GETDATE()), 2) + "_" + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("dd" , GETDATE()), 2) + "_" + (DT_STR, 4, 1252) DATEPART("yy" , GETDATE()) + ".txt"

Select the Evaluate Expression button in the lower left. If this works, it should display your full file path including a concatenation of the current date.

\XYZYUC3312B6\SHARE\Bound\bookNames_Shopping_05_04_2016.txt

Select OK button and you're all set. I ran this in BIDS under SQL Server 2008R2. If you're using SQL Server 2012, then in Visual Studio 2010, you can set up the same expression directly for the FileName variable instead.

From this point, just apply the bookNames_Shopping connection string in our dataflow.