Re: create dynamic dts package
- From: Allan Mitchell <allan@xxxxxxxxxxxxxxxxxx>
- Date: Mon, 27 Nov 2006 14:00:40 +0000 (UTC)
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
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").Value = 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,
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
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)
.
- Follow-Ups:
- Re: create dynamic dts package
- From: hedecan
- Re: create dynamic dts package
- References:
- Re: create dynamic dts package
- From: hedecan
- Re: create dynamic dts package
- Prev by Date: Re: Parameter passing for SSIS when calling from a .NET assembly
- Next by Date: Re: Dynamic Data Source Connection
- Previous by thread: Re: create dynamic dts package
- Next by thread: Re: create dynamic dts package
- Index(es):
Relevant Pages
|
