DTS parameter is incorrect

Question

Post reply

DTS parameter is incorrect

Sandra Larson

Old Hand

Points: 307
More actions
September 16, 2005 at 11:43 am

#76420

I'm calling a DTS package with global variables from a .NET application. I'm getting a SQL step error stating "The parameter
is incorrect."
I've attempted to research the problem, and the only thing I can find is that there is a compatability problem between the
SQL Server and the Server hosting SQL Server.
Specifics:
SQL Server: SQL 2000 version 8.00.760 SP3
Host Server: Windows Small Business Server 2003 (no service packs installed)
Client machine: XP Pro SP4
.NET: Visual Studio .NET 2003
Does anyone know if this could be a compatability issue, or is there something else I should be looking at. I attempted to
password protect with an owner password, but to no avail.
Thanks in advance,
Sandi

Viewing 12 posts - 1 through 11 (of 11 total)

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

Ray M SSC-Insane Points: 21093 More actions · Answer 1

Can you run the dts package without error from sql server? not thru .NET?

Sandra Larson Old Hand Points: 307 More actions · Answer 2

No, I haven't. I'm not sure how to run a package with global variables(with different values than the ones it's set to in the package).

Sandi

Ray M SSC-Insane Points: 21093 More actions · Answer 3

At this point you need to see if the dts package has the error, or if the .NET code has the error.

Go to Package properties, and set your global variable values.

Sandra Larson Old Hand Points: 307 More actions · Answer 4

The package has the global variables set. The package itself has the errors. It's a step error, and the step error description is "The parameter is incorrect."

mike gercevich SSCrazy Points: 2961 More actions · Answer 5

It sounds like you are calling a stored procedure from a Execute SQL Task. Are you using a parameter that is a global variable that cannot be converted to the correct type?

Try turning on Logging on the package, save the package and then try running it again. When it fails look at the log file to see a better description of the error that has occured.

-Mike Gercevich

Sandra Larson Old Hand Points: 307 More actions · Answer 6

Hi Mike,

Yes, I am calling a stored procdure from an Execute SQL Task. The parameters match: the first one is date in DTS and datetime in SP. The second one is is char(1) in SP and string in DTS. Maybe this is the problem???

As far as the logging - it's turned on. The error that I'm getting is from the log.

Hope you can help,

Thanks - Sandi

mike gercevich SSCrazy Points: 2961 More actions · Answer 7

Sandra,

Start up SQL Profiler and start a Trace on the server where the Stored Proc is located... You will be able to see the parameters passed to the stored proc in real time to the SQL Server. Attempt to recreate the EXEC SP call with the passed in parameters in SQL Query Analyzer... this will give you a much better direction on what SQL is complaining about.

My hunch is that your datetime parameter is not in a format that SQL can understand. The Variant/String you pass in must be able to implicitly converted to a DateTime type.

I have used this trick in the past to work around VB to SQL date problems....

DTSGlobalVariables("gv_MyDate").Value = CDate(DTSGlobalVariables("gv_MyDate").Value)

In the Workflow Active-X script of your Exec SQL Task.

-Mike Gercevich

Sandra Larson Old Hand Points: 307 More actions · Answer 8

Mike,

I'm sorry, but I gave you the wrong info. I'm not doing an Execute Sql Task, but rather using an exec SQL query in the Transform Data Task Properties which uses global variables. The results of this stored procedure are being dumped into an Excel spreadsheet.

Sandi

mike gercevich SSCrazy Points: 2961 More actions · Answer 9

Right-Click on your transform data task arrow... Select "Workflow properties"; Select the "Options" tab; Check the "Use Active-X script"; Click the "Properties..." button.

Set your Workflow Script to the following:

Function Main()

IF NOT IsDate( DTSGlobalvariables("gv_myVariable").Value ) Then

'*** Set Date Variant to a default value of your choice

DTSGlobalvariables("gv_myVariable").Value = Now()

END IF

DTSGlobalvariables("gv_myVariable").Value = CDate( DTSGlobalvariables("gv_myVariable").Value )

Main = DTSStepScriptResult_ExecuteTask

End Function

Replace "gv_myVariable" with your datetime parameter global variable you are passing to your stored procedure.

This should solve the parameter error,

-Mike Gercevich

Sandra Larson Old Hand Points: 307 More actions · Answer 10

Hi all. Sorry I haven't replied - just returned from a short vacation.

Mike,

Thanks for the suggestion. I'll try it as soon as I can unbury myself from the pile of emails and other issues I have to work through.

Sandi

Sandra Larson Old Hand Points: 307 More actions · Answer 11

That worked!!! Thanks for your help Mike - that's a neat workaround.

Sandi