Foreach Loop MultiTab Import by Month

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

SSIS and Import/Export (2012)

Foreach Loop MultiTab Import by Month

Author

Topic

brubakerbr
Yak Posting Veteran

53 Posts

Posted - 2013-11-08 : 11:20:22

I have an Excel file that I get from a vendor. The file has multiple worksheets in it all labeled by Month (Jan, Feb, Mar, etc). Each month a new worksheet is added for the previous months Data (I receive the data in March but the Excel file will have a worksheet for Jan and Feb).

I have used the Foreach Loop container to grab files, rename them and drop them into other folders. But what I want to do is set up a dynamic Foreach Loop that will read the data on the newly added worksheet in the file. So far all I can find is how to run through all of the worksheets at one time (incrementally), but I don't want to grab any of the previous data, only the newly added sheet.

Any ideas on how this can be accomplished.

(note: I am pretty good at working in SSIS but complex Expressions and Script Tasks still through me from time to time.)

Brian D. Brubaker
Business Intelligence Analyst
Viega LLC

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-08 : 12:29:45

you just need a table which will have details of months for which data has been grabbed so far. So it will have fields filename,monthname,datextracted. Each time you get a file you check this table if records is already present for filename and monthname. If not present, then do the processing.
You can implement this functionality by using a execute sql task which will have query like


SELECT COUNT(*) AS Cnt
FROM Table
WHERE Filename = ?
AND Monthname = ?

and filename and sheetname (month info) can be passed from your ForEachLoop using SSIS variables to above query as parameters. Store the count result onto another SSIS variable (@[User::Cnt])

Then add expression and constraint option for precedence constraint which will link from this task to next task and use Expression as

@Cnt == 0
this will make sure it will only process the sheets which has not yet been processed
You need also have a task which populate info on sheets processed to Table you maintain at end of successful processing inside the package

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

brubakerbr
Yak Posting Veteran

53 Posts

Posted - 2013-11-08 : 13:18:19

WOW, that's a lot to absorb.

I will try to implement that. Some of this is still new to me so I may be back with more questions at some point.

Thank you for the guidance.

Brian D. Brubaker
Business Intelligence Analyst
Viega LLC

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-09 : 10:41:06

No problem . Let me know if you need further help.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources