Solved

SSIS ForEachLoopContainer

Posted on 2011-03-15
9
682 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

Technology Partners: 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!

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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.
Viewers will learn how the fundamental information of how to create a table.

763 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