EXECUTE SQL parameters mapping problem

Question

EXECUTE SQL parameters mapping problem

darko IS

SSC-Addicted

Points: 442
More actions
December 8, 2009 at 6:48 am

#137416

I Have EXECUTE SQL Task and I am trying to insert some data into table with syntax:
"INSERT INTO test
SELECT ?"
Parameter mapping is set up like this:
Variable name: User::ParameterName
Direction: Input
Data Type: Long
Parameter Name: 0
I am using OLE DB. I have tested connection and Insert does work. Only when I put question mark "?" I think it does not map variable. I get an error:
[Execute SQL Task] Error: Executing the query "INSERT INTO test SELECT ? " failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I can't figure it out. User Variable is Int32. I belive i can map it into Long. I started working with SSIS recently so its probably some elementary stuff...
thanks...
If you don't like how things are, change it! You're not a tree.

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

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

jreece Ten Centuries Points: 1138 More actions · Answer 1

Parameter of type Long maps to variable of type Int32 just fine (aren't all the different data types maddening in SSIS?) -I've done that successfully many times using an OLE DB connection. Your Insert statement syntax is correct for a one column table in T-SQL, but not say, in PL/SQL, but I assuming you are writing to a SQL Server table. That leaves perhaps a data type incompatibility between the parameter type and the column type in your table. I have mine defined as Int in my sql server table and that works with Long type SSIS parameters.

HTH

darko IS SSC-Addicted Points: 442 More actions · Answer 2

No... it is one column table in SQL SERVER data type int. Value in Variable is "1" or some other number that fits int data type column...

But I found this as a comment on http://technet.microsoft.com/en-us/library/ms140355.aspx :

With an OLE DB connection type:

You can't declare a variable in your query, set it's value = ?, use the local variable in the query, map the parameter, and expect it to work. In fact, the only place I've been able to pass a ? variable was in the where clause of a SQL query.

And I am running SQL SERVER 2005 without any patch... so maybe that is the problem. I know same thing can be done with another string variable and Expressions so I can dynamicly write query in string variable, but I would still like to know why parameter mapping isn't working!?

Any ideas?

If you don't like how things are, change it! You're not a tree.

jreece Ten Centuries Points: 1138 More actions · Answer 3

I've successfully used parameter mapping with an Insert statement, the only difference in mine is I used a Values clause instead of a Select. why don't you try to that? Come to think of it - I think I recall reading that the Execute SQL task has a hard time parsing nested SQL statements, and your Select inside the insert would qualify as such.

The longer you work with SSIS, the more you will discover it's quirks and shortcomings, which are legion.

🙁

darko IS SSC-Addicted Points: 442 More actions · Answer 4

Yes

I changed syntax from SELECT to VALUES and now it works fine.

and yes, I am working with SSIS for 3 weeks now and on moments I am enthusiastic about it, and sometimes I am very frustrated :angry:

Thanks anyway

If you don't like how things are, change it! You're not a tree.