Post reply

package vars passed to Execute SQL Task

  • August 14, 2007 at 11:52 am

    #125273

    Hi,

    I am having trouble getting the Execute SQL Task to recognize the package-level variables in an SSIS package. The tasks fail execution. The package contains three Execute SQL tasks and none of them receive the variables. However, when I replace the '?' in the code with the variable values they execute fine. These are the steps I have taken thus far:

    Made sure the three variables are package level.

    Verified I'm using an OLE DB valid connection.

    Verified the variables are properly mapped in the parameters mapping section of the task properties.

    Tried all 4 types of variable to parameter mapping (even though '?' in the query and '0,1,2..' in the name should work).

    Changed the Bypassprepare property to 'True'.

    Breakpoints and watch windows to verify the variable values.

    I actually tried the simple tutorials on a one-liner SQL statement that uses variables, but had the same problem. I know there must be something simple that I'm missing here, but any info would be greatly appreciated!

    Thanks!

  • August 14, 2007 at 1:34 pm

    #726899

    What is the error message you are getting?  That may help diagnosis your problem.

  • August 15, 2007 at 6:48 am

    #727014

    Doh! My bad. All of the packages come back with "Syntax error, permission violation, or other nonspecific error".

    Also, the code for the first package that holds the parameter is:

    DECLARE @DatabaseName varchar(100)

    SET @DatabaseName = ?

    DECLARE @SPID smallint

    And, of course, when I replace the ? with the DB name in the Value field of the variable it executes.

    The Parameter Mapping has the following values:

    User::databasename Input Varchar 0

    And the Variables set up has the following values:

    databasename package string DB_NAME

    Is there a way to see the query after it reads the variables to see if I have something syntactically incorrect in the values? Please let me know if any further info would help.. I'm really stumped on this since I get the same results with examples found everywhere else too.

    Thanks!

Viewing 3 posts - 1 through 2 (of 2 total)

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