BAI2 File needs to be load into SSIS

Question

How can I load BAI2 file to SSIS? .BAI2 is an industry standard format used by the banks. Below is the one truncated example

01,021000021,CST_USER,110520,1610,1627,,,2/
02,CST_USER,089900137,1,110509,1610,,2/ 
03,000000370053368,USD,010,782711622,,,015,7620008 12,,,040,760753198,,/ 
88,043,760000052,,,045,760010026,,,050,760000040,, ,055,760000045,,/ 

Answer 1

Use a Flat file connection manager

I think you can import these files using a flat file connection manager, because they are similar to comma separated text, try to change the row delimiter and column delimiter properties to find the appropriate one.

From the example you mentioned i think you should use:

,  as Column delimiter
/  as Row delimiter 

To learn more about how to interpret a BAI2 file check the following link:

• EBS – How to interpret a BAI2 file

Based on this link:

The BAI2 file is a plain text file (.TXT Format), which contains values / texts one after the other.

Because the number of columns is not fixed among all rows than you must use define only one column (DT_STR,4000) in the flat file connection manager, and split columns using a Script Component:

• SSIS ragged file not recognized CRLF
• how to check column structure in ssis?
• SSIS : Creating a flat file with different row formats

Helpful links

• SQL SERVER – Import CSV File into Database Table Using SSIS
• Importing Flat Files with Inconsistent Formatting Using SSIS
• SSIS Lesson 2: First Package