How to update a database table using ssis?

Question

hi guys,

i have successfully run an ssis package and inserted data from one table in the first database to another table in the second database.but all the rows are inserted in this case.i have placed an oledb source control,a character map control and an oledb destination control.no query is written in this.i specified only the source table and the destination table in the 2 databases.but i need to transfer only some column values in one table to another,not the entire table data.for that what could be done?

Answer 1

Not quite sure I understand your question correctly, but I have been working on something similar lately and this is what I needed to do.

1. Run Tasks > Import Data on the schema you want the data to end up on
2. Select your DB source, in my case it was SQL Server, select the servername, and also the schema
3. Next select the destination Database and schema, in my case it was SQL Server again
4. I think the next screen gives you an option to select data, or write your own query. You should write your own select statement to pick out just the columns you need
5. Next screen you can edit mappings, set data lengths etc
6. Next save your package if you need Then run.

What I did after this was to rename the table it creates from "Query1" to something more meaningful. Then I stepped through the wizard again, and on the mapping screen selected "delete existing rows". This should update your table when run instead of appending duplicate rows

Hope this helps!