Hi Experts I have designed an SSIS Package Which reads data from a Flat file and loads it into MS Sql database for this I have taken a For each loop to find the Flat file from the folder and attached a DataFlow to it and in that Data flow I have added a Flat file src and OLEDB Destination and this is working fine. And the problem now is I have to do an Insert and Update on the target table based on my primary key column.
If a record from the flat file with an ID exists in my table, I have to update that record in the target table other wise I have to insert it in the Target table. I have read in all the blogs and could not find a proper solution. Can any one suggest a solution to me... Thanks in advance
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
By the way, option 1 will have a bigger hit to performance and speed since it has to query the table an extra time and make an update statement for every existing record. Option 2 will be much faster and have better performance since it is a set based solution instead of a row based solution like option 1.
Inside dataflow use OLEDB command to update or a OLEDB destination to insert.
Helped?
Regards,
Pedro
www.pedrocgd.blogspot.com
Here's a sample package that I created. I put both options in it, so if you run it, make sure one of For Each Loop containers is disabled. I also created two tables in my localhost in the tempdb database for the package to connect to, as well as a flat file for the package to pull from.
If you have that many rows in your flat file, I'd suggest that you go with option 2 that I suggested earlier. To create a staging table, go into SSMS, find your table in the object explorer, right click on it > Script Table As > CREATE To > New Query Editor Window. Delete anything in the script that relates to a PRIMARY KEY, as well as any indexes and constraints. Change CREATE TABLE YourTableName to CREATE TABLE YourTableName_Staging and execute the script to create the staging table.
Hope this helps.
Follow the attached images.
Helped?
regards,
Pedro
www.pedrocgd.blogspot.com
Step4
Step3
Step2
Step1
Hi pedro, The problem is the SSIS Packages which I have created are on the Solution Folder on my Laptop. and the SQL Server is installed on Some other Server. And to schedule these Packages What kind of credentials Should I use. Can I use the normal Windows Authentication Credentials to Create these jobs...Help me
Business Accounts
Answer for Membership
by: aaronakinPosted on 2009-03-02 at 19:47:54ID: 23780758
You really have two solutions, and the best option depends on the size of the text file. If the file is small, go with option 1, but if it's large, go with option 2 as it would be more efficient.
1. In your data flow task, add an OLEDB source component that connects to the database that you will be inserting and updating. Have it pull in the ID field from the existing records in the destination table. Connect it to a Merge Join. Have the OLEDB source be the left input and the flat file as the right input to the merge join and make it a Left Outer Join. This will all be before the OLEDB destination component. Include the ID value from the table in the merge join output along with all columns from the flat file. Add a Conditional Split component after the merge join. In the condition of the spilt component, type in ISNULL(ID) == False. The ID column should be from the table. Name the output for this condition (i.e. Exisiting Records). Name the Default Output Name to something like New Records. Connect the New Records output to the OLEDB destination and connect the Existing Records output to a OLEDB Command component. Specify an UPDATE statement in this component to update existing records in the table.
2. In your data flow task, change the destination table to point to a staging table with the same structure as your final destination table. Don't add any constraints or primary keys though. In your control flow, add an Execute SQL Task after the data flow task and write a SQL statement to update all records in the final destination table with matching records from the staging table. Add another Execute SQL Task after that to insert new records from the staging table to the destination table.