[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

SSIS Data Flow Task

Posted on 2009-04-07
14
Medium Priority
?
627 Views
Last Modified: 2013-11-10
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
0
Comment
Question by:arthrex
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 2
  • +1
14 Comments
 
LVL 22

Expert Comment

by:PedroCGD
ID: 24085410
Try using a lookup transform that checks for existence of the PK in database... if not found insert, else ignore or try to create another key...
helped?
regards,
Pedro
www.pedrocgd.blogspot.com
0
 

Author Comment

by:arthrex
ID: 24085699
Sounds good.
This is the formula that is used to create the PK:
((1000000000)*rand((datepart(month,getdate())*(100000)+datepart(second,getdate())*(1000))+datepart(millisecond,getdate())))
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
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 24086215
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
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:arthrex
ID: 24097121
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
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 24097350
could you send me the package and excel file?!
regards,
Pedro
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 24104016
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.
0
 

Author Comment

by:arthrex
ID: 24104448
As I already said,
I didn't create the DB and I cannot just change the primary key calculation
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 24106206
arthrex,
What you need is to create a unique with during the data flow for primery key, correct?
Cheers
Pedro
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 24106224
Check attached image
SSIS-Interface.JPG
0
 

Author Comment

by:arthrex
ID: 24106465
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((datepart(month,getdate())*(100000)+datepart(second,getdate())*(1000))+datepart(millisecond,getdate())))
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!
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 24107284
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.
0
 

Author Comment

by:arthrex
ID: 24107304
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.

0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 24107652
From an OLE DB Destination you can connect the Red Arrow to an Error output.  What am I missing.
0
 

Accepted Solution

by:
arthrex earned 0 total points
ID: 24867601
I couldn't solve it in SSIS.
So I just wrote a program that did all that.
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question