October 15, 2009 at 8:50 pm
I am looking to build a process using SSIS that will read in a flat text file, and for each row parse through that line and place data in an appropriate table.
Specifically, each row has data for one table, and then multiple columns that will need to be translated into multiple rows in another table. I am hoping that I can use SSIS instead of making a custom app; aren't you supposed to be able to get behind the scenes and use some C#/.net logic to code special logic?
Are there any suggestions on how to do this with SSIS? I have a flat file datasource, and my SQL Server destination, but can I route the data from the source to multiple locations? Or is there a different way altogether I should be tackling this?
Thanks for any insight!
October 15, 2009 at 11:12 pm
You might be able to use a conditional split transformation for this purpose. This object will take an input stream and will split it out to two or more outputs based on criteria defined in an expression.
Note that if the metadata differs between the various rows, you'll either have to use one or more scripts or carefully-crafted derived column transformations after the conditional split to compensate for the metadata differences.
hth,
Tim
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
October 21, 2009 at 8:58 am
Thanks! I actually think the script component looks sort of like what I'm looking for...it looks like I can manipulate incoming rows and send them to multiple outputs.
What I'm running into is how do I fully work with the row. Basically for the overall file I need a single row inserted into a "file" table, and then for each row I'll have 1-16 rows inserted into 2 other tables - so I really need multiple output cycles for each row. I'm working on it but not finding a lot of documentation.
October 21, 2009 at 9:02 am
That's too complex for anything but the Script Component - I suggest you concentrate your efforts there.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 21, 2009 at 2:44 pm
I think I've got it now where the script component is doing exactly what I want.
One thing - I notice that intellisense will off and on stop working when editing the script code. Any ideas why? I can't seem to place a reason behind it; usually it's after I add some output columns, I'll go in to use them and the intellisense is broken. I can still run the package, though, and it'll work. Is there a "reset intellisense" button somewhere? 🙂
October 21, 2009 at 7:53 pm
Can't say that I've encountered that. Intellisense may not work if you've got a syntax error somewhere above where you're working, but if that was the case, your code would likely not compile. What version of BIDS are you working with?
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
October 21, 2009 at 8:24 pm
VS2008, I'll play with it some more tomorrow at work and see if I can figure out what's going on...
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply