Problem creating Oracle sequence numbers in SSIS 2005
-
Having issues trying to create unique Oracle sequence numbers within a package in SSIS (2005). Business scenerio is as follows:
Read in a text file, do some transformations and output rows to an Oracle table with unique sequence numbers. Here is what I am doing.
Tasks within my Data Flow task:
1) Flat file source
2) Transformations
3) Ole DB command - update an SQL table which contains the sequence number. Sequence number is incremented for each record read.
4) Lookup - do a lookup to above SQL sequence table above to get the last generated sequence number.
5) Ole DB destination - output the rows to Oracle.
The problem is that the lookup always brings back the last sequence number that was created and my Oracle sequence column values contain all the same value. This appears to be caused by the fact that the tasks within my data flow perform set operations. So for example if I am processing 100 records, the Ole DB command gets executed 100 times and the value the Lookup transformation retrieves for the sequence number is 100.
Does someone have any ideas on how generate the unique seq number for each record read and pass the unique sequence number to the Oracle sequence number column for each text file record read?
-
A link that will help: http://www.sqlis.com/default.aspx?37
Another one: http://www.sqlis.com/default.aspx?93
-Jamie
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson -
Hi Walter,
Why not just call an Oracle sequence through your linked server?
SELECT isnull(ID,0) as "ID" FROM OPENQUERY(TLSTUK,'SELECT tls.seq_pcks_id.nextval as "ID" FROM sys.dual;')
That works for SQL 7 and 2000, I use it all the time, and if your tables are getting populated from somewhere other than your integration as well then all the sequences will be coming from the same place.
I'm thinking in SSIS you just add this as another datasource and then merge it with your recordset
Regards
Richard
-
Richard,
Good idea. However I read somewhere recently (darned if i can find where - google hasn't helped) that there is a huge performance degredation when you do that. Generating them in SSIS is much quicker.
Also, this guy has some experience of them as well: http://microsoftdw.blogspot.com/2005/11/ssis-and-oracle-sequences.html
Walter,
That's just a heads-up. Not a recommendation.
-Jamie
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson -
That's a great link Jamie, thanks, that guy's blog is really handy for Oracle / SSIS.
Regards
Richard
-
Yeah, Scott is top class. I was in touch with him yesterday to ask him why he's stopped blogging and he muttered something about the american cycling season starting.
He says he will be starting again soon though.
-Jamie
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply