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.
- 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
- Under parameter mapping I mapped the result to a variable (vdept)
- 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.