Solved

SSIS ForEachLoopContainer

Posted on 2011-03-15
9
683 Views
Last Modified: 2012-05-11
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
Comment
Question by:Gray5452
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
9 Comments
 

Author Comment

by:Gray5452
ID: 35138740
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
 
LVL 17

Expert Comment

by:Barry Cunney
ID: 35139242
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
 

Author Comment

by:Gray5452
ID: 35139396
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
Independent Software Vendors: 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!

 
LVL 17

Expert Comment

by:Barry Cunney
ID: 35139853
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
 
LVL 17

Expert Comment

by:Barry Cunney
ID: 35139887
If you have problems opening the attached file and seeing the design of it I will send you the whole project
0
 
LVL 17

Expert Comment

by:Barry Cunney
ID: 35140170
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
 
LVL 17

Expert Comment

by:Barry Cunney
ID: 35140200
0
 
LVL 17

Accepted Solution

by:
Barry Cunney earned 500 total points
ID: 35140237
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
 

Author Closing Comment

by:Gray5452
ID: 35140314
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question