Data Transformation Services Tips

Importing data into SQL Server 2000 using Data Transformation Services isn’t always just picking a source and destination and letting it fly. Sometimes data is formatted in a way that SQL Server won’t accept. In this case the "Transform" part of “Extract, Transform, and Load” (ETL) is most valuable, so the data can be formatted in a way that SQL Server can store as well as be used efficiently by your application. In this case we are specifically referring to invalid date formats, so here is one method for making the data transformation work. Let's work with an example.

During the creation of Data Transformation Services (DTS) packages, DTS checks to see if the objects that are referred to actually exist (i.e., connections). If the package is created on one server, but deployed on another, then some modification is necessary, otherwise the package will fail. There may also be times when you want to transfer DTS packages stored as SQL Server packages or perform other tasks using system tables. These tables do not appear in the source and destination lists of Transform Data Tasks, so you must change the table name manually. One method you can use to make corrections or access database objects not available through traditional means is the Disconnected Edit method.

Many of the objects in SQL 2000 DTS Packages require authentication to SQL Server when extracting, transforming and loading data. It is quick and easy to just add your SQL Server user name and password, but this can become a management issue. One alternative is a UDL file or Microsoft Data Link file.

Have you ever wanted to or needed to run a DTS package from outside of SQL Server? This could be from an application you develop or even just from a command line. Well SQL Server gives you the ability to run a DTS package by using the DTSRUN command in your applications, but in order to get this to work properly you need to construct the DTS command correctly.

Data doesn't always come in the acceptable formats used by SQL Server, requiring transformation prior to bringing the data into the database. The use of views to transform data formats can be done but requires moving the data twice-once to the working table and again to the production table. This may cause performance issues. Another method for transforming data is through the use of ActiveX transformations.

Have you ever wanted to or needed to run a DTS package from outside of SQL Server? This could be from an application you develop or even just from a command line. Well SQL Server gives you the ability to run a DTS package by using the DTSRUN command in your applications, but in order to get this to work properly you need to construct the DTS command correctly.

Importing data into SQL Server 2000 using Data Transformation Services isn’t always just picking a source and destination and letting it fly. Sometimes data is formatted in a way that SQL Server won’t accept. In this case the "Transform" part of “Extract, Transform, and Load” (ETL) is most valuable, so the data can be formatted in a way that SQL Server can store as well as be used efficiently by your application. In this case we are specifically referring to invalid date formats, so here is one method for making the data transformation work. Let's work with an example.

Many of the objects in SQL 2000 DTS Packages require authentication to SQL Server when extracting, transforming and loading data. It is quick and easy to just add your SQL Server user name and password, but this can become a management issue. One alternative is a UDL file or Microsoft Data Link file.

Data doesn't always come in the acceptable formats used by SQL Server, requiring transformation prior to bringing the data into the database. The use of views to transform data formats can be done but requires moving the data twice-once to the working table and again to the production table. This may cause performance issues. Another method for transforming data is through the use of ActiveX transformations.

During the creation of Data Transformation Services (DTS) packages, DTS checks to see if the objects that are referred to actually exist (i.e., connections). If the package is created on one server, but deployed on another, then some modification is necessary, otherwise the package will fail. There may also be times when you want to transfer DTS packages stored as SQL Server packages or perform other tasks using system tables. These tables do not appear in the source and destination lists of Transform Data Tasks, so you must change the table name manually. One method you can use to make corrections or access database objects not available through traditional means is the Disconnected Edit method.

During the creation of Data Transformation Services (DTS) packages, DTS checks to see if the objects that are referred to actually exist (i.e., connections). If the package is created on one server, but deployed on another, then some modification is necessary, otherwise the package will fail. There may also be times when you want to transfer DTS packages stored as SQL Server packages or perform other tasks using system tables. These tables do not appear in the source and destination lists of Transform Data Tasks, so you must change the table name manually. One method you can use to make corrections or access database objects not available through traditional means is the Disconnected Edit method.

Data doesn't always come in the acceptable formats used by SQL Server, requiring transformation prior to bringing the data into the database. The use of views to transform data formats can be done but requires moving the data twice-once to the working table and again to the production table. This may cause performance issues. Another method for transforming data is through the use of ActiveX transformations.

Importing data into SQL Server 2000 using Data Transformation Services isn’t always just picking a source and destination and letting it fly. Sometimes data is formatted in a way that SQL Server won’t accept. In this case the "Transform" part of “Extract, Transform, and Load” (ETL) is most valuable, so the data can be formatted in a way that SQL Server can store as well as be used efficiently by your application. In this case we are specifically referring to invalid date formats, so here is one method for making the data transformation work. Let's work with an example.

Many of the objects in SQL 2000 DTS Packages require authentication to SQL Server when extracting, transforming and loading data. It is quick and easy to just add your SQL Server user name and password, but this can become a management issue. One alternative is a UDL file or Microsoft Data Link file.

Have you ever wanted to or needed to run a DTS package from outside of SQL Server? This could be from an application you develop or even just from a command line. Well SQL Server gives you the ability to run a DTS package by using the DTSRUN command in your applications, but in order to get this to work properly you need to construct the DTS command correctly.