Microsoft SQL Server Product Samples: Integration Services

Source code checked in, #11622

ReedMe — Wed, 11 Jun 2008 05:42:45 GMT

Adding 'Katmai:June 2008 RC0'

Updated Wiki: SS2008!Integration Services Tutorial Package Samples

bonniefe — Wed, 11 Jun 2008 01:22:04 GMT

Readme_Creating a Simple ETL Package Tutorial: Lesson Packages

6/10/2008 1:32:27 AM

This sample works only with SQL Server 2005 and SQL Server 2008. It will not work with any version of SQL Server earlier than SQL Server 2005.
The Lesson 1, Lesson2, Lesson 3, Lesson 4, and Lesson 5 packages are the completed packages from the Integration Services tutorial, Tutorial: Creating a Simple ETL Package. The topics in Books Online provide detailed information about the functionality of each package. For more information, see the topics in "Creating a Simple ETL Package Tutorial" located in the Integration Services Tutorials section of Books Online. Each lesson's package builds on the version of the package created in the earlier lessons.
You can open and run these packages outside the tutorial. However, we do not recommend that you modify them. Modification of tutorial packages may give you results that differ from those defined in the tutorial lessons.
The following table provides a brief summary of each package.

Lessons	Description
Lesson 1	A simple ETL package that extracts data from a single flat file, transforms the data by using lookup transformations, and finally loads the result into a fact table.
Lesson 2	In this lesson, you will expand the package you created in Lesson 1 to take advantage of new looping features to extract multiple flat files into a single data flow process.
Lesson 3	In this lesson, you will expand the package you created in Lesson 2 to use a package configuration, variable, and property expression to dynamically update a property.
Lesson 4	In this lesson, you will expand the package you created in Lesson 3 to add logging.
Lesson 5	In this lesson, you will expand the package you created in lesson 4 to use error redirection to capture invalid data in a text file.

Important:
Samples are provided for educational purposes only. They are not intended to be used in a production environment and have not been tested in a production environment. Microsoft does not provide technical support for these samples.

Requirements

Running this sample package requires the following:

The sample packages and data files that it uses must be installed on the local hard disk drive.
You must have installed and have administrative permissions on the AdventureWorksDW database.
If you intend only to run the sample package from the command line, you must install Integration Services.
If you intend to open the package in SSIS Designer and run the sample package, you must also install Business Intelligence Development Studio. For more information about how to install samples, see "Installing Sample Integration Services Packages" in SQL Server Books Online.

Location of the Sample Packages

If the samples were installed to the default installation location, the tutorial sample packages are located in the following folder:
C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\Tutorial\Creating a Simple ETL Package\Completed Packages

Running the Sample

The packages can be run from the command line by using the dtexec utility, or can be run in Business Intelligence Development Studio.
The Lesson 5 package uses the package configuration file that is created in lesson 3 and used by the Lesson 3 and Lesson 4 packages. Before you run the Lesson 5 package, make sure that the ConfiguredValue element in the SSISTutorial.dtsConfig file contains the location of the Sample Data folder. The default location of this folder is C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\Tutorial\Creating a Simple ETL Package\Sample Data.
Also, the Lesson 5 package uses the CurrencyBAD.txt file. If you want to use the file provided instead of creating the file, you must copy CurrencyBAD.txt from C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\Tutorial\Creating a Simple ETL Package\Completed Packages to C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\Tutorial\Creating a Simple ETL Package\Sample Data before you run the package.
If you are using a non-English version of Windows, you may have to update the ConnectionString property of any file connection managers used in the package to run the sample package successfully. You should verify that the path used in the connection manager is valid on your computer and, if necessary, modify the path so that it uses the localized name of the Program Files folder.
For this sample, you may have to update "Program Files" in the ConnectionString property for the Sample Data connection manager.

To run the package by using dtexec

Open a Command Prompt window.
Change the directory to C:\Program Files\Microsoft SQL Server\100\DTS\Binn, the location of dtexec.
Type the following command: * dtexec /f "C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\Tutorial\Creating a Simple ETL Package\Completed Packages\<package name>" *
Press Enter.For more information about how to run the package by using the dtexec utility, see the topic, "dtexec Utility", in SQL Server Books Online.

Running the Sample in Business Intelligence Development Studio

If you do not already have an Integration Services project to which you can add the tutorial packages, you must first create a project.

To create a new Integration Services project

Open Business Intelligence Development Studio.
On the File menu, point to New, and then click Project.
In the New Project dialog box, select the Integration Services Project template from the Templates pane.
Optionally, edit the project name and the location. The solution name is automatically updated to match the project name.
In the Solution drop-down list, select Create new Solution.
To create a separate folder for the solution file, select Create directory for solution. This is the default option.
Click OK to add the solution to Solution Explorer and add the project to the solution.Next, you must add the packages to the Integration Services project.

To run a package in Business Intelligence Development Studio

In Solution Explorer, right-click SSIS Packages and then click Add Existing Package.
In the Add Copy of Existing Package dialog box, set Package location to File system, click browse (…), and locate the folder that contains the completed packages from the tutorial. The default location is C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\Tutorial\Creating a Simple ETL Package\Completed Packages.
Click the package to add, and then click Open.
Click OK.
Repeat steps 1-4 to add tutorial packages to the project.
Right-click the package to run in the SSIS Packages folder, and then click Execute Package. © 2008 Microsoft Corporation. All rights reserved.

Updated Wiki: SS2005!Integration Services Tutorial Package Samples

bonniefe — Wed, 11 Jun 2008 01:21:25 GMT

Creating a Simple ETL Package Tutorial: Lesson Packages

6/10/2008 4:38:07 PM

New: 5 December 2005
The Lesson 1, Lesson2, Lesson 3, Lesson 4, and Lesson 5 packages are the completed packages from the Integration Services tutorial, Creating a Simple ETL Package Tutorial. The topics in Books Online provide detailed information about the functionality of each package. For more information, see the topics in "Creating a Simple ETL Package Tutorial" located in the Integration Services Tutorials section of Books Online. Each lesson's package builds on the version of the package created in the earlier lessons.
You can open and run these packages outside the tutorial. However, we do not recommend that you modify them. Modification of tutorial packages may give you results that differ from those defined in the tutorial lessons.
The following table provides a brief summary of each package.

Lessons	Description
Lesson 1	A simple ETL package that extracts data from a single flat file, transforms the data by using lookup transformations, and finally loads the result into a fact table.
Lesson 2	In this lesson, you will expand the package you created in Lesson 1 to take advantage of new looping features to extract multiple flat files into a single data flow process.
Lesson 3	In this lesson, you will expand the package you created in Lesson 2 to use a package configuration, variable, and property expression to dynamically update a property.
Lesson 4	In this lesson, you will expand the package you created in Lesson 3 to add logging.
Lesson 5	In this lesson, you will expand the package you created in lesson 4 to use error redirection to capture invalid data in a text file.

Important:
Samples are provided for educational purposes only. They are not intended to be used in a production environment and have not been tested in a production environment. Microsoft does not provide technical support for these samples.

Requirements

Running this sample package requires the following:

The sample packages and data files that it uses must be installed on the local hard disk drive.

*
You must have installed and have administrative permissions on the AdventureWorksDW database.

If you intend only to run the sample package from the command line, you must install SQL Server 2005 Integration Services (SSIS).
If you intend to open the package in SSIS Designer and run the sample package, you must also install Business Intelligence Development Studio. For more information about how to install samples, see "Installing Sample Integration Services Packages" in SQL Server 2005 Books Online. To obtain the latest version of the samples, including new samples released after the original release of SQL Server 2005, see SQL Server 2005 Samples and Sample Databases (April 2006).

Location of the Sample Packages

If the samples were installed to the default installation location, the tutorial sample packages are located in the following folder:
C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Tutorial\Creating a Simple ETL Package\Completed Packages

Running the Sample

The packages can be run from the command line by using the dtexec utility, or can be run in Business Intelligence Development Studio.
The Lesson 5 package uses the package configuration file that is created in lesson 3 and used by the Lesson 3 and Lesson 4 packages. Before you run the Lesson 5 package, make sure that the ConfiguredValue element in the SSISTutorial.dtsConfig file contains the location of the Sample Data folder. The default location of this folder is C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Tutorial\Creating a Simple ETL Package\Sample Data.
Also, the Lesson 5 package uses the CurrencyBAD.txt file. If you want to use the file provided instead of creating the file, you must copy CurrencyBAD.txt from C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Tutorial\Creating a Simple ETL Package\Completed Packages to C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Tutorial\Creating a Simple ETL Package\Sample Data before you run the package.
If you are using a non-English version of Windows, you may have to update the ConnectionString property of any file connection managers used in the package to run the sample package successfully. You should verify that the path used in the connection manager is valid on your computer and, if necessary, modify the path so that it uses the localized name of the Program Files folder.
For this sample, you may have to update "Program Files" in the ConnectionString property for the Sample Data connection manager.

To run the package by using dtexec

Open a Command Prompt window.
Change the directory to C:\Program Files\Microsoft SQL Server\90\DTS\Binn, the location of dtexec.
Type the following command: * dtexec /f "C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Tutorial\Creating a Simple ETL Package\Completed Packages\<package name>" *
Press Enter.For more information about how to run the package by using the dtexec utility, see the topic, "dtexec Utility", in SQL Server 2005 Books Online.

Running the Sample in Business Intelligence Development Studio

If you do not already have an Integration Services project to which you can add the tutorial packages, you must first create a project.

To create a new Integration Services project

Open Business Intelligence Development Studio.
On the File menu, point to New, and then click Project.
In the New Project dialog box, select the Integration Services Project template from the Templates pane.
Optionally, edit the project name and the location. The solution name is automatically updated to match the project name.
In the Solution drop-down list, select Create new Solution.
To create a separate folder for the solution file, select Create directory for solution. This is the default option.
Click OK to add the solution to Solution Explorer and add the project to the solution.Next, you must add the packages to the Integration Services project.

To run a package in Business Intelligence Development Studio

In Solution Explorer, right-click SSIS Packages and then click Add Existing Package.
In the Add Copy of Existing Package dialog box, set Package location to File system, click browse (…), and locate the folder that contains the completed packages from the tutorial. The default location is C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Tutorial\Creating a Simple ETL Package\Completed Packages.
Click the package to add, and then click Open.
Click OK.
Repeat steps 1-4 to add tutorial packages to the project.
Right-click the package to run in the SSIS Packages folder, and then click Execute Package. © 2008 Microsoft Corporation. All rights reserved.

Updated Wiki: SS2008!Deploying Packages Tutorial: Lesson Packages

bonniefe — Wed, 11 Jun 2008 01:20:56 GMT

Readme_Deploying Packages Tutorial: Lesson Packages

6/10/2008 1:32:27 AM

This sample works only with SQL Server 2005 and SQL Server 2008. It will not work with any version of SQL Server earlier than SQL Server 2005.
The LoadXMLData and DataTransfer packages are the packages that you enhance and deploy in the Integration Services tutorial, Deploying Packages. The topics in Books Online provide detailed information about the functionality of each package. For more information, see the topics in "Deploying Packages Tutorial" located in the Integration Services Tutorials section of Books Online.
You can open and run these packages outside the tutorial. However, we do not recommend that you modify them. If you use modified packages in the tutorial, the lesson results may be different from those described in the tutorial.
The following table provides a brief summary of each package.

Package	Description
DataTransfer	A simple package that extracts data from a single flat file, splits the extracted dataset based on column values, and loads the resultant data into a table in the AdventureWorks database. If the table does not already exist, the package creates it. The Execute SQL task in the package logs events to an XML log file.
LoadXMLData	A simple package that extracts data from an XML data file, splits the extracted dataset based on column values, aggregates values, and loads the resultant data into a table in the AdventureWorks database. If the table does not already exist, the package creates it.

Important:
Samples are provided for educational purposes only. They are not intended to be used in a production environment and have not been tested in a production environment. Microsoft does not provide technical support for these samples.

Requirements

Running this sample package requires the following:

The sample packages and data files that it uses must be installed on the local hard disk drive.
You must have installed and have administrative permissions on the AdventureWorks database.
If you intend only to run the sample package from the command line, you must install Integration Services.
If you intend to open the package in SSIS Designer and run the sample package, you must also install Business Intelligence Development Studio. For more information about how to install samples, see "Installing Sample Integration Services Packages" in SQL Server Books Online.

Location of the Sample Packages

Running the Sample

The packages can be run from the command line by using the dtexec utility, or can be run in Business Intelligence Development Studio.
If you are using a non-English version of Windows, you may have to update the ConnectionString property of any file connection managers used in the package to run the sample package successfully. You should verify that the path used in the connection manager is valid on your computer, and if you need to, modify the path so that it uses the localized name of the Program Files folder.
For this sample, you may have to update "Program Files" in the ConnectionString property for the NewCustomers connection manager.

To run the package by using dtexec

Open a Command Prompt window.
Change the directory to C:\Program Files\Microsoft SQL Server\100\DTS\Binn, the location of dtexec.
Type the following command: * dtexec /f "C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\Tutorial\Deploying Packages\Completed Packages\<package name>" *
Press Enter.For more information about how to run the package by using the dtexec utility, see the topic, "dtexec Utility", in SQL Server Books Online.

Running the Sample in Business Intelligence Development Studio

If you do not already have an Integration Services project to which you can add the tutorial packages, you must first create a project.

To create a new Integration Services project

Open Business Intelligence Development Studio.
On the File menu, point to New, and then click Project.
In the New Project dialog box, select the Integration Services Project template from the Templates pane.
Optionally, edit the project name and the location. The solution name is automatically updated to match the project name.
In the Solution drop-down list, select Create new Solution.
To create a separate folder for the solution file, select Create directory for solution. This is the default option.
Click OK to add the solution to Solution Explorer and add the project to the solution.Next, you add the packages to the Integration Services project.

To run a package in Business Intelligence Development Studio

In Solution Explorer, right-click SSIS Packages and click Add Existing Package.
In the Add Copy of Existing Package dialog box, set Package location to File system, click browse (…), and locate the folder that contains the completed packages from the tutorial. The default location is C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\Tutorial\Deploying Packages\Completed Packages.
Click the package to add, and then click Open.
Click OK.
Repeat steps 1-4 to add the second tutorial package to the project.
Right-click the package to run in the SSIS Packages folder, and then click Execute Package. © 2008 Microsoft Corporation. All rights reserved.

Updated Wiki: SS2005!Deploying Packages Tutorial: Lesson Packages

bonniefe — Wed, 11 Jun 2008 01:20:12 GMT

Deploying Packages Tutorial: Lesson Packages

6/10/2008 4:38:07 PM

New: 14 April 2006
The LoadXMLData and DataTransfer packages are the packages that you enhance and deploy in the Integration Services tutorial, Deploying Packages. The topics in Books Online provide detailed information about the functionality of each package. For more information, see the topics in "Deploying Packages Tutorial" located in the Integration Services Tutorials section of Books Online.
You can open and run these packages outside the tutorial. However, we do not recommend that you modify them. If you use modified packages in the tutorial, the lesson results may be different from those described in the tutorial.
The following table provides a brief summary of each package.

Package	Description
DataTransfer	A simple package that extracts data from a single flat file, splits the extracted dataset based on column values, and loads the resultant data into a table in the AdventureWorks database. If the table does not already exist, the package creates it. The Execute SQL task in the package logs events to an XML log file.
LoadXMLData	A simple package that extracts data from an XML data file, splits the extracted dataset based on column values, aggregates values, and loads the resultant data into a table in the AdventureWorks database. If the table does not already exist, the package creates it.

Important:
Samples are provided for educational purposes only. They are not intended to be used in a production environment and have not been tested in a production environment. Microsoft does not provide technical support for these samples.

Requirements

Running this sample package requires the following:

The sample packages and data files that it uses must be installed on the local hard disk drive.
You must have installed and have administrative permissions on the AdventureWorks database.
If you intend only to run the sample package from the command line, you must install SQL Server 2005 Integration Services (SSIS).
If you intend to open the package in SSIS Designer and run the sample package, you must also install Business Intelligence Development Studio. For more information about how to install samples, see "Installing Sample Integration Services Packages" in SQL Server 2005 Books Online. To obtain the latest version of the samples, including new samples released after the original release of SQL Server 2005, see SQL Server 2005 Samples and Sample Databases (April 2006).

Location of the Sample Packages

Running the Sample

To run the package by using dtexec

Open a Command Prompt window.
Change the directory to C:\Program Files\Microsoft SQL Server\90\DTS\Binn, the location of dtexec.
Type the following command: * dtexec /f "C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Tutorial\Deploying Packages\Completed Packages\<package name>" *
Press Enter.For more information about how to run the package by using the dtexec utility, see the topic, "dtexec Utility", in SQL Server 2005 Books Online.

Running the Sample in Business Intelligence Development Studio

If you do not already have an Integration Services project to which you can add the tutorial packages, you must first create a project.

To create a new Integration Services project

Open Business Intelligence Development Studio.
On the File menu, point to New, and then click Project.
In the New Project dialog box, select the Integration Services Project template from the Templates pane.
Optionally, edit the project name and the location. The solution name is automatically updated to match the project name.
In the Solution drop-down list, select Create new Solution.
To create a separate folder for the solution file, select Create directory for solution. This is the default option.
Click OK to add the solution to Solution Explorer and add the project to the solution.Next, you add the packages to the Integration Services project.

To run a package in Business Intelligence Development Studio

In Solution Explorer, right-click SSIS Packages and click Add Existing Package.
In the Add Copy of Existing Package dialog box, set Package location to File system, click browse (…), and locate the folder that contains the completed packages from the tutorial. The default location is C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Tutorial\Deploying Packages\Completed Packages.
Click the package to add, and then click Open.
Click OK.
Repeat steps 1-4 to add the second tutorial package to the project.
Right-click the package to run in the SSIS Packages folder, and then click Execute Package. © 2008 Microsoft Corporation. All rights reserved.

Updated Wiki: SS2008!Creating a Basic Package Tutorial: Lesson Packages

bonniefe — Wed, 11 Jun 2008 01:19:50 GMT

Readme_Creating a Basic Package Tutorial: Lesson Packages

6/10/2008 1:32:27 AM

This sample works only with SQL Server 2005 and SQL Server 2008. It will not work with any version of SQL Server earlier than SQL Server 2005.
The Basic Lesson 1 and Basic Lesson 2 packages are the completed packages from the Integration Services tutorial, Creating a Basic Package Using a Wizard. The topics in Books Online provide detailed information about the functionality of each package. For more information, see the topics in "Creating a Basic Package Using a Wizard" located in the Integration Services Tutorials section of Books Online.
You can open and run these packages outside the tutorial. However, we do not recommend that you modify them. Modification of tutorial packages may give you results that differ from the results of packages that were defined in the tutorial lessons.

Important:
The first time that you run the Basic Lesson 1 or Basic Lesson 2 package, the task named Drop table(s) SQL Task will fail. This behavior is expected. The reason the task fails is that the package tries to drop and re-create the ProspectiveCustomers table; however, the first time that the package runs, the table does not exist and therefore the DROP statement fails. This does not cause the package to fail because the precedence constraint between the Drop table(s) SQL Task and Preparation SQL tasks has been set to Completion instead of Success.

Important:

The first time that you run the Basic Lesson 1 or Basic Lesson 2 package, the task named Drop table(s) SQL Task will fail. This behavior is expected. The reason the task fails is that the package tries to drop and re-create the ProspectiveCustomers table; however, the first time that the package runs, the table does not exist and therefore the DROP statement fails. This does not cause the package to fail because the precedence constraint between the Drop table(s) SQL Task and Preparation SQL tasks has been set to Completion instead of Success.

The following table provides a brief summary of each package.

Package	Description
Basic Lesson 1	A simple data transfer package that uses a SELECT statement to extract data from an Excel workbook file and load the data into a table in the AdventureWorks database. If the table does not already exist, the package creates it. This package was built by using the SQL Server Import and Export Wizard.
Basic Lesson 2	The Basic Lesson 1 package enhanced with more complex data flow. The data flow now includes a Sort transformation to sort the data by state and city, and a Derived Column transformation to add a new column whose value is set by the evaluation result of an expression.

Important:
Samples are provided for educational purposes only. They are not intended to be used in a production environment and have not been tested in a production environment. Microsoft does not provide technical support for these samples.

Requirements

Running this sample package requires the following:

The sample packages and data files that it uses must be installed on the local hard disk drive.
You must have installed and have administrative permissions on the AdventureWorks database.
If you intend only to run the sample package from the command line, you must install Integration Services.
If you intend to open the package in SSIS Designer and run the sample package, you must also install Business Intelligence Development Studio. For more information about how to install samples, see "Installing Sample Integration Services Packages" in SQL Server Books Online.

Location of the Sample Packages

Running the Sample

The packages can be run from the command line by using the dtexec utility, or can be run in Business Intelligence Development Studio.

Important:
This sample uses the Microsoft Jet 4.0 OLE DB provider, for which there is no 64-bit version. The package fails on a 64-bit computer.

If you are using a non-English version of Windows, you may have to update the ConnectionString property of any file connection managers used in the package to run the sample package successfully. You should verify that the path used in the connection manager is valid on your computer, and if you need to, modify the path so that it uses the localized name of the Program Files folder.
For this sample, you may have to update "Program Files" in the ExcelFilePath and ServerName properties of the SourceConnectionExcel connection manager.

To run the package by using dtexec

Open a Command Prompt window.
Change the directory to C:\Program Files\Microsoft SQL Server\100\DTS\Binn, the location of dtexec.
Type the following command: * dtexec /f "C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\Tutorial\Creating a Basic Package\Completed Packages\<package name>" *
Press Enter.For more information about how to run the package by using the dtexec utility, see the topic, "dtexec Utility", in SQL Server Books Online.

Running the Sample in Business Intelligence Development Studio

If you do not already have an Integration Services project to which you can add the tutorial packages, you must first create a project.

To create a new Integration Services project

Open Business Intelligence Development Studio.
On the File menu, point to New, and then click Project.
In the New Project dialog box, select the Integration Services Project template from the Templates pane.
Optionally, edit the project name and the location. The solution name is automatically updated to match the project name.
In the Solution drop-down list, select Create new Solution.
To create a separate folder for the solution file, select Create directory for solution. This is the default option.
Click OK to add the solution to Solution Explorer and add the project to the solution.Next, you add the packages to the Integration Services project.

To run a package in Business Intelligence Development Studio

In Solution Explorer, right-click SSIS Packages and then click Add Existing Package.
In the Add Copy of Existing Package dialog box, set Package location to File system, click browse (…), and locate the folder that contains the completed packages from the tutorial. The default location is C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\Tutorial\Creating a Basic Package\Completed Packages.
Click the package to add, and then click Open.
Click OK.
Repeat steps 1-4 to add the second tutorial package to the project.
Right-click the package to run in the SSIS Packages folder, and then click Execute Package.

Updated Wiki: SS2005!Creating a Basic Package Tutorial: Lesson Packages

bonniefe — Wed, 11 Jun 2008 01:19:19 GMT

Creating a Basic Package Tutorial: Lesson Packages

6/10/2008 4:38:07 PM

New: 14 April 2006
The Basic Lesson 1 and Basic Lesson 2 packages are the completed packages from the Integration Services tutorial, Creating a Basic Package Using a Wizard. The topics in Books Online provide detailed information about the functionality of each package. For more information, see the topics in "Creating a Basic Package Using a Wizard" located in the Integration Services Tutorials section of Books Online.
You can open and run these packages outside the tutorial. However, we do not recommend that you modify them. Modification of tutorial packages may give you results that differ from the results of packages that were defined in the tutorial lessons.

Important:
The first time that you run the Basic Lesson 1 or Basic Lesson 2 package, the task named Drop table(s) SQL Task will fail. This behavior is expected. The reason the task fails is that the package tries to drop and re-create the ProspectiveCustomers table; however, the first time that the package runs, the table does not exist and therefore the DROP statement fails. This does not cause the package to fail because the precedence constraint between the Drop table(s) SQL Task and Preparation SQL tasks has been set to Completion instead of Success.

Important:

The following table provides a brief summary of each package.

Package	Description
Basic Lesson 1	A simple data transfer package that uses a SELECT statement to extract data from an Excel workbook file and load the data into a table in the AdventureWorks database. If the table does not already exist, the package creates it. This package was built by using the SQL Server Import and Export Wizard.
Basic Lesson 2	The Basic Lesson 1 package enhanced with more complex data flow. The data flow now includes a Sort transformation to sort the data by state and city, and a Derived Column transformation to add a new column whose value is set by the evaluation result of an expression.

Important:
Samples are provided for educational purposes only. They are not intended to be used in a production environment and have not been tested in a production environment. Microsoft does not provide technical support for these samples.

Requirements

Running this sample package requires the following:

*
The sample packages and data files that it uses must be installed on the local hard disk drive.

You must have installed and have administrative permissions on the AdventureWorks database.
If you intend only to run the sample package from the command line, you must install SQL Server 2005 Integration Services (SSIS).
If you intend to open the package in SSIS Designer and run the sample package, you must also install Business Intelligence Development Studio. For more information about how to install samples, see "Installing Sample Integration Services Packages" in SQL Server 2005 Books Online. To obtain the latest version of the samples, including new samples released after the original release of SQL Server 2005, see SQL Server 2005 Samples and Sample Databases (April 2006).

Location of the Sample Packages

Running the Sample

The packages can be run from the command line by using the dtexec utility, or can be run in Business Intelligence Development Studio.

Important:
This sample uses the Microsoft Jet 4.0 OLE DB provider, for which there is no 64-bit version. The package fails on a 64-bit computer.

To run the package by using dtexec

Open a Command Prompt window.
Change the directory to C:\Program Files\Microsoft SQL Server\90\DTS\Binn, the location of dtexec.
Type the following command: * dtexec /f "C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Tutorial\Creating a Basic Package\Completed Packages\<package name>" *
Press Enter.For more information about how to run the package by using the dtexec utility, see the topic, "dtexec Utility", in SQL Server 2005 Books Online.

Running the Sample in Business Intelligence Development Studio

If you do not already have an Integration Services project to which you can add the tutorial packages, you must first create a project.

To create a new Integration Services project

Open Business Intelligence Development Studio.
On the File menu, point to New, and then click Project.
In the New Project dialog box, select the Integration Services Project template from the Templates pane.
Optionally, edit the project name and the location. The solution name is automatically updated to match the project name.
In the Solution drop-down list, select Create new Solution.
To create a separate folder for the solution file, select Create directory for solution. This is the default option.
Click OK to add the solution to Solution Explorer and add the project to the solution.Next, you add the packages to the Integration Services project.

To run a package in Business Intelligence Development Studio

In Solution Explorer, right-click SSIS Packages and then click Add Existing Package.
In the Add Copy of Existing Package dialog box, set Package location to File system, click browse (…), and locate the folder that contains the completed packages from the tutorial. The default location is C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Tutorial\Creating a Basic Package\Completed Packages.
Click the package to add, and then click Open.
Click OK.
Repeat steps 1-4 to add the second tutorial package to the project.
Right-click the package to run in the SSIS Packages folder, and then click Execute Package.

Updated Wiki: SS2008!HtmlLogProvider Sample

bonniefe — Wed, 11 Jun 2008 01:18:34 GMT

Readme_HtmlLogProvider Sample

6/10/2008 1:32:27 AM

This sample works only with SQL Server 2005 and SQL Server 2008. It will not work with any version of SQL Server earlier than SQL Server 2005.
The HtmlLogProvider sample demonstrates how to create a custom log provider, the Html Log Provider, which writes logging output to an HTML document.
For more information about how to create a custom log provider, see Creating a Custom Log Provider. For general information about the use of log providers in Integration Services, see Integration Services Log Providers.
The custom log provider obtains the name of a destination file that has the extension .htm or .html from an existing File connection manager. It outputs logged events to an HTML table in the specified destination file, ignoring the final two standard columns (dataCode and dataBytes) which may contain binary data that cannot be displayed correctly in an HTML document. The sample encapsulates the HTML logging code in a separate HtmlLogWriter helper class for ease of reuse. This helper class has OpenLogHtml, LogHtml, and CloseLogHtml subroutines, which are called from OpenLog, Log, and CloseLog respectively.
This sample is not supported on Itanium-based operating systems.
This code sample implements the following functionality:

Overriding of the LogProviderBase class.
Default persistence support that does not require the developer to write custom persistence code.

Important:
Samples are provided for educational purposes only. They are not intended to be used in a production environment and have not been tested in a production environment. Microsoft does not provide technical support for these samples.

Running the Sample

If you already know how to locate, build, and install code samples, you can go directly to the section, testing , and read about how to configure and run the code sample.

Prerequisites

This sample requires that the following components are installed.

Microsoft Visual Studio
Microsoft SQL Server Integration Services

Location

Building the Sample

If you have not already generated a strong name key file in the Samples folder, use the following procedure to generate this key file. The sample projects are configured on the Signing tab of the Project Properties dialog box to sign assemblies at build time with this key file.

To generate a strong name key file

To open a Microsoft Visual Studio command prompt, click Start, point to All Programs, point to Microsoft Visual Studio 2008, point to Visual Studio Tools, and then click Visual Studio 2008 Command Prompt.- or -To open a Microsoft .NET Framework command prompt, click Start, point to All Programs, point to Microsoft .NET Framework SDK v2.0, and then click SDK Command Prompt.
At the command prompt, use the change directory (CD) command to change the current folder of the command prompt to the Samples folder. The key file that you create in this folder will be used by all SQL Server code samples. Note: To determine the folder where samples are located, click Start, point to All Programs, point to Microsoft SQL Server 2008, point to Documentation and Tutorials, and then click Samples Directory. If the default installation location was used, the samples are located in <drive>:\Program Files\Microsoft SQL Server\100\Samples.
At the command prompt, run the following command to generate the key file: * sn -k SampleKey.snk * Important: For more information about the strong-name key pair, see "Security Briefs: Strong Names and Security in the .NET Framework " in the .NET Development Center on MSDN.

To build the sample in Microsoft Visual Studio 2008

From the File | Open menu, click Project and open the HtmlLogProviderVB.sln or HtmlLogProviderCS.sln.
On the Build menu, click Build Solution to build the solution.

Installing the Sample

This sample is provided in both Visual Basic and C#. To distinguish the assemblies for each version of the sample, the name of the output assembly has CS or VB appended. After successfully building the component, follow these steps in order to add it as a connection manager in a package in Business Intelligence Development Studio.

To copy the component to the LogProviders folder

Open Windows Explorer or your preferred application for working in the file system.
Copy the assembly (HtmlLogProviderCS.dll or HtmlLogProviderVB.dll) to the LogProviders folder located at %system%\Program Files\Microsoft SQL Server\100\DTS.

To install the component into the global assembly cache (GAC) by dragging and dropping the assemblies

Open Windows Explorer or your preferred application for working in the file system.
Drag the assembly from the LogProviders folder to the folder where the global assembly cache (GAC) is located, at %system%\assembly.

To install the component into the global assembly cache (GAC) by using gacutil.exe

Open a Command Prompt window.
Type the following commands to install the C# version of the component into the GAC: * gacutil.exe -iF " C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\Programming Samples\Control Flow\HtmlLogProvider Sample\CS\HtmlLogProviderCS\bin\Debug\HtmlLogProviderCS.dll" - or -Type the following commands to install the Visual Basic version of the component into the GAC:* * gacutil.exe -iF " C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\Programming Samples\Control Flow\HtmlLogProvider Sample\VB\HtmlLogProviderVB\bin\Debug\HtmlLogProviderVB.dll" *

Testing the Sample

You can now test the custom log provider in a package.

To use the sample log provider in a package

In an Integration Services project, add a new File connection manager and configure it to point to a file that has the extension .htm or .html.
From the SSIS menu, select Logging, and add the Custom log provider for HTML files (CS) or Custom log provider for HTML files (VB).
In the Configuration column, enter the name of the File connection manager.
Configure logging as appropriate so that the package and its components log their events to the custom log provider.
Run the package, and then open the HTML file to view the logging output. © 2008 Microsoft Corporation. All rights reserved.

Updated Wiki: SS2005!HtmlLogProvider Sample

bonniefe — Wed, 11 Jun 2008 01:18:02 GMT

HtmlLogProvider Sample

6/10/2008 4:38:07 PM

New: 14 April 2006
The HtmlLogProvider sample demonstrates how to create a custom log provider, the Html Log Provider, which writes logging output to an HTML document.
For more information about how to create a custom log provider, see Creating a Custom Log Provider. For general information about the use of log providers in Integration Services, see Integration Services Log Providers.
The custom log provider obtains the name of a destination file that has the extension .htm or .html from an existing File connection manager. It outputs logged events to an HTML table in the specified destination file, ignoring the final two standard columns (dataCode and dataBytes) which may contain binary data that cannot be displayed correctly in an HTML document. The sample encapsulates the HTML logging code in a separate HtmlLogWriter helper class for ease of reuse. This helper class has OpenLogHtml, LogHtml, and CloseLogHtml subroutines, which are called from OpenLog, Log, and CloseLog respectively. This sample is not supported on Itanium-based operating systems.
This code sample implements the following functionality:

Overriding of the LogProviderBase class.
Default persistence support that does not require the developer to write custom persistence code.

Important:
Samples are provided for educational purposes only. They are not intended to be used in a production environment and have not been tested in a production environment. Microsoft does not provide technical support for these samples.

Running the Sample

If you already know how to locate, build, and install code samples, you can go directly to the section testing , and read about how to configure and run the code sample.

Prerequisites

This sample requires that the following components are installed.

Microsoft Visual Studio 2005
Microsoft SQL Server 2005 Integration Services

Location

If the code sample was installed to the default location, the sample is located in the following directory:
C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\\Programming Samples\Control Flow\HtmlLogProvider Sample
The C# solution for the code sample is located in the CS directory, and the Visual Basic solution is located in the VB directory.
For information about the two-step process required to install the samples, see Installing Samples. To obtain the latest version of the samples, including new samples released after the original release of SQL Server 2005, see SQL Server 2005 Samples and Sample Databases (April 2006).

Building the Sample

To generate a strong name key file

To open a Microsoft Visual Studio 2005 command prompt, click Start, point to All Programs, point to Microsoft Visual Studio 2005, point to Visual Studio Tools, and then click Visual Studio 2005 Command Prompt.- or -To open a Microsoft .NET Framework command prompt, click Start, point to All Programs, point to Microsoft .NET Framework SDK v2.0, and then click SDK Command Prompt.
At the command prompt, use the change directory (CD) command to change the current folder of the command prompt to the Samples folder. The key file that you create in this folder will be used by all SQL Server 2005 code samples. Note: To determine the folder where samples are located, click Start, point to All Programs, point to Microsoft SQL Server 2005, point to Documentation and Tutorials, and then click Samples Directory. If the default installation location was used, the samples are located in <system_drive>:\Program Files\Microsoft SQL Server\90\Samples.
At the command prompt, run the following command to generate the key file: * sn -k SampleKey.snk * Important: For more information about the strong-name key pair, see "Security Briefs: Strong Names and Security in the .NET Framework" in the .NET Development Center on MSDN.

To build the sample in Microsoft Visual Studio 2005

From the File | Open menu, click Project and open the HtmlLogProviderVB.sln or HtmlLogProviderCS.sln.
On the Build menu, click Build Solution to build the solution.

Installing the Sample

To copy the component to the Connections folder

Open Windows Explorer or your preferred application for working in the file system.
Copy the assembly (HtmlLogProviderCS.dll or HtmlLogProviderVB.dll) to the LogProviders folder located at %system%\Program Files\Microsoft SQL Server\90\DTS.

To install the component into the global assembly cache (GAC) by dragging and dropping the assemblies

Open Windows Explorer or your preferred application for working in the file system.
Drag the assembly from the LogProviders folder to the folder where the global assembly cache (GAC) is located, at %system%\assembly.

To install the component into the global assembly cache (GAC) by using gacutil.exe

Open a Command Prompt window.
Type the following commands to install the C# version of the component into the GAC: * gacutil.exe -iF " C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Programming Samples\Control Flow\HtmlLogProvider Sample\CS\HtmlLogProviderCS\bin\Debug\HtmlLogProviderCS.dll" - or -Type the following commands to install the Visual Basic version of the component into the GAC:* * gacutil.exe -iF " C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Programming Samples\Control Flow\HtmlLogProvider Sample\VB\HtmlLogProviderVB\bin\Debug\HtmlLogProviderVB.dll" *

Testing the Sample

You can now test the custom log provider in a package.

To use the sample log provider in a package

In an Integration Services project, add a new File connection manager and configure it to point to a file that has the extension .htm or .html.
From the SSIS menu, select Logging, and add the Custom log provider for HTML files (CS) or Custom log provider for HTML files (VB).
In the Configuration column, enter the name of the File connection manager.
Configure logging as appropriate so that the package and its components log their events to the custom log provider.
Run the package, and then open the HTML file to view the logging output. © 2008 Microsoft Corporation. All rights reserved.

Updated Wiki: SS2005!ForEachSmoEnumerator Sample

bonniefe — Wed, 11 Jun 2008 01:17:02 GMT

SMOTableList Sample

6/10/2008 4:38:07 PM

Updated: 17 July 2006
The SmoTableList sample illustrates how to enumerate the user tables in the AdventureWorks database. A Script task retrieves the schema and table names and puts them into two variables. Next, a Transact-SQL task executes a DBCC CHECKCONSTRAINTS command using the two variables. This sample is not supported on IA64. This sample requires SQL Server 2005 SP1.
For more information, see the topic, "Foreach Loop Container", in SQL Server 2005 Books Online.

Important:
Samples are provided for educational purposes only. They are not intended to be used in a production environment and have not been tested in a production environment. Microsoft does not provide technical support for these samples.

Running the Sample

If you already know how to locate, build, and install code samples, you can go directly to the section testing , and read about how to configure and run the code sample.

Prerequisites

Running this sample package requires the following:

.NET Framework SDK 2.0 or Microsoft Visual Studio 2005. You can obtain .NET Framework SDK free of charge. See the Books Online topic, Installing the .NET Framework SDK.
Microsoft SQL Server 2005 Integration Services
The sample package and data files that it uses must be installed on the local hard disk drive.
You must have attached and have administrative permissions on the AdventureWorks OLTP database. For more information about how to install samples, see the topic, "Installing Sample Integration Services Packages", in SQL Server 2005 Books Online.

Location

If the code sample was installed to the default location, the sample is located in the following directory:
C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\\Programming Samples\Control Flow\SmoTableList\
The C# solution for the code sample is located in the CS directory, and the Visual Basic solution is located in the VB directory.
For information about the two-step process required to install the samples, see Installing Samples. To obtain the latest version of the samples, including new samples released after the original release of SQL Server 2005, see SQL Server 2005 Samples and Sample Databases (April 2006).

Building the Sample

If you have not already generated a strong name key file, use the following procedure to generate this key file.

To generate a strong name key file

To open a Microsoft Visual Studio 2005 command prompt, click Start, point to All Programs, point to Microsoft Visual Studio 2005, point to Visual Studio Tools, and then click Visual Studio 2005 Command Prompt.- or -To open a Microsoft .NET Framework command prompt, click Start, point to All Programs, point to Microsoft .NET Framework SDK 2.0, and then click SDK Command Prompt.
At the command prompt, use the change directory (CD) command to change the current folder of the command prompt window to the Samples folder. The key file that you create in this folder will be used by all SQL Server 2005 code samples.

Note:
To determine the folder where samples are located, click Start, point to All Programs, point to Microsoft SQL Server 2005, point to Documentation and Tutorials, and then click Samples Directory. If the default installation location was used, the samples are located in <systemdrive>_:\Program Files\Microsoft SQL Server\90\Samples.

At the command prompt, run the following command to generate the key file: sn -k SampleKey.snk

Important:
For more information about the strong-name key pair, see "Security Briefs: Strong Names and Security in the .NET Framework" in the .NET Development Center on MSDN.

To build the sample

Open Microsoft Visual Studio 2005.
From the File | Open menu, click Project and open the solution (.sln) for the desired sample, which is SMOTableList.sln from the CS folder or from the VB folders, depending on the language you want.
Click Build / Build SmoTableList on the menu.

Testing the Sample

The package that the sample creates is saved to the following location:
C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\\Programming Samples\Control Flow\SmoTableList\<language>\SmoTablelist\bin\debug\ SmoTableSamplePackage.dtsx.
To view the package in the designer, create a new Integration Services project and add the existing .dtsx item to the project. For more information, see the topic, "How to: Add an Item to an Integration Services Project", in SQL Server 2005 Books Online.

Change History

Release	History
17 July 2006	Changed content: Added requirement for SP1.
14 April 2006	Changed content: Removed the instructions on copying the Smo.dll, SmoEnum.dll, and ConnectionInfo.dll to the %windir%\Microsoft.NET\Framework\v2.0.xxxxx as this step is no longer required.

Updated Wiki: SS2008!ForEachDirectory Sample

bonniefe — Wed, 11 Jun 2008 01:16:42 GMT

Readme_ForEachDirectory Sample

6/10/2008 1:32:27 AM

This sample works only with SQL Server 2005 and SQL Server 2008. It will not work with any version of SQL Server earlier than SQL Server 2005.
This code sample demonstrates how to create a custom enumerator and a custom user interface to set properties for the enumerator using the .NET Framework and managed code.
This sample is not supported on Itanium-based operating systems.

Important:
Samples are provided for educational purposes only. They are not intended to be used in a production environment and have not been tested in a production environment. Microsoft does not provide technical support for these samples.

Running the Sample

If you already know how to locate, build, and install code samples, you can go directly to the section, testing , and read about how to configure and run the code sample.

Prerequisites

The ForEachDirectory sample requires that the following components are installed.

.NET Framework SDK 2.0 or Microsoft Visual Studio. You can obtain .NET Framework SDK free of charge. See the Books Online topic, Installing the .NET Framework Documentation.
Microsoft SQL Server Integration Services

Location

Building the Sample

If you have not already generated a strong name key file, use the following procedure to generate this key file.

To generate a strong name key file

To open a Microsoft Visual Studio command prompt, click Start, point to All Programs, point to Microsoft Visual Studio 2008, point to Visual Studio Tools, and then click Visual Studio 2008 Command Prompt.- or -To open a Microsoft .NET Framework command prompt, click Start, point to All Programs, point to Microsoft .NET Framework SDK 2.0, and then click SDK Command Prompt.
At the command prompt, use the change directory (CD) command to change the current folder of the command prompt window to the Samples folder. The key file that you create in this folder will be used by all SQL Server code samples. Note: To determine the folder where samples are located, click Start, point to All Programs, point to Microsoft SQL Server 2008, point to Documentation and Tutorials, and then click Samples Directory. If the default installation location was used, the samples are located in <drive>:\Program Files\Microsoft SQL Server\100\Samples.
At the command prompt, run the following command to generate the key file:sn -k SampleKey.snk Important: For more information about the strong-name key pair, see "Security Briefs: Strong Names and Security in the .NET Framework" in the .NET Development Center on MSDN.
You will need the public key token from the key file in a subsequent step. To obtain the public key token, first extract the public key from the key file by running the following command at the command prompt:sn -p SampleKey.snk SampleKeyPublic.snk
Display the public key token by running the following command at the command prompt: sn -t SampleKeyPublic.snk
Copy the public key token to the clipboard or save for later use.

To build the sample

Open Microsoft Visual Studio 2008.
From the File | Open menu, click Project and open the ForEachDirectory.sln in the desired Samples directory, which is CS for the C# language, or VB for the Visual Basic language.
In the Solution Explorer, select the ForEachDirectory.cs or ForEachDirectory.vb code.
In the code, locate the DtsForEachEnumerator attribute. In that line of code, replace the existing value of the public key token in the* UITypeName* attribute property. The alphanumeric value after "PublicKeyToken =" should be replaced with the one displayed in the sn -t command from a previous step.
Click Build / Build ForEachDirectory on the menu to build the DLL for the project.

Installing the DLL into the ForEachEnumerator folder and the Global Assembly Cache

This sample is provided in both Visual Basic and C#. To distinguish between the assemblies for each version of the sample, the name of the output assembly has CS or VB appended. After successfully building the component, the component must be copied to the ForEachEnumerators folder, and then added to the Global Assembly Cache in order to use it in Business Intelligence Development Studio.

To install the component into the ForEachEnumerators folder

Open Windows Explorer or your preferred application for working in the file system.
Copy the assembly (ForeachDirectoryCS.dll or ForeachDirectoryVB.dll) to the folder located at %system%\Program Files\Microsoft SQL Server\100\DTS\ForEachEnumerators.

To install the component into the Global Assembly Cache by dragging and dropping the assembly

Open Windows Explorer or your preferred application for working in the file system.
Drag the assemby from the ForEachEnumerators folder to the folder where the global assembly cache (GAC) is located, at %system%\assembly.

To install the component into the Global Assembly Cache by using gacutil.exe

Open a Command Prompt window.
Type the following command to install the C# version of the component and the user interface into the global assembly cache:** gacutil.exe -iF "c:\Program Files\Microsoft Sql Server\100\DTS\ ForEachEnumerators\ForEachDirectoryCS.dll"
Type the following command to install the Visual Basic version of the component and the user interface into the global assembly cache:** gacutil.exe -iF "c:\Program Files\Microsoft Sql Server\100\DTS\ForeachEnumerators\ForeachDirectoryVB.dll"

Note:
For simplicity, the user interface for this sample has been put in the same project as the task code; therefore, copying the DLL into the assembly directory is all that is required. If you decide to code the user interface in a different project, then you must add the DLL that contains the user interface to the global assembly cache, in addition to the DLL that contains the code. Only the code must be compiled into a specific directory, however.

Note:

For simplicity, the user interface for this sample has been put in the same project as the task code; therefore, copying the DLL into the assembly directory is all that is required. If you decide to code the user interface in a different project, then you must add the DLL that contains the user interface to the global assembly cache, in addition to the DLL that contains the code. Only the code must be compiled into a specific directory, however.

After you complete these steps the enumerator is available in the For Each Loop Editor of the For Each Loop container.

To use the sample in Business Intelligence Development Studio

Open Business Intelligence Development Studio.
From the menu, click File | New | Project.
From the New Project dialog box, in the Project Types, click Business Intelligence Projects and in the right pane, click Integration Services Project.
In the Name box, type ForEachDirectory as the name for the new project.
In the Location box, select the location where you want to save the file. Click OK.
From the Solution Explorer dialog box, double-click Package.dtsx.
In the Location box, select the location you want to save the file to. Click OK.
From the Solution Explorer dialog box, double-click Package.dtsx.
Add the ForEachLoop container to the Control Flow surface of a package.
Open the Foreach Loop Editor by double-clicking the ForEachLoop container added in step one.
Click the Collection node, and select the ForEachDirectory enumerator from the Enumerator dropdown.
Specify the values of the properties of the ForEachDirectory enumerator, and then click OK.

Testing the Sample

After you have added the sample component to a Data Flow task in a package, you can configure it in SSIS Designer. The ForEachDirectory enumerator has five properties that control its execution behavior.

Property	Description
RootDirectorySource	Specifies the source for the RootDirectory property which can be set by a ConnectionManager, Variable, or DirectInput.
RootDirectory	Specifies the directory where the enumerator begins its enumerating.
IncludeRootDirectory	Specifies whether the root directory is included in the directories that are enumerated.
EnumerateSubFolders	Specifies whether the subfolders of the folders that is contained in the root directory are enumerated.
SiblingFoldersBeforeSubFolders	When EnumerateSubFolders is true, specifies whether the enumerator enumerates the sibling folders first, or whether the complete directory structure is enumerated first.

Updated Wiki: SS2005!ForEachDirectory Sample

bonniefe — Wed, 11 Jun 2008 01:16:08 GMT

ForEachDirectory Sample

6/10/2008 4:38:07 PM

Updated: 5 December 2005
This code sample demonstrates how to create a custom enumerator and a custom user interface to set properties for the enumerator using the .NET Framework and managed code. This sample is not supported on IA64.

Running the Sample

If you already know how to locate, build, and install code samples, you can go directly to the section testing , and read about how to configure and run the code sample.

Prerequisites

The ForEachDirectory sample requires that the following components are installed.

.NET Framework SDK 2.0 or Microsoft Visual Studio 2005. You can obtain .NET Framework SDK free of charge. See the Books Online topic, Installing the .NET Framework SDK.
Microsoft SQL Server 2005 Integration Services

Important:
Samples are provided for educational purposes only. They are not intended to be used in a production environment and have not been tested in a production environment. Microsoft does not provide technical support for these samples.

Location

If the code sample was installed to the default location, the sample is located in the following directory:
C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\\Programming Samples\Control Flow\ForEachDirectory Sample
The C# solution for the code sample is located in the CS directory, and the Visual Basic solution is located in the VB directory.
For information about the two-step process required to install the samples, see Installing Samples. To obtain the latest version of the samples, including new samples released after the original release of SQL Server 2005, see SQL Server 2005 Samples and Sample Databases (April 2006).

Building the Sample

If you have not already generated a strong name key file, use the following procedure to generate this key file.

To generate a strong name key file

To open a Microsoft Visual Studio 2005 command prompt, click Start, point to All Programs, point to Microsoft Visual Studio 2005, point to Visual Studio Tools, and then click Visual Studio 2005 Command Prompt.- or -To open a Microsoft .NET Framework command prompt, click Start, point to All Programs, point to Microsoft .NET Framework SDK 2.0, and then click SDK Command Prompt.
At the command prompt, use the change directory (CD) command to change the current folder of the command prompt window to the Samples folder. The key file that you create in this folder will be used by all Microsoft SQL Server 2005 code samples. Note: To determine the folder where samples are located, click Start, point to All Programs, point to Microsoft SQL Server 2005, point to Documentation and Tutorials, and then click Samples Directory. If the default installation location was used, the samples are located in <system_drive>:\Program Files\Microsoft SQL Server\90\Samples.
At the command prompt, run the following command to generate the key file:sn -k SampleKey.snk *Important:

For more information about the strong-name key pair, see "Security Briefs: Strong Names and Security in the .NET Framework" in the .NET Development Center on MSDN.*

You will need the public key token from the key file in a subsequent step. To obtain the public key token, first extract the public key from the key file by running the following command at the command prompt:sn -p SampleKey.snk SampleKeyPublic.snk
Display the public key token by running the following command at the command prompt: sn -t SampleKeyPublic.snk
Copy the public key token to the clipboard or save for later use.

To build the sample

Open Microsoft Visual Studio 2005.
From the File | Open menu, click Project and open the ForEachDirectory.sln in the desired Samples directory, which is CS for the C# language, or VB for the Visual Basic language.
In the Solution Explorer, select the ForEachDirectory.cs or ForEachDirectory.vb code.
In the code, locate the DtsTask attribute. In that line of code, replace the existing value of the public key token in the* UITypeName* attribute property in the IncrementTask.cs. The alphanumeric value after "PublicKeyToken =" should be replaced with the one displayed in the sn -t command from a previous step.
Click Build / Build ForEachDirectory on the menu to build the DLL for the project.

Installing the DLL into the ForEachEnumerator folder and the Global Assembly Cache

To install the component into the ForEachEnumerators folder

Copy the assembly (ForeachDirectoryCS.dll or ForeachDirectoryVB.dll) to the folder located at %system%\Program Files\Microsoft SQL Server\90\DTS\ForEachEnumerators.
Open the directory for the global assembly cache at %system%\assembly. For most computers, this will be C:\Windows\assembly.
Select the assembly in the first window, and then drag and drop it into the window that contains the assembly folder.

To install the component into the Global Assembly Cache by dragging and dropping the assembly

Copy the assembly (ForeachDirectoryCS.dll or ForeachDirectoryVB.dll) to the folder located at %system%\Program Files\Microsoft SQL Server\90\DTS\ForEachEnumerators.
Open the directory for the global assembly cache at %system%\assembly. For most computers, this will be C:\Windows\assembly.
Select the assembly in the first window, and then drag and drop it into the window that contains the assembly folder.

To install the component into the Global Assembly Cache by using gacutil.exe

Open a Command Prompt window.
Type the following command to install the C# version of the component and the user interface into the global assembly cache:** gacutil.exe -iF "c:\Program Files\Microsoft Sql Server\90\DTS\ ForEachEnumerators\ForEachDirectoryCS.dll"
Type the following command to install the Visual Basic version of the component and the user interface into the global assembly cache:** gacutil.exe -iF "c:\Program Files\Microsoft Sql Server\90\DTS\ForeachEnumerators\ForeachDirectoryVB.dll" Note For simplicity, the user interface for this sample has been put in the same project as the task code; therefore, copying the DLL into the assembly directory is all that is required. If you decide to code the user interface in a different project, then you must add the DLL that contains the user interface to the global assembly cache, in addition to the DLL that contains the code. Only the code must be compiled into a specific directory, however.

After you complete these steps the enumerator is available in the For Each Loop Editor of the For Each Loop container.

To use the sample in Business Intelligence Development Studio

Open Business Intelligence Development Studio.
From the menu, click File / New / Project.
From the New Project dialog box, in the Project Types, click Business Intelligence Projects and in the right pane, click Integration Services Project.
In the Name box, type ForEachDirectory as the name for the new project.
In the Location box, select the location where you want to save the file. Click OK.
From the Solution Explorer dialog box, double-click Package.dtsx.
In the Location box, select the location you want to save the file to. Click OK.
From the Solution Explorer dialog box, double-click Package.dtsx.
Add the ForEachLoop container to the Control Flow surface of a package.
Open the Foreach Loop Editor by double-clicking the ForEachLoop container added in step one.
Click the Collection node, and select the ForEachDirectory enumerator from the Enumerator dropdown.
Specify the values of the properties of the ForEachDirectory enumerator, and then click OK.

Testing the Sample

Property	Description
RootDirectorySource	Specifies the source for the RootDirectory property which can be set by a ConnectionManager, Variable, or DirectInput.
RootDirectory	Specifies the directory where the enumerator begins its enumerating.
IncludeRootDirectory	Specifies whether the root directory is included in the directories that are enumerated.
EnumerateSubFolders	Specifies whether the subfolders of the folders that is contained in the root directory are enumerated.
SiblingFoldersBeforeSubFolders	When EnumerateSubFolders is true, specifies whether the enumerator enumerates the sibling folders first, or whether the complete directory structure is enumerated first.

Change History

Release	History
17 July 2006
5 December 2005	New content: Incorporated instructions on how to use the sample in Business Intelligence Development Studio. Added table describing the properties found in the custom user interface.

Updated Wiki: SS2008!Excel2 Custom Connection Manager Sample

bonniefe — Wed, 11 Jun 2008 01:15:46 GMT

Readme_Excel2 Custom Connection Manager Sample

6/10/2008 1:32:27 AM

This sample works only with SQL Server 2005 and SQL Server 2008. It will not work with any version of SQL Server earlier than SQL Server 2005.
The Excel2 Custom Connection Manager sample demonstrates how to create a custom connection manager, the Excel2 Connection Manager, and its associated user interface.
This sample is not supported on Itanium-based operating systems.
For more information about how to create a custom connection manager, see Creating a Custom Connection Manager. For general information about the use of connection managers in Integration Services, see Integration Services Connections and Connection Managers.
The custom connection manager connects to an available Microsoft Excel workbook by using the OleDb .NET Framework Data Provider and the Microsoft OLE DB Provider for Jet, and returns an open OleDbConnection object from its AcquireConnection method. When you create a new instance of the Excel2 Connection Manager in a package, its custom user interface prompts you for the path of the Excel workbook. This connection manager also provides a check box to specify Import Mode. This resolves an issue that occurs frequently when an Excel column contains mixed text and numeric data.

Note:
The Excel2 Custom Connection Manager sample cannot be used with the Excel source and destination. Test it with the ADO NET source and destination instead.

This code sample implements the following functionality:

Overriding of the ConnectionManagerBase class.
Persistence support.

Important:
Samples are provided for educational purposes only. They are not intended to be used in a production environment and have not been tested in a production environment. Microsoft does not provide technical support for these samples.

Running the Sample

If you already know how to locate, build, and install code samples, you can go directly to the section, testing , and read about how to configure and run this sample.

Prerequisites

This sample requires that the following components are installed.

Microsoft Visual Studio
Microsoft SQL Server Integration Services

Location

If the code sample was installed to the default location, the sample is located in the following directory:
C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\Programming Samples\Control Flow\Excel2ConnectionManager Sample
The C# solution for the code sample is located in the Excel2ConnMgrCS directory, and the Visual Basic solution is located in the Excel2ConnMgrVB directory.
For information about the two-step process required to install the samples, see Considerations for Installing SQL Server Samples and Sample Databases. To obtain the latest version of the samples, including new samples released after the original release of SQL Server, see the Microsoft SQL Server Samples and Community Projects Web site at http://www.codepex.com/sqlserversamples.

Building the Sample

If you have not already generated a strong name key file in the Samples folder, use the following procedure to generate this key file. The sample projects are configured on the Signing tab of the Project Properties dialog box to sign assemblies at build time with this key file.

To generate a strong name key file

To open a Microsoft Visual Studio command prompt, click Start, point to All Programs, point to Microsoft Visual Studio 2008, point to Visual Studio Tools, and then click Visual Studio 2008 Command Prompt.- or -To open a Microsoft .NET Framework command prompt, click Start, point to All Programs, point to Microsoft .NET Framework SDK v2.0, and then click SDK Command Prompt.
At the command prompt, use the change directory (CD) command to change the current folder of the Command Prompt window to the Samples folder. The key file that you create in this folder will be used by all SQL Server code samples. Note: To determine the folder where samples are located, click Start, point to All Programs, point to Microsoft SQL Server 2008, point to Documentation and Tutorials, and then click Samples Directory. If the default installation location was used, the samples are located in <drive>:\Program Files\Microsoft SQL Server\100\Samples.
At the command prompt, run the following command to generate the key file: * sn -k SampleKey.snk * Important: For more information about the strong-name key pair, see "Security Briefs: Strong Names and Security in the .NET Framework" in the .NET Development Center on MSDN.
You will need the public key token from the key file in a subsequent step. To obtain the public key token, first extract the public key from the key file to a new file by running the following command at the command prompt: * sn -p SampleKey.snk SampleKeyPublic.snk *Now display the public key token from the new file by running the following command at the command prompt: * sn -t SampleKeyPublic.snk *Copy the public key token to the clipboard or save it for later use.

To build the sample in Microsoft Visual Studio 2008

From the File | Open menu, click Project and open the Excel2ConnMgrVB.sln or Excel2ConnMgrCS.sln.
In the Solution Explorer, select the Excel2ConnMgrVB or Excel2ConnMgrCS project.
Locate the DtsConnection attribute before the class declaration in the Excel2ConnMgrCS.cs file or the Excel2ConnMgrVB.vb file and replace the alphanumeric value of the Public Key Token in the UITypeName property of the attribute with the public key token obtained earlier from the key file.
On the Build menu, click Build Solution to build the solution.

Installing the Sample

To copy the component to the Connections folder

Open Windows Explorer or your preferred application for working in the file system.
Copy the assemblies (Excel2ConnMgrCS.dll and Excel2ConnMgrUICS.dll, or Excel2ConnMgrVB.dll and Excel2ConnMgrUIVB.dll) to the Connections folder located at %system%\Program Files\Microsoft SQL Server\100\DTS\Connections.

To install the component into the global assembly cache (GAC) by dragging and dropping the assemblies

Open Windows Explorer or your preferred application for working in the file system.
Drag the assemblies from the Connections folder to the folder where the global assembly cache (GAC) is located, at %system%\assembly.

To install the component into the global assembly cache (GAC) by using gacutil.exe

Open a Command Prompt window.
Type the following commands to install the C# version of the component and the user interface into the GAC: * {" gacutil.exe -iF "C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\Programming Samples\Control Flow\Excel2ConnectionManager Sample\CS\Excel2ConnMgrCS\bin\Debug\Excel2ConnMgrCS.dll"

gacutil.exe -iF "C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\Programming Samples\Control Flow\Excel2ConnectionManager Sample\CS\Excel2ConnMgrUICS\bin\Debug\Excel2ConnMgrUICS.dll" "}- or -Type the following commands to install the Visual Basic version of the component and the user interface into the GAC:* * {" gacutil.exe -iF " C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\Programming Samples\Control Flow\Excel2ConnectionManager Sample\VB\Excel2ConnMgrVB\bin\Debug\Excel2ConnMgrVB.dll"
gacutil.exe -iF " C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\Programming Samples\Control Flow\Excel2ConnectionManager Sample\VB\Excel2ConnMgrVB\bin\Debug\Excel2ConnMgrUIVB.dll" "}*

Testing the Sample

You can now test the custom connection manager in a package.

To use the sample connection manager in a package

In the connection managers area of SSIS Designer, right-click, select New Connection, and in the list of available connection types, select EXCEL2. Click OK.
Edit the new connection manager. In the Excel2 Connection Manager Editor, locate an available Excel workbook file. Use the check boxes to specify settings for the headers and Import Mode settings. Note: The Excel2 Custom Connection Manager sample cannot be used with the Excel source, which supports only the built-in Excel connection manager. Test it with the ADO NET source instead. © 2008 Microsoft Corporation. All rights reserved.

Microsoft SQL Server Product Samples: Integration Services

Source code checked in, #11622

Updated Wiki: SS2008!Integration Services Tutorial Package Samples

Readme_Creating a Simple ETL Package Tutorial: Lesson Packages

Requirements

Location of the Sample Packages

Running the Sample

To run the package by using dtexec

Running the Sample in Business Intelligence Development Studio

To create a new Integration Services project

To run a package in Business Intelligence Development Studio

Updated Wiki: SS2005!Integration Services Tutorial Package Samples

Creating a Simple ETL Package Tutorial: Lesson Packages

Requirements

Location of the Sample Packages

Running the Sample

To run the package by using dtexec

Running the Sample in Business Intelligence Development Studio

To create a new Integration Services project

To run a package in Business Intelligence Development Studio

Updated Wiki: SS2008!Deploying Packages Tutorial: Lesson Packages

Readme_Deploying Packages Tutorial: Lesson Packages

Requirements

Location of the Sample Packages

Running the Sample

To run the package by using dtexec

Running the Sample in Business Intelligence Development Studio

To create a new Integration Services project

To run a package in Business Intelligence Development Studio

Updated Wiki: SS2005!Deploying Packages Tutorial: Lesson Packages

Deploying Packages Tutorial: Lesson Packages

Requirements

Location of the Sample Packages

Running the Sample

To run the package by using dtexec

Running the Sample in Business Intelligence Development Studio

To create a new Integration Services project

To run a package in Business Intelligence Development Studio

Updated Wiki: SS2008!Creating a Basic Package Tutorial: Lesson Packages

Readme_Creating a Basic Package Tutorial: Lesson Packages

Requirements

Location of the Sample Packages

Running the Sample

To run the package by using dtexec

Running the Sample in Business Intelligence Development Studio

To create a new Integration Services project

To run a package in Business Intelligence Development Studio

See Also

Other Resources

Help and Information

Updated Wiki: SS2005!Creating a Basic Package Tutorial: Lesson Packages

Creating a Basic Package Tutorial: Lesson Packages

Requirements

Location of the Sample Packages

Running the Sample

To run the package by using dtexec

Running the Sample in Business Intelligence Development Studio

To create a new Integration Services project

To run a package in Business Intelligence Development Studio

See Also

Other Resources

Help and Information

Updated Wiki: SS2008!HtmlLogProvider Sample

Readme_HtmlLogProvider Sample

Running the Sample

Prerequisites

Location

Building the Sample

To generate a strong name key file

To build the sample in Microsoft Visual Studio 2008

Installing the Sample

To copy the component to the LogProviders folder

To install the component into the global assembly cache (GAC) by dragging and dropping the assemblies

To install the component into the global assembly cache (GAC) by using gacutil.exe

Testing the Sample

To use the sample log provider in a package

Updated Wiki: SS2005!HtmlLogProvider Sample

HtmlLogProvider Sample

Running the Sample

Prerequisites