4

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

Improve this question
2
  • So you have a dynamic file name but you don't want to use the common idiom of a for each file enumerator. Can we assume that the most recently modified file is the one you're looking for? Commented May 3, 2016 at 16:32
  • 1
    You say you don't want to use a ForEach - but you do not explain why? ForEach is the best solution to this requirement. Commented May 5, 2016 at 15:17

2 Answers 2

Reset to default
7

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

haha! sorry - this was meant to be against the original question! Deleted now.
Thanks user,, I'll be using a variation of your solution. - Matt
Joe C -- The c# solution is helpful as well, but the other solution is closer to what I'm looking for. I'd still like to give your answer points -- how do I do that? - Matt
Click on the gray up arrow to the left of the answer. Thanks.
2

I would use the loop for even one file, there is no harm. Another option would be to use a script task and the directoryinfo and fileinfo classes. Pass in the directory and if you wish the static part of the file name to use as a file mask when calling directoryinfo.

Improve this answer

5 Comments

1. So there is not way other than using a loop or through a script task? 2. I'm a noob with ssis/.net, could you provide an eg? thanks
@DamonMatt Is there a business rule you aren't mentioning that would help someone (SSIS) decide which file to process? If your response is current date, what happens if the process fails on a weekend/holiday and no one corrects it until the next day - then what do you do? Change server time to make it yesterday?
@billinkc All valid questions Bill, I don't have all the answers at this point. The assumptions at this point though are: 1. there will be one file every day with the following naming pattern - bookNames_Shopping_05_02_2016.txt with the date stamp dynamic 2. As soon as my package completes, it moves the file into a separate folder 3. If package fails, this isn't mission critical, and someone will notice and bring to my attention to manually re-run after fixing issue So essentially at this point I need to know how to detect the file with the pattern dynamically, and store it in a variable - thx
Here is a good example using loop task - simple-talk.com/sql/ssis/…
This a good article using c# - c-sharpcorner.com/uploadfile/99aadd/…

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.