0

My question is about using "Derived Column Transformation".

Situation:

A row already retrieved from flat file successfully. I am trying to derive multiple columns by using "Derived Column" and substring function. There are as many as 36 columns that I need to derive from the given string. Issue occurs when after deriving first 3 columns from the row, substring() somehow cannot read row or derive further columns.

Input Row: (Total length of each row would be 400 characters)

ANICHOLLS, JASON, ROBERT 19700613136643-590 AB5731 MAIDSTONE CRES NE CALGARY T2A4C3113 GAST A00405860F DRUPDRUMHELLER TSA131(1)(G) 20030520 OPERATE OVERWEIGHT VEH 2007061801055STRSTRATHMORE 00005750{BBB235 AB0020070625 200707191005209

Effort to Derive column: enter image description here trying to see output while debugging

Am I trying wrong syntax here?

Binoy
  • 390
  • 5
  • 19
  • 2
    If you know your column widths, you should change your flat file connection to a fixed-width format and scrap trying to parse out the columns yourself. Use the tools to make your work easier, not more complicated. – digital.aaron Oct 02 '18 at 19:40
  • I agree with @digital.aaron Use the fixed width importer. It will also let you choose the correct data types. However, you should be able to do exactly what you are attempting without any issues. I've done what you are doing only when there are multiple row types. Like Header and details in the same file. – KeithL Oct 02 '18 at 19:59
  • I have already used fixed-width format and so it shows me correct number of rows as well while deriving columns from them. @digital.aaron, Can you further elaborate or show reference which tools could I use for easy derivation? – Binoy Oct 02 '18 at 20:03
  • 2
    @Binoy here is an example https://stackoverflow.com/questions/10289640/how-to-import-a-fixed-width-flat-file-into-database-using-ssis – KeithL Oct 02 '18 at 20:04
  • @KeithL, +1. your response helped. Just for knowledge, what if the column width is known but values may or may not appear in file to derive particular column! – Binoy Oct 02 '18 at 21:06
  • fixed width files typically are whitespace (or zero for numeric) padded. if you have inconstant widths then you have to conditionally split and have a split like you currently started you project with or you can save the split files and use the GUI tool on each file type in it's own data flow.. – KeithL Oct 03 '18 at 13:20
  • @Binoy, by "tools" i was referring to the built-in functionality of BIDS/SSDT. In software development in general, and SSIS in particular, there are often several completely different approaches to solving a problem. Yes, you can build your own fixed-width file parser in SSIS, or you can use the one that comes standard. Both can theoretically solve the problem, but one takes much less effort to implement. – digital.aaron Oct 03 '18 at 15:32

0 Answers0