I have a raw data file and it doesn't have headers or body.
The file rows
01abc ban cvs orangeapplebanana
02bcd ban cvs applebananaorange
03na ban cvs ornageapplebanana
04new row cvs strawberrylemon
05na wor svc appleorangebanana.
I want to create SSIS package to upload raw data Into SQL tables.
What I want to achieve is use 01 as header column and anything between 01 and 03 insert in table 1
Anything starting with 04 insert in table 2
Anything starting with 05 insert in table 3
What I would do is setup a file connection manager to read the full row as one field. Pass that to a script component and split the data in the script component to separate outputs based on the record identifier.
This will then create separate outputs for each record type which can then be directed to a different OLEDB Destination for each table.
With that said - it might be possible to just use a conditional split component and split the data based on the record identifier. But that requires that each record type has the same field definitions - and that can be defined in the file connection manager. I am assuming here that each record type has different fields - which would require separating the data based on each record types definition.
The final option would be to setup the file connection manager as a single field - load that field into a staging table in SQL and then use T-SQL to parse each record type into the final destination tables. If you have repeating sections - for example a new 01 record type in the same file, with following 02/03/04... records that need to be associated with the 01 record type - then you would need an additional computed row number type column added so you can associate the rows to the correct header row.
thanks alot I have used Conditional Split