SSIS Packages basically are made of components, variables and connection managers (which helps in establishing a connection to a database / SMTP server / file).
Let’s begin with a simple package, to extract data from a flat (.csv) file and load it into an Oracle Table. Achieve this in 10 Easy steps:
1) Choose the “Data Flow Task” component from the Toolbox and drag it on to the Control Flow Area.
2) Double Click the “Data Flow Task” (Note: you’d be in the Data Flow Area)
OR
Click the “Data Flow Task” and click on the “Data Flow” Tab.
3) Choose “Flat File Source” from the Data Flow Sources present within the toolbox.
(Note: The components in the Control Flow and Data Flow are not the same)
4) Double Click the component to configure the same:
i. Creation of Flat File Connection Manager: Click on the button “New” from the Dialog box. (The reason is we are interested in creating a Flat File Connection manager, who would help us, read data from the “.csv “ file)
ii. Give it a name and provide a description (Good Practice: Name it on its functionality Test_Flat_File_Source_Conn) and browse the “.csv” file.
iii. The Format is delimited and can be retained. The Check box can be checked if the (.csv) file contains the header.
iv. Click Columns on the left side tab and you would be able to preview the data.
v. Click OK. (Note: A connection manager with the name “Test_Flat_File_Source_Conn” would be seen in the Connection Managers area).
5) Choose “OLEDB Destination” from the Data Flow Destinations in the Toolbox and drag it on to the Data Flow Area.
6) Click on the “Flat File Source” component and drag the green connector to the “OLEDB Destination” (This would make the data available as input to the OLEDB Destination).
7) Double Click on the component to configure the same.
i. Creation of an OLEDB Connection Manager: on the button “New” from the Dialog box.
ii. Click on the button “New” from the second Dialog box.
iii. Choose the appropriate Provider from the drop down (In our case Microsoft OLEDB Provider for Oracle).
iv. Enter the TNS Name / Server name and provide the credentials for the schema.
v. Click OK.
vi. Returning back to dialog box #1, from you can choose the desired table from the drop down list.
vii. Click on the Mappings Tab in the pane on the left hand side; establish the desired mapping.
viii. Click OK.
8) If the above steps were performed appropriately, there shouldn’t be any errors or warning!
9) Save your work, and Choose Debug -> Start Debugging and there goes your maiden packages run.
10) The graphics provided by SSIS would make the process easy to comprehend. To Stop Debugging choose Debug -> Stop Debugging.
