SQL Server Integration Services (SSIS)

SQL Server Integration Services (SSIS)  -  to get the data from various sources and put the data to tables or create Cubes for data warehouse, used for ETL.

SSIS is a visual tool with drag and drop feature, which enables us to create SSIS, packages to perform ETL in a very short amount of time. 

Business Intelligence Development Studio (BIDS) is the only way where you develop and test the SSIS package. SSIS package is nothing but a complex XML file.

Start All Programs ->  Microsoft SQL Server 2008 R2 -> SQL Server Business Intelligence Development Studio.

Create Simple Flat File SSIS Package

1. Go to Menu -> File -> Project -> Integration Services Project ( see the screen shot)
   
2. From Toolbox drag and drop a Data Flow Task onto the Control Flow tab designer. The tools on the Control Flow are called Tasks.
   
3. Double Click the Data Flow Task,  tab changed to Data Flow tab designer and also Toolbox tools have changed. The tools on the Data Flow are called Transforms/Transformation.
4. From Toolbox drag and drop 2 transformation (Flat File Source and OLD DB Destination) onto the Data Flow tab designer.
5. From the Flat File Source (used to define the source from where the data needs to be pulled) drag a green arrow to the OLE DB Destination. 
6. A red cross mark on the right side of the both transforms, because the transforms are not yet setup correctly and if we execute the package now, we will get an error.
   
7. Double click the Flat File Source, Flat File Source Editor window will pop-up, click the New button to create a new connection.
8. Click the Browse button to select the flat file (here i selected .csv file). Leave all other options default, except to check the Column names in the first data row, otherwise the header row would be treated as data line. Click OK.
9. Double click the OLEDB Destination, Editor window will pop-up, click the NEW OLEDB Connection button to set up a connection to the SQL Server.
10. Click the Browse button to select the flat file (here i selected .csv file). Leave all other options default, except to check the Column names in the first data row, otherwise the header row would be treated as data line. Click OK.
11. Name of the table of the view - Choose exiting table from the list or create a new table by clicking NEW button.
12. The red mark has gone from both the source and destination, this means that the Source and destination are setup correctly
13. Press F5 to execute the package. You will notice that the Source and Destination turn yellow then green.
14. On turning green, you will see that there is a XXXX number alongside the arrow connecting the source and destination. This means how many records have been sent from the source to destination.
    
15. Fianlly, go to the Progress tab, it show the log, like how much time was consumed in the execution what the steps were taken etc.