Import data to Excel sheet's specific region

using SSIS load data to excel sheet at specific region

this is actually quite easy to load or ETL data from any source to Excel sheet's specific cell range or group.
the Excel Source component of Data flow considers excel sheet and range as a table. So once you define a range can easily load data into that using ETL Operation.

The steps of defining range in Excel sheet is given as below

Step 1: Select any cell range in your excel sheet


Step 2: Type any one word name for selected range into Excel Name Box left to the formula box. press Enter , the Excel sheet range Name is defined  now




Step 3 :  Come to your SSIS ETL Package, now in Data Flow Tab double click your Excel Destination  component. The "Excel Destination Editor" window Opens, Here if OLEDB Connection manager is already pointing out the that Excel sheet then its fine else you can create a new connection managerby by clicking "New" Button right to "OLEDB Connection manager" drop down box.



Step 4 :  Now in the "Name of the Excel Sheet" drop down box in "Excel Destination Editor" you can see that Named range



now simply select the this range and execute ETL , you can check after successful execution that the source data would come to this defined region only.

5 comments:

  1. PPC Expert For Tech Support |inbound calls for tech support, pop up calls for tech support Call at: +91 981-142-0083 ppc management experts ppc call provide by osiel web
    expertppc

    ReplyDelete
  2. This is a great solution rather than writing a custom script but find two issues wit this approach. When implementing this approach it seems to drop a row from the starting defined range. Is there a way to prevent it from dropping a row? Can you also overwrite any existing data when writing to the defined ranges?

    ReplyDelete
  3. Hi
    I am creating excel using template.
    I copy template file to dynamic excel file name. This time it is not working.

    ReplyDelete