September 16, 2010 at 8:56 pm
I am trying to export the SQL Server 2005 database tables(10) to csv files.
But some of the tables columns data have NULL values. I need to replace the NULL values with empty space. Easy way to find and replace in csv file.
I tried to write the T-sql statement using ISNULL function. But I could see 0 in NULL place.
Is there any way in SSIS where I can get it done? Appriciate your help.
September 16, 2010 at 9:39 pm
what was the statement that you used for isnull?
What is the datatype that has null values in the database?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 16, 2010 at 9:44 pm
laddu4700 (9/16/2010)
I am trying to export the SQL Server 2005 database tables(10) to csv files.But some of the tables columns data have NULL values. I need to replace the NULL values with empty space. Easy way to find and replace in csv file.
I tried to write the T-sql statement using ISNULL function. But I could see 0 in NULL place.
Is there any way in SSIS where I can get it done? Appriciate your help.
You'll need to convert all numerics to strings if you want ISNULL(somenumber,'') to actually convert to an empty space.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 16, 2010 at 9:47 pm
I used below query
USE dbname;
GO
select column1, column2, ISNULL(Column3, ' ') AS 'xxxx'
FROM Table1;
GO
column3 (FK, int, null)
September 16, 2010 at 11:04 pm
Jeff's response should help you fix that. You can use the convert function in TSQL to change the int data type to a varchar or char (string) datatype so you can display an empty string rather than a 0.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 17, 2010 at 3:07 am
CirquedeSQLeil (9/16/2010)
Jeff's response should help you fix that. You can use the convert function in TSQL to change the int data type to a varchar or char (string) datatype so you can display an empty string rather than a 0.
And to be complete so the OP knows why this is happening: an empty string is converted to a 0 when you cast it to an int.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 17, 2010 at 1:20 pm
I created below script and added to source in SSIS package, destination I have taken csv file.
when I execute the package I am getting below error.
Column 'OfficePhone' cannot convert between unicode and non-unicode string data types
SELECT [UserID]
,[UserName]
,ISNULL(Cast(OfficePhone AS VARCHAR(10)), '') AS 'OfficePhone'
,ISNULL(Cast(OfficeFax AS VARCHAR(10)), '') AS 'OfficeFax'
,CONVERT(varchar(10), DateCreated, 110) + SUBSTRING(CONVERT(varchar, DateCreated,
109), 12, 9) + SUBSTRING(CONVERT(varchar, DateCreated, 109),25,2)as DateCreated
,CONVERT(varchar(10), ModifiedDate, 110) + SUBSTRING(CONVERT(varchar, ModifiedDate,
109), 12, 9) + SUBSTRING(CONVERT(varchar, ModifiedDate, 109),25,2)as ModifiedDate
,[UserZipcode]
FROM [DBName].[dbo].[Table1]
Please advice,whether I need to add a transformation to get it done.
September 17, 2010 at 1:30 pm
Yes, I would add a transformation task and include all of the columns in this case.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 17, 2010 at 1:42 pm
CirquedeSQLeil (9/17/2010)
Yes, I would add a transformation task and include all of the columns in this case.
Thanks for your reply. Can you please explain in details, what transformation need to add and how to map.
Kind of new to SSIS packages. Appreciate your help.
September 17, 2010 at 3:18 pm
Try this as a starting point
http://www.bimonkey.com/2009/06/the-data-conversion-transformation/
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply