In this post, you will learn
- How to create and map Excel destination dynamically in SSIS
- How burst the out put into multiple files using For loop container
- how to dynamically create sheet names
In this post we will use a OLEDB source and a Excel destination , for the OLEDB source we will use the Adventure works database [Production].[ProductListPriceHistory] table
This table has a history of price changes for each product, so our goal is to create a history Excel file for each product id
Create a variable type object to hold the distinct productID’s
Drag and drop a Data flow task add a oledb source with SQL query as data access mode and modify the query to get the distinct product ids and
Use a record set destination to load all the distinct product ids into the Object variable
drag and drop a For each loop enumerator and configure it as shown below
create a variable with string type and evaluate the variable as expression and open the expression editor modify the sheet create syntax to create the exact sheet name for the excel file, in our case we will use this ‘product_’+ productid
go back to the for loop drag and drop excel with connection type as excel and source input will be variable and select the sheet creation variable that we just created
Go back into the data flow task and edit the expression of excel connection manager and select the connection property as excel file path if you choose connection string then you have to use the full length connection string like this or go for the excel file as shown in the below picture
“Provider=Microsoft.Jet.OLEDB.4.0;Data Source=”+@excelfilename+”;Extended Properties=/”Excel 8.0;HDR=YES/”;”
you will get error like this
TITLE: Microsoft Visual Studio
——————————
Error at Exceldestination [Connection manager “Excel Connection Manager”]: The connection string format is not valid. It must consist of one or more components of the form X=Y, separated by semicolons. This error occurs when a connection string with zero components is set on database connection manager.
Error at Exceldestination: The result of the expression “@[User::Excelfilename]” on property “ConnectionString” cannot be written to the property. The expression was evaluated, but cannot be set on the property.
Error at Create and load Excel [Excel Destination [57]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E37.
Error at Create and load Excel [Excel Destination [57]]: Opening a rowset for “productid_0” failed. Check that the object exists in the database.
——————————
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC02020E8 (Microsoft.SqlServer.DTSPipelineWrap)
——————————
BUTTONS:
OK
——————————
TITLE: Microsoft Visual Studio
——————————
Error at Create and load Excel [Excel Destination [57]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E37.
Error at Create and load Excel [Excel Destination [57]]: Opening a rowset for “productid_0” failed. Check that the object exists in the database.
——————————
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC02020E8 (Microsoft.SqlServer.DTSPipelineWrap)
——————————
BUTTONS:
OK
——————————
to fix this error create the excel file and the sheet during the design time
after clearing the errors, you can now go ahead and delete the excel file we created during the design time
If you have any problems please use the comments section
Thank You
Sqljunkieshare 
May I request your dtsx file for this project ?
Thanks,
Jerie
Hi Jerie
Yeah sure, can you ping me your email address to
sqljunkieshare@gmail.com , I will reply you with the package
Hi Akhil,
I have a doubt in this. have created the same process but when i open the excel its coming as the error is “File Your opening is in different format specified by file extension verify file is not corrupted and open it”.Due to this i cannot automate my process.Pl suggest me what can i do.
Hi Krishnan,
I have some questions to better understand what you are trying to do?
When you created a excel connection manager what Excel version did u specify
1) Excel 3
2) Excel 4
3) Excel 5
4) Excel 97 – 2003
5) Excel 2007
??
And
What version of Microsoft Office do you have on your machine
Are you dinamically changing file name if ‘Yes’ what is extension that your providing in variable ?
can i review the dtsx file for this project thanks in advance
hi…can you please add your dtsx file? i’d like to review it…and also, i have a question…is it possible for me to set the excel destination dynamically inside the foreach loop container? i already have the flat file source set up, it’s currently connected to a derived column, then data conversion and finally excel destination. all my files have a matching pattern (i.e., points_1.txt = points_1.xlsx and so on)
Hi Akhil,
i hv followed your post on sqljunkieshare.com (“How to create and map Excel destination dynamically in SSIS”).
The post is realy nice . I followed each step to create the one however i m getting following errors & package get failed:
[Excel Destination [34]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
[Excel Destination [34]] Error: Opening a rowset for “CREATE TABLE `CST_CUSTOMERNAME ` (
`Country` LongText),
`Customers` LongText,
`Products` LongText,
`C` LongText,
`MLC` Decimal(15,2),
`MSU` Decimal(15,2)
)” failed. Check that the object exists in the database.
[SSIS.Pipeline] Error: component “Excel Destination” (34) failed the pre-execute phase and returned error code 0xC02020E8.
I hv tried may thing to get rid of this but doesnt work…Please guid me
Further if possible please mail me the dtsx file. on the following mail id. (patilpranavkumar@gmail.com)
Above issue has been resolved…
Here, i just made the sheet naming static instead of dynamic & that’s it…all things running smoothly…(However workbooks are dynamic)
Thanks again for this post…
Thank you sorry for late reply I was gona reply to your original but you fixed it by ur self that’s great way to learn keep going ..if you need any help le me know
Thank you
Hi, i would like to make the name of the excel sheet in the excel destination dynamic, I use BIDS 2005 , it need to put a default sheet , so that the trouble , what would you suggest to me?
Its always good to use default name for the sheet during the design time when in BIDS, during run time name this can be changed using variable? So to answer your question you have use a default name
May I request your dtsx file for this project ? my email is marcelafv9@gmail.com
Thanks,
Marcela
Hi. Can u pls let me know why I am getting this error…basically the table exists. I am not able to create the sheet dynamically. pls help me
Hi Do you have DTSX for this ? If so can you share the output is am getting multiple sheets but not multiple excel files .
hi i need to create excel destination with multiple sheet while passing parameter it shows destination table has not been provided can you help me in this??
Can you please share the dtsx file athenacorp14@gmail.com