June 6, 2007 at 12:12 pm
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.
July 9, 2010 at 1:29 pm
You can use a Derived Column task in your data flow to replace "" with whatever you wish. You may need to do a Data Conversion on that column if it isn't already a string based column.
July 9, 2010 at 11:13 pm
Well fortunately this is no longer my problem to solve. (No longer with the company.)
I went with a reprocess solution. I can't remember the exact, but I had it import and do a replace on the "" with a ''.
It worked, but only added seconds once optimized.
----------------
Jim P.
A little bit of this and a little byte of that can cause bloatware.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply