How to add specific columns from DataTable to the DB using SSIS ?

Question

I have a CSV File, from that file i have to consider only two columns suppose A & B.

Now my requirement is Map all the values which are there in the column A from the CSV to the table and update other column in the table with the values from Column B.

So far, i have created a DataTable which has these two columns. But I dont know how to update the DB using this DataTable.

I got to know that SSIS can help me out here. But never used it before.

Please help me out :)

Answer 1

Here's a little guidance on what you must do. If you use @Manjoor's reference to get help on this, you should be able to do this.

In a new SSIS package, you need to add a Data Flow task in the Control Flow and two Connections in the Connection Manager.

Your first connection will connect to the your CSV file. The second connection will connect to the database table.

1. Go to the Data Flow tab to add details for the data flow task.
2. Add a Flat File Source to the data flow.
3. Configure the source to reference your flat file connection.
4. Add an OLE DB Command data flow transformation to the data flow.
5. Link the two objects with a data-flow path (creating a green arrow from source to destination).
6. Edit the the OLE DB Command object and configure it to update the table connection.

In the OLE DB Command, your SQLCommand will look something like this.

UPDATE TableName
SET ColumnB = ?
WHERE ColumnA = ?

The question marks (?) are parameters that you will define in the OLE DB Command.

This is a basic solution. So, if performance is an issue, you'll need to develop enhanced solutions.