SQL Server Performance

Convert fixed width fields to null when empty

Discussion in 'SQL Server 2005 Integration Services' started by Malcatrazz, Apr 10, 2008.

  1. Malcatrazz New Member

    Hi there,
    I am creating an SSIS package for importing an external text file. The file contains fixed-width fields (padded with spaces). Most of these fields are nullable.
    My problem is how do I import to nullable integer type database fields.
    I've added a Flat File Connection object, referenced the file and created the column definitions. Since all fields contain spaces I've defined all columns as string (defining them as int types gives me errors).
    Next I use a Flat File Source that references the source.
    Now comes the tricky part: I use a Derived Column Transformation Editor to transform my string column to a nullable column like this:
    RTRIM([originalfield])==""? NULL(DT_I2) : (DT_I2) RTRIM([originalfield])
    My question is, is there an alternative (more elegant) solution to this? I have many of this type of column and I don't want to repeat this for each column.
    Cheers,
    Michel
  2. satya Moderator

    If the format is fixed for these columns, then you can relevant column to convrt.

Share This Page