March 6, 2013 at 12:18 pm
I would like to add something to SSIS package, which will allow it to only insert rows that don't already exist in the database table. Customer table export from a cloud based software exports the entire customer list, so I don't have an option to export just new customers. How can this be accomplished?
March 6, 2013 at 12:22 pm
How will you identify whether a customer already exists - what is the PK?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
March 6, 2013 at 12:24 pm
There is a PK in place, which is a Customer ID.
March 6, 2013 at 12:31 pm
Cool - is it an ascending number?
The reason I ask: one way to do this would be to query your existing data, get max(id) and then get the new data by selecting from it where id > maxid.
By the way, what happens if changes to existing data occur - don't you want to update that too?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
March 6, 2013 at 12:37 pm
Phil Parkin (3/6/2013)
Cool - is it an ascending number?The reason I ask: one way to do this would be to query your existing data, get max(id) and then get the new data by selecting from it where id > maxid.
By the way, what happens if changes to existing data occur - don't you want to update that too?
Yes, ascending number. It is auto generated by the system, and customers are never deleted. In this case I'm only interested in appending the data.
March 6, 2013 at 1:01 pm
OK, here's one way.
1) Create an integer variable in your SSIS package - say MaxId.
2) An an ExecuteSQL task which connects to your target db (the one you are importing to) and does something like this:
select MaxId = max(Id) from dbo.Customers
3) Configure the ExecuteSQL task to return the result into the MaxId variable.
4) Create a data flow task and add an OLE DB source. Define the connection as your source db.
5) Set the data access mode to SQL Command.
6) Write the SQL command text, using a parameterised query to select [column list] where Id > MaxId.
7) Connect the OLEDB source to a suitable configured OLEDB destination.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply