How to split the Records?

Question

How to split the Records?

Charmer

SSChampion

Points: 14903
More actions
November 15, 2011 at 3:13 am

#251899

Hi Friends,
i have a source which consists of 1500 records...
my need is that i have to split the records....first 500 records must go into a destination and the next 1000 records must go into another destination...
By using which, i can split the records?
Can any one help me, pls?
Thanks for any information...
Thanks,
Charmer

Viewing 9 posts - 1 through 8 (of 8 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Import all the records into a staging table, then use whatever logic is necessary to move the first 500 records to one table and the rest to the other.

John

Charmer SSChampion Points: 14903 More actions · Answer 2

I am not clear, John...

Please,Could you brief me..?

is there no transformation to split the records..?

Thanks,
Charmer

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

I don't know, because you haven't said on what criteria the record set will be split. There are transformations you may be able to use, such as the Conditional Split, but I prefer to use T_SQL to do the manipulation, which is why I suggested using a staging table.

If you provide a sample of your data (don't disclose anything confidential), showing which rows you'd move to which tables, we should be able to help you better.

John

Charmer SSChampion Points: 14903 More actions · Answer 4

if i had a condition, i would have used condition split....but the worst thing is i don't have any specific conditions....its just like that the first 500 records in one and then rest of it into another destination...

is this possible..?

Thanks,
Charmer

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

If you create your staging table with an identity column, you can do something like this:

INSERT INTO Table1 (colA, colB, colC,...)

SELECT (colA, colB, colC,...)

FROM MyStagingTable

WHERE IDCol <= 500

INSERT INTO Table2 (colA, colB, colC,...)

SELECT (colA, colB, colC,...)

FROM MyStagingTable

WHERE IDCol BETWEEN 501 AND 1500

If you do it like that, you can be sure you're not copying the same row to more than one table. I don't know how you'd do that just using SSIS transformations.

John

Charmer SSChampion Points: 14903 More actions · Answer 6

Hi John...I got something on this...but i am not sure how far this works...

if you want to see this , then here is the URL

http://www.sqlis.com/sqlis/post/Data-Generator-Source-Adapter.aspx

Thanks,
Charmer

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

If you think that would be useful, then by all means add it. But I wouldn't use third-party add-ins to achieve something that can be done with out-of-the-box functionality.

John

Charmer SSChampion Points: 14903 More actions · Answer 8

oh..yeh..i understood you ,but any way i used script component and then conditional split to separate the records..may be we don't want to go for staging tables...

Thanks,
Charmer