SSIS - one xml configuration file for multiple packages

#11

I messed around with storing ALL my connection info in one place like a
config file, but it cause issues:

Suppose Package 1 uses all 3 Connection Strings saved in the XML file. You
then create Package 2, which only needs 2 of those 3. You can hook it up to
the config file, but when it runs, the config file will want to try and
modify that 3rd Connection, which it can't find, causing an error.

I have worked with a combination of XML and SQL Server Configurations in
SSIS for a couple of years and have posted my methodology on a blog:

http://toddchitt.wordpress.com/2008/06/27/ssis_config

You are more than welcome to adapt it.
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]

"xyz_jat" wrote:

Quote:

FYI...I'm using SQL Server 2005 and BIDS 2005.

Thanks

"xyz_jat" wrote:

Hi,

Can we use one xml config file for multiple packages? If yes, how can we
store the different connection strings (like I have different database
connections, flat file connections, FTP connections for each package) in one
config file and how to idenetify that which connection string will be used by
which package?

Also, If I created one xml config file with connection strings for one
package then how and where can I specify that the other package can use the
same file and how to add 2nd package connection strings?

I have googled it but couldn't find any example with using one config file
for multiple packages. Though using some online help I tried to use env
variable along with XML file for 2 packages but I couldn't make any of the
package work.

Any help is appreciated.

Thanks

#12

I messed around with storing ALL my connection info in one place like a
config file, but it cause issues:

Suppose Package 1 uses all 3 Connection Strings saved in the XML file. You
then create Package 2, which only needs 2 of those 3. You can hook it up to
the config file, but when it runs, the config file will want to try and
modify that 3rd Connection, which it can't find, causing an error.

I have worked with a combination of XML and SQL Server Configurations in
SSIS for a couple of years and have posted my methodology on a blog:

http://toddchitt.wordpress.com/2008/06/27/ssis_config

You are more than welcome to adapt it.
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]

"xyz_jat" wrote:

Quote:

FYI...I'm using SQL Server 2005 and BIDS 2005.

Thanks

"xyz_jat" wrote:

Hi,

Can we use one xml config file for multiple packages? If yes, how can we
store the different connection strings (like I have different database
connections, flat file connections, FTP connections for each package) in one
config file and how to idenetify that which connection string will be used by
which package?

Also, If I created one xml config file with connection strings for one
package then how and where can I specify that the other package can use the
same file and how to add 2nd package connection strings?

I have googled it but couldn't find any example with using one config file
for multiple packages. Though using some online help I tried to use env
variable along with XML file for 2 packages but I couldn't make any of the
package work.

Any help is appreciated.

Thanks

#13

I messed around with storing ALL my connection info in one place like a
config file, but it cause issues:

Suppose Package 1 uses all 3 Connection Strings saved in the XML file. You
then create Package 2, which only needs 2 of those 3. You can hook it up to
the config file, but when it runs, the config file will want to try and
modify that 3rd Connection, which it can't find, causing an error.

I have worked with a combination of XML and SQL Server Configurations in
SSIS for a couple of years and have posted my methodology on a blog:

http://toddchitt.wordpress.com/2008/06/27/ssis_config

You are more than welcome to adapt it.
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]

"xyz_jat" wrote:

Quote:

FYI...I'm using SQL Server 2005 and BIDS 2005.

Thanks

"xyz_jat" wrote:

Hi,

Can we use one xml config file for multiple packages? If yes, how can we
store the different connection strings (like I have different database
connections, flat file connections, FTP connections for each package) in one
config file and how to idenetify that which connection string will be used by
which package?

Also, If I created one xml config file with connection strings for one
package then how and where can I specify that the other package can use the
same file and how to add 2nd package connection strings?

I have googled it but couldn't find any example with using one config file
for multiple packages. Though using some online help I tried to use env
variable along with XML file for 2 packages but I couldn't make any of the
package work.

Any help is appreciated.

Thanks

#14

I messed around with storing ALL my connection info in one place like a
config file, but it cause issues:

Suppose Package 1 uses all 3 Connection Strings saved in the XML file. You
then create Package 2, which only needs 2 of those 3. You can hook it up to
the config file, but when it runs, the config file will want to try and
modify that 3rd Connection, which it can't find, causing an error.

I have worked with a combination of XML and SQL Server Configurations in
SSIS for a couple of years and have posted my methodology on a blog:

http://toddchitt.wordpress.com/2008/06/27/ssis_config

You are more than welcome to adapt it.
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]

"xyz_jat" wrote:

Quote:

FYI...I'm using SQL Server 2005 and BIDS 2005.

Thanks

"xyz_jat" wrote:

Hi,

Can we use one xml config file for multiple packages? If yes, how can we
store the different connection strings (like I have different database
connections, flat file connections, FTP connections for each package) in one
config file and how to idenetify that which connection string will be used by
which package?

Also, If I created one xml config file with connection strings for one
package then how and where can I specify that the other package can use the
same file and how to add 2nd package connection strings?

I have googled it but couldn't find any example with using one config file
for multiple packages. Though using some online help I tried to use env
variable along with XML file for 2 packages but I couldn't make any of the
package work.

Any help is appreciated.

Thanks

#15

I messed around with storing ALL my connection info in one place like a
config file, but it cause issues:

Suppose Package 1 uses all 3 Connection Strings saved in the XML file. You
then create Package 2, which only needs 2 of those 3. You can hook it up to
the config file, but when it runs, the config file will want to try and
modify that 3rd Connection, which it can't find, causing an error.

I have worked with a combination of XML and SQL Server Configurations in
SSIS for a couple of years and have posted my methodology on a blog:

http://toddchitt.wordpress.com/2008/06/27/ssis_config

You are more than welcome to adapt it.
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]

"xyz_jat" wrote:

Quote:

FYI...I'm using SQL Server 2005 and BIDS 2005.

Thanks

"xyz_jat" wrote:

Hi,

Can we use one xml config file for multiple packages? If yes, how can we
store the different connection strings (like I have different database
connections, flat file connections, FTP connections for each package) in one
config file and how to idenetify that which connection string will be used by
which package?

Also, If I created one xml config file with connection strings for one
package then how and where can I specify that the other package can use the
same file and how to add 2nd package connection strings?

I have googled it but couldn't find any example with using one config file
for multiple packages. Though using some online help I tried to use env
variable along with XML file for 2 packages but I couldn't make any of the
package work.

Any help is appreciated.

Thanks

#16

I messed around with storing ALL my connection info in one place like a
config file, but it cause issues:

Suppose Package 1 uses all 3 Connection Strings saved in the XML file. You
then create Package 2, which only needs 2 of those 3. You can hook it up to
the config file, but when it runs, the config file will want to try and
modify that 3rd Connection, which it can't find, causing an error.

I have worked with a combination of XML and SQL Server Configurations in
SSIS for a couple of years and have posted my methodology on a blog:

http://toddchitt.wordpress.com/2008/06/27/ssis_config

You are more than welcome to adapt it.
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]

"xyz_jat" wrote:

Quote:

FYI...I'm using SQL Server 2005 and BIDS 2005.

Thanks

"xyz_jat" wrote:

Hi,

Can we use one xml config file for multiple packages? If yes, how can we
store the different connection strings (like I have different database
connections, flat file connections, FTP connections for each package) in one
config file and how to idenetify that which connection string will be used by
which package?

Also, If I created one xml config file with connection strings for one
package then how and where can I specify that the other package can use the
same file and how to add 2nd package connection strings?

I have googled it but couldn't find any example with using one config file
for multiple packages. Though using some online help I tried to use env
variable along with XML file for 2 packages but I couldn't make any of the
package work.

Any help is appreciated.

Thanks

#17

I messed around with storing ALL my connection info in one place like a
config file, but it cause issues:

Suppose Package 1 uses all 3 Connection Strings saved in the XML file. You
then create Package 2, which only needs 2 of those 3. You can hook it up to
the config file, but when it runs, the config file will want to try and
modify that 3rd Connection, which it can't find, causing an error.

I have worked with a combination of XML and SQL Server Configurations in
SSIS for a couple of years and have posted my methodology on a blog:

http://toddchitt.wordpress.com/2008/06/27/ssis_config

You are more than welcome to adapt it.
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]

"xyz_jat" wrote:

Quote:

FYI...I'm using SQL Server 2005 and BIDS 2005.

Thanks

"xyz_jat" wrote:

Hi,

Can we use one xml config file for multiple packages? If yes, how can we
store the different connection strings (like I have different database
connections, flat file connections, FTP connections for each package) in one
config file and how to idenetify that which connection string will be used by
which package?

Also, If I created one xml config file with connection strings for one
package then how and where can I specify that the other package can use the
same file and how to add 2nd package connection strings?

I have googled it but couldn't find any example with using one config file
for multiple packages. Though using some online help I tried to use env
variable along with XML file for 2 packages but I couldn't make any of the
package work.

Any help is appreciated.

Thanks

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SSIS - one xml configuration file for multiple packages

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts

RE: SSIS - one xml configuration file for multiple packages - 11-20-2008 , 08:09 AM

RE: SSIS - one xml configuration file for multiple packages - 11-20-2008 , 08:09 AM

RE: SSIS - one xml configuration file for multiple packages - 11-20-2008 , 08:09 AM

RE: SSIS - one xml configuration file for multiple packages - 11-20-2008 , 08:09 AM

RE: SSIS - one xml configuration file for multiple packages - 11-20-2008 , 08:09 AM

RE: SSIS - one xml configuration file for multiple packages - 11-20-2008 , 08:09 AM

RE: SSIS - one xml configuration file for multiple packages - 11-20-2008 , 08:09 AM