INSERT MyTable(Col1, Col2, Col3) VALUES(1, @MyParam, 3)
INSERT MyTable(Col1, Col2, Col3) VALUES(4, @MyParam, 6)
as well as:
INSERT MyTable(Col1, Col2, Col3) VALUES(1, ?, 3)
INSERT MyTable(Col1, Col2, Col3) VALUES(4, ?, 6)
The parameter is defined in the task. It fails consistently. The error in the output shows the SQL without the parameter replaced. Any ideas?
Thanks,
Chris
Are you inserting into a SQL Server DB? If so, run SQL Profiler and see what SQL gets issued against SQL Server.
-Jamie
I found that I couldn't get parameters to work with an OLE DB connection only with ADO.NET.
Mine was a simple update based on a passed variable:
UPDATE RawLastReference
SET LastReference = @lastref
WHERE (LastReferenceKey = 'lastref')
I then added the parameter mapping and renamed the parameter to "lastref".
This is what I see in the profiler trace:
exec sp_executesql N'UPDATE RawLastReference
SET LastReference = @lastref
WHERE (LastReferenceKey = ''lastref'')',N'@lastref int',@lastref=123456
Hope this helps,
Andrew
Andrew Gaskin wrote:
exec sp_executesql N'UPDATE RawLastReference
SET LastReference = @lastref
WHERE (LastReferenceKey = ''lastref'')',N'@lastref int',@lastref=123456
1. Is that what you see when you execute using an OLE DB Connection or an ADO.Net connection? It looks valid to me so I imagine the latter.
2. Again, what error do you get? Providing an error message means you're more likely to get an answer.
3. Try
UPDATE RawLastReference
SET LastReference = ?
WHERE (LastReferenceKey = 'lastref')
with an OLE DB Connection and see what you get.
4. If you still can't get it to work, open your .dtsx file in notepad and post the contents as a reply to this thread.
-Jamie
With OLE DB I get different errors.
If I put just ? in and attempt to parse the query I get "The query failed to parse. Parameter Information cannot be derived from SQL statements. Set parameter information before preparing command."
If I use Build Query the ? gets replaced with @Param1 but on attempting to parse the message '"The query failed to parse. Must declare the scalar variable "@Param1"'. It doesn't matter what name is used in Parameter Mapping- this error always appears.
If I attempt to execute the OLE DB version it doesn't get as far as sending the statement to the engine as it fails with "Parameter name is unrecognised".
My original post was more a suggestion for a workarounfd for Chris if he was seeing similar problems.
Cheers,
Andrew
Thanks,
- Joel
Maybe this will help... from my forthcoming tomb. :)
Connection Managers and Substitution Parameters for Queries
| Connection Manager Type | Parameter Marker | Parameter Name | Example Query | Example Parameter Name |
| OLEDB | ? | 0 1 … | SELECT Name FROM People WHERE Name LIKE ? | 0 |
| ADO.Net | @Varname | @Varname | SELECT Name FROM people WHERE Name LIKE @Name | @Name |
| | ? | @Param1 @Param2 … | SELECT Name From People WHERE Name LIKE ? | @Param1 |
| ODBC | ? | 1 2 … | SELECT Name FROM People WHERE Name Like ? | 1 |
Maybe this will help... from my forthcoming tomb. :)
What about output parameters in a Stored Procedure? I need to have an OLE DB Command transformation in a Data flow that will create a row on a table and add the @@IDENTITY value into the stream. The only way I can think of doing that is to execute a Stored Proc with an Output parameter and have that value overlay a field in the stream.
I've coded it as such but the value is not populating when I display a Grid after the OLD DB Command.
Clues? Direction?
Thanks
The SQL statement is "storedproc ?, 23, ?".
The error I get is "[OLE DB Source [1]] Error: The SQL command requires a parameter named "@mailing_id", which is not found in the parameter mapping." (@mailing_id is the first parameter in the procedure.)
On the parameter mapping page, I've tried the names Parameter0 & Parameter1, Paremeter1 & Parameter2, Param1 & Param2, and just 0 & 1. I get the same error message. The parameter values are both package variables.
This works for me.
One wired thing is, if the next component is script component it fails. I don't know the reason.
I am just trying to capture the return code from a stored proc, and there are no results sets, and no parameters. pretty simple I would think....
Using OLEDB connection, I utilize SQL: "exec ? = testingdups"
I can not seem to map the return code to a parameter. I defined a variable, mapped the variable to returncode, and used the previous mentioned parameter names to no avail.
The proc is valid, and the return code is present when running from SQL direct.
Any suggestions?
Thanks In Advance...
The error message is:
SSIS package "Package1.dtsx" starting. Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "exec ? = testingdups" failed with the following error: "Invalid parameter number". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. Task failed: Execute SQL Task Warning: 0x80019002 at Package1: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. SSIS package "Package1.dtsx" finished: Failure.
Hi Chris,
Were you able to find any solution for the problem...
I did somewhat same routine what you did using OLEDB connection.
Let me know if you have a elegant solution.
Thanks
Mahesh
