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

Question

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

billinkc · Accepted Answer · 2013-04-12 18:54:30Z

1

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.

answered Apr 12, 2013 at 18:54

billinkc

61.4k10 gold badges110 silver badges169 bronze badges

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related