Dynamically configuring Fuzzy Grouping

Hello,

I have been struggling with this for quite awhile so any help would be appreciated.

I need to know if there is away to populate the fuzzy grouping control dynamically. I know you programmatically design a package and customize it in C# but for our purposes we would like to control the SSIS package via database settings. When the settings change the package would then act different. Its a simple a package consisting of an Input - fuzzy grouping - conditional split - output. The connections are setup dynamically using parameters, expressions and a script task. Is there anyway I could do a similar thing for Fuzzy Grouping?

No, the FG component needs to know its columns up-front. Either you'll have to use a script component, or code the package from scratch.|||

Thanks for getting back to me.

Could you give me an example of how I would use the script component to do this? For instance would I create an FG control, with the columns setup and then edit the settings using parameters? If so how do I access the control from the script Components (I have tried unsuccessfully to do this), or would I create the control and design it in the script task? Any examples would be a great help, thanks again.

|||

Brat wrote:

Thanks for getting back to me.

Could you give me an example of how I would use the script component to do this? For instance would I create an FG control, with the columns setup and then edit the settings using parameters? If so how do I access the control from the script Components (I have tried unsuccessfully to do this), or would I create the control and design it in the script task? Any examples would be a great help, thanks again.

No, none of the above. You'd have to perform the fuzzy grouping inside a script component. It is likely quite a bit of work.|||

One more question...

The pivot function that is available in SSIS is also available in SQL Management Studio therefore,is the same true for the FG function? Is it available in SQL Managment Studio?

Thanks

|||

Brat wrote:

One more question...

The pivot function that is available in SSIS is also available in SQL Management Studio therefore,is the same true for the FG function? Is it available in SQL Managment Studio?

Thanks

No. But see if these help you... (From the Transact-SQL forum)
http://forums.microsoft.com/MSDN/Search/Search.aspx?words=fuzzy&localechoice=9&SiteID=1&searchscope=forumscope&ForumID=85|||thanks|||

I have tried with out success to modify the FG component using the Script Task. It seems that a package cannot modify itself at runtime (http://blogs.conchango.com/jamiethomson/archive/2005/02/28/SSIS_3A00_-Dynamic-modification-of-SSIS-packages.aspx). I haven't tested this theory yet, but as a work around I created a package that contains the source - FG - destination components and then created another package that contains a Script Task. The idea would be that the script task would reference and load the first package. I can do this like so:

Code Snippet

Dim application AsNew Application()

Dim pkgName AsString = "C:\Program Files\Microsoft SQL Server\90\DTS\Packages\DynamicFG.dtsx"

Dim pkg As Package = application.LoadPackage(pkgName, Nothing)

but how do I get a handle on the FG component from within the first package? Please help, this is getting extremely frustrating, thanks

|||Once the package is loaded, you need to iterate navigate from the package to the data flow that contains the FG. A package object implements the IDTSSequence interface, which has an Executables collection. Executables can contain other Executables (a sequence container, for example), so you may have to traverse multiple levels to get to your data flow.|||

Thank you for your response!

I have managed to get into the Data Flow Task by doing the following:

Code Snippet

PublicSub Main()

Dim application AsNew Application()

Dim pkgName AsString = "C:\Program Files\Microsoft SQL Server\90\DTS\Packages\DynamicFG.dtsx"

Dim pkg As Package = application.LoadPackage(pkgName, Nothing)

Dim pgkExecs As Executables = pkg.Executables

Dim exec As Executable

ForEach exec In pgkExecs

Dim th As TaskHost = CType(exec, TaskHost)

Dim i AsInteger = 0

Dim s AsString

While i < th.Properties.Count

s = th.Properties(i).Name

i = i + 1

EndWhile

Next

Dts.TaskResult = Dts.Results.Success

EndSub

The question I now wanted to know if you could help me with is this:

The th.Properties(0).Name is "[Dynamic Fuzzy Grouping].[MaxMemoryUsage]" which is my FG Control. Any idea how to access this control and now change the properties? I have tried several types of casts and I cant seem to get it right, any ideas would be appreciated. Thanks!

|||

You're close. The TaskHost object is the wrapper around the actual dataflow object. The properties you are seeing are the properties of the data flow's inner objects (but only the ones exposed). To get to the actual pipeline, you need to take one more step.

Code Snippet

Dim pipe as MainPipe = CType(taskHost.InnerObject, MainPipe)

Then you can use the ComponentMetaDataCollection property to access the components in the pipeline, which should include your Fuzzy Grouping.

|||

Thank you! Thank you! Thank you!

I finally got it working! I see my problem was also that I was working with the control flow and not the data flow script task, once I changed over I was able to cast my innerobject to MainPipe, thank you so much for your help, I have been battling with this for two weeks now and even though the code itself is quite simple I couldn't seem to find any help about what to do, so thank you so much!