SSIS or Sql Server Integration Services can used in current requirements, to perform opertions like Extraction Transformation Load (ETL). If there is requirement to produce flat files, excel files, Comma Separated Files or any other types of requirements like movement of data into database or from database.
SSIS code is saved in .dtsx extension and requires DTExec runtime to execute. Dtexec in turn requires .net framework.
Inorder to Start with SSIS, you need to have Business Intelligence Studio which comes with SQL server and during installation please check install BI option during installation.
SSIS package has two main components 1) Control Flow 2) Data Flow component.

In order to Perform ETL using SSIS Data Flow Task or DFT plays an important role. DFT is responsible for Movement of Data from Source-to-Stage or from Stage-to-Mart. DFT has three main components

  • a) Source
  • b) Transformation
  • c) Destination

Below image show an empty Data Flow Task.
SSIS_DFT

  • 1) Depending upon source from where we need to Extract the data, The source is selected from different Sources e.g. Excel, Flat File, OLE DB, RAW file, XML etc.
  • 2) A suitable Transformation is selected
  • 3) Finally Depending upon requirement The suitable Destination is selected.