Integration Services Connection Managers Tips

In SQL Server 2000 connections were made by dragging a connection type to the designer. The problem was that the number of connections was limited, and many of them had to be made using a scripting language like VBScript or JScript. The arrival of SQL Server 2005 included a brand-new SQL Server Integration Services that offers many ways to extract, tranform, and load data. Along with those new methods came a certain re-arrangement of objects as well as new ways to connect to the objects needed to move and manipulate data. Perhaps the most important is the new Connection Manager.

In SQL Server 2000 DTS, creating a connection to an object is relatively straightforward but limited. Making a connection to a file, particularly if you need a dynamic connection string, likely requires a global variable, a dynamic properties task, and ActiveX scripting. Using ActiveX scripts in DTS packages tends to slow the package down because the code needs to be compiled at run-time. In SQL Server 2005 SSIS a connection to a flat file is much easier and makes use of new programming techniques, making the package run more efficiently and smoothly.

As a portion of our daily data upload process, we receive data in the form of Microsoft Access files (*.mdb) which needs to get uploaded to a SQL Server 2005 database. We want to build a SQL Server 2005 Integration Services package that loads all our Microsoft Access files and uploads data based on parameters in a table in our database. We want to make this an automated process, but the number of Access files may change over time. Do you have any solutions on how to build the SSIS package such that we do not have to modify our SSIS package for each new file?

In SQL Server 2000 DTS, creating a connection to an object is relatively straightforward but limited. Making a connection to a file, particularly if you need a dynamic connection string, likely requires a global variable, a dynamic properties task, and ActiveX scripting. Using ActiveX scripts in DTS packages tends to slow the package down because the code needs to be compiled at run-time. In SQL Server 2005 SSIS a connection to a flat file is much easier and makes use of new programming techniques, making the package run more efficiently and smoothly.

As a portion of our daily data upload process, we receive data in the form of Microsoft Access files (*.mdb) which needs to get uploaded to a SQL Server 2005 database. We want to build a SQL Server 2005 Integration Services package that loads all our Microsoft Access files and uploads data based on parameters in a table in our database. We want to make this an automated process, but the number of Access files may change over time. Do you have any solutions on how to build the SSIS package such that we do not have to modify our SSIS package for each new file?

In SQL Server 2000 connections were made by dragging a connection type to the designer. The problem was that the number of connections was limited, and many of them had to be made using a scripting language like VBScript or JScript. The arrival of SQL Server 2005 included a brand-new SQL Server Integration Services that offers many ways to extract, tranform, and load data. Along with those new methods came a certain re-arrangement of objects as well as new ways to connect to the objects needed to move and manipulate data. Perhaps the most important is the new Connection Manager.

As a portion of our daily data upload process, we receive data in the form of Microsoft Access files (*.mdb) which needs to get uploaded to a SQL Server 2005 database. We want to build a SQL Server 2005 Integration Services package that loads all our Microsoft Access files and uploads data based on parameters in a table in our database. We want to make this an automated process, but the number of Access files may change over time. Do you have any solutions on how to build the SSIS package such that we do not have to modify our SSIS package for each new file?

In SQL Server 2000 connections were made by dragging a connection type to the designer. The problem was that the number of connections was limited, and many of them had to be made using a scripting language like VBScript or JScript. The arrival of SQL Server 2005 included a brand-new SQL Server Integration Services that offers many ways to extract, tranform, and load data. Along with those new methods came a certain re-arrangement of objects as well as new ways to connect to the objects needed to move and manipulate data. Perhaps the most important is the new Connection Manager.

In SQL Server 2000 DTS, creating a connection to an object is relatively straightforward but limited. Making a connection to a file, particularly if you need a dynamic connection string, likely requires a global variable, a dynamic properties task, and ActiveX scripting. Using ActiveX scripts in DTS packages tends to slow the package down because the code needs to be compiled at run-time. In SQL Server 2005 SSIS a connection to a flat file is much easier and makes use of new programming techniques, making the package run more efficiently and smoothly.