CODEWITHSUNDEEP
visual-studio-2013ssissql-server-2012
Thomas
Thomas

Reputation: 2984

Using variables in From part of a task flow source

Is there any way to use a variable in the from part (for example SELECT myColumn1 FROM ?) in a task flow - source without having to give the variable a valid default value first?

To be more exact in my situation it is so that I'm getting the tablenames out of a table and then use a control workflow to foreach over the list of tablenames and then call a workflow from within that then gets data from these tables each. In this workflow I have the before mentioned SELECT statement.

To get it to work properly I had to set the variable to a valid default value (on package level) as else I could not create the workflow itself (as the datasource couldn't be created as the select was invalid without the default value).

So my question here is: Is there any workaround possible in this case where I don't need a valid default value for the variable?

The datatables: The different tables which are selected in the dataflow have the exact same tables in terms of columns (thus which columns, naming of columns and datatypes of columns). Only the data inside of them is different (thus its data for customer A, customer B,....).

Upvotes: 0

Views: 245

Answers (2)

billinkc
billinkc

Reputation: 61239

You're in luck as this is a trivial thing to implement with SSIS.

The base problem for most people is that they come at SSIS like it's still DTS where you could do whatever you want inside a data flow. They threw out the extreme flexibility with DTS in favor of raw processing performance.

You cannot parameterize the table in a SQL statement. It's simply not allowed.

Instead, the approach that people take is to use Expressions. In your case, assuming you had two Variables of type String created, @[User::QualifiedTableName] and @[User::QuerySource]

Assume that [dbo].[spt_values] is assigned to QualifiedTableName. As you loop through the table names, you will assign the value into this variable.

The "trick" is to apply an expression to the @[User::QuerySource]. Make the expression 

"SELECT T.* FROM " + @[User::QualifiedTableName] + " AS T;"

This allows you to change out your table name whenever the value of the other variable changes.

In your data flow, you will change your OLE DB Source to be driven by a query contained in a variable instead of the traditional table selection.

If you want an example of where I use QuerySource to drive a data flow, there's an example on mixing an integer and string in an ssis derived column

Upvotes: 2

Bill
Bill

Reputation: 4585

  1. Create a second variable. Set its Expression to create the full Select statement, using the value of the first variable.
  2. In the Data Source, use "SQL command from variable" option for the Data Access Mode property.
  3. If you can, set a default value for the variable you created in step
    1. That will make filling out the columns from your data source much easier.
  4. If you can't use a default value for the variable, set the Data Source's ValidateExternalMetadata property to False.
  5. You may have to open the data source with the Advanced Editor and create Output columns manually.

Upvotes: 2

Related Questions