DTS Package Parameter Issue

Question

DTS Package Parameter Issue

HookSQLDBA7

SSChampion

Points: 10555
More actions
April 6, 2004 at 7:44 am

#161789

In the Execute SQL Task Properties, I am trying to set up the following SQL Statement with an input parameter (GlbPrevBusDate).
INSERT INTO IndicativeHistory
SELECT LastUpdat = ?, * FROM IndicativeTemp
I have also tried the statement like this:
INSERT INTO IndicativeHistory
SELECT ? As LastUpdat, * FROM IndicativeTemp
I used the question mark but when I click on the Parameter Button, I get the following error:
"Error Source: Microsoft OLE DB Provider for SQL Server
Error Description: Syntax error or access violation"
I click OK and get the following:
"An error occurred while parsing the SQL statement for parameters. Please check the syntax of the SQL statement and verify that it is valid for the connection selected."
I Click Parse Query and get the following:
"Error Source: Microsoft OLE DB Provider for SQL Server
Error Description: Syntax error or access violation"
I have used a Parameter in a simple Select Statement like:
Select Column1 From Table1 Where StartDate = ?
I do not know why I can not get SQL to accept the Insert ... Select Statement with the Parameter. I have executed this statement in Query Analyzer using a variable parameter. Please help. Thanks in advance.
Kevin

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Robert W Marda SSChampion Points: 13413 More actions · Answer 1

I have tried to use parameters in DTS packages in places other than the WHERE clause and got similar results. I don't think DTS supports using parameters anywhere in the SELECT clause.

Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems

Jeff Burton SSCommitted Points: 1550 More actions · Answer 2

Have you tried doing:

INSERT INTO IndicativeHistory (<all columns&gt SELECT ? AS LastUpdat, <list all columns from IndicativeTemp> FROM IndicativeTemp

If this still gives you a syntax error you can change the above to a SELECT statement and use a DDQ task to do the INSERT.

The only place I have not been able to use parameters in a SQL task has been in the WHERE clause, but you don't have a WHERE clause.

Jeff

vadba SSChampion Points: 11133 More actions · Answer 3

It's not necessary to assign a column name to the parameter in the SELECT. Try this:

INSERT INTO IndicativeHistory SELECT ?, * FROM IndicativeTemp

The SELECT just needs to return the correct number of columns and matching data types. The column names are irrelevant.

Mike

cross SSCommitted Points: 1735 More actions · Answer 4

You could convert the TSQL statement into a stored procedure with an input parameter, and then call the stored procecure from the DTS task.

For example: execute pr_editmanager ?

djt Valued Member Points: 55 More actions · Answer 5

I had the exact same problem. I can't offer a "why" this is happening, but I can offer a work around. Reboot your machine. Plain and simple. For some reason Enterprise Manager gets confused and doesn't allow this action to take place. In the past I've seen similar Enterprise Manager "hang-ups" that a reboot fixes. Good luck.