What you can't do is take SSIS functionality out of SSIS and use it elsewhere - it must all run within a package.
-Jamie|||But can I pass in info such as a parameter into the package and have it run it based on that parameter?
For example, I want to look up any and all customers that match to 'Blue something' ('Blue Cross / Blue Shield', 'Blue Cros / Blue Shield', 'Blu Cross & Shield', etc.), so I want to pass in the parameter 'Blue' and it will execute and pull out any customer that fuzzies to anything relating to 'Blue'
I haven't been able to find in the documentation any way of doing this. I have seen a lot of 'hints' saying that you can do this, but nothing on how to do this.
Thanks|||
Tazzmann wrote:
But can I pass in info such as a parameter into the package and have it run it based on that parameter? For example, I want to look up any and all customers that match to 'Blue something' ('Blue Cross / Blue Shield', 'Blue Cros / Blue Shield', 'Blu Cross & Shield', etc.), so I want to pass in the parameter 'Blue' and it will execute and pull out any customer that fuzzies to anything relating to 'Blue'
I haven't been able to find in the documentation any way of doing this. I have seen a lot of 'hints' saying that you can do this, but nothing on how to do this.
Thanks
Right, I get you now.
Hmm...yes and no really. Yes, you can pass values into a package quite easy but fuzzy lookup isn't designed to be used using values that exist in your package. It does a lookup of incoming data (e.g. from a table) against a lookup table. The last bit that I mentioned here is key, it looks up against a table NOT against a value that exists in your package which I think is what you want to do.
Why not put the value "Blue" into a table and lookup against that table. That would work fine.
Basically, yes, you can integrate fuzzy lookup functionality into other applications, but with the confines of the way the fuzzy components work in SSIS. you seem to want something slightly different, and it doesn't do that!
Does that make any sense at all?
-Jamie|||Yes Jamie that does make sense, thanks. I did find a way to kind of do what I want and it works good through the Business Intelligence designer, but I still have a couple of questions if you can be patient with a total newbie into this new SQL Server technology...
What I did is set up a package-wide variable called User::Test and placed the value 'Blue' into it. I then took the OLE DB Source component and changed the Data Access Mode to use SQL Command. I then Set the SQL Command Text to the following:
Select * From Tablename where ColumnName like ? + '%'
I also mapped a parameter called @.Input to User::Test variable
I then connected this to the Fuzzy Grouping (not Lookup, yet) and then put the output from that to OLE DB Destination source.
When ran, it gives me the expected results of all the matches for 'Blue'.
Now my questions are these:
1. When I execute this package from a Stored Procedure, can I pass a value to the parameter @.Input (variable User::Test) at runtime?
2. Do I need to set up another parameter / Variable to hold the results so that I may pass that back to the calling Stored Procedure or do I have to have the results dumped to a table?
I am making headway and I thank you for your help so far.
Tazz|||
Yes Jamie that does make sense, thanks. I did find a way to kind of do what I want and it works good through the Business Intelligence designer, but I still have a couple of questions if you can be patient with a total newbie into this new SQL Server technology...
You're a beginner you say? Blimey, you progress quickly 
What I did is set up a package-wide variable called User::Test and placed the value 'Blue' into it. I then took the OLE DB Source component and changed the Data Access Mode to use SQL Command. I then Set the SQL Command Text to the following: Select * From Tablename where ColumnName like ? + '%'
I also mapped a parameter called @.Input to User::Test variable
I then connected this to the Fuzzy Grouping (not Lookup, yet) and then put the output from that to OLE DB Destination source.
When ran, it gives me the expected results of all the matches for 'Blue'.
Now my questions are these:
1. When I execute this package from a Stored Procedure, can I pass a value to the parameter @.Input (variable User::Test) at runtime?
Yes, I think you can (I should know how but I don't, sorry). Are you sure you want to execute this from a sproc tho - I thought you were calling it from .Net?
2. Do I need to set up another parameter / Variable to hold the results so that I may pass that back to the calling Stored Procedure or do I have to have the results dumped to a table?
The ADO.Net DataReader destination enables your data to be consumed by .Net applications. Look up "Datareader Destination" in BOL for more info.
I am making headway and I thank you for your help so far. Tazz
This sounds like an interesting problem, good luck with it.
-Jamie|||Thanks for the compliment. 
I have been programming for about 13 years and the last few years have been in .NET.
The 2005 version has seen a lot of improvements and changes especially in the SQL Server side of things. We just installed the Beta where I work so I am trying to get my head wrapped around it and am trying to do some proof of concept things such as what I am trying to accomplish above.
No, I don't have to call it from a stored proc, if I can call it from .NET I would like that much better (and so would our DBA's).
Thank you for the ADO.NET Datareader destination hint, that is exactly what I needed. You are a blessing in disguise. I will post back here if I either get it running or find that it is not possible to do what I am trying to do. So far it looks very possible.
Thanks again!
No comments:
Post a Comment