BI - Load an Excel File to DB

The purpose is to upload an Excel file into a Database without duplicate information.

• Install SQL Server Data Tools for Visual Studio 2012
• Create a New Project 
• Type: Integration Services Project
• Create New Package
• Menu: Project -> New SSIS Package 
• Inside the Package
• In the tab Control Flow , drag a "Data Flow Task"
• Inside the Data Flow tab, drag the following Items, as the image
• Excel Source
• OLE DB Source
• Sort elements (x2)
• Mege Join
• Conditional Split
• Data Conversion
• OLE DB Destination

Excel Source

• Double click in the element
• In Connection Manager, Select "New" and then choose the file path

• Select the Excel tab 

• In Columns, select all the items

OLE DB Source

• Double click in the element
• In Connection Manager, Select "New" and then choose the Server Name and Table
• Click Ok

• Select the table where you are comparing the information (is the same table where you are going to store the new data)

• In Columns, select all the items
• Click OK

SORT

• Double click in the left Sort
• Select the key that you want to use to compare

• Do the same for the right Sort, selecting the same key to compare

MERGE JOIN

• Double click in the Merge Join
• Select in Join Type: Left outer join
• Select all the left colums and only the key in the right table
• Change the Output Alias for the right key
• Click OK

CONDITIONAL SPLIT

• Double click in the Conditional Split
• Open the Colums Menu at the left and drag and drop the right key to the down part
• Change the conditional to ISNULL([NewCodigo])
• Connect to DATA Conversion with the line "New Rows"

DATA CONVERSION

• Double click in the element
• Change the Data Type as in the image, especially in "Codigo"

OLE DB DESTINATION

• Select the connection manager
• Select the table to store the new rows
• Save the project

TEST

• Execute the Package 
• Do a select query to review the new data added