Use variables in SSIS Select and From clause

Question

I'm importing several stores' sales and purchase COSTs where each store has its own package (to utilize parallellism). Setting this up can be rather time consuming, as I have to to go into each stores OLE DB Source and adjust the FROM clause in order to capture that a store has a seperate data base (the database for store number 9999 is [database].[dbo].[sales9999]).

I also want to use the store number in the Select clause for each store. For instance for customer 9999 I need to have Select 9999 AS StoreKey as a column.

Hence I'd like to have like this:

SELECT ? as StoreKey, a.saleprice, b.purchasecost
FROM ? AS a JOIN ? AS b ON a.ID = b.ID

In my OLE DB Source, and match the ?'s with the corresponding parameters. However when I tried to implement this I've received the error that parameters only can be used in the Where clause, which I don't need.

Anyone got any ideas?

Regards,

Your table design is bad and you can't use parameters for column or table names - only for values. Can you change you design? — juergen d
– juergen d, Commented Feb 6, 2017 at 10:46
What are you trying to do and why do you think that "variables" in the statement are the answer? SSIS can't work without knowing the metadata it has to work with - the columns, names, sizes, lineage etc. Each flow is different, each table is different. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Feb 6, 2017 at 10:49
If you want to use the same flow, with identical metadata to load data from multiple tables, you can use a variable to calculate the entire expression. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Feb 6, 2017 at 10:50
The metadata is identical, only the StoreKey and the databases where I fetch the data differs for each Store (i.e. each package). My thought was that instead of importing the whole database to my company's server (I lack the ability to write in the customers' databases), and add these StoreKey using a similar method like these, and subsequently save a redundant database in my company's server, I could as well import them "manually" by using a column which I populate with a constant value, for each package. Perhaps there's a smoother way though. — Cenderze
– Cenderze, Commented Feb 6, 2017 at 10:57
@PanagiotisKanavos You mean using SQL Statement from a variable? I tried to google such solutions as well, but I only found solutions where the variables were present in the Where clause, but never in the From or Select clause. — Cenderze
– Cenderze, Commented Feb 6, 2017 at 10:58

Ferdipux · Accepted Answer · 2017-02-06 11:36:52Z

3

You can certainly do it with an aid of Variable being evaluated as an Expression. For example, your select statement can be written as the following Expression for Variable:

"SELECT N'"+@[User::strStoreKey]+"' as StoreKey, a.saleprice, b.purchasecost 
    FROM "+@[User::strTableName]+" AS a JOIN "+@[User::strAnotherTable]+"
    AS b ON a.ID = b.ID"

Here the variables have to be defined with String data type or type casted at Expression.
Several important points to mind when using this approach:

Result sets of all Select queries have to be identical, in terms of columns, its names and data types.
You have to provide default values to query building variables, i.e. strStoreKey, strTableName and strAnotherTable which results in valid query. Otherwise your package will fail validation at saving.
Your Data Flow should have property set as follows DelayValidation=True. This validates Data Source or destination right before execution, not at Package load.

You can use this approach with variable destinations as well, when destination table name is set at variable; the same restrictions apply.

answered Feb 6, 2017 at 11:36

Ferdipux

5,2791 gold badge22 silver badges34 bronze badges

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

2 Comments

Cenderze Over a year ago

Thanks for reply! Thought it ought to be possible. What does that N mean? From googling "SELECT N.." it seems it returns N rows, or does it mean something else I havent found?

Ferdipux Over a year ago

@Cenderze, N'...' means that string literal inside quotes is Unicode. I prefer to use Unicode throughout SSIS package to avoid implicit conversion to default codepage issues.

Collectives™ on Stack Overflow

Use variables in SSIS Select and From clause

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related