May 19, 2009 at 7:10 am
Hello,
I have a file text like on the folowing example:
1,x1,id1
2,x2,rep2
4,x6,rep1
2,x2,id2
2,x2,rep1
this means that the first line(id1) depends on the 2 next rows (rep2 and rep1)
and the id2 on the rep1
and i want to import it on sql server table like this:
id1 rep2
id1 rep1
id2 rep1
please can help me to make an SSIS package to do that?
May 19, 2009 at 7:13 pm
What is the significance of the first field (1, 2, 4, 2, 2)?
Is it true that the only thing linking rows 1 and 2 is the order in which they appear? I.e. a logical, rather than physical, link?
I think you'll have to use a script task to do this one ... either pre-process the file and write it in the format you suggest (then you can run a standard flat-file import on the file which you create), or do the whole import via script.
Phil
May 19, 2009 at 8:52 pm
If I interpret your request correctly, you want to read through the file and associate all of the repX records with the most recent idX record. If that is the case, you'll need to use a script component as Phil suggested.
I recently did a presentation about this type of ETL, you can download the code samples here: http://www.bucketofbits.com/resources.aspx, under "Unconventional ETL Using SSIS Scripting". Take a look at Demo 5 (the sample file as well as the script component in the SSIS task). It's important to note that you'll need to configure multiple outputs for your script task.
hth,
Tim
Tim Mitchell
TimMitchell.net | @tmitch.net | Tyleris.com
ETL Best Practices
May 19, 2009 at 9:54 pm
Nice sample code, cheers Tim.
May 20, 2009 at 4:42 am
thank you very much, its exactly what i looking for.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply