November 15, 2007 at 12:21 pm
Hi all,
I use SSIS for data's integration with a flat file to a table in sql server 2005.
In the table, i've a primary key [ArtID]. In the flat file, ArtID is duplicate and i've a date that i already sort to have a historic.
I use a script to know if the row must be inserted or be updated.
I use two sql command to insert and update data in server.
I test this in the script
count = select count(*) from table where primary key = value.
If (count = 0) then insert else update.
When i've a duplicate row in flat file i've a error with the primary key. I don't understand why i've it.
Perhaps the SQL Command commit all the row at the end of the flow.
If it's true, can i change the commit each time the line in flat file is readed !! In this case, how can i do that ??
Or i use a OLE Destination to insert with option table or view (no fast) to have a commit all the line of flat file ??
What do thing about it ??
Thanks a lot
Jérôme
November 15, 2007 at 12:54 pm
SSIS comes with a great task called the slowly changing dimension wzard.
This task will take a row in your data flow, determine by the primary key if the record already exists in the destination, and then either insert or update that row.
It works very well and using this pre-packaged task will probably be easier and more efficient that writing something yourself.
November 15, 2007 at 12:59 pm
Thanks for your reply.
Where is the name of the composant ?
With the dataflow task ?
November 16, 2007 at 4:47 am
It is a data flow transformation task like a lookup, or derived column.
It is called "Slowly Changing Dimension" in the toolbox.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply