Solved

SSIS use database name as a parameter to sql task

Posted on 2011-09-06
4
593 Views
Last Modified: 2013-11-10
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.
0
Comment
Question by:patd1
4 Comments
 
LVL 21

Expert Comment

by:Jason Yousef, MS
ID: 36490928
Yes of course, sure
Just add Expressions in a variable and it will do.
0
 
LVL 8

Accepted Solution

by:
stalhw earned 500 total points
ID: 36490991
dynamic SQL would do it...
declare @sqlquery varchar(4000)
SELECT @sqlquery='Select * from '+@yourvariable+'.prod.mytable1'
EXEC(@sqlquery)
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 36491156
Or, perhaps, a better approach might be to use variables in the ConnectionManager for the Instance and catalog settings.
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 36493764
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How to share SSIS Package? 6 37
Query Help - MSSQL - Averages 5 27
VB.net and sql server 4 36
CPU high usage when update statistics 2 30
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question