SQLTeam.com | Weblogs | Forums

How to pass string variable to parameter in SSIS package



I'm trying to find the best way to pass a string variable as a parameter. Basically, I have a table on Server A which contains a list of departments#'s (i.e. dept1, dept2,..etc). I need to use the department#'s in from Server A, to return records from a table in Server B. However, I can not use linked server. All of this needs to be done in a SSIS package.

  1. In Control Flow I have create a Execute SQL Task, and added the SQL query to pull the departments as a string... The result of the SQL below gives me depts = dept1','dept2','dept3

_DECLARE @tmp table (Dept_ID varchar(25)) _
DECLARE @tmp2 varchar(250)
SET @tmp2 = ''

_ SELECT ('''' + CAST(Dept_ID as varchar(100)) + '''') _
_ _
--select * from @tmp
select @tmp2 = @tmp2 + Dept_ID + ',' from @tmp

select SUBSTRING(@tmp2,0,LEN(@tmp2)) as Depts

  1. Under parameter mapping I mapped the result to a variable (vdept)
  2. The variable is then used in the Data Flow, in a OLE DB source, where the variable is mapped to a parameter.

This seems to work when the variable is a single value (i.e. vdept = dept1 ). But when the variable is multiple departments it does not work (i.e. vdept = 'dept1','dept2','dept3' ) and SQL query in the OLE DB source use a IN statement. For example: Select * from ServerB.TableB where department_ID in (?).

Am I going about this the wrong way? Is there a better method to grab the department#'s from another server without using linked servers?

Any help or suggestions would be appreciated.


Not sure if there is a better way - but for now instead of using a SQL Command on your OLEDB source you need to use a variable. The variable will then be created as something like:

"SELECT ... FROM ... WHERE department_ID in (" + @[User::vdept] + ") AND ..."

This will build the query string to be executed against the source system with the appropriate IN clause.

Another option - maybe better - would be to create a permanent table in tempdb using an execute SQL task...in your task you check for the table, if it exists truncate it - if it doesn't exist - create it.

Then you have a data flow that pulls the list of valid departments and populates this temp table. You then create your normal data flow and in that one you use a query that joins to your temp table to filter the list of departments.

At the end of your process you then drop the temp table.


Hi Jeff,

Thx for the reply. In your first suggestions of ... The variable will then be created as something like:

"SELECT ... FROM ... WHERE department_ID in (" + @[User::vdept] + ") AND ..."

Do you mean the whole SQL above should be the value of the variable?

For the second option I loaded all the department numbers into my temp table ##TMPDEPT, (done in Control Flow)

but when I try to access it in the (Data Flow) OLE DB Source Editor, SQL command text, it says that ##TMPDEPT is an invalid object name.

*Select V. **
where DateStart >= ?
**and **
DateStop <= ?
**and **
dept_ID IN (Select Dept_id from ##TMPDEPT)

Thank you so much for the suggestions.


You cannot use actual temp or global temp tables - that is why I recommended a permanent table in the tempdb database.


Hi Jeff,

I still get the same error when I use a permanent table in TempDB.
TempDB.tblDEPT is an invalid object name.

Select V.
FROM TableB Vwhere DateStart= ?
DateStop= ?
deptID IN (Select Dept_id from TempDB.tblDEPT)

So just to be clear the above SQL command is used in a OLE DB Source Editor, under the SQL command text window.


The table in the tempdb database should be: tempdb.dbo.tblDEPT - what you have put here is a table in the 'current' database with the schema TempDB.


Thank you Jeff. I finally got it working!!