Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 687
  • Last Modified:

SSIS ForEachLoopContainer

I'm killing myself trying to simply load multiple flat files from 6 months of nightly feeds. Basically trying to use a ForEachloop container to loop thru the flat files and load them into a SQL Server table in SSIS 2008..

Using the example shown here

http://www.bidn.com/blogs/BrianKnight/ssis/43/ssis-for-each-loop-configuring

but I only load the first file...When I try to set the flat file connection mngr to just a folder it fails and when I set it to a file, I'm not getting the looping effect...

Please, any suggestion would be welcome,,,and yes, I am slightly new to this...

Thanks, Gray
0
Gray5452
Asked:
Gray5452
  • 6
  • 3
1 Solution
 
Gray5452Author Commented:
Why do all the sites say to set the connection string to a folder rather than a file but that produces this error:

[Flat File Source [105]] Error: Cannot open the datafile "H:\Gray\SSIS\NightlyData\".
0
 
Barry CunneyCommented:
Hi Gray,
Forgetting about SSIS for a moment, what is the logic of what you need to be done.
Are all the files in a single folder and for every file in that single folder you wish to import each file into a single table?
0
 
Gray5452Author Commented:
I need(very badly) to load roughly 180 flat files(one per night from a client for the last six months) into a table otherwise known as Calls...The Files are simply .txt and they are all sitting in a folder and have a fixed format roughly like TELEPHONENUM(10)CLIENTID(4) or 55586753093456 so I'll just use derived columns and create those two rows..Hope this makes sense as I am somewhat at wit's end bc while there are several exaples online I can't seem make it loop through all the files when I give it a filename and get an error when I just give it the folder name...
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Barry CunneyCommented:
Hi Gray,
We will start off simple and build it up.
Download the attached file - rename the extension to .dtsx(I had to change the extension to allow it to be uploaded)
Try and open this FileLoop.dtsx file in BIDS

Change the value of the RootFolder variable to be the folder in which your text files reside.
Execute the package
This is a simple package which displays a message box for every .txt file in the specified RootFolder
This will prove that we can loop through your required files
 
FileLoop.txt
0
 
Barry CunneyCommented:
If you have problems opening the attached file and seeing the design of it I will send you the whole project
0
 
Barry CunneyCommented:
Hi Gray,
This latest attached file contains additions to do the import as well - so it loops through all text files I have in a single folder and imports each file into a single table in SQL server
The for each loop container updates the vraiable FileName with the name of the current file and in the Expressions in Properties for the Flat File Connection Manager I have specified that the ConnectionString property as @[User::FileName] - i.e. the Flat file Connection manager is dynamically set to the current file in the For Each Loop iteration - This is one of the key points in the whole thing - I will send a subsequent print screen of this
FileLoop.txt
0
 
Barry CunneyCommented:
0
 
Barry CunneyCommented:
This next print screen depicts how the FileName variable is specified in the ForEachLoop container object - The For Each loop then dynamically updates this variable with the name of each file as it iterates through them. - and then therfore the flat file connection manager gets dynamically updated to connect to each file in the iteration
SSISForEachContainerUpdatesFileN.bmp
0
 
Gray5452Author Commented:
VOILA,,,it finally worked...THANK YOU VERY MUCH,,,it was the expression for the connectionstring on the props page I was botchiing...

Thanks Again, Gray
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now