SSIS Using Variables in Query Name

Question

Let's say I have the following scenario

Select
      age
     ,address
     ,full_name
from
     [dbo].customers

I want to replace the [dbo] part with an ssis variable, so when we go to run this package and need to replace the schema, it's a piece of cake.

What I have tried so far was creating a SQL variable, named schema_var_name, and appending it to the front, but this is not working out for me.

Can you tell me what I am doing wrong?

Here is what I have:

"Select
      age
     ,address
     ,full_name
from
     '@[User::schema_var_name]' + '.[customers]' "

userfl89 · Accepted Answer · 2018-12-03 19:46:38Z

1

You will need to concatenate the @[User::schema_var_name] variable within the SQL text, with the other parts of the query enclosed with double-quotes ("). An example of an expression for your SQL command is below. This can then be used as the expression for a variable that is set for the source statement in an Execute SQL Task or source component within a Data Flow Task.

"Select age, address, full_name from " +  @[User::schema_var_name] + ".[customers]"

answered Dec 3, 2018 at 19:46

userfl89

4,8201 gold badge13 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

You could also build a dynamic sql statement and execute it in an Execute SQL Task. The ? tells the task that you want to use a variable in that location. You then set up the variable under Parameter Mapping. Your query would then look like this:

DECLARE @schemaName NVARCHAR(100) = ?
DECLARE @sqlQuery NVARCHAR(MAX) = N'
SELECT
      age
     ,address
     ,full_name
FROM
     [' + @schemaName + '].customers'

EXEC(@sqlQuery)

EDIT:

Here's what the Parameter Mapping would look like:

The Parameter Name is most often the least intuitive of these fields. It's a 1-based id sequence for Input parameters. If you had another input variable to use, its Parameter Name would be 2.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Dec 3, 2018 at 20:10

digital.aaron

5,7152 gold badges27 silver badges46 bronze badges

Collectives™ on Stack Overflow

SSIS Using Variables in Query Name

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related