Sunday, May 7, 2017

Exporting data as flat file by SSIS: Step by step guideline


Exporting data from database and convert that into comma seperated file(.csv) is pretty straight forward and easy task by using SSIS,

As an example, I need to export ResellerInfo from AdventureWorksDW2012 and generate .csv file by using SSIS.

In SSIS Toolbox you have transformations called a) Data flow Task b) OLE DB source and c) Flat File Destination , to complete the excercise we need those two transformations and one Task.

Fig 1: SSIS Toolbox (Data flow Task)



Fig 2: SSIS Toolbox
In OLE DB Source you should write your SQL query to populate the result, in this case, I have below SQL:   SELECT
       [ResellerName]
      ,[NumberEmployees]
      ,[OrderFrequency]
      ,[OrderMonth]
      ,[FirstOrderYear]
      ,[LastOrderYear]
      ,[ProductLine]
      ,[AddressLine1]
      ,[BankName]
      ,[MinPaymentType]
      ,[YearOpened]

  FROM [AdventureWorksDW2012].[dbo].[DimReseller]
 

At your package you need drag a data flow task and then double click the data flow task, now under the data flow drag and drop the two transformations OLE DB Data source and Flat File Destination.

Fig 3: Under Data Flow task

In the above design, 'Populate Data' is a dataflow task and 'destination CSV' is Flat file destination transformation.

While we connected from 'Populate data' to the 'Destination CSV', we had to configure the 'Destination CSV' which is Destination Flat file Transformation. Destination Flat File configuration must connect with Flat file connection manager as like below:

Fig 4: Flat file destination is conneted with Flat file connection manager

Flat File connection manager should include, connection manager name, file destination and name at least. You can have options to choose from different delimeter like comma, semicolon etc.

Fig 5: Configure flat file connection


Now, from your package you can run the data flow task "Generate CSV for Reseller" by right click.

Fig 6: Execute Data flow task



and then you should have the .csv under the location that you have already put in the flat file connection manager.

Fig 7: CSV result set at the specified location