Databases
StackOverflow2013
Postsdbo
POReferencing a variable in an SSIS package
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POReferencing a variable in an SSIS package
    text
    copied!<p>I have an SSIS package which iterates over a table, and for each row in the table, passes the values from two different columns as parameters to a stored procedure. At least, that's what it is supposed to do. What it actually does is give me this error:</p> <pre><code>[Execute SQL Task] Error: Executing the query "EXEC sproc_AccessImport @mAccessLocation (SELECT M..." failed with the following error: "Must declare the scalar variable "@mAccessLocation".". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. </code></pre> <p>I followed <a href="http://www.mssqltips.com/sqlservertip/1437/dynamically-build-connection-objects-for-ms-access-databases-in-ssis/" rel="nofollow">this tutorial</a> in order to make the part that grabs the info, and the code that is supposed to execute the stored procedure is:</p> <pre><code>EXEC sproc_AccessImport @mAccessLocation (SELECT MVID FROM tbl_AccessMailDataFiles WHERE FileLocation = @mAccessLocation) </code></pre> <p>I've been looking around online, but haven't found anything useful. This is the first time I've used SSIS, so I suspect I'm simply doing something wrong. Does anyone know what exactly that is? Thanks.</p> <p>Edit: I tried mapping the variable as a parameter, with the options:</p> <pre><code>Variable Name: User::mAccessLocation Direction: Input Data Type: VARCHAR Parameter Name: Path Parameter Size: 200 </code></pre> <p>And changed the SQL query to:</p> <pre><code>EXEC sproc_AccessImport @Path (SELECT MVID FROM tbl_AccessMailDataFiles WHERE FileLocation = @Path) </code></pre> <p>It now gives me the error:</p> <pre><code>[Execute SQL Task] Error: Executing the query "EXEC sproc_AccessImport @Path (SELECT MVID FROM tb..." failed with the following error: "Must declare the scalar variable "@Path".". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. </code></pre> <p>So, really, the same error.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload