Hello:

Does anyone know of an example showing how to import a delimited text
file into SQL via the DTS api in C++??? I can get this to work from VB
but when I translate it to C++ it doesn't work, even though I get a
HRESULT that succeeded. It is so frustrating!! Short of posting the
code here, an C++ example (or link)would be helpful.

BTW - I can get the reverse to work, exporting from SQL to a delimited
file in C++.

Thanks!

Here is an example of executing a package in C++ if that helps any?

http://www.sqldev.net/dts/ExecutePackage.htm

--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


<ezelasky (AT) hotmail (DOT) com> wrote

I found my problem. I'm going to post my code since there seems to be
a lack of C++ examples illustrating the various DTS APIs. It is in
rough form but it works.

CoInitialize(NULL);
try
{
LPTSTR ptrName = "Transfer";

//create smart pointers to package
_Package2Ptr pkg(__uuidof(Package2));
pkg->Name = "DTS_Import_ex";
pkg->Description = "DTS package description";

//*** connection object -- this is for the source
ConnectionPtr connSource;
connSource = pkg->Connections->New("DTSFlatFile");
connSource->ConnectionProperties->Item("Data
Source")->PutValue("C:\\DataLoad\\DTS\\delimited\\test2.txt ");
connSource->ConnectionProperties->Item("Mode")->PutValue(_variant_t((long)1)); connSource->ConnectionProperties->Item("Row
Delimiter")->PutValue("\r\n");
connSource->ConnectionProperties->Item("File
Format")->PutValue (_variant_t((long)1));
connSource->ConnectionProperties->Item("Column
Delimiter")->PutValue(",");
//connSource->ConnectionProperties->Item("File
Type")->PutValue (_variant_t((long)1));
//connSource->ConnectionProperties->Item("Skip
Rows")->PutValue (_variant_t((long)0));
connSource->Name = "Connection 1";
connSource->ID = 1;
connSource->DataSource = "C:\\DataLoad\\DTS\\delimited\\test2.txt";
connSource->ConnectionTimeout = 60;

pkg->Connections->Add(connSource);

//*** connection object -- this is for the target
ConnectionPtr connTarget;
connTarget = pkg->Connections->New("SQLOLEDB");
connTarget->ConnectionProperties->Item("Integrated
Security")->PutValue("SSPI");
_variant_t vTemp; vTemp.vt = VT_BOOL; vTemp.boolVal = true;
connTarget->ConnectionProperties->Item("Persist Security
Info")->PutValue(vTemp.boolVal);
connTarget->ConnectionProperties->Item("Initial
Catalog")->PutValue("DataTransTest ");
connTarget->ConnectionProperties->Item("Data
Source")->PutValue("Socrates");
connTarget->ConnectionProperties->Item("Application
Name")->PutValue("DTS Import/Export Wizard");
connTarget->Name = "Connection 2";
connTarget->ID = 2;
connTarget->DataSource = "Socrates";
connTarget->ConnectionTimeout = 60;
connTarget->Catalog = "DataTransTest ";
connTarget->UseTrustedConnection = true;
pkg->Connections->Add(connTarget);

// Create the Step Object
StepPtr ptrStep;
ptrStep = pkg->Steps->New();
ptrStep->Name = ptrName;
ptrStep->Description = ptrName;
ptrStep->TaskName = ptrName;

// Add the Step Object to a package
pkg->Steps->Add(ptrStep);

// Create Task Object
TaskPtr ptrTask1;
ptrTask1 = pkg->Tasks->New("DTSDataPumpTask");

//Create custom task object from task object
DataPumpTaskPtr ptrCustomTask;
ptrCustomTask = ptrTask1->CustomTask;
ptrCustomTask->Name = ptrName;
ptrCustomTask->Description = ptrName;
ptrCustomTask->SourceConnectionID = 1;
ptrCustomTask->SourceObjectName =
"C:\\DataLoad\\DTS\\delimited\\test2.txt";
ptrCustomTask->DestinationConnectionID = 2;
ptrCustomTask->DestinationObjectName = "[DataTransTest
].[dbo].[test2]";;

//Create transformation pointer from custom task pointer
Transformation2Ptr ptrTrans;
ptrTrans =
ptrCustomTask->Transformations->New("DTS.DataPumpTransformCopy");
ptrTrans->Name = "DirectCopyXform";
/*
//not needed if map is one-to-one!!!!!!!
ColumnPtr srcCol, destCol;
srcCol = ptrTrans->SourceColumns->New("au_id", 1);
destCol = ptrTrans->DestinationColumns->New("au_id",1);
ptrTrans->SourceColumns->Add(srcCol);
ptrTrans->DestinationColumns->Add(destCol);
*/
PropertiesPtr ptrProp;
ptrProp = ptrTrans->Properties;

ptrCustomTask->Transformations->Add(ptrTrans);

// Add the Task to the Package
pkg->Tasks->Add(ptrTask1);

// Execute the Package
hr = pkg->Execute();
}
catch(_com_error &e)
{

bstrErr = e.Description();
bErr = true;
}