Copying data from one database to another using SSIS

Question

I am writing a SSIS package and trying to extract the data from one database to another. I have created a Execute SQL task and using the following statement INSERT INTO dbo.getParties EXEC dbo.getParties to perform the operation. This works when the source and destination our in the same database. How do I handle this when the source and destination are in different databases. You can associate only one connection manager to a task.

Do I connect to the source and in the SP call the destination. Is it the right way of doing it

Below is the design of my template

Solution

Use Data Flow component where you can set up the source and destination connection strings.

..and inside the data flow task use the Destination and Source Assistants that let you define its own connection string for each.

Of course, besides these you can also apply any sort of data transformations you wish.

Edit: Since you your source is SQL Command (stored procedure) you need to define it in your source assistant. As you can see here, just change Data Access Mode to SQL Command and set to EXEC getParties:

Comments

[Tom]

Hi Ivan, how do I handle the sql statement in this case as my current statement was INSERT INTO dbo.getParties EXEC dbo.getParties

[Ivan Sivak]

@Tom I edited my answer. Basically, you only need to set the correct data access mode. Your source is stored procedure (EXEC getParties) and destination is a table.

[Tom]

Your solution is good but just want to check how you are handling incremental insert. That is if I have inserted 100 records in the destination then i dont want to insert the same 100 records again. I need to insert from 101 record onwards

[Tom]

Earlier method I was using select max query on the destination table. How do I do it here

[Ivan Sivak]

@Tom You mean primary key in destination table? It depends. If it is Identity Column then you shouldn't worry about it. If however you simply want to ensure whether the same records already exist in the destination then you only need to add some logic. E.g. before Data Flow task you can decide in Script Task whether you proceed or not. There are many options and it only depends on your architecture, needs etc.

[Tom]

Though there is an identity column in the destination , I dont want to end up in error while it is trying to insert the same record. I just had a look at the sctipt task. Didnt find a place to write script to fetch the max record in the destination table. Sorry I am new to SSIS hence asking a bit basic question

[Ivan Sivak]

@Tom If there is an identity column then you should not worry about duplicated primary keys. As I said, if you need to prevent from saving a duplicated rows on non-primary-key columns there are more ways. you can halt the operation before Data Flow in Script Task where in C# nothing stops you from using the SqlCommand class or use SQL Task and play with expressions to halt the flow where you need to.

[Tom]

I do undertstand that identity column does take into account duplication but the package would definitely fail due to primary key violation which i dont want. I want the tasks to execute smoothly. The way I am thinking is to create an execute sql task prior to the dataflow to dertermine the max from the destination table store it in a variable and then use that variable and pass it as parameter to the storeprocedure in the dataflow task. I am not able to add the parameter in the datadlow task. How do i do it

[Tom]

When I click on the parameters button i get an execption saying the storedprocedures does not contain a parameter

[Tom]

is there a easier way to do this.All I need is to get the max of the destination table and then your proposed solution will work for me

[Ivan Sivak]

@Tom .... OK, look. You already have the max in your variable, right? After that you can call Script Task where in simple C# code you check that max variable and return success or fail to Data Flow.

[Tom]

I think you didnt get what I am looking for. Its not about checking success or failure. If the source has 1000 records and if 100 records are inserted into the destination the first time then in the next execution the next record that 101 onwards should be filtered and inserted into the destination

[Ivan Sivak]

@Tom I see. What could maybe help you is the conditional split in between the source and destination tasks. Based on the max from destination you could set up the conditional split to either omit or insert the record. docs.microsoft.com/en-us/sql/integration-services/data-flow/‌​…