CODEWITHSUNDEEP
sqlsql-servert-sqlssisetl
noobMan
noobMan

Reputation: 123

SSIS variable filter on different table in non linked Database

I am uploading a data set from excel into out local server that we use to run offline queries and wish to use the information in that table to filter on a table that is in our production system so i can then pull through the filtered table onto out local DB rather than pull the entire table from prod to our local. I was looking at creating a variable that contained all the account numbers in a string and then use that in an IN(?) statement. This worked, but it only picked up the first account in the string and not the remainder.

I am unable to create a temp table in prod or anything as it is locked down for DBA only. I have tried some stuff in SSIS like variable creation etc but it hasn't worked. I am trying to avoid dumping the entire table. If I have to do this I will set it up to update from the valid from field to capture new records.

SELECT
*
FROM account_table
where [account number] in (?)

I have created the ? variable in an separate Execute task SQL box where the ? is

select
string_agg([account number], ', ')
from [distinct account_table].

I have also tried: 

select
string_agg([account number], ',')
from [distinct account_table]

but that didn't work.

Upvotes: 2

Views: 358

Answers (2)

Hadi
Hadi

Reputation: 37313

You can use expressions instead of passing parameters.

Open the Execute SQL Task editor, go to Expressions tab, add a new expression for the SqlStatementSource property as the following:

"SELECT
 *
 FROM account_table
 where [account number] in ('" + 
 @[User::Variable] + "')"

On the other hand, if i am facing a similar situation i will use lookup transformation to do that instead of SQL statements.

Upvotes: 2

holder
holder

Reputation: 585

First of all, linked servers is not needed here, it's merely a bridge that connects two servers.

I might be missing something obvious here.. You say that you've loaded the dataset to your local db and now you want to get the data from prod that contains those accounts.

My suggestion is to create a dataflow and use a oledb source to get the data from prod. Then add a lookup that joins to your data set in your local db (join on account, make sure that you connect to your local db and that you redirect no matching rows to no match output ). Then add a oledb destination to load the data to your local db.

Upvotes: 0

Related Questions