Can SSIS support loading of files with varying column lengths in each row?

2
votes

Currently I receive a daily file of around 750k rows and each row has a 3 character identifier at the start.

For each identifier, the number of columns can change but are specific to the identifier (e.g. SRH will always have 6 columns, AAA will always have 10 and so on).

I would like to be able to automate this file into an SQL table through SSIS.

This solution is currently built in MSACCESS using VBA just looping through recordsets using a CASE statement, it then writes a record to the relevant table.

I have been reading up on BULK INSERT, BCP (w/Format File) and Conditional Split in SSIS however I always seem to get stuck at the first hurdle of even loading the file in as SSIS errors due to variable column layouts.

The data file is pipe delimited and looks similar to the below.

AAA|20180910|POOL|OPER|X|C
SRH|TRANS|TAB|BARKING|FORM|C|1.026
BHP|1
*BPI|10|16|18|Z
BHP|2
*BPI|18|21|24|A

(* I have added the * to show that these are child records of the parent record, in this case BHP can have multiple BPI records underneath it)

I would like to be able to load the TXT file into a staging table, and then I can write the TSQL to loop through the records and parse them to their relevant tables (AAA - tblAAA, SRH - tblSRH...)

1
sql-serverssisetlflat-file
You could try this and see if it works for you : techbrothersit.com/2015/06/… Note that you will have to change the delimiter from "," to "|". – camba1
@camba1 based on that link there is no need for SSIS at all he can create a console application to do that. While there are plenty of solutions using SSIS – Yahfoufi
Thank you for this both - i'm not massively familiar with C# so for now wanted to do it through SSIS until I can understand the code my package would be running. – JbP
Use C# Source Component to split columns and direct output using a switch – KeithL
Thanks @KeithL , going to stick with SSIS approach until I am more familiar with C# but will definitely come back to this post when the time is right. – JbP

1 Answers

1
votes

I think you should read each row as one column of type DT_WSTR and length = 4000 then you need to implement the same logic written using vba within a Script component (VB.NET / C#), there are similar posts that can give you some insights: