Import Flat file to SQL - No error thrown after adding a character

Question

I am working on a package that loads data from a text file onto a table on SQL server.

The text file has data in the below format

I am including the flat file connection manager here -

I have created a test table

executed the package and moved data onto the table.

And, then edited one of the column to include more characters (included s in the second column)

and instead of the package erroring out..it ran successfully and moved the extra characters to the next column.

Can someone help me out here ?

Answer 1

Why package is not throwing an exception?

This is normal, you are reading the Flat file Source as a fixed width columns, (Fixed width or Ragged right) with the following specifications.

"Ragged Right", which is exactly the same as "Fixed Width", except that it gives you the option to insert a linefeed character (or CRLF, etc.) at the end of each line of data.

(You can check the Flat File connection manager to see the specifications)

Initial State

Data:

0001aijn fkds jmcl wuj

Specifications and Result :

Col001: From 0 -> 3      '0001'
Col002: From 4 -> 7      'aijn'
Col003: From 8 -> 11     ' fkd'
Col004: From 12 -> 13    's '
Col005: From 14 -> 18    'jmcl '
Col006: From 19 -> end   'wuj'

Second State

So when you added a S character the data looks like:

0001aijns fkds jmcl wuj

Specifications and Result :

Col001: From 0 -> 3      '0001'
Col002: From 4 -> 7      'aijn'
Col003: From 8 -> 11     's fk'
Col004: From 12 -> 13    'ds'
Col005: From 14 -> 18    ' jmcl'
Col006: From 19 -> end   ' wuj'

For more informations:

• How to import a fixed width flat file into database using SSIS?
• SSIS Flat File Export - "Fixed Width" vs "Ragged Right"

---

Is there any work around...where it can throw error in such case?

1. In the Flat File Connection manager use the Delimited Format
2. Go To the Advanced Tab, Delete all Columns Except one column
3. Change the Datatype to DT_WSTR and the length to 4000
4. In the Data Flow Task add a Flat File Source, Conditional Split, Script Component, Derived Column Transformation, OLEDB Destination as Shown in the image below

5. Use the Conditional Split to filter rows based on the length of the column:

   LEN([Column0]) == 22
   

6. In the Script Component write the Following Code (used to raise error if there is a row of length > 22):

   Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
   
       Throw New Exception("Length constraint violated")
   
   End Sub
   

7. Specify the following Derived Columns

8. Map the result to the OLEDB Destination

Answer 2

I don't see any difference in the behaviour of the two cases. Both times it is reading 4 characters from the input file and putting them in column 1, reading the next 4 characters into column 2, the next four characters into column 3, then 2 characters into column 4, 5 characters into column 5, and finally 4 characters (if there are that many left) into column 6. Note that it is counting the spaces as characters, so in the first run it is including the space between aijn and fkds at the beginning of the entry in column 3, whereas in the second run the extra s is now the first character in column 3 and the space becomes the second character.