Dynamic File Connections Not Working

Question

Hi all-

Business scenario:

1) A monthly .txt file is dropped into a folder on the 12th of each month, with a naming convention like "Customer_04122012.txt"

2) File needs to be parsed with results exported to Excel with a naming convention that contains the date of the source file i.e. "Customer_Codes_04122012.xls"

Some package details:

The file connections on both sides need to be dynamic.  To accomplish this I created:

1) Variables FileName, FileDate

2) For Each Loop container that loops over the folder with the .txt file and *supposedly* maps this to variable FileName

3) Flat file connection with ConnectionString property set to expression "ImportFolder\" + @[User::FileName]

4) Excel connection manager with ExcelFilePath property set to "ExportFolder\" + @[User::FileDate]. 

Problems:

Nothing is working dynamically!  The package runs fine with initial setup, but if I change the .txt file name everything breaks.

1) The For Each Loop container does not map the file name to variable FileName.  If I change the file name, set a break point after the container run the package and check FileName value, it has the old value.

2) The Excel Connection Manager breaks.  I need this pacakge to create a new Excel workbook every time it is run.  In production the output file will be moved to a different location after being generated.  It seems I cannot base the Excel connection on an expression without having a static workbook in place, which by default negates the dynacism of the process.

Any advice is much appreciated!!

---

Bonediggler

Answer 1

What is the scope of the Filename variable?

---

Chuck

Answer 2

Package.

---

Bonediggler

Answer 3

I am clearly missing something.  It might be easier to start over.  How would you build a package to accomodate these requirements:

1) Dynamic flat file source

2) Dynamic Excel file output

?

---

Bonediggler

Answer 4

I am clearly missing something.  It might be easier to start over.  How would you build a package to accomodate these requirements:

1) Dynamic flat file source

2) Dynamic Excel file output

?

---

Bonediggler

Pretty much the same way you are doing it - with one change.

1) For each file loop that looks through the directory and populates Filename variable - which gets used in connection expression for flatfile

2) Expression for excel target would strip the filename without extension out of the filename variable (you are in trouble using date if it processes more than one file)

Pretty standard setup

---

Chuck

Answer 5

Why are you using a foreach loop if there is only one file and you already know the that the filename is "Customer_04122012.txt"? An expression on the flatfile connection manager could do the trick.

If you're sure there is only one file and you don't want to create an expression with year, month and day then you can use the Foreach loop, here is how you implement it: http://microsoft-ssis.blogspot.com/2011/02/how-to-configure-foreach-loop-file.html

---

Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

Answer 6

Why are you using a foreach loop if there is only one file and you already know the that the filename is "Customer_04122012.txt"? An expression on the flatfile connection manager could do the trick.

If you're sure there is only one file and you don't want to create an expression with year, month and day then you can use the Foreach loop, here is how you implement it: http://microsoft-ssis.blogspot.com/2011/02/how-to-configure-foreach-loop-file.html

---

Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

He said that the name of the file changes and unless you assume that you will always be able to process the file on the date it created - there would be no way to actually know the file name up front.  Additionaly if the process stopped running for a day or 2 you would need to be able to import all of the generated files.

---

Chuck

Answer 7

Why are you using a foreach loop if there is only one file and you already know the that the filename is "Customer_04122012.txt"? An expression on the flatfile connection manager could do the trick.

If you're sure there is only one file and you don't want to create an expression with year, month and day then you can use the Foreach loop, here is how you implement it: http://microsoft-ssis.blogspot.com/2011/02/how-to-configure-foreach-loop-file.html

---

Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

He said that the name of the file changes and unless you assume that you will always be able to process the file on the date it created - there would be no way to actually know the file name up front.  Additionaly if the process stopped running for a day or 2 you would need to be able to import all of the generated files.

---

Chuck

 monthly .txt file is dropped into a folder on the 12th of each month, with a naming convention like "Customer_04122012.txt"

"Customer_04"+ current year + current month + 12 ?

But if the name can change then the foreach loop is your friend.

---

Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

Answer 8

Why are you using a foreach loop if there is only one file and you already know the that the filename is "Customer_04122012.txt"? An expression on the flatfile connection manager could do the trick.

If you're sure there is only one file and you don't want to create an expression with year, month and day then you can use the Foreach loop, here is how you implement it: http://microsoft-ssis.blogspot.com/2011/02/how-to-configure-foreach-loop-file.html

---

Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

He said that the name of the file changes and unless you assume that you will always be able to process the file on the date it created - there would be no way to actually know the file name up front.  Additionaly if the process stopped running for a day or 2 you would need to be able to import all of the generated files.

---

Chuck

 monthly .txt file is dropped into a folder on the 12th of each month, with a naming convention like "Customer_04122012.txt"

"Customer_04"+ current year + current month + 12 ?

But if the name can change then the foreach loop is your friend.

---

Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

I always assume that the rule actually means - its dropped on the 12th - unless it is a weekend, holiday or the process just decided not to do it this month ;)

---

Chuck

Answer 9

Fist thing is you have to lear on how to loop the text files, check this link and read upto STEP 8

after that you willhave to add a SQL task to MAKE the EXCEL file, because on each run or lets say for each file you need to make an excel file, my suggestion is insted of making an excel file use a excel template, and move the excel template form a SAMPLE folder to the DESTINATION folder and rename it to the format you want

now check the steps that i have set for the STEP 9 but insted of an OLE you must use a EXCEL DESTINATION CONNECTOIN

another suggestion is, move the SAMPLE excel file to a folder that is know to your DFT as destination excel file, go with a fix name, once the file gets populated with data then renam it, i feel that might be easier for you to set.

good luck

---

Sincerely SH -- MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

Answer 10

Chuck -

Sorry for the late reply.  You are suggesting one change, however my package already does both things suggested.

I solved the For Each Loop problem (just needed a wildcard before .txt) so that's ok.  However I am still struggling with the Excel destination.  I have implemented Nik's solution in other packages but feel there are too many steps involved and would like to avoid this approach if possible.

I have the expressions etc set up for the Excel destination but everything breaks if I change the name of the source file (e.g. from Customer_04122012.txt to Customer_03122012.txt).  As this file name will be changing from month to month in production I obviously need a workaround...

Thank you~

---

Bonediggler

Answer 11

What expression are you using for the excel destiantion?

---

Chuck

Answer 12

Assuming that variable User::testfilename= Customer_04122012.txt

All you really need to do is this:

REPLACE(UPPER(@[User::testfilename]),".TXT",".XLS")

---

Chuck

Answer 13

Let me just describe the whole excel connection manager:

1) Points to "Customer_04122012.xls" (a dummy file because it initially has to point somewhere.)

2) "Name of the Excel Sheet" was originally built via New-->Create Table etc

3) ExcelFilePath is expressed as "File Path\Customer_" + substring( @[User::FileDate],1,2) + substring( @[User::FileDate],4,2) + substring( @[User::FileDate],7,4)  + ".xls" (this won't match exactly because the actual file specs are different).  This works in the expression builder and gives the desired result, i.e. "Customer_04122012.xls"

4) Upon changing the source file name, the "Name of the Excel Sheet" gets lost and the whole thing is rendered useless

---

Bonediggler

Answer 14

Forget about using that User::FileDate variable.    Just use the variable you are using for your source file and do a replace on the .txt -

 "File Path\" + REPLACE(UPPER(@[User::FileName]),".TXT",".XLS")

---

Chuck