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