SSIS Filter data flow source using object type variable parameter

itcouple
itcouple used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Reza RadConsultant, Trainer

Commented:
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

Author

Commented:
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

Author

Commented:
I remember now..... to avoid extracting alll data from source just to then filter it :)
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

Reza RadConsultant, Trainer

Commented:
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?

Author

Commented:
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
Consultant, Trainer
Commented:
yes, use string variable to make values and make t-sql then.
if your sources is sql server and they are on same server, you can use T-sql commands

Author

Commented:
Thanks
Reza RadConsultant, Trainer

Commented:
you're welcome,
Regards,

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial