Microsoft KB Archive/316717

= HOW TO: Create a Package With Visual C++ =

Article ID: 316717

Article Last Modified on 8/15/2005

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 2000 64-bit Edition
 * Microsoft Visual Studio 6.0 Enterprise Edition

-



This article was previously published under Q316717



IN THIS TASK
SUMMARY
 * DTS Visual C++ Programming Example

REFERENCES



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.

// Provide the correct path for Dtspkg.dll. int main(int argc, char* argv[]) {   struct _finddata_t dir; long hFile;
 * 1) include 
 * 2) include 
 * 3) include 
 * 4) include 
 * 5) undef EOF
 * 1) import &quot;C:\program files\Microsoft SQL Server\80\Tools\Binn\dtspkg.dll&quot; no_namespace

CoInitialize(NULL); try {

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

// Create Connection Source Object ConnectionPtr connectionSource;

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

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

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

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

connectionTarget->Name = &quot;myTest&quot;; connectionTarget->ID = 2;

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

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

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

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

// Transformations - All the fields must be defined. PropertiesPtr myProperty; TransformationPtr myTrans; myTrans = myDataPumpTask->Transformations->New(&quot;DTS.DataPumpTransformCopy&quot;); myTrans->Name = &quot;DTSTransformation__1&quot;; ColumnPtr sourceColumn1, destinationColumn1; sourceColumn1 = myTrans->SourceColumns->New(&quot;au_fname&quot;, 1); destinationColumn1 = myTrans->DestinationColumns->New(&quot;au_fname&quot;,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 // Replace the. with your server name. pkg->SaveToSQLServer(connectionSource->DataSource, &quot;&quot;, &quot;&quot;, DTSSQLStgFlag_UseTrustedConnection, &quot;&quot;, &quot;&quot;, &quot;&quot;, NULL, TRUE); }   catch (_com_error& e)

{           cout << &quot;Source     : &quot; << e.Source << endl; cout << &quot;Error     : [&quot; << e.Error << &quot;] &quot; << endl; cout << &quot;Description: &quot; << e.Description << endl ; }

return 0; } back to the top

