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 ?

Can you show the tasks that actually consume the text file and insert into the table? — digital.aaron, Jan 09 '18 at 21:40
@digital.aaron the situation is clear, he is reading the Flat file Source as a fixed width columns — Hadi, Jan 09 '18 at 21:54
@tanu this is what i mentioned in my question you are using "Ragged right" flat file source which is the same as fixed width. check my answer update for more info — Hadi, Jan 09 '18 at 22:35
@Hadi The flat file source screenshot wasn't included in the original version of the question. — digital.aaron, Jan 09 '18 at 22:48
@Hadi - is there any work around...where it can throw error in such case — Tara, Jan 09 '18 at 23:07
and @digital.aaron - is there any work around...where it can throw error in such case — Tara, Jan 09 '18 at 23:07
The only way I know would be to use a delimited format for your flat file. Why do you want the task to fail in this case? — digital.aaron, Jan 09 '18 at 23:22
@tanu you have to use delimited format, and read each row as one column `(DT_WSR(4000))` and check if the length is > 21 then throw an error else split the column using derived columns transformation (`SUBSTRING` function) — Hadi, Jan 09 '18 at 23:30
@digital.aaron - so, the source file is a text file and destination is a table on sql server. I just want it to fail whenever it comes in a format that is not supposed to...does it make sense ? — Tara, Jan 10 '18 at 15:36
@Hadi - sure. I'll def try it out. I am not sure how it works but I am going to try — Tara, Jan 10 '18 at 15:40
@Hadi - can you explain about it in much more detail. I am not really sure how this can be done on derived column transformation. Thank you — Tara, Jan 10 '18 at 16:21
@tanu i edited my answer 2 days ago, did yoy try my suggestions? — Hadi, Jan 13 '18 at 18:12
@Hadi - I tried but not really sure about how to do it. i'll try again and let you know if it works. Thanks for all the help :) — Tara, Jan 16 '18 at 21:56

Hadi · Accepted Answer · 2018-01-10T23:02:52.813

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:

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

In the Flat File Connection manager use the Delimited Format
Go To the Advanced Tab, Delete all Columns Except one column
Change the Datatype to DT_WSTR and the length to 4000
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

Use the Conditional Split to filter rows based on the length of the column:
```
LEN([Column0]) == 22
```

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

Specify the following Derived Columns

Map the result to the OLEDB Destination

score 2 · Answer 2 · answered Jan 09 '18 at 21:46

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.

score 0 · Answer 3 · answered Jan 09 '18 at 21:48

You need to update your SSIS package to accommodate added length.

For this, right click on your target Destination - Show advanced editor:

Check and fix the length of the internal column:

And lastly check the length of external column:

Hope this help :)

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

3 Answers3

Why package is not throwing an exception?

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