XML insertion into SQL Server using Integration Services

Question

XML insertion into SQL Server using Integration Services

JamesNZ

SSCommitted

Points: 1615
More actions
April 25, 2009 at 12:48 am

#155606

I've got the attached package which successfully inserts a whole heap of data from XML into a SQL Server table.
My problem lies is that if the XML has 400 rows in it, it keeps inserting 400 rows each time it runs when I just want to update the existing records and insert new ones. There's an ID within the XML file named SupplierID which is unique in the XML document and is my primary key in the table its inserting into.
How can I achieve this in IS?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

COZYROC One Orange Chip Points: 28499 More actions · Answer 1

JamesNZ (4/25/2009)
I've got the attached package which successfully inserts a whole heap of data from XML into a SQL Server table.
My problem lies is that if the XML has 400 rows in it, it keeps inserting 400 rows each time it runs when I just want to update the existing records and insert new ones. There's an ID within the XML file named SupplierID which is unique in the XML document and is my primary key in the table its inserting into.
How can I achieve this in IS?

Check these posts:

Get all from Table A that isn't in Table B

Checking if a row exists and if it does, has it changed?

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

JamesNZ SSCommitted Points: 1615 More actions · Answer 2

Thanks for that.

Based off those links you provided I've managed to create the attached (see screenshots).

I managed to get the Insert working just fine, but couldn't manage to get an Update working with it. Any ideas on what I need to consider for that?

I managed to get it all working using a Slowly Changing Dimension, but I've been reading that it's a bad object to be using and should be avoided.

Can someone help please?

COZYROC One Orange Chip Points: 28499 More actions · Answer 3

JamesNZ (4/26/2009)
Thanks for that.
Based off those links you provided I've managed to create the attached (see screenshots).
I managed to get the Insert working just fine, but couldn't manage to get an Update working with it. Any ideas on what I need to consider for that?
I managed to get it all working using a Slowly Changing Dimension, but I've been reading that it's a bad object to be using and should be avoided.
Can someone help please?

The standard SCD component is not very efficient, but in your case where you need to process only 400 rows it will work just fine. There is enhanced SCD component created by Todd McDermid[/url]. It is free and you can download it from here.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

JamesNZ SSCommitted Points: 1615 More actions · Answer 4

CozyRoc (4/27/2009)
JamesNZ (4/26/2009)
Thanks for that.
Based off those links you provided I've managed to create the attached (see screenshots).
I managed to get the Insert working just fine, but couldn't manage to get an Update working with it. Any ideas on what I need to consider for that?
I managed to get it all working using a Slowly Changing Dimension, but I've been reading that it's a bad object to be using and should be avoided.
Can someone help please?
The standard SCD component is not very efficient, but in your case where you need to process only 400 rows it will work just fine. There is enhanced SCD component created by Todd McDermid[/url]. It is free and you can download it from here.

I'll be processing down the track a few hundred thousand rows, so I'd rather look at alternatives to the standard SCG component.

Thanks for the link to Todd McDermid's component. What do you think about http://www.sqlbi.eu/Projects/TableDifference/tabid/74/language/en-US/Default.aspx as well?

What's the general opinion on using third party components like this? Considered alright?

COZYROC One Orange Chip Points: 28499 More actions · Answer 5

JamesNZ (4/27/2009)
CozyRoc (4/27/2009)
JamesNZ (4/26/2009)
Thanks for that.
Based off those links you provided I've managed to create the attached (see screenshots).
I managed to get the Insert working just fine, but couldn't manage to get an Update working with it. Any ideas on what I need to consider for that?
I managed to get it all working using a Slowly Changing Dimension, but I've been reading that it's a bad object to be using and should be avoided.
Can someone help please?
The standard SCD component is not very efficient, but in your case where you need to process only 400 rows it will work just fine. There is enhanced SCD component created by Todd McDermid[/url]. It is free and you can download it from here.
I'll be processing down the track a few hundred thousand rows, so I'd rather look at alternatives to the standard SCG component.
Thanks for the link to Todd McDermid's component. What do you think about http://www.sqlbi.eu/Projects/TableDifference/tabid/74/language/en-US/Default.aspx as well?
What's the general opinion on using third party components like this? Considered alright?

I know both of them have similar goals. Todd's component is very well maintained. If you are looking to save time, it is definitely preferable to look for third-party components. Some companies have strange policies not to deploy additional components, but this is usually the exception.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/