Description for Configuration Values

Hi,
From where can I get the description for the Config Values for both SQL
2000 and SQL 2005
Similar to the one that SQL-DMO returns when queried for the description
of a particular configuration values, is it stored in some system tables.
The comment field gives very little information about the configuration
value as compared to the description given by SQL-DMO.
Also, the description field value in SQL 2005 is not the same when
queried through SQL-DMO.
e.g. In the table sys.configurations the config value "affinity mask"
has description "affinity mask"
but when queried thru SQL-DMO it gives the following
"Indicates which processors SQL Server may use (default is 0, or any).
A non-zero value is interpreted as a bit mask; e.g, processors 1, 2, and 5
are specified with a hexadecimal value of 0x13 or the decimal equivalent
of 19."
TIA
PraHi, Pra
It seems that these descriptions are stored in the SQLDMO.RLL file, so
I see no easy way of retrieiving them from T-SQL (since these
descriptions are not stored in any system table).
Razvan|||Thanks Razvan
Can u tell me one more thing how can I relate the config value with the
message.
Thanks
Pra
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1139988679.719312.27840@.g47g2000cwa.googlegroups.com...
> Hi, Pra
> It seems that these descriptions are stored in the SQLDMO.RLL file, so
> I see no easy way of retrieiving them from T-SQL (since these
> descriptions are not stored in any system table).
> Razvan
>|||Hi, Pra
You can use SQL-DMO to iterate through the each of the values in the
ConfigValues collection to get the Name and Description properties and
store them in your own table.
You may also want to take a look at:
http://msdn2.microsoft.com/en-us/library/ms131974.aspx
[url]http://msdn.microsoft.com/library/en-us/sqldmo/dmoref_cnst02_2w9x.asp?frame=true[/
url]
to check if you have found all the documented configuration values in
the ConfigValues collection.
Razvan|||Thanks
But I cant do that. Any other way.
Pra
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1140002871.149041.272720@.z14g2000cwz.googlegroups.com...
> Hi, Pra
> You can use SQL-DMO to iterate through the each of the values in the
> ConfigValues collection to get the Name and Description properties and
> store them in your own table.
> You may also want to take a look at:
> http://msdn2.microsoft.com/en-us/library/ms131974.aspx
> http://msdn.microsoft.com/library/e...me=true

> to check if you have found all the documented configuration values in
> the ConfigValues collection.
> Razvan
>|||Hi, Pra
Please tell me the reason why you can't do that, so I can think of
another way that works (not another way that you can't do, either). You
can also state the overall scenario of what you are trying to do with
the description and why you need this description instead of the
description from the sys.configurations table.
In the mean time, I would say that you can invoke the SQL-DMO code from
T-SQL using the sp_OACreate / sp_SetProperty / sp_OAGetProperty
procedures. This is the "non-easy" way that I thought when I wrote the
first message.
Razvan|||Thanks Razvan
I never knew about OLE prog. using T-SQL.
See the scenario is previously we used the SQL-DMO to report on SQL
Servers.
Now with Yukon coming into picture and SQL-DMO not fully compatible with
Yukon. I have to change the implementation to ADO. I can't use SMO bcoz
there is some code which is already written in ADO so I want reuse that, so
I was thinking of getting these values from the tables/system tables/views
anything but DMO and SMO. So, I'm hunting for description other
values I got throught the sysconfigures, syscurconfigs, spt_values, only
description I couldn't get.
I'm stuck only bcoz of this field.
Thanks
Pra
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1140007841.077219.207640@.g44g2000cwa.googlegroups.com...
> Hi, Pra
> Please tell me the reason why you can't do that, so I can think of
> another way that works (not another way that you can't do, either). You
> can also state the overall scenario of what you are trying to do with
> the description and why you need this description instead of the
> description from the sys.configurations table.
> In the mean time, I would say that you can invoke the SQL-DMO code from
> T-SQL using the sp_OACreate / sp_SetProperty / sp_OAGetProperty
> procedures. This is the "non-easy" way that I thought when I wrote the
> first message.
> Razvan
>|||Hi, Pra
What do you mean by "SQL-DMO not fully compatible with Yukon" ? As far
as I know, everything that you can do using SQL-DMO against a SQL
Server 2000 server, you can also do using SQL-DMO against a SQL Server
2005 server, as long as the updated SQL-DMO components are installed
(by default they are not).
If you need to use the new features of SQL Server 2005, you should use
SMO instead of SQL-DMO (especially if you are writing a .Net
application). SMO supports SQL Server 2000, too.
If you really don't want to use SQL-DMO or SMO, you can store the
descriptions in your application: retrieve them using SQL-DMO (only
once, using a separate little app) then store them as constants (or
something else) in your code.
Razvan|||Thanks Razvan
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1140015110.381400.268180@.g43g2000cwa.googlegroups.com...
> Hi, Pra
> What do you mean by "SQL-DMO not fully compatible with Yukon" ? As far
> as I know, everything that you can do using SQL-DMO against a SQL
> Server 2000 server, you can also do using SQL-DMO against a SQL Server
> 2005 server, as long as the updated SQL-DMO components are installed
> (by default they are not).
> If you need to use the new features of SQL Server 2005, you should use
> SMO instead of SQL-DMO (especially if you are writing a .Net
> application). SMO supports SQL Server 2000, too.
> If you really don't want to use SQL-DMO or SMO, you can store the
> descriptions in your application: retrieve them using SQL-DMO (only
> once, using a separate little app) then store them as constants (or
> something else) in your code.
> Razvan
>

Description for Configuration Values

Hi,
From where can I get the description for the Config Values for both SQL
2000 and SQL 2005
Similar to the one that SQL-DMO returns when queried for the description
of a particular configuration values, is it stored in some system tables.
The comment field gives very little information about the configuration
value as compared to the description given by SQL-DMO.
Also, the description field value in SQL 2005 is not the same when
queried through SQL-DMO.
e.g. In the table sys.configurations the config value "affinity mask"
has description "affinity mask"
but when queried thru SQL-DMO it gives the following
"Indicates which processors SQL Server may use (default is 0, or any).
A non-zero value is interpreted as a bit mask; e.g, processors 1, 2, and 5
are specified with a hexadecimal value of 0x13 or the decimal equivalent
of 19."
TIA
PrasadHi, Prasad
It seems that these descriptions are stored in the SQLDMO.RLL file, so
I see no easy way of retrieiving them from T-SQL (since these
descriptions are not stored in any system table).
Razvan|||Thanks Razvan
Can u tell me one more thing how can I relate the config value with the
message.
Thanks
Prasad
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1139988679.719312.27840@.g47g2000cwa.googlegroups.com...
> Hi, Prasad
> It seems that these descriptions are stored in the SQLDMO.RLL file, so
> I see no easy way of retrieiving them from T-SQL (since these
> descriptions are not stored in any system table).
> Razvan
>|||Hi, Prasad
You can use SQL-DMO to iterate through the each of the values in the
ConfigValues collection to get the Name and Description properties and
store them in your own table.
You may also want to take a look at:
http://msdn2.microsoft.com/en-us/library/ms131974.aspx
[url]http://msdn.microsoft.com/library/en-us/sqldmo/dmoref_cnst02_2w9x.asp?frame=true[/
url]
to check if you have found all the documented configuration values in
the ConfigValues collection.
Razvan|||Thanks
But I cant do that. Any other way.
Prasad
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1140002871.149041.272720@.z14g2000cwz.googlegroups.com...
> Hi, Prasad
> You can use SQL-DMO to iterate through the each of the values in the
> ConfigValues collection to get the Name and Description properties and
> store them in your own table.
> You may also want to take a look at:
> http://msdn2.microsoft.com/en-us/library/ms131974.aspx
> http://msdn.microsoft.com/library/e...me=true

> to check if you have found all the documented configuration values in
> the ConfigValues collection.
> Razvan
>|||Hi, Prasad
Please tell me the reason why you can't do that, so I can think of
another way that works (not another way that you can't do, either). You
can also state the overall scenario of what you are trying to do with
the description and why you need this description instead of the
description from the sys.configurations table.
In the mean time, I would say that you can invoke the SQL-DMO code from
T-SQL using the sp_OACreate / sp_SetProperty / sp_OAGetProperty
procedures. This is the "non-easy" way that I thought when I wrote the
first message.
Razvan|||Thanks Razvan
I never knew about OLE prog. using T-SQL.
See the scenario is previously we used the SQL-DMO to report on SQL
Servers.
Now with Yukon coming into picture and SQL-DMO not fully compatible with
Yukon. I have to change the implementation to ADO. I can't use SMO bcoz
there is some code which is already written in ADO so I want reuse that, so
I was thinking of getting these values from the tables/system tables/views
anything but DMO and SMO. So, I'm hunting for description other
values I got throught the sysconfigures, syscurconfigs, spt_values, only
description I couldn't get.
I'm stuck only bcoz of this field.
Thanks
Prasad
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1140007841.077219.207640@.g44g2000cwa.googlegroups.com...
> Hi, Prasad
> Please tell me the reason why you can't do that, so I can think of
> another way that works (not another way that you can't do, either). You
> can also state the overall scenario of what you are trying to do with
> the description and why you need this description instead of the
> description from the sys.configurations table.
> In the mean time, I would say that you can invoke the SQL-DMO code from
> T-SQL using the sp_OACreate / sp_SetProperty / sp_OAGetProperty
> procedures. This is the "non-easy" way that I thought when I wrote the
> first message.
> Razvan
>|||Hi, Prasad
What do you mean by "SQL-DMO not fully compatible with Yukon" ? As far
as I know, everything that you can do using SQL-DMO against a SQL
Server 2000 server, you can also do using SQL-DMO against a SQL Server
2005 server, as long as the updated SQL-DMO components are installed
(by default they are not).
If you need to use the new features of SQL Server 2005, you should use
SMO instead of SQL-DMO (especially if you are writing a .Net
application). SMO supports SQL Server 2000, too.
If you really don't want to use SQL-DMO or SMO, you can store the
descriptions in your application: retrieve them using SQL-DMO (only
once, using a separate little app) then store them as constants (or
something else) in your code.
Razvan|||Thanks Razvan
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1140015110.381400.268180@.g43g2000cwa.googlegroups.com...
> Hi, Prasad
> What do you mean by "SQL-DMO not fully compatible with Yukon" ? As far
> as I know, everything that you can do using SQL-DMO against a SQL
> Server 2000 server, you can also do using SQL-DMO against a SQL Server
> 2005 server, as long as the updated SQL-DMO components are installed
> (by default they are not).
> If you need to use the new features of SQL Server 2005, you should use
> SMO instead of SQL-DMO (especially if you are writing a .Net
> application). SMO supports SQL Server 2000, too.
> If you really don't want to use SQL-DMO or SMO, you can store the
> descriptions in your application: retrieve them using SQL-DMO (only
> once, using a separate little app) then store them as constants (or
> something else) in your code.
> Razvan
>

Description for Configuration Values

Hi,
From where can I get the description for the Config Values for both SQL
2000 and SQL 2005
Similar to the one that SQL-DMO returns when queried for the description
of a particular configuration values, is it stored in some system tables.
The comment field gives very little information about the configuration
value as compared to the description given by SQL-DMO.
Also, the description field value in SQL 2005 is not the same when
queried through SQL-DMO.
e.g. In the table sys.configurations the config value "affinity mask"
has description "affinity mask"
but when queried thru SQL-DMO it gives the following
"Indicates which processors SQL Server may use (default is 0, or any).
A non-zero value is interpreted as a bit mask; e.g, processors 1, 2, and 5
are specified with a hexadecimal value of 0x13 or the decimal equivalent
of 19."
TIA
Prasad
Hi, Prasad
It seems that these descriptions are stored in the SQLDMO.RLL file, so
I see no easy way of retrieiving them from T-SQL (since these
descriptions are not stored in any system table).
Razvan
|||Thanks Razvan
Can u tell me one more thing how can I relate the config value with the
message.
Thanks
Prasad
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1139988679.719312.27840@.g47g2000cwa.googlegro ups.com...
> Hi, Prasad
> It seems that these descriptions are stored in the SQLDMO.RLL file, so
> I see no easy way of retrieiving them from T-SQL (since these
> descriptions are not stored in any system table).
> Razvan
>
|||Hi, Prasad
You can use SQL-DMO to iterate through the each of the values in the
ConfigValues collection to get the Name and Description properties and
store them in your own table.
You may also want to take a look at:
http://msdn2.microsoft.com/en-us/library/ms131974.aspx
http://msdn.microsoft.com/library/en...asp?frame=true
to check if you have found all the documented configuration values in
the ConfigValues collection.
Razvan
|||Thanks
But I cant do that. Any other way.
Prasad
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1140002871.149041.272720@.z14g2000cwz.googlegr oups.com...
> Hi, Prasad
> You can use SQL-DMO to iterate through the each of the values in the
> ConfigValues collection to get the Name and Description properties and
> store them in your own table.
> You may also want to take a look at:
> http://msdn2.microsoft.com/en-us/library/ms131974.aspx
> http://msdn.microsoft.com/library/en...asp?frame=true
> to check if you have found all the documented configuration values in
> the ConfigValues collection.
> Razvan
>
|||Hi, Prasad
Please tell me the reason why you can't do that, so I can think of
another way that works (not another way that you can't do, either). You
can also state the overall scenario of what you are trying to do with
the description and why you need this description instead of the
description from the sys.configurations table.
In the mean time, I would say that you can invoke the SQL-DMO code from
T-SQL using the sp_OACreate / sp_SetProperty / sp_OAGetProperty
procedures. This is the "non-easy" way that I thought when I wrote the
first message.
Razvan
|||Thanks Razvan
I never knew about OLE prog. using T-SQL.
See the scenario is previously we used the SQL-DMO to report on SQL
Servers.
Now with Yukon coming into picture and SQL-DMO not fully compatible with
Yukon. I have to change the implementation to ADO. I can't use SMO bcoz
there is some code which is already written in ADO so I want reuse that, so
I was thinking of getting these values from the tables/system tables/views
anything but DMO and SMO. So, I'm hunting for description other
values I got throught the sysconfigures, syscurconfigs, spt_values, only
description I couldn't get.
I'm stuck only bcoz of this field.
Thanks
Prasad
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1140007841.077219.207640@.g44g2000cwa.googlegr oups.com...
> Hi, Prasad
> Please tell me the reason why you can't do that, so I can think of
> another way that works (not another way that you can't do, either). You
> can also state the overall scenario of what you are trying to do with
> the description and why you need this description instead of the
> description from the sys.configurations table.
> In the mean time, I would say that you can invoke the SQL-DMO code from
> T-SQL using the sp_OACreate / sp_SetProperty / sp_OAGetProperty
> procedures. This is the "non-easy" way that I thought when I wrote the
> first message.
> Razvan
>
|||Hi, Prasad
What do you mean by "SQL-DMO not fully compatible with Yukon" ? As far
as I know, everything that you can do using SQL-DMO against a SQL
Server 2000 server, you can also do using SQL-DMO against a SQL Server
2005 server, as long as the updated SQL-DMO components are installed
(by default they are not).
If you need to use the new features of SQL Server 2005, you should use
SMO instead of SQL-DMO (especially if you are writing a .Net
application). SMO supports SQL Server 2000, too.
If you really don't want to use SQL-DMO or SMO, you can store the
descriptions in your application: retrieve them using SQL-DMO (only
once, using a separate little app) then store them as constants (or
something else) in your code.
Razvan
|||Thanks Razvan
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1140015110.381400.268180@.g43g2000cwa.googlegr oups.com...
> Hi, Prasad
> What do you mean by "SQL-DMO not fully compatible with Yukon" ? As far
> as I know, everything that you can do using SQL-DMO against a SQL
> Server 2000 server, you can also do using SQL-DMO against a SQL Server
> 2005 server, as long as the updated SQL-DMO components are installed
> (by default they are not).
> If you need to use the new features of SQL Server 2005, you should use
> SMO instead of SQL-DMO (especially if you are writing a .Net
> application). SMO supports SQL Server 2000, too.
> If you really don't want to use SQL-DMO or SMO, you can store the
> descriptions in your application: retrieve them using SQL-DMO (only
> once, using a separate little app) then store them as constants (or
> something else) in your code.
> Razvan
>
sql

Description for Configuration Values

Hi,
From where can I get the description for the Config Values for both SQL
2000 and SQL 2005
Similar to the one that SQL-DMO returns when queried for the description
of a particular configuration values, is it stored in some system tables.
The comment field gives very little information about the configuration
value as compared to the description given by SQL-DMO.
Also, the description field value in SQL 2005 is not the same when
queried through SQL-DMO.
e.g. In the table sys.configurations the config value "affinity mask"
has description "affinity mask"
but when queried thru SQL-DMO it gives the following
"Indicates which processors SQL Server may use (default is 0, or any).
A non-zero value is interpreted as a bit mask; e.g, processors 1, 2, and 5
are specified with a hexadecimal value of 0x13 or the decimal equivalent
of 19."
TIA
PrasadHi, Prasad
It seems that these descriptions are stored in the SQLDMO.RLL file, so
I see no easy way of retrieiving them from T-SQL (since these
descriptions are not stored in any system table).
Razvan|||Thanks Razvan
Can u tell me one more thing how can I relate the config value with the
message.
Thanks
Prasad
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1139988679.719312.27840@.g47g2000cwa.googlegroups.com...
> Hi, Prasad
> It seems that these descriptions are stored in the SQLDMO.RLL file, so
> I see no easy way of retrieiving them from T-SQL (since these
> descriptions are not stored in any system table).
> Razvan
>|||Hi, Prasad
You can use SQL-DMO to iterate through the each of the values in the
ConfigValues collection to get the Name and Description properties and
store them in your own table.
You may also want to take a look at:
http://msdn2.microsoft.com/en-us/library/ms131974.aspx
http://msdn.microsoft.com/library/en-us/sqldmo/dmoref_cnst02_2w9x.asp?frame=true
to check if you have found all the documented configuration values in
the ConfigValues collection.
Razvan|||Thanks
But I cant do that. Any other way.
Prasad
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1140002871.149041.272720@.z14g2000cwz.googlegroups.com...
> Hi, Prasad
> You can use SQL-DMO to iterate through the each of the values in the
> ConfigValues collection to get the Name and Description properties and
> store them in your own table.
> You may also want to take a look at:
> http://msdn2.microsoft.com/en-us/library/ms131974.aspx
> http://msdn.microsoft.com/library/en-us/sqldmo/dmoref_cnst02_2w9x.asp?frame=true
> to check if you have found all the documented configuration values in
> the ConfigValues collection.
> Razvan
>|||Hi, Prasad
Please tell me the reason why you can't do that, so I can think of
another way that works (not another way that you can't do, either). You
can also state the overall scenario of what you are trying to do with
the description and why you need this description instead of the
description from the sys.configurations table.
In the mean time, I would say that you can invoke the SQL-DMO code from
T-SQL using the sp_OACreate / sp_SetProperty / sp_OAGetProperty
procedures. This is the "non-easy" way that I thought when I wrote the
first message.
Razvan|||Thanks Razvan
I never knew about OLE prog. using T-SQL.
See the scenario is previously we used the SQL-DMO to report on SQL
Servers.
Now with Yukon coming into picture and SQL-DMO not fully compatible with
Yukon. I have to change the implementation to ADO. I can't use SMO bcoz
there is some code which is already written in ADO so I want reuse that, so
I was thinking of getting these values from the tables/system tables/views
anything but DMO and SMO. So, I'm hunting for description other
values I got throught the sysconfigures, syscurconfigs, spt_values, only
description I couldn't get.
I'm stuck only bcoz of this field.
Thanks
Prasad
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1140007841.077219.207640@.g44g2000cwa.googlegroups.com...
> Hi, Prasad
> Please tell me the reason why you can't do that, so I can think of
> another way that works (not another way that you can't do, either). You
> can also state the overall scenario of what you are trying to do with
> the description and why you need this description instead of the
> description from the sys.configurations table.
> In the mean time, I would say that you can invoke the SQL-DMO code from
> T-SQL using the sp_OACreate / sp_SetProperty / sp_OAGetProperty
> procedures. This is the "non-easy" way that I thought when I wrote the
> first message.
> Razvan
>|||Hi, Prasad
What do you mean by "SQL-DMO not fully compatible with Yukon" ? As far
as I know, everything that you can do using SQL-DMO against a SQL
Server 2000 server, you can also do using SQL-DMO against a SQL Server
2005 server, as long as the updated SQL-DMO components are installed
(by default they are not).
If you need to use the new features of SQL Server 2005, you should use
SMO instead of SQL-DMO (especially if you are writing a .Net
application). SMO supports SQL Server 2000, too.
If you really don't want to use SQL-DMO or SMO, you can store the
descriptions in your application: retrieve them using SQL-DMO (only
once, using a separate little app) then store them as constants (or
something else) in your code.
Razvan|||Thanks Razvan
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1140015110.381400.268180@.g43g2000cwa.googlegroups.com...
> Hi, Prasad
> What do you mean by "SQL-DMO not fully compatible with Yukon" ? As far
> as I know, everything that you can do using SQL-DMO against a SQL
> Server 2000 server, you can also do using SQL-DMO against a SQL Server
> 2005 server, as long as the updated SQL-DMO components are installed
> (by default they are not).
> If you need to use the new features of SQL Server 2005, you should use
> SMO instead of SQL-DMO (especially if you are writing a .Net
> application). SMO supports SQL Server 2000, too.
> If you really don't want to use SQL-DMO or SMO, you can store the
> descriptions in your application: retrieve them using SQL-DMO (only
> once, using a separate little app) then store them as constants (or
> something else) in your code.
> Razvan
>

Deployment and Configuration

Hi:

I have a SSIS package on my local machine, and would like to deploy it to DEV server. Which files should i be moving to the SQL DEV Server? and where? How do I modify connection managers or is there a way to do that from the Management Studio or some other way? Thanks and I would definitely appreciate some prompt advise.

Someone will provide more information, I'm sure, but have you searched this forum for "deploy"?|||

Yes, I have checked out some posts on Deployment, but havent ran into anything standard. There seems to be more than one way of deploying. I would like to follow best practices. Thanks.

|||That's the beauty of SSIS. There are many ways to accomplish one task. The "best practice" is usually best determined on your own so that it fits your way of life. We each have our own requirements/standards so what my best practices are may not be Jamie's, or Rafael's, or.....|||

I prefer to have my packages as File system files (.dtsx); then all I have to do is to move (copy the files) them into a folder in the server. I use SQL Server table based package configurations to change connection strings at run time (and any other required property). I know there is a Deployment wizard in BIDS but I have never looked into that; so you do the research. Some times I use the SET option in the execution command line to override properties in the packages while testing.

As Phil points out; the best practice sometimes depends in your preferences, IT standards, etc....

|||

Lets say I would like to use the "copy over the files" method. What files am I moving over? and where on the server? Also for changing connection strings, how do i set the properties? Where? Thanks.

|||

Only the .dtsx files are required. In case you are using package configurations based on a XML file; you need to copy and secure it as appropriate. You choose the location; just keep in mind that that location needs to be accessible to the user running the packages.

To set connection strings; you can use package configurations; I use a combination of XML file to set the connection string of my SQL server based configuration table. If you search for 'Package configurations' you will find a lot of info. Jamie Thomson's blog, http://www.sqlis.com/ and BOL have some good info.

|||

The package I have will be scheduled daily, although, the connection strings config is only meant for Deployment Process, because the DTSX has to go thru DEV, QA, BETA, and onwards to PROD. Thanks.

|||

MA2005 wrote:

The package I have will be scheduled daily, although, the connection strings config is only meant for Deployment Process, because the DTSX has to go thru DEV, QA, BETA, and onwards to PROD. Thanks.

Your scenario is a very common one. SQL Server agent will be your best bet for scheduling your packages. I too, like Rafael, prefer to store my packages in the file system, versus storing them in MSDB.|||

If the only thing that needs to be changed/configured is a single connection string, and you have only one package (no parent package calling child packages)...you may want to use the SET option of the package execution command line instead of package configurations. I don’t like that approach when I have many packages that need to be configured in the same way; as I have to maintain every command line, as opposed of maintaining an entry in a config file/table

Deployment and Configuration

I have several clients using several SQL server databases that have all been
setup on one machine which they access remotely using terminal services. Each
client has their own software installation with their own set of databases.
So, there would be many client installations on the same machine. How would i
setup reporting services so that each client has their own set of reports?
How would i go about deploying the reports for each client given that the
clients' databases would have different names and hence the connection
strings will be different? Please help!
regardsSet up a datasource for each client - unless you have hundreds, in which case
I am not sure.sql

Deploying packages with SQL Server based configuration

Hello,

We have been conducting some testing regarding package deployment and SQL Server based configuration. It seems there is a problem that was documented in an MS Feedback entry (

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126797

)

I searched for a viable answer, work-around, or hotfix that would address this issue but found none.

The jist is that a package that uses SQL Server based configuration must have an xml-based configuration entry to "re-point" the SQL Server based configuration connection manager to the deployment target server. We cannot use environment variable or registry as this is against internal policy. The problem is that even though the xml based config is specified first in the list of package configurations it does not get applied first at run-time. So, when the package runs from a SQL Server Agent job the package's connection manager for the configuration entries is not updated.

The package runs correctly through BIDS. You can change the connection string in the .dtsConfig file and the SQL Server based package configuration is obtained from the correct source.

Environment is SQL Server Enterprise Edition 64-bit w/ SP1, Windows Server 2003 Enterprise Ed. 64-bit.

Does anyone have any experience with this issue? Know any hotfixes, other work-arounds?

Thanks in advance!

In terms of environment, 64 bit SSIS (w/ post SP1 hotfix) and Windows 2003 x64 SP1, we have the same setup. I am also running this exact scenario and have no problem repointing the SQL server Configuration's connection manager's connection string via a previous direct XML configuration.

So, the only thing I can tell you is to look for configuration warnings (unable to read configuration), which is a dead giveaway that you'll be pointing back to your design time config. (e.g. Warning with a message "Unable to read configuration file...").

Another diagnostic is to print out ("fire info events") your connection strings, presuming these are part of your SQL Server configuration, as a preliminary script task. This information will tell you right away if configurations are being properly overlaid.

Public Sub Main()
Dts.TaskResult = Dts.Results.Success
Dim conn As ConnectionManager

For Each conn In Dts.Connections
Dts.Events.FireInformation(0, String.Empty, String.Format("Connection: {0} DataSource: {1}", conn.Name, conn.ConnectionString), String.Empty, 0, False)
Next conn

End Sub|||

I ran accross that as well and thought I was sunk. Turns out it must be an old error and fixed at some point. (I'm running 2005 SP1).

We use an XML configuration in C:\MSSQL to define where the Configuration connection points to and pull all other entries from Configuration source. We just deploy an XML file to each developer desktop (that points to development) and on each of the servers (pointing to themselves). We can move packages from development to test to production with no issues as well as run them in debug mode on our local desktop. In the off chance you HAVE to run a production job in debug from your desktop, just replace your local XML file (and make sure you run with appropriate permissions).

To be more clear, our configuration looks like:

XML Configuration File
SQL Server

This seems to work well for us. Hope this helps,

Larry C

|||

Larry & jaegd,

Thank you for replying to this thread.

Ok, the package has two connection managers, 1. ConfigurationConnection used for the SQL based configuration connection 2. DW_ETL is used for the DataFlow and other tasks in the package.

Using the script from jaegd to log the connection strings for all of the connection managers in the package I have found that the connection managers' connection strings are being changed appropriately to their configured values as stored in the SQL based configuration.

However, I am seeing, and this is the observation that caused me to post here, that a package variable User::TestConfig is not being changed appropriately. The script is listed below. The variable User::TestConfig is specified to get its value from the SQL based configuration.

Am I missing something here, shouldn't the variable's value be changed to what is specifed in the confguration table?

Public Sub Main()

Dim conn As ConnectionManager

Dts.Events.FireInformation(0, String.Empty, String.Format(Dts.Variables("User::TestConfig").Value.ToString()), String.Empty, 0, False)

For Each conn In Dts.Connections

Dts.Events.FireInformation(0, String.Empty, String.Format("CONNECTION: {0} DATASOURCE: {1}", conn.Name, conn.ConnectionString), String.Empty, 0, False)

Next conn

Dts.Events.FireInformation(0, String.Empty, String.Format(Dts.Variables("User::TestConfig").Value.ToString()), String.Empty, 0, False)

Dts.TaskResult = Dts.Results.Success

End Sub

-Eric

|||

Wanted to update this thread.

I have confirmed, that a user variable that has its Value stored in configuration is not being properly updated from SQL based configuration. This happens when the package is run via a SQL Server Agent job. However, the Value is changed correctly when the package is run interactively through BIDS.

Is there some sort of caching mechanism in SSIS (just a swag) that is causing the variable's Value to NOT change even though it is set up to be configured from SQL based configuration.

As a reminder other information such as connection string which are stored in SQL based configuration are changing based on their configured values, this happens correctly in a SQL Agent environment as well as BIDS.

Thanks!

-Eric

|||

Larry,

I am curious, what environment are you running on where the xml and SQL based configuration is working properly? The following is my environment.

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

(No column name) (No column name) (No column name)
9.00.2153.00 SP1 Enterprise Edition (64-bit)

I am asking because using xml config to point the table-based configuration's connection string is not working for me. Are there any gotcha's regarding the connection managers, or the job step configuration, or....

In the package I specified the order of the package configurations with the xml config listed first, then the sql based config. In the job step I had added the location of the xml-based configuration in the configurations tab. I did not have any check boxes checked on the Data Sources tab in the job step. The configuration data itself was correct. But, the sql based configuration was not being pointed to the connection string that is specified in the xml config. The logging output showed that the sql-based connection string was changed, however the values that were specified in the sql-based configuration were not being updated.

Any tips or help would be most appreciated.

-Eric

|||

Eric,

Here's the values you requested.

(No column name) (No column name) (No column name)
9.00.2047.00 SP1 Enterprise Edition (64-bit)

As a validation I did the following:
I created an SSIS package
I created a Connection called Configuration using integrated security pointing to the Development server and SSIS configuration database.
I created a variable called Test and set the value to Test
I Enabled configuration
I created an XML configuration file at C:\MSSQL\Configuration.xml (I'd recommend using a default extension)
I put Configuration data source in the XML file
I named the Configuration XML
I added a new SQL Server Configuration, specified server, table, and Key
I chose to store the configured Value of the Test variable
I enabled Logging to a file C:\MSSQL\Logs\Sample.log
I added a Script Task
I set ReadOnly variable to Test
In Design Script I put the following

Public Sub Main()

Dts.Log(Dts.Variables("User::Test").Value.ToString, 0, Nothing)

Dts.TaskResult = Dts.Results.Success

End Sub

I enabled logging for the Script Task and enabled the ScriptTaskLogEntry

Important items:
The XML configuration file must be on a local hard drive
The developer will need an XML configuration file pointing to development
Each server will need an XML configuration file pointing to itself and the configuration database.
The database and table the configurations are stored in, must be what is referenced in it's local XML configuration file.
When creating an XML configuration for a new package Reuse the existing XML file.

The output from running on my desktop and the development server was:
User:ScriptTaskLogEntry,IT-HQ4Z971,CHARLOTTE\lcharlton,Script Task,{E14E7D5F-F31C-4717-A7E2-31FA3A12E157},{2179265C-2855-4A02-962F-4D1D5E2F27E0},11/1/2006 2:22:47 PM,11/1/2006 2:22:47 PM,0,(null),Test

The output from running on the production server was
User:ScriptTaskLogEntry,xxx-xxx-xx,CHARLOTTE\xxxxxxxxxx,Script Task,{E14E7D5F-F31C-4717-A7E2-31FA3A12E157},{FC1A8FF0-B4D4-4604-98C6-F5BD60C2DEE3},11/1/2006 2:27:57 PM,11/1/2006 2:27:57 PM,0,(null),Production

The configuration connection is getting reset correctly and the variable from configuration is comming from the production server. We have equally good luck setting other connections to different databases. I even have a SSIS package that updates the configuration entries for another SSIS package and then executes it, allowing us to iterate through a list of servers collecting information. This works as expected in Development, Test, and Production with the only thing needing to change being the configuration values in the SSIS database.

|||

Larry,

Thank you for the time and response in the validation of this. The description of your implementation including the "Important items:" helps me validate that we are attempting to do the same thing.

So, your version is the SQL Server 2005 Service Pack 1 build, x64, while my version is the Cumulative hotfix package (build 2153) for SQL Server 2005 build, x64.

I thought I recalled that this issue was fixed in SP1. I am wondering if this was "un-fixed", for lack of a better word, in the cumulative hotfix package?

I am thinking this may be the case.

Any ideas beyond taking this to PSS?

-Eric

|||Sorry, nothing here. PSS is probabbly your best bet.

Deploy to production using Configuration Properties

Hi,

When you check out the project properties of a RS project, you can find the deployment attributes like targetdatasourcefolder, etc... There's also a button "Configuration Manager...". Here I can see a column "Platform" which is empty. I got the feeling I should be able to configure different platforms here, select an active environment and deploy to it some how. Is this correct; how does this work, and why not?

Regards, Jeroen

The Platform thing is not what you're looking for (it's like different CPU types). It doesn't really apply to what you want to do, it's for compilation purposes.

You deploy to different servers by creating different named configurations (Debug, Production, etc) and setting different target servers for those configurations.

In the Configuration Manager you "attach" one of those named configurations for your report project to one or more configuration setups for the solution as a whole and specifying whether the report project should be build and/or deployed as part of the build process for that solution configuration.

In the Configuration Manager, you also specify one configuration for the solution as "active" so that when you choose to build that is the set of instructions should be used.

When you choose to build and/or deploy specifically for a your report project, rather than for the full solution, I think that the server you have specified in the currently-active configuration settings in Configuration Manager still apply.

I hope that makes sense <g>.

>L<

DependOnService does not work as expected

I have several services that retrieve their initialization
configuration from a database hosted on a SQL server express. All the
services have the SQL server defined in their DependOnService value in
the registry and should therefore not be launched until the SQL server
is ready to accept their connection. What I observe however is that
only a subset of the services is able to connect to the sql server,
and not even always the same subset.
I have tried to Google this problem but without success. Has no one
out there encountered this problem' If so, is there a solution to it?
Any ideas are welcome,
AriI seem to have found a solution: By pausing for 5seconds between
starting up my services they are all successful in connecting to the
database. The problem seems to be that SQL Server express is not able
to service 'simultaneous' logins.
/Ari
developari@.hotmail.com wrote:
> I have several services that retrieve their initialization
> configuration from a database hosted on a SQL server express. All the
> services have the SQL server defined in their DependOnService value in
> the registry and should therefore not be launched until the SQL server
> is ready to accept their connection. What I observe however is that
> only a subset of the services is able to connect to the sql server,
> and not even always the same subset.
> I have tried to Google this problem but without success. Has no one
> out there encountered this problem' If so, is there a solution to it?
> Any ideas are welcome,
> Ari|||<developari@.hotmail.com> wrote in message
news:1173271735.100019.112430@.s48g2000cws.googlegroups.com...
>I seem to have found a solution: By pausing for 5seconds between
> starting up my services they are all successful in connecting to the
> database. The problem seems to be that SQL Server express is not able
> to service 'simultaneous' logins.
>
More likely that SQL Server takes a few seconds to be ready to accept
connections, after the service starts up.
David|||Hi David,
It was my initial thought that the SQL Server had to wait some time
after the service started up before being able to accept connections.
But then I did some tests that dis-proved it: I tried waiting up to 5
minutes after the computer start-up before starting up the services
and then starting them up all in one go. This gave me the same
problems.
My conclusion is that the server is not able to accept such a rapid
succession of login-attempts.
Can you think of a better way of avoiding this problem then starting
up the services in a bat-file where I wait a few seconds between each
start-up?
/Ari
P.s. The SQL Server service is configured to start up automatically.
David Browne wrote:
> <developari@.hotmail.com> wrote in message
> news:1173271735.100019.112430@.s48g2000cws.googlegroups.com...
> >I seem to have found a solution: By pausing for 5seconds between
> > starting up my services they are all successful in connecting to the
> > database. The problem seems to be that SQL Server express is not able
> > to service 'simultaneous' logins.
> >
> More likely that SQL Server takes a few seconds to be ready to accept
> connections, after the service starts up.
> David