1

Step By step: Creating a ETL process in MS SQL Server Integration Services (SSIS) The article describe the ETL process of integration service. Learning objectives: 1. Retrieve data from text file 2. How to use temporary tables in SSIS 3. Life Cycle of SSIS. This article is for who want to learn SSIS and want to start the data warehousing jobs. Step :1 Before installation you have to see the Pre-request for installation Hardware and Software Requirements for Installing SQL Server 2014 Step :2 At the time of MS SQL Server installation, you need to select Integration Services for installation. To know Details Installation Guide line and installation scenarios say previous version is installed on your machine, you want to upgrade previously installation, your machine is 64-bit etc. Step :3 Open new Integration Services project : 3.1 On the Start menu > Scroll and click SQL Server Data Tools.

3.2 File Menu > New > Click on Integration Services Project template

2

Step :4 Now a project has opened and solution explorer is visible on the right side.

Step :5 Download the Adventure Works Sample Database Step :6 Now we are going to make connection with flat file from where we are going to import data. Right-click in the Connection Managers area >Select New Flat File Connection.

Step :7 Now “Flat File Connection Manager Editor” dialog box will be appear. By default the General option is selected. 1. Assign Path: browse text File location. 2. Set Delimiter: File format need to be select within delimited, fixed width and Ragged Right. 3. Header row delimited: Text file contain the header file then delimiter need to be select with available options. 4. Column Names in the first data row: The check box also need to be select because the first row contain the column name.

3

5. Click on the Column option and following view will be appear. 1. Row delimiter: By default {CR}{LF} is selected as row delimiter. You have to select delimiter according to the text file nature. 2. Column delimiter: Again you have to select the Column delimiter. All the data of text file will be appear on the Preview section if the delimiter is selection corrected. Otherwise no data will be viewable on there.

4

6. Click on the Advance now the following view will be appear. Here available column and corresponding nature of data has appeared .From here you can change column name, data type and few other listed options. Also you can create new columns and suggest types. Whenever text file don’t have any column header then then default value will be Column 0, Column 1 ,.. so on. But in the case where column header is available then it will be available.

5

7. Last option is Preview when you can get the all data exported from the text file. Here is an option the “Data Row to Skip”. By that you can skip number rows to import from text file. By default you will see all the rows. After setting 2 and press refresh button you will see few rows are not available 8. In the same way make another Flat file Connection rename it to ‘Connection for Import Attendance’. It will connect to Attendance text file and get data from there.

Step :8 In this step we will go through the Configuration of an OLE DB Connection Manager name it ’ Connection ForWH Database’ from Property Window. 1. Right-click > Connection Managers area>Select OLE DB Connection>click New.

6

1. Select Server name > set the authentication > Connect to the desired database. 2. Click Test Connection to verify that the connection settings you have specified are valid. Step :9 In the step we are going to add Data Flow task by that extract, transform, and load (ETL) process occurs. 1. Click on the “Control Flow” of package design surface. 2. Add Data Flow Task from SSIS Toolbox 3. Go to Property Window Selecting it >Press ( Alt+ Enter or F4) 4. Select the Name property and rename it . 5. In this way ,Place the Execute SQL Task from Favorites and another Data Flow task so that it look As below :

7 Step: 11 now go to Control flow tab 1. Clicking the Data Flow tab 2. Drag Flat File Source from other Sources of toolbox and place onto the design surface of the Data Flow tab. 3. After that place OLE DB Destination from other Destination Sources. 4. Now Data Flow design Surface will be look like as below :

5. Double Click on ‘Flat File Source’ and Select the Flat file connection manager.

8 6. Next go to the Column option now you available column will be come from flat file. Better to cross check the columns with text file. If any mismatch found then check the row and column .delimiter setting of flat file connection manager.

7. Press OK button. Now connect to Blue arrow with the OLE DB Destination by drag the arrow and drop it on to the OLE DB Destination. 8. Now we are going to configure the OLE DB Destination. Press double click on OLE DB connection manager set Connection Manger, Data Access mode and name of the table or then view.

9

9. After that go to Mapping option, existing mapping between source and destination will be viewable here :

10

10. Now we have to do same for Data Flow task attendance.

11. Click on the Control Flow task. We have to configure the Execute SQL Task. Double Click on it and Execute SQL Task dialog box will appear .Copy below SQL and paste it to the SQL Statement

of Execute SQL Task.

11

12. connect to Data Flow Task Employee, Execute SQL Task and Data Flow task Attendance Step: 11 Now we will do debugging. Go to Debug menu, click Start Debugging. Now the package will run.

Step: 11 After the package has completed running, on the Debug menu, click Stop Debugging.