SSIS use database name as a parameter to sql task

patd1
patd1 used Ask the Experts™
on
Creating an SSIS packege. Is it possible to create a sql task that runs a query that uses database name as a parameter?

example : Insert into mydb.ssis.mytable1
Values
(Select * from ?.prod.mytable1)

The parameter values should be coming from a user variable. Is that possible?

Thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jason YousefSr. BI  Developer

Commented:
Yes of course, sure
Just add Expressions in a variable and it will do.
Commented:
dynamic SQL would do it...
declare @sqlquery varchar(4000)
SELECT @sqlquery='Select * from '+@yourvariable+'.prod.mytable1'
EXEC(@sqlquery)
Or, perhaps, a better approach might be to use variables in the ConnectionManager for the Instance and catalog settings.
Alpesh PatelSolution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)

Commented:

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial