Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need to process different file types in SSIS Expand / Collapse
Author
Message
Posted Wednesday, September 26, 2012 7:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, November 23, 2012 7:03 AM
Points: 15, Visits: 41
I have what I think will be a simple question. I need to have SSIS loop through a folder for only xls or txt files. I then need to base the processing on what type of file it is. I am trying to think of an approach. Should I move the files to separate folder based on their type and then process? Or should I have two different foreach loops to process the file types? Any help is appreciated!
Post #1364647
Posted Wednesday, September 26, 2012 7:18 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:54 AM
Points: 5,149, Visits: 4,961
Swings and roundabouts on that one, two folders one for xls the other for txt or two loops one for xls the other for txt will provide exactly the same methodoligy.

But then you have to look at if there is a process between them, say for example the xls is header information and the txt is the data and they need to be imported xls, txt, txt, txt, txt etc if so then two loops wont cut it as they will always insert in parallel or serial depending how you setup the precendence constraints etc.





Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1364654
Posted Wednesday, September 26, 2012 7:19 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, September 30, 2013 2:09 AM
Points: 3,131, Visits: 1,058
You better maintain two for each loop so the process will be faster


Post #1364655
Posted Wednesday, September 26, 2012 7:34 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, November 23, 2012 7:03 AM
Points: 15, Visits: 41
The file actually contains the same information. The excel file is manual entries. So the information will go into the same table.
Post #1364670
Posted Wednesday, September 26, 2012 7:35 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, November 23, 2012 7:03 AM
Points: 15, Visits: 41
What makes the 2 foreach loop faster in processing that the other way?
Post #1364672
Posted Tuesday, October 2, 2012 3:29 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, July 7, 2014 9:38 AM
Points: 1,780, Visits: 582
you shoud use two for each loop to get each type of file from the same folder.
it makes the system fast, since you do not have to define the logic to get the files from the folder in single for each loop.
Post #1367309
Posted Friday, October 5, 2012 6:20 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 9:47 AM
Points: 1,546, Visits: 1,334
You can keep them in the same folder. Within your foreach loop, however, inspect the file extension and have two branches off of the inspection object and do the separate processing in those branches.

Hope that this helps. Thanks.

- Chris
Post #1368977
Posted Saturday, October 6, 2012 11:43 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, November 23, 2012 7:03 AM
Points: 15, Visits: 41
Thanks Chris and everyone for your responses. I have implemented a for each loop with a dummy script task to be able to set a constraint to check for file type and branch processing based on that. Thanks for all your help.
Post #1369452
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse