Solved

SSIS ForEachLoopContainer

Posted on 2011-03-15
9
678 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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

747 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

11 Experts available now in Live!

Get 1:1 Help Now