Post reply

Handling empty quotes

  • June 8, 2007 at 7:48 am

    #117277

    I previously posted this in the Data Warehouse -- Integration Services but haven't gotten a response.

    We are still in testing to to convert off of SQL2K to SQL2K5. We run a monthly DTS import that once we had setup works fine. Now we want to convert them to true SSIS when we get the production servers upgraded. We know we can still use the DTS for now.

    The problem: Our data is exported from another system, that when it has a blank/null date it exports it as an empty pair of double-quotes (""). SSIS is choking on this, but DTS has no problem with it. We can not change the remote systems export.

    Re-processing the file(s) to clean them up is not really an option. Some of these files are over 80K rows of data. An example of the data is below.

    M Account Appl Id Address,M Application Code,M Card Name,M Card Type Code,M Date Closed,M Date Open,M Date Updated Last,Card Id,M Date Used Last,M Date Expiration
1,00,SMITH,5,"",02/22/2007,04/30/2007,9792,04/30/2007,01/01/2010
1,00,JONES,5,"",02/22/2007,04/30/2007,9636,04/30/2007,01/01/2010"
1,00,DOE,5,"",02/22/2007,04/30/2007,9379,04/30/2007,11/01/2009
0,00,SMYTHE,5,06/03/2004,03/02/2004,07/26/2004,3225,06/03/2004,03/01/2007
0,00,GONZALES,5,06/03/2004,04/12/2004,07/26/2004,7159,06/03/2004,04/01/2007
0,00,KIM,1,06/03/2004,05/06/2002,07/26/2004,5972,06/03/2004,12/31/2049
0,00,WANG,5,06/04/2004,03/18/2004,07/26/2004,9859,06/04/2004,03/01/2007
0,00,SCHMIDT,5,07/07/2004,06/29/2004,07/26/2004,6158,"",06/01/2007
0,00,FRANKLIN,5,07/09/2004,08/12/2003,07/26/2004,9693,"",08/01/2006
0,00,COVEY,5,07/12/2004,12/02/2003,07/26/2004,9561,"",12/01/2005

    Any Ideas?



    ----------------
    Jim P.

    A little bit of this and a little byte of that can cause bloatware.

  • June 8, 2007 at 12:46 pm

    #711782

    Yes, setup a DataFlow task which imports these files. Add a Connection to the Connection Managers for the File (you might need to setup a different Connection manager if there are different schemas for the files). In the File Connection, make sure the Columns are all setup. Next, go to the "Advanced" link on the left, and for all your Date Columns, change the DataType to "String" (DT_STR).

    Add a "Flat File Source" in your DataFlow that uses the Connection you created. Next add a "Derived Column" task to the DataFlow. Inside the Derived Column, set up a new column like so:

    Derived Column Name: (this should be a new Column name because you can't replace your existing String column with a NULL datetime)

    Derived Column:

    Expression: [MyDateString] == "" ? NULL(DT_DATE) : (DT_DATE)[MyDateString] (replace MyDateString with your DateColumn that's coming in as a string)

    Data Type: DT_DATE or DT_DBTIMESTAMP

    Now, that new column will be added in the Output for your subsequent Tasks. Use it instead of the column being imported. One thing you have to realize is that SSIS is very strict and doesn't do a lot of "thinking" for you anymore.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply