Can anyone help with handling nulls or blank data being imported from foxpro
..dbf files into a normalized sql database? The data insert tasks are failing
because the normalization in sql server requires a value in the incoming
data, where this data was optional.

Thanks in advance...
Frango

why dont you check something like this

IF Trim(DTSSource("columnname")) <> "" Then
transform data
end if

"Frango" <f_gofa (AT) hotmail (DOT) com> wrote

Thanks Krish,
I am going to try that, its just that I can't seem to find the correct
dialog in the DTS designer to accomplish this. If I create a new package
using the wizard, I can see where this would occur, but I, so far, have not
figured out where the corresponding dialog is accessed in the designer...

Frango

"Krish" <NOspam (AT) Nospam (DOT) org> wrote

Does Foxpro support the syntax of something similar to

ISNULL(<col>,0)

i.e. If this col is NULL then substitute a 0.

And to get around the "" then you could do

NULLIF(<col>,"")

Combine them together and you get

ISNULL(NULLIF(<col>,""),0)


The solution offered by Krish I am not sure I understand but you would want
to use an Active Script transform and check for nulls there. If you find
one in the attribute then you could substitute a value instead.

Have a look on page 12 of this article

Building a Package in the DTS Designer
(http://www.sqldts.com/default.aspx?278)

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know


"Frango" <f_gofa (AT) hotmail (DOT) com> wrote

Let me add to this that the reason for this custom transform is because the
CSV data contains periods Chr(46) inplace of the NULL values Chr(0). I want
to preserve any NULL values in the data sets imported.

"Jamie Carper" wrote:

I have a data transformation where the source column is a number value from a
comma delimited text file and the destination column is to a SQL table where
the field is an integer type that accepts NULL values.

Given the example code below I should be able to complete the above
transformation. Using the "TEST" option within the ActiveX Script object it
works without a single error.

However when I attempt to execute the Transformation object of which calls
the code below, I get the following error: "...invalid data value for
'Col007' destination column".

'************************************************* *******
Function Main()
DTSDestination("Col007") = Replace(DTSSource("Col007"), Chr(46), Chr(0))
Main = DTSTransformStat_OK
End Function
'************************************************* *******

Also I get the same error message if I use "" instead.

I cannot seem to find a workaround for what seems to me to be a bug in the
DTS.

Can anyone offer up a solution? I cannot alter the incoming data prior to
the import.