how to loop through the table, load data to excel and send email in for each loop container.
Hi, i have a table1 like : Source SourceID ExcelFileName ExcelFilePath AAA 1 AAA.xls C:\Source\AAA.xls BBB 2 BBB.xls C:\Source\BBB.xls CCC 3 CCC.xls C:\Source\CCC.xls Rightnow its only 3 sources but there will be more sources added in future. I have a variable ResultSet which i am using to loop through the Sources from the table above. I have execute sql task, result set --> Full result Set, sql statement --> select source from table1 I am using for each loop container using the variable ResultSet. I have a data flow task in For each loop container having 3 oledb sources and 3 excel destinations. In oledb i am using a script to generate the data for each source and load it into excel. The script looks like : SELECT * FROM dbo.TableB WHERE PROCESS_RESULT_ID = 0 AND PROCESS_DATE BETWEEN DATEADD(DAY,-1, GETDATE()) AND DATEADD(DAY,1,GETDATE()) AND Source = 'AAA' The script for all three sources is same except the Source = 'BBB' AND Source = 'CCC'. I want to use single oledb source and single excel destination and send success or failure email for each source. How do i do this in SSIS or any link similar to my question will be helpful? Thanks
September 30th, 2012 3:12pm

You can use a Foreach ADO enumerator to loop through your table. That loop fills a variable that can be used as parameter in your OLE DB Source. Here is an example of that: http://bisherryli.wordpress.com/2011/03/06/ssis-pass-a-variable-to-a-ole-db-source-in-a-data-flow/Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter
Free Windows Admin Tool Kit Click here and download it now
September 30th, 2012 3:37pm

@SSSIJoost, i have created the package and It does loop through but it creates the single spreadsheet having data for all 3 sources. SELECT * FROM dbo.TableB WHERE PROCESS_RESULT_ID = 0 AND PROCESS_DATE BETWEEN DATEADD(DAY,-1, GETDATE()) AND DATEADD(DAY,1,GETDATE()) AND Source = ? -----I used this script in OLEDB source and mapped the variable ResultSet in parameters window. I want to create 3 seperate spreadsheets for 3 sources. Also how do i send 3 seperate emails for 3 sources based on success or falure?
September 30th, 2012 4:41pm

You should also do something with the excel connection manager. Add an expression on the excelfilepath property. Here is an example of that: http://bidn.com/blogs/kylewalker/ssis/997/setting-up-an-ssis-package-with-a-dymanic-excel-source Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter
Free Windows Admin Tool Kit Click here and download it now
September 30th, 2012 4:55pm

Thanks SSISJoost. i tried the link in your answer. If i create the variable say exceldest having value say C:\Destination\ it still creates one spreadsheet and loads data for all 3 sources. also if i change the value of variable exceldest it throws error in excel destination. is there any way it could create 3 sheets dynamically for each source?
September 30th, 2012 11:27pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics