August 19, 2012 at 10:27 pm
hi friends i have small doubt in ssis plz tell me how to solve this task.
suppose i have a table that table contains 50 records .that records like
deptno 10 contains 10 records and deptno 20 contains 11 records and deptno 30 contains 9 records and deptno 40 contain 5 records and deptno 50 contains 15 recods so totaly a table contain 50 reords.
Based on this table we want load this reords in one execel sheet in execel sheets we want load records seperately each sheet like sheet1 contains....>deptno10 related records and shhet2 ....> deptno 20 records and sheet3 .....> deptno30 and sheet4 ....> deptno40 records and sheet5 ....>deptno 50 related records.
and iam trying this task like in control flow level i taken task foreach loop container and dft task and dft level i taken oledb source and condition split and finaly i load execel shhet but it not load each shheet.
plz tell me how to solve this one
August 20, 2012 at 9:11 am
Do you have separate Excel Destinations for each sheet or are you using variables and an expression for the separate sheets?
August 20, 2012 at 9:43 am
I usually generate multiple tab spreadsheets in a sequence container to manage the spreadsheet in one section of the package. It does not take a for/each task.
These are the steps:
1. Create a connection manager to your Excel spreadsheet.
2. Prepare the spreadsheet and first tab using an 'Execute SQL' task with an EXCEL connection. It is written as a CREATE TABLE statement. The name of the table is the tab (worksheet) name. The table name and the column names should be bracketed using the ` symbol. If this isn't clear, the easiest thing is to generate a test package using the export wizard, export to excel, save the package and then examine how the package generator set up the spreadsheet in an SSIS package.
3. Execute the sql task to generate the spreadsheet (necessary during development)
4. Use a data flow to populate the tab using whatever query you'd like to generate the data. The destination is your excel spreadsheet, to the tab (sheet) that you defined in #2. You will likely need a 'Date Conversion Transformation' task between the source and the destination to generate the proper datatype for Excel to accept. I've had the most success mapping datetime to [DT_DATE], char or varchar to [DT_WSTR] and numbers to [DT_R8] but there may be better options. Again, your test package can be helpful.
5. Repeat steps 2-4 for each tab you want to generate,. I usually set the control flow to have each tab built and populated one after another.
If you'd rather do this in two steps, #2 should contain the SQL for generating each tab separated by GO. Step #4 should have source, transform and destination for each tab. If you want to take a look at this approach, generate a test package using the export wizard and have two or more outputs.
There are probably better ways to accomplish this but the methods described here have worked for me. 😀
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply