1

Closed

SSIS Variables Window Extensions - not all variables are highlighted when package configurations impact them

description

most variables are highlighted when package configurations impact them. But some of this variables are not highlighted. In the example the stringCO_Config_ConnectionString is not highlighted but it gets the value from configuration CONSTANTS
\Package.Variables[User::stringCO_Config_ConnectionString].Properties[Value]

file attachments

Closed Jul 22, 2010 at 1:16 AM by furmangg
fixed. will be in the release after 1.4.3.0

comments

furmangg wrote Jul 20, 2010 at 1:29 AM

Let's see if I follow what's going on...
  1. There's an environment variable called ETL_Config_08 which contains the full path to the dtsConfig file.
  2. That dtsConfig file contains a configuration that sets the stringCO_Config_ConnectionString package variable.
  3. There's a package configuration setup that is an indirect XML configuration (with the env variable providing the path to the dtsConfig)
  4. BIDS Helper doesn't mark that that variable is controlled by a package configuration.
Was the ETL_Config_08 environment variable created recently? If so, have you restarted Visual Studio since you created it? I was able to get this to work after I restarted Visual Studio (because environment variables are picked up when you start a program, not after.)

Germo wrote Jul 20, 2010 at 10:40 AM

environment variable has been defined long time ago and not changed. Only the content of the XML dtsConfig file has changed. In the new version only \Package.Connections[Framework].Properties[ConnectionString] gets the configuration from the XML file, in older versions also some other properties got their information from the XML file, but not the stringCO_Config_ConnectionString. The stringCO_Config_ConnectionString gets the configuration in the second step from SQL Server configuration table. So I think all target properties in this filter should also be marked in the variables list. (And of course all variables from the other confgurations)

Name:
CONSTANTS
Type:
SQL Server
Connection name:
Framework
Any existing configuration information for selected configuration filter will be overwritten with new configuration settings.

Configuration table name:
[dbo].[ETL_Config]
Configuration filter:
PC
Target Property:
\Package.Variables[User::V_RunInOptimizedMode].Properties[Value]
\Package.Variables[User::V_DefaultBufferSize].Properties[Value]
\Package.Variables[User::V_BufferTempStoragePath].Properties[Value]
\Package.Variables[User::V_BLOBTempStoragePath].Properties[Value]
\Package.Variables[User::stringCO_Config_ConnectionString].Properties[Value]
\Package.Variables[User::C_Unknown_Text].Properties[Value]
\Package.Variables[User::C_Unknown_Text].Properties[Namespace]
\Package.Variables[User::C_Unknown_Text].Properties[Name]
\Package.Variables[User::C_Unknown_Key].Properties[Value]
\Package.Variables[User::C_Unknown_Key].Properties[Namespace]
\Package.Variables[User::C_Unknown_Key].Properties[Name]
\Package.Variables[User::C_Unknown_Date].Properties[Value]
\Package.Variables[User::C_Unknown_Date].Properties[Namespace]
\Package.Variables[User::C_Unknown_Date].Properties[Name]
\Package.Variables[User::C_Status_Success].Properties[Value]
\Package.Variables[User::C_Status_Success].Properties[Namespace]
\Package.Variables[User::C_Status_Success].Properties[Name]
\Package.Variables[User::C_Status_OutOfBalance].Properties[Value]
\Package.Variables[User::C_Status_OutOfBalance].Properties[Namespace]
\Package.Variables[User::C_Status_OutOfBalance].Properties[Name]
\Package.Variables[User::C_Status_Load_Phase_Complete].Properties[Value]
\Package.Variables[User::C_Status_Load_Phase_Complete].Properties[Namespace]
\Package.Variables[User::C_Status_Load_Phase_Complete].Properties[Name]
\Package.Variables[User::C_Status_Fatal_Abort].Properties[Value]
\Package.Variables[User::C_Status_Fatal_Abort].Properties[Namespace]
\Package.Variables[User::C_Status_Fatal_Abort].Properties[Name]
\Package.Variables[User::C_Min_Date].Properties[Value]
\Package.Variables[User::C_Min_Date].Properties[Namespace]
\Package.Variables[User::C_Min_Date].Properties[Name]
\Package.Variables[User::C_Max_Date].Properties[Value]
\Package.Variables[User::C_Max_Date].Properties[Namespace]
\Package.Variables[User::C_Max_Date].Properties[Name]
\Package.Variables[User::C_Invalid_Key].Properties[Value]
\Package.Variables[User::C_Invalid_Key].Properties[Namespace]
\Package.Variables[User::C_Invalid_Key].Properties[Name]

furmangg wrote Jul 20, 2010 at 1:16 PM

Can you attach the XML file?

Germo wrote Jul 20, 2010 at 5:03 PM

here it is

furmangg wrote Jul 22, 2010 at 12:59 AM

Your configuration string for the CONSTANTS configuration reads:
"Framework";"[dbo].[ETL_Config]";"PC"

When I create a new SQL Server configuration with the same specs, it reads:
"Framework";"[dbo].[ETL_Config]";"PC";

Notice there's a semi-colon on the end. Note even the COCONF configuration contains that ending semi-colon.

How did yours get that way? Were you editing the XML in the dtsx or something like that?

If you change the connection string in the ETL_Config table to something invalid, does it pick up that invalid connection string when you run the package? (I'm wanting to know if SSIS itself recognizes that configuration string format without the ending semi-colon.

furmangg wrote Jul 22, 2010 at 1:15 AM

I just checked in a fix for this. Thanks for reporting. When I studied the logic SSIS is using, it's more forgiving than BIDS Helper was about the format of the configuration string. This fix will be in the next release. Until then, a workaround would be to stick the semi-colon on the end.