April 23, 2012 at 4:16 am
Hi,
I need to import an excel that contain the following columns
Order Date, Customer ID, Name, Country, SerialNo
and perform the following tasks.
Step 1
I need to check the serial no exists in the database(Table CustomerDetails) . If the serial no already exists in the table then ignore the record.
Step 2
Then I need to check the customer ID exists in the database, if it exists then I need to check the name and country are same for that customer ID, if not I need to update the name and country of the customer ID of the existing records and insert this record.
Kindly let me know the procedure to perform this tasks in SSIS.
Thanks,
Sandesh
April 23, 2012 at 4:27 am
Sandesh
Sounds like you're asking us to do all the work for you without telling us what you've already tried.
You have two options. You could import all data into a staging table and manipulate it with T-SQL. Alternatively, you can use SSIS transformations such as Merge Joins and Slowly Changing Dimensions to transform the data as it's loaded. You will want to test which is more efficient - it will depend on what percentage of rows you throw away and keep, and on the resources available on the respective computers on which you run SQL Server and SSIS.
John
April 23, 2012 at 11:50 pm
1. Use a Lookup and keep only the match output.
2. Use again a Lookup and retrieve the name and country columns. The match output is routed to an OLE DB Destination for inserts and the no match output is routed to a conditional split. In this component you check if those columns have changed or not and route the rows to the appropriate destination.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply