Post reply

Execute SQL Task passing a variable as a parm - "Must Declare the scalar Variable"

  • July 1, 2016 at 4:54 am

    #311455

    I've spent the last hour looking at this and just can't see what I've done wrong. I'm sure it's going to be something obvious though.

    I have a Execute SQL task based on an ADO.Net connection with the following SQL text:-

    Declare @Tody Date = GetDate()

    Insert into Warehouse.PersonIndicator

    Select PMAP.WarehousePersonID, PIn.IndicatorCode, PIn.IndicatorValue, PIn.Date, @Tody, @ImportID, PIn.EventRef, AddressLine1, AddressLine2, AddressLine3, PostCode, PIn.SupplementaryText

    From Staging.PersonIndicators PIn

    Join Staging.Staging_To_Warehouse_PersonMap PMAP

    on PIn.PersonID = PMAP.StagingPersonID

    I have an Int64 User Variable called ImportID and I've mapped this as a parameter to the Execute SQL task as follows:-

    Variable Name - User::ImportID

    Direction - Input

    Data Type - Long

    Parameter Name - @ImportID

    Parameter Size - -1

    Every time I run the task I get the error:-

    Insert into Wareho..." failed with the following error: "Must declare the scalar variable "@ImportID".". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    My understanding is that I shouldn't have to declare the @ImportID in the SQL as it's being passed in as a parameter. Am I missing something?

    Edit> I should mention this is SSQIS 2008 R2

  • July 1, 2016 at 5:13 am

    #1888048

    I'm not sure whether you can parameterise with an ADO.Net connection. Try changing it to OLE DB, or building your query into a variable and executing that instead.

    John

  • July 1, 2016 at 5:53 am

    #1888060

    This says you can. I'll try switching it to an OLEDB though and see if I get anywhere.

    I'd rather keep it as an ADO.Net if possible - mostly just for consistency and to satisfy my OCD though.

  • July 1, 2016 at 6:03 am

    #1888065

    Ah, found it. There's a connection type property on the task which was set to OLEDB. I didn't spot that and assumed it would infer it from the connection but apparently not. Setting that to ADO.Net sorted it out.

    Thanks for the help.

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

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