CODEWITHSUNDEEP
sql-serverssis
ChefBrian
ChefBrian

Reputation: 11

SSIS - How to use user defined variable as database path?

I am wanting to have a user enter the database path they will be using.

For Example: @SourceDB = DatabaseA.dbo.

I would then like to use the variables in a Data Flow or Execute SQL task.

My question: How do I reference the variables in these two tasks? Neither of these ways seem to be working for me:

SELECT Field1, Field2 FROM @SourceDB + tablename

or

"SELECT Field1, Field2 FROM " + @[User::SourceDB] + "tablename"

or

SELECT Field1, Field2 FROM ? tablename

Upvotes: 1

Views: 746

Answers (1)

billinkc
billinkc

Reputation: 61269

You won't be be able to use it as a parameter ?. Others have tried to do that and run into what you're experiencing.

What I would do is define a variable, call it SourceQuery, data type of String, scoped at the package level. In the properties window for that variable (F4), change the EvaluateAsExpression property to True. Then use a formula in the expression like you have already specified. "SELECT Field1, Field2 FROM " + @[User::SourceDB] + "tablename"

Finally in your OLE DB Source, change the access mode to table or view to "SQL Command from variable" and then things should just work.

The one caveat to be aware of is that if the data types for these columns change, your SSIS packages will fail the Validation step.

Upvotes: 1

Related Questions