sql-servercsvssisetlflat-file

How to remove particular columns in SSIS package while doing transformation?


I want to remove a few columns from my flat file as I want to load in SQL server Data Warehouse using SSIS, How I can achieve this transformation using SSIS, see attachment for description. For e.g. I don't want %Salary and Address in my transformation.

enter image description here


Solution

  • You can follow the below steps:

    1. Create Data Flow Task
    2. Define Flat File Source and create new connection manager and point to source file.
    3. Create empty flat file with the headers you want. Define Flat File Destination and point to Flat file empty destination, by following the subsequent steps.
    4. Right click Flat file destination, click Edit. It will be throwing a popup as given below. Click Yes.

    enter image description here

    1. Define new connection manager pointing to destination file.

    enter image description here

    1. Now, point to your empty destination file. If you go to columns tab, only specific columns will be present.
      Browse to empty destination file

    2. Now, connect the Flat file source to Flat file destination. Now, only specific columns will be mapped as given below:

    Mapping specific columns

    6.Now, you run the package, only specific columns will be loaded.

    running specific columns load package

    UPDATE: Sorry. I misunderstood destination as Flat File Destination. If you are having SQL Server Data Warehouse as the destination, you need to follow below steps.

    1. you can create Table in destination with specific columns.
    2. Connect Flat file source to OLEDB Destination, as given below:

    SQL Server Destination

    1. Mapping should be done only for specific columns. You dont need specific transformations in the middle.

    OLEDB mapping