EXCEL SOURCE TO EXCEL TARGET

Requirement:

I am having an “Excel” file with two fields ‘ID, Name’ shown below screen. This file is located in source location (D:\Programs\Source).

I want to load source data it into a new excel file in target location (D:\Programs\Destnation\).

Package design:

• Open the BIDS (Business Intelligence  Development Studio)
• In control flow, drag and drop the DFT (Data flow task) into designer surface.
• Right click on “Data flow task” and select Edit option, then it will go to “Data flow”.
• From tool box drag and drop the “Excel source” into designer surface.

 

• Right click on “Excel source” and select “Edit” option, then “Excel Source Editor” window will open.
• In the “Excel source editor” we nee to provide “Connection manager", "Data access mode" and "Excel sheet name" information. For Excel connection manager creation click on “New” button, shown below.

 

• “Excel Connection Manager” window will open. Give the “Excel file path” by using “Browse” option and press “OK” button to finish source excel file connection.

 

• Choosing "Data access mode" as “Table or view” in “Excel source editor” window.
• The Source excel file is having three sheets, in those first sheet is having data. So we should select first sheet for “Name of the Excel sheet” drop down list, shown below. 

  

• Select “Columns” option in the “Excel source editor” window and verify the column names, and press “OK” to finish the “Excel source” mappings.

 

• “Excel source” mapping is completed. Now we need to create “Excel destination” mappings to create target excel file.
• Go to the tool box, then drag and drop the “Excel destination” into “Designer surface”. Select “Excel source”, and connect to the green arrow to “Excel destination”.

• Edit the “Excel destination”. We need to create New excel file as target so new connection manager is required for target file.
• In the “Excel destination editor” window choose “New” option to create “Excel connection manager”.

• Excel connection manager window will open.
• In the “Excel file path” box choose “Browse” button and give the file path and name, shown below.

• Click “OK” button (shown in the above screen shot) then Excel connection manager window will disappear.
• In Excel destination editor window choose “Data access mode” as “Table or view”.
• If we try to drop down the box “Name of the excel sheet”, it show “No table or view could be loaded”. Because the target file doesn’t have table structure. First we need to create a table in target excel file.

• Click on “New” button opposite to the “Name of the Excel sheet” box. “Create Table” box will open with code (code is generated based on source field names and data types).

• Verify the table creation code and click “OK” button. Then one new window will open, saying that “select the new excel sheet from the drop down list” press “OK” button.

• In the “Excel destination editor” window select excel sheet name and then select “Mappings” option shown below screen shot. 

 

• Verify the mappings and click “OK” button to complete target mappings.

• Source and target mappings are completed, execute the package.

Data from target file: