i want to dynamic dts package on dotnet
i can working on data from dynamic txt file to table in sql server .but
txt and table name is changeable.

can i do in vs.net ??(C#.Net)

Hello hedecan,

You most certainly can but I am not sure you need to.

From your post I get that you want to move data from a Flat File to a Table
in SQL Server and the names of the file and or table name is changeable.

If the structure remains the same in the file and the table then this is
relatively easy and you should look at the Dynamic Properties Task to sort
this out. If you insist on doing this through C# (Nothing wrong with it)
then you can use the DTS object model and hook into the properties you need
to change and do it that way.


If the structure changes on both or either side of the datapump then you
can still build a shell of a DT Package using EM and then change the properties
through code.

I have an example of doing this in VB Script. I read a SELECT statement
from a variable and I construct a Datapump (Both Sides in Code)

let me know if you think it will help.



Regards

Allan Mitchell
Konesans Ltd
T +44 7966 476 572
F +44 2071 008 479
http://www.konesans.com

thx for your reply.
i can sending my code in vb.net
code is running but not correct.
i can writing dynamic dts package . but not working.

code is here :

Public goPackageOld As New Package
Public goPackage As Package2


Public Sub RunDTS()
Dim goPackage As Package2
goPackage = CType(goPackageOld, Package2)
goPackage.Name = "DTS3"
goPackage.Description = "DTS package description"
goPackage.WriteCompletionStatusToNTEventLog = False
goPackage.FailOnError = False
goPackage.PackagePriorityClass = CType(2, DTSPackagePriorityClass)
goPackage.MaxConcurrentSteps = 4
goPackage.LineageOptions = 0
goPackage.UseTransaction = True
goPackage.TransactionIsolationLevel = CType(4096, DTSIsolationLevel)
goPackage.AutoCommitTransaction = True
goPackage.RepositoryMetadataOptions = 0
goPackage.UseOLEDBServiceComponents = True
goPackage.LogToSQLServer = False
goPackage.LogServerFlags = 0
goPackage.FailPackageOnLogFailure = False
goPackage.ExplicitGlobalVariables = False
goPackage.PackageType = 0


Dim oConnProperty As OleDBProperty
'---------------------------------------------------------------------------

' create package connection information

'---------------------------------------------------------------------------

Dim oConnection As Connection2
'------------- a new connection defined below.


oConnection = CType(goPackage.Connections.New("DTSFlatFile"),
Connection2)

oConnection.ConnectionProperties.Item("Data Source").Value =
"C:\hede\50.txt"
oConnection.ConnectionProperties.Item("Mode").Valu e = 1
oConnection.ConnectionProperties.Item("Row Delimiter").Value = "||##"
oConnection.ConnectionProperties.Item("File Format").Value = 1
oConnection.ConnectionProperties.Item("Column Delimiter").Value =
"|#$,"
oConnection.ConnectionProperties.Item("File Type").Value = 1
oConnection.ConnectionProperties.Item("Skip Rows").Value = 0
oConnection.ConnectionProperties.Item("First Row Column Name").Value()
= True
oConnection.ConnectionProperties.Item("Max characters per delimited
column").Value = 8000
oConnection.Name = "Connection 1"
oConnection.ID = 1
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = "C:\hede\50.txt"
oConnection.ConnectionTimeout = 60
oConnection.UseTrustedConnection = False
oConnection.UseDSL = False
goPackage.Connections.Add(CType(oConnection, Connection))


oConnection = CType(goPackage.Connections.New("SQLOLEDB"), Connection2)
oConnection.ConnectionProperties.Item("Integrated Security").Value =
"SSPI"
oConnection.ConnectionProperties.Item("Persist Security Info").Value()
= True
oConnection.ConnectionProperties.Item("Initial Catalog").Value = "**"
oConnection.ConnectionProperties.Item("Data Source").Value = "(local)"
oConnection.ConnectionProperties.Item("Application Name").Value = "DTS
Import/Export Wizard"
oConnection.Name = "Connection 2"
oConnection.ID = 2
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = "(local)"
oConnection.UserID = "**"
oConnection.Password = "**"
oConnection.ConnectionTimeout = 60
oConnection.Catalog = "**"
oConnection.UseTrustedConnection = True
oConnection.UseDSL = False
goPackage.Connections.Add(CType(oConnection, Connection))
oConnection = Nothing

'---------------------------------------------------------------------------

' create package steps information

'---------------------------------------------------------------------------

Dim oStep As Step2
Dim oPrecConstraint As PrecedenceConstraint


oStep = CType(goPackage.Steps.New, Step2)
oStep.Name = "Copy Data from myTextFile to [(local)].[dbo].[111] Step"
oStep.Description = "Copy Data from myTextFile to [(local)].[dbo].[111]
Step"
oStep.ExecutionStatus = CType(1, DTSStepExecStatus)
oStep.TaskName = "Copy Data from myTextFile to [(local)].[dbo].[111]
Task"
oStep.CommitSuccess = False
oStep.RollbackFailure = False
oStep.ScriptLanguage = "VBScript"
oStep.AddGlobalVariables = True
oStep.RelativePriority = CType(3, DTSStepRelativePriority)
oStep.CloseConnection = False
oStep.ExecuteInMainThread = False
oStep.IsPackageDSORowset = False
oStep.JoinTransactionIfPresent = False
oStep.DisableStep = False
oStep.FailPackageOnError = False
goPackage.Steps.Add(oStep)
oStep = Nothing
goPackage.SaveToSQLServer("(local)", "**", "**",
DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, "", "", "")
Try
goPackage.Execute()
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub


not have error. try clause is running.but not correct result.

thx..





Allan Mitchell yazdi:

thx for your reply.
i can sending my code in vb.net
code is running but not correct.
i can writing dynamic dts package . but not working.

code is here :

Public goPackageOld As New Package
Public goPackage As Package2


Public Sub RunDTS()
Dim goPackage As Package2
goPackage = CType(goPackageOld, Package2)
goPackage.Name = "DTS3"
goPackage.Description = "DTS package description"
goPackage.WriteCompletionStatusToNTEventLog = False
goPackage.FailOnError = False
goPackage.PackagePriorityClass = CType(2, DTSPackagePriorityClass)
goPackage.MaxConcurrentSteps = 4
goPackage.LineageOptions = 0
goPackage.UseTransaction = True
goPackage.TransactionIsolationLevel = CType(4096, DTSIsolationLevel)
goPackage.AutoCommitTransaction = True
goPackage.RepositoryMetadataOptions = 0
goPackage.UseOLEDBServiceComponents = True
goPackage.LogToSQLServer = False
goPackage.LogServerFlags = 0
goPackage.FailPackageOnLogFailure = False
goPackage.ExplicitGlobalVariables = False
goPackage.PackageType = 0


Dim oConnProperty As OleDBProperty
'---------------------------------------------------------------------------

' create package connection information

'---------------------------------------------------------------------------

Dim oConnection As Connection2
'------------- a new connection defined below.


oConnection = CType(goPackage.Connections.New("DTSFlatFile"),
Connection2)

oConnection.ConnectionProperties.Item("Data Source").Value =
"C:\hede\50.txt"
oConnection.ConnectionProperties.Item("Mode").Valu e = 1
oConnection.ConnectionProperties.Item("Row Delimiter").Value = "||##"
oConnection.ConnectionProperties.Item("File Format").Value = 1
oConnection.ConnectionProperties.Item("Column Delimiter").Value =
"|#$,"
oConnection.ConnectionProperties.Item("File Type").Value = 1
oConnection.ConnectionProperties.Item("Skip Rows").Value = 0
oConnection.ConnectionProperties.Item("First Row Column Name").Value()
= True
oConnection.ConnectionProperties.Item("Max characters per delimited
column").Value = 8000
oConnection.Name = "Connection 1"
oConnection.ID = 1
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = "C:\hede\50.txt"
oConnection.ConnectionTimeout = 60
oConnection.UseTrustedConnection = False
oConnection.UseDSL = False
goPackage.Connections.Add(CType(oConnection, Connection))


oConnection = CType(goPackage.Connections.New("SQLOLEDB"), Connection2)
oConnection.ConnectionProperties.Item("Integrated Security").Value =
"SSPI"
oConnection.ConnectionProperties.Item("Persist Security Info").Value()
= True
oConnection.ConnectionProperties.Item("Initial Catalog").Value = "**"
oConnection.ConnectionProperties.Item("Data Source").Value = "(local)"
oConnection.ConnectionProperties.Item("Application Name").Value = "DTS
Import/Export Wizard"
oConnection.Name = "Connection 2"
oConnection.ID = 2
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = "(local)"
oConnection.UserID = "**"
oConnection.Password = "**"
oConnection.ConnectionTimeout = 60
oConnection.Catalog = "**"
oConnection.UseTrustedConnection = True
oConnection.UseDSL = False
goPackage.Connections.Add(CType(oConnection, Connection))
oConnection = Nothing

'---------------------------------------------------------------------------

' create package steps information

'---------------------------------------------------------------------------

Dim oStep As Step2
Dim oPrecConstraint As PrecedenceConstraint


oStep = CType(goPackage.Steps.New, Step2)
oStep.Name = "Copy Data from myTextFile to [(local)].[dbo].[111] Step"
oStep.Description = "Copy Data from myTextFile to [(local)].[dbo].[111]
Step"
oStep.ExecutionStatus = CType(1, DTSStepExecStatus)
oStep.TaskName = "Copy Data from myTextFile to [(local)].[dbo].[111]
Task"
oStep.CommitSuccess = False
oStep.RollbackFailure = False
oStep.ScriptLanguage = "VBScript"
oStep.AddGlobalVariables = True
oStep.RelativePriority = CType(3, DTSStepRelativePriority)
oStep.CloseConnection = False
oStep.ExecuteInMainThread = False
oStep.IsPackageDSORowset = False
oStep.JoinTransactionIfPresent = False
oStep.DisableStep = False
oStep.FailPackageOnError = False
goPackage.Steps.Add(oStep)
oStep = Nothing
goPackage.SaveToSQLServer("(local)", "**", "**",
DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, "", "", "")
Try
goPackage.Execute()
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub


not have error. try clause is running.but not correct result.

thx..





Allan Mitchell yazdi:

Hello hedecan,

In my reply I said that all this may not be necessary and you may be able
to simply rebuild the Transform data Task in an existing package. Is that
the case?

You say it is not correct. What is not correct?

All is see is you setting up the connections. Where do you do the Transforms?


Regards

Allan Mitchell
Konesans Ltd
T +44 7966 476 572
F +44 2071 008 479
http://www.konesans.com

hi,
my code is running but i cant set Step name.
and error is = Package failed because Step 'DTSStep_DTSDataPumpTask_1'
failed
code is here :

DTS.Package objPackage = new DTS.Package();
UCOMIConnectionPointContainer cpContainer;
cpContainer =
((UCOMIConnectionPointContainer)(objPackage));
UCOMIConnectionPoint cpPoint;

Guid guid = new
Guid("10020605-EB1C-11CF-AE6E-00AA004A34D5");
cpContainer.FindConnectionPoint(ref guid, out cpPoint);
int intCookie;


DTS.Connection objConnect1;
DTS.Connection objConnect2;
DTS.Step2 objStep;
DTS.Task objTask;
DTS.DataPumpTask2 objDataPump;
DTS.Transformation objTransformation;

objPackage.Name = "DTS100";
objConnect1 = objPackage.Connections.New("SQLOLEDB");
objConnect1.ID = 2;
objConnect1.DataSource = "(local)";
objConnect1.Catalog = "**";
objConnect1.UserID = "**";
objConnect1.Password = "**";
objConnect1.UseTrustedConnection = false;
objPackage.Connections.Add(objConnect1);

objConnect2 =
objPackage.Connections.New("DTSFlatFile");
objConnect2.ConnectionProperties.Item("Data
Source").Value = @"C:\hede\hede.txt";
objConnect2.ConnectionProperties.Item("File
Format").Value = 1;
objConnect2.ConnectionProperties.Item("Number of
Column").Value = 3;
objConnect2.ConnectionProperties.Item("Mode").Valu e =
3;
objConnect2.ID = 1;
objConnect2.DataSource = @"C:\hede\hede.txt";
objConnect2.UseTrustedConnection = false;
objConnect2.UseDSL = false;
objConnect2.Reusable = true;
objPackage.Connections.Add(objConnect2);


objTask = objPackage.Tasks.New("DTSDataPumpTask");
objTask.Name = "DTSTransformation__1";


objDataPump = (DTS.DataPumpTask2)objTask.CustomTask;
objDataPump.Name = @"Copy Data from C:\hede\hede.txt to
[.].[dbo].[111] Task";

objStep = (DTS.Step2)objPackage.Steps.New();
objStep.Name = "DTSStep_DTSDataPumpTask_1";
objStep.TaskName = objDataPump.Name;
objStep.FailPackageOnError = false;
objStep.RollbackFailure = false;
objStep.AddGlobalVariables = true;
objStep.DisableStep = false;
objPackage.Steps.Add(objStep);

objDataPump.SourceConnectionID = 1;
objDataPump.SourceObjectName = @"C:\hede\hede.txt";

objDataPump.DestinationConnectionID = 2;

objDataPump.UseFastLoad = true;
objDataPump.FastLoadOptions =
DTS.DTSFastLoadOptions.DTSFastLoad_Default;

objDataPump.ExceptionFileColumnDelimiter = ",";
objDataPump.ExceptionFileRowDelimiter = "\n\r";
objDataPump.AllowIdentityInserts = false;


objDataPump.FirstRow = 0;
objDataPump.LastRow = 0;

objDataPump.ExceptionFileOptions =
DTS.DTSExceptionFileOptions.DTSExcepFile_AbortOnRo wLogFailure;
objDataPump.DataPumpOptions = 0;

objTransformation =
objDataPump.Transformations.New("DTSPump.DataPumpT ransformCopy");
objPackage.AutoCommitTransaction = true;
objPackage.FailOnError = true;
objTransformation.TransformFlags = 63;
objPackage.Tasks.Add(objTask);

objPackage.WriteCompletionStatusToNTEventLog = true;
try
{
objPackage.Execute();
return "OK";
}
catch (Exception erroreDTS)
{
return messaggio = messaggio + "Errore nella
Execute: " + erroreDTS.Message;
}


my target is data transfer from txt file to table in sql server
but not using DTS.i must writing code for DTS programming.
if you send code for dts programming i am be happy..

Allan Mitchell yazdi: