Dynamically name Excel Files via ssis
-
I am currently trying to create a ssis package which will allow me to to export data to excel and dynamically name those files. I am using a for each container to do this.
I have a sql task outside of the for each container which creates a a column of data which are stored in an object variable that will be used for the iteration to dynamically name the files.
Within the ForEach container I have a variable that is mapped to the object variable outside the for each container. With this how do I then use that variable to create the excel files. I followed some of the articles on here which describe how to do it but I do not seem to be creating the excel spread sheets.
The data in the object variable is as follows:
RAL
RYJ
RV8
RT3
RY1
With this data I would like to name each excel spreadsheet as follows
ContractTerms_RAL.xls
ContractTerms_RYJ.xls
ContractTerms_RV8.xls
Do i have to create the excel spreadsheet first in the destination folder? If so what do I name it as?
Any help would be much appreciated.
Regards
Sauce1979
-
Create a template .XLS file. In your loop, copy the template to a another file with the name you require for the current iteration.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
-
I am a bit of a beginner when it comes to ssis so please bear with me.
Firstly how do I create an excel template? Is this done with an execute sql task outside of the for each container?
How do I copy the template to another file with the name used in the iteration? Is that with the use of a variable?
The data which is in the object variable refers to provider_codes which are found in numerous tables in my database. For each dynamic created Excel workbook I want to add worksheets which correspond to various tables with only records that have the provider_code that the excel spreadsheet is named after.
For example: in my database I have 3 tables:
birth ratios, c2c_follow ups and u_codes
Each of these tables contains records which have provider_codes such as RV8,RYJ,RT3
For the Excel spreadsheet ContractTerms_RV8.xls I want to have 3 worksheets called birth ratios, c2c_follow ups and u_codes with data form those 3 tables where the provider_code is RV8. Then likewise for the other provider Codes.
How would I set about doing this? Is all of this done within the For Each container?
-
oh, you have actually expanded your original question considerably. You should get ready to limit your expectations regarding what can be accomplished with SSIS. This sounds now more like a task for SSRS.
Now up to your questions:
1. The Excel template file is created by you manually in Excel. It will contain a specific set of worksheets with the appropriate names. The idea is to have the Excel file ready up front because SSIS Excel Destination component doesn't support dynamic creation. The Excel destination can populate only existing Excel file.
2. You can use File System Task to copy the template file. You have to parametrize it by using variables in combination with expressions. These expressions will depend on the current iteration in the loop.
---
For the rest of your explanation you have to elaborate more. I didn't quite understand your description. In general as I said you have to limit your expectations. Automating Excel and Office applications in general is very hard issues to solve. The Office was never designed to be used as a server application and it is unstable.
If you are beginner SSIS developer, I would definitely recommend you get a good book. I would recommend this.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/ -
Thank you for the book. i have already ordered it.
I think I may have made my problem more complicated than it was. I did the same task in DTS but I have forgot the logic I used to do it.
Basically I want to populate each workbook with 3 worksheets.
For example.
For Workbook ContrctTerms_RV8.xls There will be 3 worksheets called birth_ratios, u_codes and c2c_followup.
Now the data that goes into each workbook would be as follows:
-- data for birth_ratios worksheet
select * from bithratios
where provider_code = 'RV8'
-- data for u_codes worksheet
select * from ucodes
where provider_code = 'RV8'
-- data for c2c_followup worksheet
select * from ucodes
where provider_code = 'RV8'
The same is then repeated for the other books where the provider code will be the same as the provider code in the excel file name.
I am not sure if I have made this any clearer.
-
Do you expect to have worksheets other than birth_ratios, u_codes and c2c_followup ?
If not , then this is straightforward:
1. First create a workbook in Excel, which contains the 3 mentioned worksheets. This is your template, which you will populate with data.
2. Create the SSIS package.
3. If the list of codes you iterate over is coming from database, insert Execute SQL Task. Set ResultSet = "Full result set". Select variable where to store it.
4. If the list of codes you iterate over is coming from another source, create a collection with custom script code and store in variable.
5. Insert as a next step a Foreach loop container.
6. If the previous step was:
* Execute SQL Task retrieving data from ADO - select enumerator to "Foreach ADO Enumerator".
* Execute SQL Task retrieving data from ADO.NET - select enumerator to "Foreach ADO.NET Schema Rowset Enumerator".
* Custom script code - select enumerator to "Foreach from Variable Enumerator".
7. Setup your foreach loop container, setting up a variable for current iteration item. Lets say the variable is called CurrentCode. This variable will contain your current iterated code.
8. Insert a new variable and lets say it is called TemplateFile variable. Open the Properties pane and set EvaluateAsExpression = TRUE. Go to Expressions and for expression use:
"ContrctTerms_" + @[User::CurrentCode] + ".xls"
This is the variable, which will contain your Excel workbook name.
9. Insert Excel connection manager. After this go to the Properties pane and go to Expressions. Set expression for ExcelFilePath property to: @[User::Template]. Now you have dynamic Excel connection manager, which depends on the current foreach loop iteration.
10. In the foreach loop container insert File System Task. Setup operation to "Copy file". Set the source to your template Excel file. Set IsDestinationPathVariable = TRUE and select User::TemplateFile variable for DestinationVariable. The variable was defined in step 8.
11. In the foreach loop container insert Data Flow Task. Name it birth ratios.
12. In the data flow insert "OLEDB Source". Set data access mode to "SQL Command". Set the command to "select * from bithratios where provider_code = ?" . Click Parameters and then set Parameter0 to "User::CurrentCode".
13. In the data flow insert "Excel Destination". Connect the OLEDB Source to the Excel Destination. For Excel workbook, use the connection manager setup in step 9. Set data access mode to "Table or view". Select "birth_ratios" worksheet.
14. Now you have to repeat step 11, 12, 13 for the other worksheets you want to populate, using the appropriate SQL select statement and destination Excel worksheet.
---
There you go . I will leave the details for you to find.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/ -
Thank you very much. That worked really well. With this knowledge I can expand my package.
-
eseosaoregie (6/20/2009)
Thank you very much. That worked really well. With this knowledge I can expand my package.Wow, really????? :w00t: You should start spamming all men in the world.:hehe:
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply