DTS Parameter on Execute SQL Task

#1

First I am trying to use DTS for purging data. The first thing I want to do
is export the data to be purged to a csv file. I am using parameters to hold
variables such as dates.

The export routines work using transform data task using the following query:

select sub.* from proddta.f3102 as sub
join proddta.f4801 as main on sub.igdoco=main.wadoco
where main.watrdj<?

the parameter is named FIRSTOFLASTYEAR as an unsigned int 4 with a value of
104001 (our software uses integers to represent dates)

The next step I am trying to do is to use an execute sql task to delete the
affected data. The query is below:

delete sub from proddta.f3102 as sub
join proddta.f4801 as main on sub.igdoco=main.wadoco
where main.watrdj<?

When trying to assign the parameter I get an error saying "invalid object
name sub". It also won't parse the sql. However, if I replace the ? with
the 104001 then everything parses correctly.

What am I doing wrong?

Thanks in advance

Chris

#2

Don't parse it, it will still run correctly
Write a little test package to confirm that
Parameters work only with select statements if you try to parse
Parse with the select change the query to delete and just run the package

http://sqlservercode.blogspot.com/

"Chris" wrote:

Quote:

First I am trying to use DTS for purging data. The first thing I want to do
is export the data to be purged to a csv file. I am using parameters to hold
variables such as dates.

The export routines work using transform data task using the following query:

select sub.* from proddta.f3102 as sub
join proddta.f4801 as main on sub.igdoco=main.wadoco
where main.watrdj<?

the parameter is named FIRSTOFLASTYEAR as an unsigned int 4 with a value of
104001 (our software uses integers to represent dates)

The next step I am trying to do is to use an execute sql task to delete the
affected data. The query is below:

delete sub from proddta.f3102 as sub
join proddta.f4801 as main on sub.igdoco=main.wadoco
where main.watrdj<?

When trying to assign the parameter I get an error saying "invalid object
name sub". It also won't parse the sql. However, if I replace the ? with
the 104001 then everything parses correctly.

What am I doing wrong?

Thanks in advance

Chris

#3

this is probably a bug of sql parser. i remember old threads about this
issue.
first write a simple query , map parameters , then change your query. it
would work ,even when you get this error.
another solution would be creating the sql query in an activex script task
, then setting the query of execute sql task..

"Chris" <Chris (AT) discussions (DOT) microsoft.com> wrote

Quote:

First I am trying to use DTS for purging data. The first thing I want to
do
is export the data to be purged to a csv file. I am using parameters to
hold
variables such as dates.

The export routines work using transform data task using the following
query:

select sub.* from proddta.f3102 as sub
join proddta.f4801 as main on sub.igdoco=main.wadoco
where main.watrdj<?

the parameter is named FIRSTOFLASTYEAR as an unsigned int 4 with a value
of
104001 (our software uses integers to represent dates)

The next step I am trying to do is to use an execute sql task to delete
the
affected data. The query is below:

delete sub from proddta.f3102 as sub
join proddta.f4801 as main on sub.igdoco=main.wadoco
where main.watrdj<?

When trying to assign the parameter I get an error saying "invalid object
name sub". It also won't parse the sql. However, if I replace the ? with
the 104001 then everything parses correctly.

What am I doing wrong?

Thanks in advance

Chris

#4

this is probably a bug of sql parser. i remember old threads about this
issue.
first write a simple query , map parameters , then change your query. it
would work ,even when you get this error.
another solution would be creating the sql query in an activex script task
, then setting the query of execute sql task..

"Chris" <Chris (AT) discussions (DOT) microsoft.com> wrote

Quote:

First I am trying to use DTS for purging data. The first thing I want to
do
is export the data to be purged to a csv file. I am using parameters to
hold
variables such as dates.

The export routines work using transform data task using the following
query:

select sub.* from proddta.f3102 as sub
join proddta.f4801 as main on sub.igdoco=main.wadoco
where main.watrdj<?

the parameter is named FIRSTOFLASTYEAR as an unsigned int 4 with a value
of
104001 (our software uses integers to represent dates)

The next step I am trying to do is to use an execute sql task to delete
the
affected data. The query is below:

delete sub from proddta.f3102 as sub
join proddta.f4801 as main on sub.igdoco=main.wadoco
where main.watrdj<?

When trying to assign the parameter I get an error saying "invalid object
name sub". It also won't parse the sql. However, if I replace the ? with
the 104001 then everything parses correctly.

What am I doing wrong?

Thanks in advance

Chris

#5

Thank you very much for all the quick responses. That solution fixed the
problem!

Chris

"Chris" wrote:

Quote:

First I am trying to use DTS for purging data. The first thing I want to do
is export the data to be purged to a csv file. I am using parameters to hold
variables such as dates.

The export routines work using transform data task using the following query:

select sub.* from proddta.f3102 as sub
join proddta.f4801 as main on sub.igdoco=main.wadoco
where main.watrdj<?

the parameter is named FIRSTOFLASTYEAR as an unsigned int 4 with a value of
104001 (our software uses integers to represent dates)

The next step I am trying to do is to use an execute sql task to delete the
affected data. The query is below:

delete sub from proddta.f3102 as sub
join proddta.f4801 as main on sub.igdoco=main.wadoco
where main.watrdj<?

When trying to assign the parameter I get an error saying "invalid object
name sub". It also won't parse the sql. However, if I replace the ? with
the 104001 then everything parses correctly.

What am I doing wrong?

Thanks in advance

Chris

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

DTS Parameter on Execute SQL Task

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

DTS Parameter on Execute SQL Task - 10-17-2005 , 01:21 PM

RE: DTS Parameter on Execute SQL Task - 10-17-2005 , 01:51 PM

Re: DTS Parameter on Execute SQL Task - 10-17-2005 , 01:54 PM

Re: DTS Parameter on Execute SQL Task - 10-17-2005 , 01:54 PM

RE: DTS Parameter on Execute SQL Task - 10-18-2005 , 09:39 AM