Can I use a variable to hold the target table name.

We need to pull from a table that is named tablename_mmddyy and populate a table with the same format tablename_mmddyy. The date will be different every month so I want to be able to build the tablenames every month. Is there a way to do this in SSIS? Thank you.Yes, using expressions in your variables, you can do what you want.

Create a variable and then for its properties, set EvaluateAsExpression to True and then build your expression accordingly in the Expression box.

Then in your OLE DB source/destination, you can use that variable as the table. Just know that you'll want to probably set DelayValidation to True on your connection managers when you do this.

There are plenty of examples in this forum if you search for them on how to build expressions, if you need.|||I found the option for this under Data Access Mode but we need to do the same thing for the database name which will be in a similar format e.g. databasename_mmddyy and the source db name will change every month.|||Same thing. You can use expressions on your connection managers to dynamically create the current connection string.|||The same principals can be applied to a connection manager (i.e. database name of database server). You can use an expression to build the connection string from a variable in the properties window of the connection manager.|||Any examples of this?|||

agentf1 wrote:

Any examples of this?

In the ConnectionString expression box for a connection manager. Also it can't hurt to set DelayValidation to True.

"Data Source=" + @.[User::YourDatabaseNameVariable] +";Initial Catalog=Report;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;"|||What goes in the database name field? I am assuming that it gets overridden by the expression.|||

agentf1 wrote:

What goes in the database name field? I am assuming that it gets overridden by the expression.

The ConnectionString will override any other parameters.|||

agentf1 wrote:

Any examples of this?

http://search.live.com/results.aspx?FORM=QBJK&q1=macro%3Ajamiet.ssis&q=expressions

-JT

|||This seems to be changing both of my OLE DB Connections. Is there a way one can be static and the other updated by the variable? It also appears that Data Source controls the server and Catalog is the database name.|||

agentf1 wrote:

This seems to be changing both of my OLE DB Connections. Is there a way one can be static and the other updated by the variable?

That's impossible (unless one of us has misunderstood). An expression on the ConnectionString property of a connection manager only changes that connection manager.

agentf1 wrote:

It also appears that Data Source controls the server and Catalog is the database name.

Correct.

-Jamie

|||

Jamie Thomson wrote:

agentf1 wrote:

This seems to be changing both of my OLE DB Connections. Is there a way one can be static and the other updated by the variable?

That's impossible (unless one of us has misunderstood). An expression on the ConnectionString property of a connection manager only changes that connection manager.

agentf1 wrote:

It also appears that Data Source controls the server and Catalog is the database name.

Correct.

-Jamie

My bad, I had the expression on both connections. I am OK, thanks for all the help, worked beautifully.