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

• FunkyDexter

  Hall of Fame

  Points: 3907

  More actions

  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

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  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

• FunkyDexter

  Hall of Fame

  Points: 3907

  More actions

  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.

• FunkyDexter

  Hall of Fame

  Points: 3907

  More actions

  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.