arthrex
asked on
SSIS Data Flow Task
Hi experts,
I have an SSIS package which takes rows from an excel sheet and inserts them into a SQL DB.
The table where I want to do the inserts has a primary key which is calculated by a complex formula. This formula not always creates a unique value therefore I get an error in my SSIS package.
Unfortunately, I can't change that formula. So my only chance is to try the insert for so long until no error is thrown (the created value is unique).
But how do I implement this process in SSI?
Can anyone help?
Thanks a lot
I have an SSIS package which takes rows from an excel sheet and inserts them into a SQL DB.
The table where I want to do the inserts has a primary key which is calculated by a complex formula. This formula not always creates a unique value therefore I get an error in my SSIS package.
Unfortunately, I can't change that formula. So my only chance is to try the insert for so long until no error is thrown (the created value is unique).
But how do I implement this process in SSI?
Can anyone help?
Thanks a lot
ASKER
Sounds good.
This is the formula that is used to create the PK:
((1000000000)*rand((datepa rt(month,g etdate())* (100000)+d atepart(se cond,getda te())*(100 0))+datepa rt(millise cond,getda te())))
So I tried to use this expression in the "Derived Column Transformation", but I can't use the random function there.
How would you do it?
Thanks for your help
This is the formula that is used to create the PK:
((1000000000)*rand((datepa
So I tried to use this expression in the "Derived Column Transformation", but I can't use the random function there.
How would you do it?
Thanks for your help
but you never create duplicate PK with your expression... if you use year, month day, minute, second and milisec you always have unique values... use the format with 2 values for each part of date and 4 for year...
20090207_08103918
20090207_08103918
ASKER
Hello Pedro,
I think I know what the problem is now. Check the attachment to see my data flow task.
The excel source rows are pulled through the orchestration all at the same time. I can see that in the data viewer.
So I guess the OLE DB destination task tries to do a bulk insert and creates just one PK for all the rows.
I tried to limit the "Rows per batch" to 1 and the "Maximum insert commit size" to 1 but it didn't help.
I still get the "duplicate PK" Error message.
Do have any suggestions?
Thanks
dataflowtask.png
I think I know what the problem is now. Check the attachment to see my data flow task.
The excel source rows are pulled through the orchestration all at the same time. I can see that in the data viewer.
So I guess the OLE DB destination task tries to do a bulk insert and creates just one PK for all the rows.
I tried to limit the "Rows per batch" to 1 and the "Maximum insert commit size" to 1 but it didn't help.
I still get the "duplicate PK" Error message.
Do have any suggestions?
Thanks
dataflowtask.png
could you send me the package and excel file?!
regards,
Pedro
regards,
Pedro
Why aren't you using a IDENTITY or GUID for your primary key? You should never use a timestamp to try and create a unique key - hardware will always catch up with you eventually.
ASKER
As I already said,
I didn't create the DB and I cannot just change the primary key calculation
I didn't create the DB and I cannot just change the primary key calculation
arthrex,
What you need is to create a unique with during the data flow for primery key, correct?
Cheers
Pedro
What you need is to create a unique with during the data flow for primery key, correct?
Cheers
Pedro
Check attached image
SSIS-Interface.JPG
SSIS-Interface.JPG
ASKER
Hi Pedro,
well, that's one option.
Originally, I wanted to leave the primary key out and let the database table deal with it.
Since this didn't work, I tried to generate a key with this formula
((1000000000)*rand((datepa rt(month,g etdate())* (100000)+d atepart(se cond,getda te())*(100 0))+datepa rt(millise cond,getda te())))
inside a script component and save the calculated value to a column or variable (That's why I had this primaryKey row there). But it didn't work with the script component.
Do you think you can create the script component for me?
That'd be awesome.
Thanks for the great support!
well, that's one option.
Originally, I wanted to leave the primary key out and let the database table deal with it.
Since this didn't work, I tried to generate a key with this formula
((1000000000)*rand((datepa
inside a script component and save the calculated value to a column or variable (That's why I had this primaryKey row there). But it didn't work with the script component.
Do you think you can create the script component for me?
That'd be awesome.
Thanks for the great support!
Did you try using an Error Output on the Insert. If the Insert fails to just write the row to the Error table? That would keep your package from ending in error.
ASKER
Hi HoggZilla,
the problem is the error occurs in the destination DB shape.
And, as it's name says, it's the destination.
If an error occurs there you can't redirect to another shape.
the problem is the error occurs in the destination DB shape.
And, as it's name says, it's the destination.
If an error occurs there you can't redirect to another shape.
From an OLE DB Destination you can connect the Red Arrow to an Error output. What am I missing.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
helped?
regards,
Pedro
www.pedrocgd.blogspot.com