Trying to Export data from sql server db 2005 to flat file and replacing null with ''

• kathy.plamann

  Mr or Mrs. 500

  Points: 536

  More actions

  July 27, 2015 at 8:43 am

  #302801

  Hi

  I am trying to use SSIS 2005 to export data from a sql server 2005 database to a flat file, comma delimited. The customer wants and NULL values to be blank. I created the script below and obtained the required results within SSMS 2005 but when I tried to export to the flat comma delimited file using SSIS no rows were exported. The job finished successfully but 0 rows were written. Could anyone give me insight into why 0 rows were written when there are 55 rows in this table?

  Thanks

  Kathy

  USE xxdb;

  SELECT

  xxID,

  xxType,

  xxDate,

  xxUser,

  ISNULL(CONVERT(varchar(10), xyzDate, 121), '') AS xyzDate,

  ISNULL(xyzUser, ' ') AS xyzUser,

  ISNULL(xxxMethod,'') AS xxxMethod,

  ISNULL(xyzType,'') AS xyzType,

  ISNULL(xyzRegion,'') AS xyzRegion,

  ISNULL(xyzOffice, '') AS xyzOffice,

  ISNULL(Duration, '') AS Duration,

  ISNULL(zzzType,'') AS zzzType,

  ISNULL(bbbType,'') AS bbbType,

  ISNULL(Comments,'') AS Comments

  FROM dbo.CustomerService;

• Luis Cazares

  SSC Guru

  Points: 183698

  More actions

  July 27, 2015 at 9:18 am

  #1815164

  Are you pointing to the correct database/server?

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• kathy.plamann

  Mr or Mrs. 500

  Points: 536

  More actions

  July 27, 2015 at 9:28 am

  #1815166

  Yes I have the correct server and database selected. When I do the preview on the OLE DB Source Editor I see the data but when I try and look at the

  preview for the Flat File Destination I just see column headers without data.

  Thanks

  Kathy

• kathy.plamann

  Mr or Mrs. 500

  Points: 536

  More actions

  July 27, 2015 at 10:07 am

  #1815185

  I found the answer--I took any semi-colons off my statements and the 55 rows exported.

  Thanks

  Kathy

• Luis Cazares

  SSC Guru

  Points: 183698

  More actions

  July 27, 2015 at 10:12 am

  #1815187

  That's weird.

  I suppose that it just stopped at the first semicolon. I generally avoid using "USE database_name" and that's probably why I've never encountered that problem.

  Thank you for sharing the solution.

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2