Set destination column to NULL if incorrect data type?

#1

Is there a way to set the destination column to NULL if there is a data type
mismatch, like trying to insert a string into a numeric type column? Thanks.

#2

in the ActiveX, you can put

dim checking

checking = DTSSource("Col002")
if isnumeric(testing) = false Then
DTSDestination("test2") = null
Else
DTSDestination("test2") = DTSSource("Col002")
End if

Ed

"Stefan Wrobel" wrote:

Quote:

Is there a way to set the destination column to NULL if there is a data type
mismatch, like trying to insert a string into a numeric type column? Thanks.

#3

Right, but the data type of the destination column varies, so it won't always
be isnumeric that i'm checking for. Thanks for the response, any other ideas?

"Ed" wrote:

Quote:

in the ActiveX, you can put

dim checking

checking = DTSSource("Col002")
if isnumeric(testing) = false Then
DTSDestination("test2") = null
Else
DTSDestination("test2") = DTSSource("Col002")
End if

Ed

"Stefan Wrobel" wrote:

Is there a way to set the destination column to NULL if there is a data type
mismatch, like trying to insert a string into a numeric type column? Thanks.

#4

I do not think this will be possible because as you say you have no idea what is the source and what is the destination datatypes.
Also you may not want to abort the insert because the datatypes and data may be implicitly convertable

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com

"Stefan Wrobel" <StefanWrobel (AT) discussions (DOT) microsoft.com> wrote

Quote:

Right, but the data type of the destination column varies, so it won't always
be isnumeric that i'm checking for. Thanks for the response, any other ideas?

"Ed" wrote:

in the ActiveX, you can put

dim checking

checking = DTSSource("Col002")
if isnumeric(testing) = false Then
DTSDestination("test2") = null
Else
DTSDestination("test2") = DTSSource("Col002")
End if

Ed

"Stefan Wrobel" wrote:

Is there a way to set the destination column to NULL if there is a data type
mismatch, like trying to insert a string into a numeric type column? Thanks.

#5

Isn't there just some exception I can catch or something? I just wish this
was .Net...

"Allan Mitchell" wrote:

Quote:

I do not think this will be possible because as you say you have no idea what is the source and what is the destination datatypes.
Also you may not want to abort the insert because the datatypes and data may be implicitly convertable

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com

"Stefan Wrobel" <StefanWrobel (AT) discussions (DOT) microsoft.com> wrote

Right, but the data type of the destination column varies, so it won't always
be isnumeric that i'm checking for. Thanks for the response, any other ideas?

"Ed" wrote:

in the ActiveX, you can put

dim checking

checking = DTSSource("Col002")
if isnumeric(testing) = false Then
DTSDestination("test2") = null
Else
DTSDestination("test2") = DTSSource("Col002")
End if

Ed

"Stefan Wrobel" wrote:

Is there a way to set the destination column to NULL if there is a data type
mismatch, like trying to insert a string into a numeric type column? Thanks.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Set destination column to NULL if incorrect data type?

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts

Set destination column to NULL if incorrect data type? - 04-15-2005 , 10:43 AM

RE: Set destination column to NULL if incorrect data type? - 04-17-2005 , 02:57 AM

RE: Set destination column to NULL if incorrect data type? - 04-18-2005 , 11:21 AM

Re: Set destination column to NULL if incorrect data type? - 04-18-2005 , 01:45 PM

Re: Set destination column to NULL if incorrect data type? - 04-18-2005 , 02:11 PM