Hello--
Is it possible to install/configure SQL-Server 2005 on a multi-processor machine so that the relational DB utilizes a given subset of processors while Analysis Services utilizes another subset?
Thanks,
- Paul
No, in the current version it is not possible to limit Analysis Services to a subset of the CPUs|||Although you could use OS services to do such a thing, we haven't tried it ourselves, though (you could let everyone know how it works out!
)
I try to store a SSIS-configuration value in the registry.
But it's failed. I get a warning, that the registry-key is not found.
What is the syntax ? I have searched in BO,MSDN and Google/Internet ==> nothing.
There is no example.
The syntax of how to specify the name of the registry key is straightforward. See BOL: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/uirfsql9/html/212e52e7-27a7-48bb-8bad-34aa290d2003.htm
If you're asking how to define the syntax of a configuration (i.e. The thing that should be IN the registry key) then that's a bit more difficult. A quick scan of BOl didn't bring anything up. The way I always do it is to create an XML COnfiguration file from within SSIS Designer and then copy and paste the resultant configuration into the registry key.
Hope that halps.
-Jamie
|||Hey Jamie,
there is no example:
Configuration Type Option = Registry Entry
Specify configuration settings directly
Use to specify settings directly.
Value Description
Registry entry
Type the registry key that contains the configuration information.
I think it is very easy, but I don't know how to type the registry key!
With regedit I have defined a new entry:
HKEY_LOCAL_MACHINE\SOFTWARE\SSIS\Var
Var is a string and contains for example the servername.
What is the syntax in the SSIS-Configuration-Wizard?
You create an XML-Configuration file then you copy and paste the result into the registry key?
The registry key can store only one proberty. The XML-Configuration can store many proberties. You have an example?
Thanks
Loom
|||It sounds to me as though you have done everything that you need to do. What error are you getting when you try and usee it?
-Jamie
|||
In the german version I get this warning:
Warnung: 0x80012015 bei Package: Der Konfigurationsregistrierungsschlüssel 'HKEY_LOCAL_MACHINE\SOFTWARE\SSIS\Var' wurde nicht gefunden. Für einen Konfigurationseintrag ist ein nicht verfügbarer Registrierungsschlüssel angegeben. überprüfen Sie die Registrierung, und stellen Sie sicher, dass der Schlüssel vorhanden ist.
Translation:
The Configuration-Registry-Key 'HKEY_LOCAL_MACHINE\SOFTWARE\SSIS\Var' was not found.
Loom
|||The key must be defined in HKCU.
The Value must be stored in the Value key.
HKCU\Configuration\FirstConfig\Value
HKCU\Configuration\SecondConfig\Value
The ConfigString is Configuration\FirstConfig , this is the string you put into the Configuration String field in the Configuration Wizard.
The Registry Configuration assumes the HKCU and Value sections. Everything in between can be whatever you want.
HTH,
K
|||
Kirk,
Is this information in BOL anywhere? If it isn't, it should be.
-Jamie
|||Nah, don't think so. I couldn't find it anywhere...|||
Bingo!
I have tried everything, but not the value key....
To store the configuration in the registry has an advantage compared with an environment-variable. A change in the registry automaticly change your configuration.
A change of an environment-variable has no effect in the BI-Studio. The only way to refresh the variable is: Close the BI-Studio and then reopen it.
And this is a problem for a developer. Environment-variables are not the first choice for switching between Dev / Test / Pro - Servers.
Or I have missed something?
Loom
|||You're right that you need to shut down BIDS for the EV Config to take effect, but, why does that matter? You can simply change the value of the configured property as though the configuration had been applied.
Again, it's a preference thing. That's why there are several options.
Environment Variables are the only option for Indirect Configurations.
K
|||There is another option for Indirect Configuration: The SQL-Server option.
The SQL-Server use a normal connection-manager and you can configure the proberties of this connection-manager.
In the FirstConfig you can change the ServerName and the InitialCatalog of the connection-manager.
In the SecondConfig you use the SQL-Server-Option with the changed ServerName/InitialCatalog.
In my opinion it is an indirect configuration too.
Loom
|||Yep, got that right. Very flexible, yes.
K
|||Loom wrote:
Bingo!
I have tried everything, but not the value key....
To store the configuration in the registry has an advantage compared with an environment-variable. A change in the registry automaticly change your configuration.
A change of an environment-variable has no effect in the BI-Studio. The only way to refresh the variable is: Close the BI-Studio and then reopen it.
And this is a problem for a developer. Environment-variables are not the first choice for switching between Dev / Test / Pro - Servers.
Or I have missed something?
Loom
Its a matter of opinion isn't it? there isn't one right answer. Personally I think the issue about having to close BIDS isn't really a problem as long as you know about the issue.
I use indirect configurations exclusively. That's just my choice.
-Jamie
|||
I try to use this option to change the "conenction string" property of a Flat file connection, but nothing happens.
What is the correct format of registry Key string to fill the box?
I try :
HKEY_LOCAL_MACHINE\SOFTWARE\ProjectName\Connections\ConnectionString1
and
[HKEY_LOCAL_MACHINE\SOFTWARE\ProjectName\Connections\ConnectionString1]
and
"HKEY_LOCAL_MACHINE\SOFTWARE\ProjectName\Connections\ConnectionString1"
The value Key "ConnectionString1" is a String Value that contains the address of file like: C:\input files\Test.csv
Don't Work!!!! What′s wrong?
Thanks
|||In the registry you have this entry:
HKEY_LOCAL_MACHINE\SOFTWARE\ProjectName\Connections\ConnectionString1\value
In Value you store your connection string
In the SSIS-Configuration the reference is:
SOFTWARE\ProjectName\Connections\ConnectionString1
Loom
I try to store a SSIS-configuration value in the registry.
But it's failed. I get a warning, that the registry-key is not found.
What is the syntax ? I have searched in BO,MSDN and Google/Internet ==> nothing.
There is no example.
The syntax of how to specify the name of the registry key is straightforward. See BOL: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/uirfsql9/html/212e52e7-27a7-48bb-8bad-34aa290d2003.htm
If you're asking how to define the syntax of a configuration (i.e. The thing that should be IN the registry key) then that's a bit more difficult. A quick scan of BOl didn't bring anything up. The way I always do it is to create an XML COnfiguration file from within SSIS Designer and then copy and paste the resultant configuration into the registry key.
Hope that halps.
-Jamie
|||Hey Jamie,
there is no example:
Configuration Type Option = Registry Entry
Specify configuration settings directly
Use to specify settings directly.
Value Description
Registry entry
Type the registry key that contains the configuration information.
I think it is very easy, but I don't know how to type the registry key!
With regedit I have defined a new entry:
HKEY_LOCAL_MACHINE\SOFTWARE\SSIS\Var
Var is a string and contains for example the servername.
What is the syntax in the SSIS-Configuration-Wizard?
You create an XML-Configuration file then you copy and paste the result into the registry key?
The registry key can store only one proberty. The XML-Configuration can store many proberties. You have an example?
Thanks
Loom
|||It sounds to me as though you have done everything that you need to do. What error are you getting when you try and usee it?
-Jamie
|||
In the german version I get this warning:
Warnung: 0x80012015 bei Package: Der Konfigurationsregistrierungsschlüssel 'HKEY_LOCAL_MACHINE\SOFTWARE\SSIS\Var' wurde nicht gefunden. Für einen Konfigurationseintrag ist ein nicht verfügbarer Registrierungsschlüssel angegeben. überprüfen Sie die Registrierung, und stellen Sie sicher, dass der Schlüssel vorhanden ist.
Translation:
The Configuration-Registry-Key 'HKEY_LOCAL_MACHINE\SOFTWARE\SSIS\Var' was not found.
Loom
|||The key must be defined in HKCU.
The Value must be stored in the Value key.
HKCU\Configuration\FirstConfig\Value
HKCU\Configuration\SecondConfig\Value
The ConfigString is Configuration\FirstConfig , this is the string you put into the Configuration String field in the Configuration Wizard.
The Registry Configuration assumes the HKCU and Value sections. Everything in between can be whatever you want.
HTH,
K
|||
Kirk,
Is this information in BOL anywhere? If it isn't, it should be.
-Jamie
|||Nah, don't think so. I couldn't find it anywhere...|||
Bingo!
I have tried everything, but not the value key....
To store the configuration in the registry has an advantage compared with an environment-variable. A change in the registry automaticly change your configuration.
A change of an environment-variable has no effect in the BI-Studio. The only way to refresh the variable is: Close the BI-Studio and then reopen it.
And this is a problem for a developer. Environment-variables are not the first choice for switching between Dev / Test / Pro - Servers.
Or I have missed something?
Loom
|||You're right that you need to shut down BIDS for the EV Config to take effect, but, why does that matter? You can simply change the value of the configured property as though the configuration had been applied.
Again, it's a preference thing. That's why there are several options.
Environment Variables are the only option for Indirect Configurations.
K
|||There is another option for Indirect Configuration: The SQL-Server option.
The SQL-Server use a normal connection-manager and you can configure the proberties of this connection-manager.
In the FirstConfig you can change the ServerName and the InitialCatalog of the connection-manager.
In the SecondConfig you use the SQL-Server-Option with the changed ServerName/InitialCatalog.
In my opinion it is an indirect configuration too.
Loom
|||Yep, got that right. Very flexible, yes.
K
|||Bingo!
I have tried everything, but not the value key....
To store the configuration in the registry has an advantage compared with an environment-variable. A change in the registry automaticly change your configuration.
A change of an environment-variable has no effect in the BI-Studio. The only way to refresh the variable is: Close the BI-Studio and then reopen it.
And this is a problem for a developer. Environment-variables are not the first choice for switching between Dev / Test / Pro - Servers.
Or I have missed something?
Loom
Its a matter of opinion isn't it? there isn't one right answer. Personally I think the issue about having to close BIDS isn't really a problem as long as you know about the issue.
I use indirect configurations exclusively. That's just my choice.
-Jamie
|||
I try to use this option to change the "conenction string" property of a Flat file connection, but nothing happens.
What is the correct format of registry Key string to fill the box?
I try :
HKEY_LOCAL_MACHINE\SOFTWARE\ProjectName\Connections\ConnectionString1
and
[HKEY_LOCAL_MACHINE\SOFTWARE\ProjectName\Connections\ConnectionString1]
and
"HKEY_LOCAL_MACHINE\SOFTWARE\ProjectName\Connections\ConnectionString1"
The value Key "ConnectionString1" is a String Value that contains the address of file like: C:\input files\Test.csv
Don't Work!!!! What′s wrong?
Thanks
|||In the registry you have this entry:
HKEY_LOCAL_MACHINE\SOFTWARE\ProjectName\Connections\ConnectionString1\value
In Value you store your connection string
In the SSIS-Configuration the reference is:
SOFTWARE\ProjectName\Connections\ConnectionString1
Loom
I try to store a SSIS-configuration value in the registry.
But it's failed. I get a warning, that the registry-key is not found.
What is the syntax ? I have searched in BO,MSDN and Google/Internet ==> nothing.
There is no example.
The syntax of how to specify the name of the registry key is straightforward. See BOL: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/uirfsql9/html/212e52e7-27a7-48bb-8bad-34aa290d2003.htm
If you're asking how to define the syntax of a configuration (i.e. The thing that should be IN the registry key) then that's a bit more difficult. A quick scan of BOl didn't bring anything up. The way I always do it is to create an XML COnfiguration file from within SSIS Designer and then copy and paste the resultant configuration into the registry key.
Hope that halps.
-Jamie
|||Hey Jamie,
there is no example:
Configuration Type Option = Registry Entry
Specify configuration settings directly
Use to specify settings directly.
Value Description
Registry entry
Type the registry key that contains the configuration information.
I think it is very easy, but I don't know how to type the registry key!
With regedit I have defined a new entry:
HKEY_LOCAL_MACHINE\SOFTWARE\SSIS\Var
Var is a string and contains for example the servername.
What is the syntax in the SSIS-Configuration-Wizard?
You create an XML-Configuration file then you copy and paste the result into the registry key?
The registry key can store only one proberty. The XML-Configuration can store many proberties. You have an example?
Thanks
Loom
|||It sounds to me as though you have done everything that you need to do. What error are you getting when you try and usee it?
-Jamie
|||
In the german version I get this warning:
Warnung: 0x80012015 bei Package: Der Konfigurationsregistrierungsschlüssel 'HKEY_LOCAL_MACHINE\SOFTWARE\SSIS\Var' wurde nicht gefunden. Für einen Konfigurationseintrag ist ein nicht verfügbarer Registrierungsschlüssel angegeben. überprüfen Sie die Registrierung, und stellen Sie sicher, dass der Schlüssel vorhanden ist.
Translation:
The Configuration-Registry-Key 'HKEY_LOCAL_MACHINE\SOFTWARE\SSIS\Var' was not found.
Loom
|||The key must be defined in HKCU.
The Value must be stored in the Value key.
HKCU\Configuration\FirstConfig\Value
HKCU\Configuration\SecondConfig\Value
The ConfigString is Configuration\FirstConfig , this is the string you put into the Configuration String field in the Configuration Wizard.
The Registry Configuration assumes the HKCU and Value sections. Everything in between can be whatever you want.
HTH,
K
|||
Kirk,
Is this information in BOL anywhere? If it isn't, it should be.
-Jamie
|||Nah, don't think so. I couldn't find it anywhere...|||
Bingo!
I have tried everything, but not the value key....
To store the configuration in the registry has an advantage compared with an environment-variable. A change in the registry automaticly change your configuration.
A change of an environment-variable has no effect in the BI-Studio. The only way to refresh the variable is: Close the BI-Studio and then reopen it.
And this is a problem for a developer. Environment-variables are not the first choice for switching between Dev / Test / Pro - Servers.
Or I have missed something?
Loom
|||You're right that you need to shut down BIDS for the EV Config to take effect, but, why does that matter? You can simply change the value of the configured property as though the configuration had been applied.
Again, it's a preference thing. That's why there are several options.
Environment Variables are the only option for Indirect Configurations.
K
|||There is another option for Indirect Configuration: The SQL-Server option.
The SQL-Server use a normal connection-manager and you can configure the proberties of this connection-manager.
In the FirstConfig you can change the ServerName and the InitialCatalog of the connection-manager.
In the SecondConfig you use the SQL-Server-Option with the changed ServerName/InitialCatalog.
In my opinion it is an indirect configuration too.
Loom
|||Yep, got that right. Very flexible, yes.
K
|||Loom wrote:
Bingo!
I have tried everything, but not the value key....
To store the configuration in the registry has an advantage compared with an environment-variable. A change in the registry automaticly change your configuration.
A change of an environment-variable has no effect in the BI-Studio. The only way to refresh the variable is: Close the BI-Studio and then reopen it.
And this is a problem for a developer. Environment-variables are not the first choice for switching between Dev / Test / Pro - Servers.
Or I have missed something?
Loom
Its a matter of opinion isn't it? there isn't one right answer. Personally I think the issue about having to close BIDS isn't really a problem as long as you know about the issue.
I use indirect configurations exclusively. That's just my choice.
-Jamie
|||
I try to use this option to change the "conenction string" property of a Flat file connection, but nothing happens.
What is the correct format of registry Key string to fill the box?
I try :
HKEY_LOCAL_MACHINE\SOFTWARE\ProjectName\Connections\ConnectionString1
and
[HKEY_LOCAL_MACHINE\SOFTWARE\ProjectName\Connections\ConnectionString1]
and
"HKEY_LOCAL_MACHINE\SOFTWARE\ProjectName\Connections\ConnectionString1"
The value Key "ConnectionString1" is a String Value that contains the address of file like: C:\input files\Test.csv
Don't Work!!!! What′s wrong?
Thanks
|||In the registry you have this entry:
HKEY_LOCAL_MACHINE\SOFTWARE\ProjectName\Connections\ConnectionString1\value
In Value you store your connection string
In the SSIS-Configuration the reference is:
SOFTWARE\ProjectName\Connections\ConnectionString1
Loom
Hi,
Quite new to SSIS am currently trying to set up a registry configuration which will hold a connection string which will enable the main SQL configurations for a package to load. I wish to put the registry entry under the HKEY_LOCAL_MACHINE directory or more precisely in a key such as below:
HKEY_LOCAL_MACHINE\SOFTWARE\My Company\My Project\ETLConfigDbString
Have set this key up with a default value of the connection string and an associated registry configuration with the above as the value in the regsitry entry box. This configuration is listed as the first configurartion for the package to ensure it gets applied before the SQL configuration loads.
However on load or execute I get a warning of the type
A configuration entry specifies a registry key that is not available. Check the registry to ensure that the key is there.
Are there any known problems with regsitry configurations or am I just missing something obvious here ?
Thanks
Right may have answered my own question. Digging around on tghe Net a bit more I think it's that you can only have a registry entry which is under HKEY_CURRENT_USER (or one of its subkeys). It also must have a value called Value which must be a DWORD or string.
Can someone confirm this is correct ? Was hoping to use the HKEY_LOCAL_MACHINE key as that's what our company already uses for other software values but obviously if SSIS is restricted to HKEY_CURRENT_USER then that's not going to work.
Thanks
|||From the example given in Books Online, it does appear that keys have to be located under HKEY_CURRENT_USER. However, the text isn't as clear as it could be, so I'm not sure this is a definitive answer.
|||
Thanks John. I couldn't get the HKEY_LOCAL_MACHINE key to work so I've switched to using a system enviroment variable instead now. Didn't want to use HKEY_CURRENT_USER as the SQL configuration database is always going to be the same regardless of who the user running the package is so it didn't seem a good solution to use HKEY_CURRENT_USER.
To be honest (IMO) it's not really paticularly good from a deployment/production point of view either. I was intending to supply our DBA with a .reg key they could run to set up the registry entry put obviously I can't do that if I have to use HKEY_CURRENT_USER as I don't know what the SQL Agent user GUID is going to be in the registry.
So all in all a system enviroment variable seemed a better solution.
|||I prefer environment variables myself, or a single XML configuration file.
