search

keif888 / SQLServerMetadata

SQL Server Metadata Toolkit
Microsoft Public License
82 stars 32 forks source link

tool cannot see variable defined at data flow task level and used by source #27

Closed tgauchet closed 2 years ago

tgauchet commented 6 years ago

I have one SSIS 2012 package with a data flow task using as source a SQL query defined in string variable. This variable is not defined at package level but at Data Flow Task level. but the code is looking for variable at package level only. Exception is triggered by GetVariable method.

tgauchet commented 6 years ago

As a workaround I have transmitted from the component to the data flow source the variables list. It is not very nice but it allows me to catch the issue and then use in catch section the array. Of course I am interested If you have better! SsisEnumerator.txt

tgauchet commented 6 years ago

Just for information. Note that issue is also existing at other placer like with task to execute child packages. We are using "local" variables to pass parameters.. Thank you for your great work!

keif888 commented 6 years ago

Latest code commit has fix for this issue. Corrected by passing the taskHost down to where the variables are being extracted, and checking both the SSIS package level, and the taskHost level for the variable. This has been tested against variables in a Data Flow, and in the For Loop, that contains a Data Flow.

tgauchet commented 6 years ago

Thanks a lot. My first test are working fine. Just a remark, you are coding: if (package.Variables.Contains(strVariableName)) return package.Variables[strVariableName].Value.ToString(); else if (taskHost.Variables.Contains(strVariableName)) return taskHost.Variables[strVariableName].Value.ToString(); else but I believe that SSIS is doing the contrary in case you have 2 variable with same name. It will first check the local one and then the upper ones. (I will check tomorrow). So I would write: if (taskHost.Variables.Contains(strVariableName))

            else if (package.Variables.Contains(strVariableName))

            else

Thanks a lot!

fmms commented 6 years ago

Yes. The inner scope is always over layering the outer scope

Von meinem iPhone gesendet

Am 11.09.2018 um 18:00 schrieb tgauchet notifications@github.com<mailto:notifications@github.com>:

Thanks a lot. My first test are working fine. Just a remark, you are coding: if (package.Variables.Contains(strVariableName)) return package.Variables[strVariableName].Value.ToString(); else if (taskHost.Variables.Contains(strVariableName)) return taskHost.Variables[strVariableName].Value.ToString(); else but I believe that SSIS is doing the contrary in case you have 2 variable with same name. It will first check the local one and then the upper ones. (I will check tomorrow). So I would write: if (taskHost.Variables.Contains(strVariableName))

        else if (package.Variables.Contains(strVariableName))

        else

Thanks a lot!

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHubhttps://github.com/keif888/SQLServerMetadata/issues/27#issuecomment-420325821, or mute the threadhttps://github.com/notifications/unsubscribe-auth/AA3dvpQudjQf3Ck6r1TA1fdpvs2MJ60Yks5uZ939gaJpZM4WeieN.

keif888 commented 6 years ago

I have changed the order of execution to taskHost, then package, for the selection of a variable. I have since tested, and seen that you can have the same named variable at different Scope's.

tgauchet commented 6 years ago

Thanks a lot!