asked on

SSIS Filter data flow source using object type variable parameter

Hi

I want to get several rows from one database/server and then use it to with T-SQL Data Flow Source which uses different database/server

I'm trying to do something like that Select Field From Table Where Field IN (?)
The ? is object variable populate with data using Execute SQL Task.

Can I do that this way or does it accept only "scalar" parameter?

Many thanks in advance

Reza Rad

I'm not sure what you mean, but if you want to implement a IN structure or NOT IN structure you can use Lookup Transform or Merge Join Transform for this.
for detailed help explain more about what you want to do, samples of data can be helpful too

itcouple

ASKER

Hi

I'm trying to filter my table using IN and I'm trying to use object variable to do this.

The data for the IN parameter comes from a different database/server therefore I just want to extract the relevant rows; have it in table variable and use it to filter my data source. I could add another datasource and add some extra data flow tasks but I was told that I might be slightky slower but I will actually discuss that again with my colleague and if there is a special reason for that and if there is any drawback of using data flow tasks in this case. (just in case)

Regards
Emil

itcouple

ASKER

I remember now..... to avoid extracting alll data from source just to then filter it :)

Reza Rad

there are three ways which you can implement an IN structure in SSIS:
1- fetch all data from source, then use LOOKUP Transform to find match data
2- fetch all data from source, then use a Merge Join Transform with INNER JOIN type
3- implement IN structure in mere T-SQL in source

there is no 4th way.

now, for choosing right way between these options, let me know what is your source types?

itcouple

ASKER

Thanks for listing the options.... I need to avoid extracting all source table data so 1 & 2 is no go in this particular scenario. 3 seems to be my only option; the issue is that I need the pass filter data dynamically so I've tried 'object' variable with the data and use IN (Works with SSRS but unfortunatelly doesn't with SSIS). I've treid string variable with 'values', 'value2' but it seems to treat at as one big string value. So it seems my only option is to construct T-SQL using script and then use with datasource (as suggested to me initially.... what a shame)?

Regards
Emil

ASKER CERTIFIED SOLUTION

Reza Rad

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

itcouple

ASKER

Thanks

Reza Rad

you're welcome,
Regards,