1

I am creating a SSIS package that involves a where clause as: WHERE effectiveDate >= @effectiveDate

Therefore, I defined @effectiveDate at the package level so that I can pass in value from a sql command using a different connection.

At the OLE DB source, I selected 'SQL command', and put the code as Select ... FROM ... WHERE effectiveDate >= ?

When I click on the 'parameters', an error appears: 'Parameters cannot be extracted from the SQL command. blablabla... use "SQL command from variable"...'

I am trying to avoid using SQL command from variable and try to locate the problem since I should be able to pass in variable.

Can anyone help? Thanks a lot!!

billinkc
  • 59,250
  • 9
  • 102
  • 159
user2683470
  • 189
  • 1
  • 3
  • 15
  • possible duplicate of [How do I pass value to a stored procedure parameter in OLE DB Source component?](http://stackoverflow.com/questions/13961534/how-do-i-pass-value-to-a-stored-procedure-parameter-in-ole-db-source-component) – billinkc Dec 16 '13 at 19:34

1 Answers1

0

When building an OleDbCommand, you should actually name the parameter as the package is expecting, such as 

OleDbCommand cmd = new OleDbCommand( "select ... from... where... eff > @yourPkgPameterName");

cmd.Parameters.AddWithValue( "@yourPkgPameterName", whateverValueToSend );

It's been a while for SQL-based parameters and don't remember if the AddWithValue() method is expecting the "@" or not... may need to try without it if it fails.

billinkc
  • 59,250
  • 9
  • 102
  • 159
DRapp
  • 47,638
  • 12
  • 72
  • 142
  • 1
    You answer is correct for C# but this question is about the SSIS execution environment which does not use the @ nomenclature for variables. – billinkc Dec 17 '13 at 03:23
  • @billinkc, I guess that was your down-vote then... But the user then explicitly was referencing an OleDbCommand with select with ?, So, which is it... the query to get data, or calling the report that is failing.. a newbie score of 3? Not going to be the greatest in clarification. – DRapp Dec 17 '13 at 03:34