There was a requirement in one of my recent projects to export data from table in SQLServer database to an Excel file. Though it sounds a straightforward one, there was a tricky part in it. The data has to go to separate sheets of the Excel file based on a column value. This blog explains the method I used for achieving the requirement.
The scenario can be illustrated by the below example.
Consider the case of Marklist table containing details of marks obtained by various students for different subjects. The table looks like below (for simplicity I’m including only a small amount of rows)
The requirement was to export this data to excel with each subject details going to separate sheet.
The package looks like below
The tasks used are
1. File System Task to copy Excel template from a shared location to actual directory. This is required for setting up of file metadata prior to sheet creation. The data will be copied into separate sheets within this file.
2. SQL Task to generate sheet list . This task will execute SQL script to get distinct list of subjects from table. This will then be stored in a object variable for use within the loop to create the sheets.
3. For Each Loop which loops through the subject list from the object variable
4. SQL task which will connect to excel file and create sheets based on dynamic CREATE TABLE statement as below by storing query in a variable (ExcelSQL)
The CREATE TABLE expression will make use of variable to pass Subject name from loop to create the sheet at runtime.
5. Data Flow Task to populate the created sheet with data for that subject. The Data Flow looks like below
The query will have Subject value passed as a parameter using the variable created inside the loop.
The destination will use dynamic sheet name by mapping to the same subject variable
On executing the package we will get excel generated as below
As you see the Excel will have separate sheets based on Subject names with each sheet having data for the subject.
6. The final file system task renames the template to set current date as the filename
This approach can be applied to similar scenarios to get data exported to multiple sheets. Feel free to revert in case you need more clarification on this.
Published by dataplatformcentral
Hi, I have some doubts regarding the second execute sql task. Can you please send screenshots of the properties used in that.
I've already shown it under point 4 above. Its the step which creates the sheet using CREATE TABLE statement based on Excel connection manager
Did it work for you, this whole package? since, mine is failing again and again, can you please share the .dtsx file of this package?
Yes
I posted the article based on my working package.
Not sure I've it handy with me now.
Let me search through the archives and I'll share it once found