September 6, 2005 at 1:33 pm
I see the option to "Keep NULLS" in the Transform Data Task Properties, but it does not work. Is there a secret to this? I am trying to simply export the data in SQL 2k table to a text file. Instead of "" I want it to be <NULL> like in the original table.
September 6, 2005 at 2:20 pm
That option only works if the destination is a SQL Server table. I'm not familiar with retaining nulls in a text file. I'll have to research a bit. Maybe someone else has an idea.
Greg
Greg
September 6, 2005 at 2:21 pm
Null in sql server is a "Special" Value,
Null means there is no value, Text files do not have this "Special" value. Sql has to represent it with "" blank string. If you want the value to be something other than a blank string, aka '<Null>' then you should put a coalesce statement in your output select.
Coalesce(Myfield,'<NULL>') which means a string = "<NULL>",
September 6, 2005 at 4:27 pm
Thanks for your time and answer. However, what I need is to pass nothing where NULL.
In other words, what I have now is this:
SQL Table: Somevalue, Somevalue, <NULL>, SomeOtherValue (All fields varchar)
Output:"Somevalue", "Somevalue","","SomeOtherValue"
What I want to output is this:"Somevalue", "Somevalue",,"SomeOtherValue"
Any ideas
September 7, 2005 at 1:19 am
Hi Tony,
The problem is that you are using quotes to denote string values, and that is why you get the "" (empty string) in the text file. If you set the text qualifier to <nothing> you will get the desired result.
I never use text qualifiers, as it is easy enough to make provision for text values when you import or view it again.
Hope this helps,
Martin.
September 7, 2005 at 7:21 am
You could always "cheat" and use SELECT ISNULL(somefield,'<NULL>') FROM
Good Luck,
Darrell
September 7, 2005 at 8:27 am
Thank you again for your thoughtful responses.
I have to do this for a company that is then converting the data to XML. They require this delimiter and there is a good reason peculiar to the project that requires us to pass nulls instead of empty strings.
What I literally need is nothing between the commas delimiting the fields. I ended up using an application called "Actual Search and Replace" It has command line functionality and it looks for ,"", and replaces it with ,,.
It is a hack, but the data is right.
If any one cracks this "nut" the "right" way please post it here.
Thanks again
Tony
September 7, 2005 at 11:22 am
You can turn off quotes for the specified column and use ActiveX in your Transform Data task to place quotes around data and no quotes around null fields.
Go to Properties in the Transform Data task and choose the "Destination" tab. Click the "Define Columns" button. A window will open. From here you can choose whether each column is quotable. Go to the column that contains the null values and change the type to "not quotable". Choose "Execute" and close the window.
Go to the "Transformations" tab and select the Delete All button to remove the current links. Click the New button. Choose "ActiveX Script". Go to the "Source Columns" tab, remove any columns currently in the "Selected Columns" window and then add all columns by choosing the ">>" button. Go to the "Destination Columns" window and remove any columns currently in the "Selected Columns" window and then add all columns by choosing the ">>" button. Go to the "General" tab and choose "Properties". In this ActiveX script window you will need to add a function to accomodate your requirements. I have provided and example below of what your script should look like when done:
Function Main()
DTSDestination("Col004") = DTSSource("Col004")
DTSDestination("Col003") = null_field(DTSSource("Col003"))
DTSDestination("Col002") = DTSSource("Col002")
DTSDestination("Col001") = DTSSource("Col001")
Main = DTSTransformStat_OK
End Function
Function null_field(source_col)
If isnull(source_col) Then
source_col = null
Else
null_field = """" + (source_col) + """"
End If
End Function
Your results should look like this (sample of 2 rows - one with char data and one with a null value):
"SomeValue","SomeValue2","CharValue","SomeValue3"
"SomeValue","SomeValue2",,"SomeValue3"
I hope this helps.
Jamie
September 9, 2005 at 10:08 am
Perfect Thanks
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply