Create DataFlow Package Sample

The Create DataFlow Package sample demonstrates how to programmatically create a package and add a Data Flow. The package that is created in this sample performs the following actions:

• Extracts data from the Products table of the AdventureWorks database.
• Creates a variable to identify the column used to sort the output.
• Saves the results to a flat file destination.
• Executes the package in memory.
• Saves the package for later review.

Running the Sample

If you already know how to locate, build, and install code samples, you can go directly to the section Testing the Sample, and read about how to configure and run this code sample. If you have not used code samples before, you should review the prerequisites first.

Prerequisites

Before you run this code sample, ensure that the following conditions are met:

• Open SQL Server Configuration Manager; Click Start, point to All Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Configuration Manager.
• . Expand the SQL Server 2005 Network Configuration. Select the Protocols for MSSQLServer. Right-click TCP/IP and then click Enable. Stop and restart the service. For more information, see "How to: Set Properties of Integration Services Service Using the Services Snap-in" in SQL Server Books Online.
  or
  Open the main.cs file, and replace the localhost value given to the Source parameter in the adventureWorks.ConnectionString with the name of your server.
• The code sample and its associated sample data must first be installed to the local hard disk. For more information, refer to "Installing and Compiling Integration Services Programming Samples" in SQL Server Books Online.
• You must have attached and have administrative permissions to the AdventureWorks database. For more information, see "Installing AdventureWorks Sample Databases and Samples" in SQL Server Books Online."

To compile the coding samples, you will need .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.

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\CreatePackage 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

1. 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.

2. 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.

3. At the command prompt, run the following command to generate the key file:

   sn -k SampleKey.snk

To build the sample

1. Open Microsoft Visual Studio 2005.

2. From the File | Open menu, click Project and open the solution (.sln) for the desired sample, which is CreatePackageCS.sln from the CS folder or CreatePackageVB.sln from the VB folders, depending on the language you want.

3. Click Build / Build CreateTask*<lang>* on the menu.

Testing the Sample

When the code sample is run, the package that is programmatically created is executed in memory and is saved to the local computer.

You can review the saved version of the package created by this code sample by opening the package in SSIS Designer. For more information about how to open a package in SSIS Designer, refer to the topic, "How to: Add an Existing Integration Services Project to a Solution", in SQL Server 2005 Books Online.

The package is saved to the following location:

C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\\Programming Samples\Control Flow\CreatePackage Sample\<language>\CreatePackage\bin\Debug\SampleRuntimePackage.dtsx

The results created by the package are saved to the following location:

C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\ \Programming Samples\Control Flow\CreatePackage Sample\<language>\CreatePackage\bin\Debug\SampleRuntimeData.txt

You can verify that the package has executed successfully by examining the results in the destination flat file. The column specified by the SortColumn variable determines how the results are sorted. Subsequent running of the sample will overwrite both the saved package and the previous results in the flat file.

During execution, information about package status, including the number of rows written, is sent to the console. The following is an example of the information that is output to the console.

OnInformation

SubComponent: DTS.Pipeline

Description: "component "FlatFileDestination" (221)" wrote 504 rows.

Change History