CODEWITHSUNDEEP
ssis
Adam McC
Adam McC

Reputation: 213

process csv File with multiple tables in SSIS

i trying to figure out if its possible to pre-process a CSV file in SSIS before importing the Data into SQL.

I currently receive a file that contains 8 tables with different structures in one flat file.

the Tables are identified by a row with the Table name in it encapsulated by Square Brackets i.e. [DOL_PROD]

the the data is underneath in standard CSV format. Headers first and then the data.

the tables are split by a blank line and the process repeats for the next 7 tables. 

[DOL_CONSUME]
TP Ref,Item Code,Description,Qty,Serial,Consume_Ref
12345,abc,xxxxxxxxx,4,123456789,abc

[DOL_ENGPD]
TP Ref,EquipLoc,BackClyLoc,EngineerCom,Changed,NewName

is it posible to split it out into seperate CSV files? or Process it in a loop?

i would really like to be able to perform this all with SSIS automatically.

Kind Regards,

Adam

Upvotes: 1

Views: 1420

Answers (1)

AdamL
AdamL

Reputation: 13191

You can't do that by flat file source and connection manager alone. There are two ways to achieve your goal:

You can use Script Component as source of the rows and to process the files, and then you'd do whatever you want with a file programatically.

enter image description here

The other way, is to read your flat file treating every row as a single column (i.e. without specifying delimiter), and then, via Data Flow Transformations, you'd be splitting rows, recognizing table names, splitting flows and so on.

I'd strongly advise you to use Script Component, even if you'd have to learn .NET first, because the second option will be a nightmare :). I'd use Flat File Source to extract lines from file as single column, and thet work it in Script Component, rather than reading a "raw" file directly.

Here's a resource that should get you started: http://furrukhbaig.wordpress.com/2012/02/28/processing-large-poorly-formatted-text-file-with-ssis-9/

Upvotes: 1

Related Questions