SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 SSIS and Import/Export (2012)
 Foreach Loop MultiTab Import by Month
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

brubakerbr
Yak Posting Veteran

USA
53 Posts

Posted - 11/08/2013 :  11:20:22  Show Profile  Reply with Quote


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

India
52317 Posts

Posted - 11/08/2013 :  12:29:45  Show Profile  Reply with Quote
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
Go to Top of Page

brubakerbr
Yak Posting Veteran

USA
53 Posts

Posted - 11/08/2013 :  13:18:19  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/09/2013 :  10:41:06  Show Profile  Reply with Quote
No problem . Let me know if you need further help.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000