Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Inserting new rows only from Excel to OLE DB Source Expand / Collapse
Author
Message
Posted Wednesday, March 6, 2013 12:18 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 5:34 PM
Points: 75, Visits: 299
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?
Post #1427578
Posted Wednesday, March 6, 2013 12:22 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, September 15, 2014 7:23 AM
Points: 5,074, Visits: 11,852
How will you identify whether a customer already exists - what is the PK?


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1427580
Posted Wednesday, March 6, 2013 12:24 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 5:34 PM
Points: 75, Visits: 299
There is a PK in place, which is a Customer ID.
Post #1427584
Posted Wednesday, March 6, 2013 12:31 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, September 15, 2014 7:23 AM
Points: 5,074, Visits: 11,852
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?



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1427589
Posted Wednesday, March 6, 2013 12:37 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 5:34 PM
Points: 75, Visits: 299
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.
Post #1427593
Posted Wednesday, March 6, 2013 1:01 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, September 15, 2014 7:23 AM
Points: 5,074, Visits: 11,852
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.




Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1427599
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse