Solved

SSIS ForEachLoopContainer

Posted on 2011-03-15
9
679 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
  • 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
 
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

911 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now