How to pass string variable to parameter in SSIS package

SSIS and Import/Export
1

Hello,

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 = ''

INSERT INTO @tmp
_ SELECT ('''' + CAST(Dept_ID as varchar(100)) + '''') _
_ FROM JOB_CONTROL_
_ WHERE JOB_ID = (SELECT MAX(job_id)FROM JOB_CONTROL)_
_ _
--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.

2

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.

3

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. **
FROM TableB V
where DateStart >= ?
**and **
DateStop <= ?
**and **
dept_ID IN (Select Dept_id from ##TMPDEPT)

Thank you so much for the suggestions.

4

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

5

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= ?
and
DateStop= ?
and
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.

6

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.

7

Thank you Jeff. I finally got it working!!