How to create and map Excel destination dynamically in SSIS

In this post, you will learn

  1. How to create and map Excel destination dynamically in SSIS
  2. How burst the out put into multiple files using For loop container
  3. 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

Advertisements