HOW TO: Create a Package With Visual C++ (316717)

SUMMARY

This article contains an example of how to create a Data Transformation Services (DTS) package by using Microsoft Visual C++. This is useful when you need to create a DTS package programmatically.

back to the top

DTS Visual C++ Programming Example

The following DTS programming sample demonstrates how to create a package that will transfer one column au_fname in the pubs..authors table from SQL Server 2000 to a flat file.

NOTE: Please verify that the DTS package name and file name do not exist.

#include <iostream.h>
#include <ole2.h>
#include <conio.h>
#include <io.h>
#undef EOF
// Provide the correct path for Dtspkg.dll.
#import "C:\program files\Microsoft SQL Server\80\Tools\Binn\dtspkg.dll" no_namespace
int main(int argc, char* argv[])
{
	struct _finddata_t dir;
	long hFile;

	CoInitialize(NULL);
	try
	{

	// Create Package Object 
	_PackagePtr pkg(__uuidof(Package));

	// Create Connection Source Object
	ConnectionPtr connectionSource;

	connectionSource = pkg->Connections->New("SQLOLEDB");
	connectionSource->Name = "Pubs";
	connectionSource->ID = 1;
        // Replace the . with your server name.
	connectionSource->DataSource = ".";
        // Replace the pubs database name with yours.
	connectionSource->Catalog ="pubs";
	connectionSource->UseTrustedConnection = TRUE;

	// Add Connection to Package
	pkg->Connections->Add(connectionSource);

	// Create the Connection Target Object
	ConnectionPtr connectionTarget;
	connectionTarget = pkg->Connections->New("DTSFlatFile");

        // Add Properties 
connectionTarget->ConnectionProperties->Item("Mode")->PutValue(_variant_t((long)3)); 
        connectionTarget->ConnectionProperties->Item("Row Delimiter")->PutValue("\r\n");
        connectionTarget->ConnectionProperties->Item("File Format")->PutValue (_variant_t((long)1));
        connectionTarget->ConnectionProperties->Item("Column Lengths")->PutValue (_variant_t((long)20));
        connectionTarget->ConnectionProperties->Item("Column Delimiter")->PutValue(",");
        connectionTarget->ConnectionProperties->Item("File Type")->PutValue (_variant_t((long)1));
        connectionTarget->ConnectionProperties->Item("Skip Rows")->PutValue (_variant_t((long)0));
        connectionTarget->ConnectionProperties->Item("Text Qualifier")->PutValue ("\"");
        connectionTarget->ConnectionProperties->Item("First Row Column Name")->PutValue (VARIANT_TRUE);
	connectionTarget->ConnectionProperties->Item("Column Names")->PutValue ("au_fname");
        connectionTarget->ConnectionProperties->Item("Number of Column")->PutValue (_variant_t((long)1));
        connectionTarget->ConnectionProperties->Item("Text Qualifier Col Mask: 0=no, 1=yes, e.g. 0101")->PutValue("1");
        connectionTarget->ConnectionProperties->Item("Max characters per delimited column")->PutValue(_variant_t((long)255));
        connectionTarget->ConnectionProperties->Item("Blob Col Mask: 0=no, 1=yes, e.g. 0101")->PutValue ("0");

	connectionTarget->Name = "myTest";
	connectionTarget->ID = 2;

            // Replace a directory and file name in the following statements.
   	connectionTarget->DataSource = "C:\\temp\\createDTS.txt";
   	
	if ((hFile = _findfirst("C:\\temp\\*", &dir )) == -1L)    {
		cout << "The directory is invalid!!!\n" << "Please verify the path.\n\n";
		return 0;
        }
	
	connectionTarget->UseTrustedConnection = FALSE;

	// Add the Connection Target to the Connection Source
	pkg->Connections->Add(connectionTarget);
	pkg->Name = "myDTSTest";

	// Create the Step Object
	StepPtr myStep;
	myStep = pkg->Steps->New();
	myStep->Name = "DTSStep_DTSDataPumpTask_1";
	myStep->Description = "Copy Data to File";
	myStep->TaskName = "DTSTask_DTSDataPumpTask_1";
       
        // Add the Step Object to a package
	pkg->Steps->Add(myStep);

	// Create Task Object
	TaskPtr myTask;
	myTask = pkg->Tasks->New("DTSDataPumpTask");
	myTask->Name = "DTSDataPumpTask";
	myTask->Description = "Transform Data Task";
	
	// Add Database query to the task
	DataPumpTaskPtr myDataPumpTask;
	myDataPumpTask = myTask->CustomTask;
	myDataPumpTask->Name = "DTSTask_DTSDataPumpTask_1";
	myDataPumpTask->Description = "Copy Data Pump Task";
	myDataPumpTask->SourceConnectionID = 1;
	myDataPumpTask->SourceSQLStatement = "select au_fname from pubs.dbo.authors";
	myDataPumpTask->DestinationConnectionID = 2;

	// Transformations - All the fields must be defined.
	PropertiesPtr myProperty;
	TransformationPtr myTrans;
	myTrans = myDataPumpTask->Transformations->New("DTS.DataPumpTransformCopy");
	myTrans->Name = "DTSTransformation__1";
	ColumnPtr sourceColumn1, destinationColumn1;
	sourceColumn1 = myTrans->SourceColumns->New("au_fname", 1);
	destinationColumn1 = myTrans->DestinationColumns->New("au_fname",1);
	myTrans->SourceColumns->Add(sourceColumn1);
	myTrans->DestinationColumns->Add(destinationColumn1);
	myProperty = myTrans->Properties;
	myDataPumpTask->Transformations->Add(myTrans);
	
	// Add the Task to the Package
	pkg->Tasks->Add(myTask);
	
        // Execute the Package
	pkg->Execute();

	// Save the Package to SQL Server<BR/>
        // Replace the . with your server name.
	pkg->SaveToSQLServer(connectionSource->DataSource, "", "", DTSSQLStgFlag_UseTrustedConnection, "", "", "", NULL, TRUE);
	}
	catch (_com_error& e)

      {
            cout << "Source     : " << e.Source() << endl;
            cout << "Error      : [" << e.Error() << "] " << endl;
            cout << "Description:  " << e.Description() << endl ;
      }

	return 0;
} 
				

back to the top