Newbie question activex transform

Hi,

This may be incredibly stupid question but how do you execute a sql
statement from within a dts transform data task.

I'm transfering data from one table to another one on the same
database on sqlserver 2000. Most of the rows are copy columns but some
require some manipulations done on them so I'm using activex tasks to
do it.

I have to filter on a part number. There can be more than one row in
the source table with the same part number and I need to select the
earliest date field from the table for that part number.

"select min(first_date_time) from table_name where [part number] = '"
& DTSSource("part Number") & "'"

This is the sql statement i use. It gets parsed correctly and the part
number gets correctly assigned into the statement.

However I have no idea how to execute the statement inside the activex
script and assign the value returned to a variable name to be used
later in the script.

Can someone enlighten me on if it can be done and if not how I amy
achieve a result by other means if it can't

Thanks.It's ok, I discovered what lookup queries are :)

On Sat, 06 Sep 2003 14:54:04 +0100, Mirth1314 <not@.ahope.net> wrote:

>Hi,
>This may be incredibly stupid question but how do you execute a sql
>statement from within a dts transform data task.
>I'm transfering data from one table to another one on the same
>database on sqlserver 2000. Most of the rows are copy columns but some
>require some manipulations done on them so I'm using activex tasks to
>do it.
>I have to filter on a part number. There can be more than one row in
>the source table with the same part number and I need to select the
>earliest date field from the table for that part number.
>"select min(first_date_time) from table_name where [part number] = '"
>& DTSSource("part Number") & "'"
>This is the sql statement i use. It gets parsed correctly and the part
>number gets correctly assigned into the statement.
>However I have no idea how to execute the statement inside the activex
>script and assign the value returned to a variable name to be used
>later in the script.
>Can someone enlighten me on if it can be done and if not how I amy
>achieve a result by other means if it can't
>Thanks.

Newbie - DTS Transformation Task

Hi all,

I have a problem. I am running a Transform Data Task between 2
sqlserver tables on the same database. Most of the transformations are
straight copy columns. However there are a few that require an entry
corresponding to a datediff between 2 dates in the source table.

I can't get this to work. I have no idea how to do it basically. Here
is what i'm trying to do but it generates all sorts of errors.

DTSDestination("column") = datediff(mi, DTSSource("column1"),
DTSSource("Column2"))

I'm sure this is fundamentally incorrect somewhere. I really don't
know what to do, I can pull the datediff value fine in Query anlyser
with this...

datediff(mi, convert(datetime, dateval1, 103), convert(datetime,
[dateval2], 103)) as RestoreTime

but that doesnt work in the activex script either.

Any help or pointers would be greatly appreciated.
Thanks all.Hi

I am not sure if mi is a valid unit type in VBScript, and it should be in
quotes!

http://msdn.microsoft.com/library/d...xconversion.asp

John

"Mirth1314" <not@.ahope.net> wrote in message
news:2av9lvsh4p0dglsunonm37rorhfo6tkimm@.4ax.com...
> Hi all,
> I have a problem. I am running a Transform Data Task between 2
> sqlserver tables on the same database. Most of the transformations are
> straight copy columns. However there are a few that require an entry
> corresponding to a datediff between 2 dates in the source table.
> I can't get this to work. I have no idea how to do it basically. Here
> is what i'm trying to do but it generates all sorts of errors.
> DTSDestination("column") = datediff(mi, DTSSource("column1"),
> DTSSource("Column2"))
> I'm sure this is fundamentally incorrect somewhere. I really don't
> know what to do, I can pull the datediff value fine in Query anlyser
> with this...
> datediff(mi, convert(datetime, dateval1, 103), convert(datetime,
> [dateval2], 103)) as RestoreTime
> but that doesnt work in the activex script either.
> Any help or pointers would be greatly appreciated.
> Thanks all.