Save DTS results to user defied location
-
Hi,
I have discovered DTS in the past couple of days and think it is wonderful! I am working with a SQL query that I need to export and I am doing it through DTS. I am running a VB application to make it easy on the front end, but I have no idea on how to let the user specify where he/she wants to save the info. Can someone help me or show me the right article so that I can make this wonderful tool work.
I also need some help on MSGBox'es. To let the user know the save was successful or not.
Again, Thanks!!!
-
If you guve the user a form and have them enter the location (or browse) from VB, you can open and access the package and it's objects from VB. Save the package as a Structured Storage file and then open it in your VB project.
-
I'm going to try to answer the question of setting the connection. There are a couple ways to do it.
You can add the following code (adapted from http://www.sqldts.com/200.aspx).
' Pkg 200Option Explicit
Function Main()
Dim oConn, sFilename
sFilename = DTSGlobalVariables("InputFile").Value
Set oConn = _ DTSGlobalVariables.Parent.Connections("Text File (Source)")
oConn.DataSource = sFilename
Set oConn = Nothing
Main = DTSTaskExecResult_Success
End Function
You pass in the Global variable in you VB Shell command (I will let you look up how to Call the shell command):
dtsrun /Sserver_name /Uuser_nName /Ppassword
/Npackage_name /Mpackage_password /A "InputFile":"8":"FileValue.txt"
I am not sure how to determine success or failure.
Russel Loski, MCSE Business Intelligence, Data Platform
-
RLoski,
That is excactly what I was looking for. I will have to play around with it. I have not gotten it to work yet, but will let you know. Thank you so much for the answer.
Lawrence
-
Can someone help me please.
I am having issues with the following code. This is the first time I am doing this, so it is Brand new to me. Sorry if I look and sound like an idiot.
I am having issues with 'DTS.OleDBProperties' cannot be indexed because it has no default property at the following line :oConnection.ConnectionProperties
I used DTS wizard to create my vb code and I am following this page: http://msdn2.microsoft.com/en-us/library/aa176248(SQL.80).aspx
Can someone please guide me in the right direction.
Option Explicit
Public goPackageOld As New DTS.Package
Public goPackage As DTS.Package2
Private Sub Main()
set goPackage = goPackageOld
goPackage.Name = "Test"
goPackage.Description = "Casier"
goPackage.WriteCompletionStatusToNTEventLog = False
goPackage.FailOnError = False
goPackage.PackagePriorityClass = 2
goPackage.MaxConcurrentSteps = 4
goPackage.LineageOptions = 0
goPackage.UseTransaction = True
goPackage.TransactionIsolationLevel = 4096
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 DTS.OleDBProperty
'---------------------------------------------------------------------------
' create package connection information
'---------------------------------------------------------------------------
Dim oConnection as DTS.Connection2
'------------- a new connection defined below.
'For security purposes, the password is never scripted
Set oConnection = goPackage.Connections.New("SQLOLEDB")
oConnection.ConnectionProperties("Integrated Security") = "SSPI"
oConnection.ConnectionProperties("Persist Security Info") = True
oConnection.ConnectionProperties("Initial Catalog") = "EndofY"
oConnection.ConnectionProperties("Data Source") = "(local)"
oConnection.ConnectionProperties("Application Name") = "DTS Import/Export Wizard"
oConnection.Name = "Connection 1"
oConnection.ID = 1
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = "(local)"
oConnection.ConnectionTimeout = 60
oConnection.Catalog = "EndofY"
oConnection.UseTrustedConnection = True
oConnection.UseDSL = False
'If you have a password for this connection, please uncomment and add your password below.
'oConnection.Password = "<put the password here>"
goPackage.Connections.Add oConnection
Set oConnection = Nothing
'------------- a new connection defined below.
'For security purposes, the password is never scripted
Set oConnection = goPackage.Connections.New("DTSFlatFile")
oConnection.ConnectionProperties("Data Source") = ""
oConnection.ConnectionProperties("Mode") = 3
oConnection.ConnectionProperties("Row Delimiter") = vbCrLf
oConnection.ConnectionProperties("File Format") = 1
oConnection.ConnectionProperties("Column Delimiter") = ","
oConnection.ConnectionProperties("File Type") = 1
oConnection.ConnectionProperties("Skip Rows") = 0
oConnection.ConnectionProperties("Text Qualifier") = """"
oConnection.ConnectionProperties("First Row Column Name") = False
oConnection.ConnectionProperties("Column Names") = "HQID,LastUpdated,Number,StoreID,ID,Name,Password,FloorLimit,ReturnLimit,CashDrawerNumber,SecurityLevel,Privileges,EmailAddress,FailedLogonAttempts,DBTimeStamp,MaxOverShortAmount,MaxOverShortPercent,OverShortLimitType,AutoID,Telephone"
oConnection.ConnectionProperties("Number of Column") = 20
oConnection.ConnectionProperties("Text Qualifier Col Mask: 0=no, 1=yes, e.g. 0101") = "00100110000010000001"
oConnection.ConnectionProperties("Max characters per delimited column") = 8000
oConnection.ConnectionProperties("Blob Col Mask: 0=no, 1=yes, e.g. 0101") = "00000000000000000000"
oConnection.Name = "Connection 2"
oConnection.ID = 2
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = ""
oConnection.ConnectionTimeout = 60
oConnection.UseTrustedConnection = False
oConnection.UseDSL = False
'If you have a password for this connection, please uncomment and add your password below.
'oConnection.Password = "<put the password here>"
goPackage.Connections.Add oConnection
Set oConnection = Nothing
'---------------------------------------------------------------------------
' create package steps information
'---------------------------------------------------------------------------
Dim oStep as DTS.Step2
Dim oPrecConstraint as DTS.PrecedenceConstraint
'------------- a new step defined below
Set oStep = goPackage.Steps.New
oStep.Name = "Copy Data from Results to Step"
oStep.Description = "Copy Data from Results to Step"
oStep.ExecutionStatus = 1
oStep.TaskName = "Copied data in table "
oStep.CommitSuccess = False
oStep.RollbackFailure = False
oStep.ScriptLanguage = "VBScript"
oStep.AddGlobalVariables = True
oStep.RelativePriority = 3
oStep.CloseConnection = False
oStep.ExecuteInMainThread = False
oStep.IsPackageDSORowset = False
oStep.JoinTransactionIfPresent = False
oStep.DisableStep = False
oStep.FailPackageOnError = False
goPackage.Steps.Add oStep
Set oStep = Nothing
'---------------------------------------------------------------------------
' create package tasks information
'---------------------------------------------------------------------------
'------------- call Task_Sub1 for task Copied data in table (Copied data in table )
Call Task_Sub1( goPackage 
'---------------------------------------------------------------------------
' Save or execute package
'---------------------------------------------------------------------------
'goPackage.SaveToSQLServer "(local)", "sa", ""
goPackage.Execute
tracePackageError goPackage
goPackage.Uninitialize
'to save a package instead of executing it, comment out the executing package line above and uncomment the saving package line
set goPackage = Nothing
set goPackageOld = Nothing
End Sub
'-----------------------------------------------------------------------------
' error reporting using step.GetExecutionErrorInfo after execution
'-----------------------------------------------------------------------------
Public Sub tracePackageError(oPackage As DTS.Package)
Dim ErrorCode As Long
Dim ErrorSource As String
Dim ErrorDescription As String
Dim ErrorHelpFile As String
Dim ErrorHelpContext As Long
Dim ErrorIDofInterfaceWithError As String
Dim i As Integer
For i = 1 To oPackage.Steps.Count
If oPackage.Steps(i).ExecutionResult = DTSStepExecResult_Failure Then
oPackage.Steps(i).GetExecutionErrorInfo ErrorCode, ErrorSource, ErrorDescription, _
ErrorHelpFile, ErrorHelpContext, ErrorIDofInterfaceWithError
MsgBox oPackage.Steps(i).Name & " failed" & vbCrLf & ErrorSource & vbCrLf & ErrorDescription
End If
Next i
End Sub
'------------- define Task_Sub1 for task Copied data in table (Copied data in table )
Public Sub Task_Sub1(ByVal goPackage As Object)
Dim oTask As DTS.Task
Dim oLookup As DTS.Lookup
Dim oCustomTask1 As DTS.DataPumpTask2
Set oTask = goPackage.Tasks.New("DTSDataPumpTask")
oTask.Name = "Copied data in table "
Set oCustomTask1 = oTask.CustomTask
oCustomTask1.Name = "Copied data in table "
oCustomTask1.Description = "Copied data in table "
oCustomTask1.SourceConnectionID = 1
oCustomTask1.SourceSQLStatement = "Select * from Cashier"
oCustomTask1.DestinationConnectionID = 2
oCustomTask1.DestinationObjectName = "
oCustomTask1.ProgressRowCount = 1000
oCustomTask1.MaximumErrorCount = 0
oCustomTask1.FetchBufferSize = 1
oCustomTask1.UseFastLoad = True
oCustomTask1.InsertCommitSize = 0
oCustomTask1.ExceptionFileColumnDelimiter = "|"
oCustomTask1.ExceptionFileRowDelimiter = vbCrLf
oCustomTask1.AllowIdentityInserts = False
oCustomTask1.FirstRow = 0
oCustomTask1.LastRow = 0
oCustomTask1.FastLoadOptions = 2
oCustomTask1.ExceptionFileOptions = 1
oCustomTask1.DataPumpOptions = 0
Call oCustomTask1_Trans_Sub1( oCustomTask1 
goPackage.Tasks.Add oTask
Set oCustomTask1 = Nothing
Set oTask = Nothing
End Sub
Public Sub oCustomTask1_Trans_Sub1(ByVal oCustomTask1 As Object)
Dim oTransformation As DTS.Transformation2
Dim oTransProps as DTS.Properties
Dim oColumn As DTS.Column
Set oTransformation = oCustomTask1.Transformations.New("DTS.DataPumpTransformCopy")
oTransformation.Name = "DirectCopyXform"
oTransformation.TransformFlags = 63
oTransformation.ForceSourceBlobsBuffered = 0
oTransformation.ForceBlobsInMemory = False
oTransformation.InMemoryBlobSize = 1048576
oTransformation.TransformPhases = 4
Set oColumn = oTransformation.SourceColumns.New("HQID" , 1)
oColumn.Name = "HQID"
oColumn.Ordinal = 1
oColumn.Flags = 24
oColumn.Size = 0
oColumn.DataType = 3
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("LastUpdated" , 2)
oColumn.Name = "LastUpdated"
oColumn.Ordinal = 2
oColumn.Flags = 24
oColumn.Size = 0
oColumn.DataType = 135
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("Number" , 3)
oColumn.Name = "Number"
oColumn.Ordinal = 3
oColumn.Flags = 8
oColumn.Size = 9
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("StoreID" , 4)
oColumn.Name = "StoreID"
oColumn.Ordinal = 4
oColumn.Flags = 24
oColumn.Size = 0
oColumn.DataType = 3
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("ID" , 5)
oColumn.Name = "ID"
oColumn.Ordinal = 5
oColumn.Flags = 24
oColumn.Size = 0
oColumn.DataType = 3
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("Name" , 6)
oColumn.Name = "Name"
oColumn.Ordinal = 6
oColumn.Flags = 8
oColumn.Size = 50
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("Password" , 7)
oColumn.Name = "Password"
oColumn.Ordinal = 7
oColumn.Flags = 8
oColumn.Size = 12
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("FloorLimit" , 8)
oColumn.Name = "FloorLimit"
oColumn.Ordinal = 8
oColumn.Flags = 24
oColumn.Size = 0
oColumn.DataType = 6
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("ReturnLimit" , 9)
oColumn.Name = "ReturnLimit"
oColumn.Ordinal = 9
oColumn.Flags = 24
oColumn.Size = 0
oColumn.DataType = 6
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("CashDrawerNumber" , 10)
oColumn.Name = "CashDrawerNumber"
oColumn.Ordinal = 10
oColumn.Flags = 24
oColumn.Size = 0
oColumn.DataType = 2
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("SecurityLevel" , 11)
oColumn.Name = "SecurityLevel"
oColumn.Ordinal = 11
oColumn.Flags = 24
oColumn.Size = 0
oColumn.DataType = 2
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("Privileges" , 12)
oColumn.Name = "Privileges"
oColumn.Ordinal = 12
oColumn.Flags = 24
oColumn.Size = 0
oColumn.DataType = 3
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("EmailAddress" , 13)
oColumn.Name = "EmailAddress"
oColumn.Ordinal = 13
oColumn.Flags = 8
oColumn.Size = 255
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("FailedLogonAttempts" , 14)
oColumn.Name = "FailedLogonAttempts"
oColumn.Ordinal = 14
oColumn.Flags = 24
oColumn.Size = 0
oColumn.DataType = 3
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("DBTimeStamp" , 15)
oColumn.Name = "DBTimeStamp"
oColumn.Ordinal = 15
oColumn.Flags = 592
oColumn.Size = 0
oColumn.DataType = 128
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("MaxOverShortAmount" , 16)
oColumn.Name = "MaxOverShortAmount"
oColumn.Ordinal = 16
oColumn.Flags = 24
oColumn.Size = 0
oColumn.DataType = 6
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("MaxOverShortPercent" , 17)
oColumn.Name = "MaxOverShortPercent"
oColumn.Ordinal = 17
oColumn.Flags = 24
oColumn.Size = 0
oColumn.DataType = 5
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("OverShortLimitType" , 18)
oColumn.Name = "OverShortLimitType"
oColumn.Ordinal = 18
oColumn.Flags = 24
oColumn.Size = 0
oColumn.DataType = 3
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("AutoID" , 19)
oColumn.Name = "AutoID"
oColumn.Ordinal = 19
oColumn.Flags = 32784
oColumn.Size = 0
oColumn.DataType = 3
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("Telephone" , 20)
oColumn.Name = "Telephone"
oColumn.Ordinal = 20
oColumn.Flags = 8
oColumn.Size = 30
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("HQID" , 1)
oColumn.Name = "HQID"
oColumn.Ordinal = 1
oColumn.Flags = 24
oColumn.Size = 12
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("LastUpdated" , 2)
oColumn.Name = "LastUpdated"
oColumn.Ordinal = 2
oColumn.Flags = 24
oColumn.Size = 25
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("Number" , 3)
oColumn.Name = "Number"
oColumn.Ordinal = 3
oColumn.Flags = 8
oColumn.Size = 9
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("StoreID" , 4)
oColumn.Name = "StoreID"
oColumn.Ordinal = 4
oColumn.Flags = 24
oColumn.Size = 12
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("ID" , 5)
oColumn.Name = "ID"
oColumn.Ordinal = 5
oColumn.Flags = 24
oColumn.Size = 12
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("Name" , 6)
oColumn.Name = "Name"
oColumn.Ordinal = 6
oColumn.Flags = 8
oColumn.Size = 50
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("Password" , 7)
oColumn.Name = "Password"
oColumn.Ordinal = 7
oColumn.Flags = 8
oColumn.Size = 12
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("FloorLimit" , 8)
oColumn.Name = "FloorLimit"
oColumn.Ordinal = 8
oColumn.Flags = 24
oColumn.Size = 21
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("ReturnLimit" , 9)
oColumn.Name = "ReturnLimit"
oColumn.Ordinal = 9
oColumn.Flags = 24
oColumn.Size = 21
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("CashDrawerNumber" , 10)
oColumn.Name = "CashDrawerNumber"
oColumn.Ordinal = 10
oColumn.Flags = 24
oColumn.Size = 7
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("SecurityLevel" , 11)
oColumn.Name = "SecurityLevel"
oColumn.Ordinal = 11
oColumn.Flags = 24
oColumn.Size = 7
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("Privileges" , 12)
oColumn.Name = "Privileges"
oColumn.Ordinal = 12
oColumn.Flags = 24
oColumn.Size = 12
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("EmailAddress" , 13)
oColumn.Name = "EmailAddress"
oColumn.Ordinal = 13
oColumn.Flags = 8
oColumn.Size = 255
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("FailedLogonAttempts" , 14)
oColumn.Name = "FailedLogonAttempts"
oColumn.Ordinal = 14
oColumn.Flags = 24
oColumn.Size = 12
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("DBTimeStamp" , 15)
oColumn.Name = "DBTimeStamp"
oColumn.Ordinal = 15
oColumn.Flags = 592
oColumn.Size = 8
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("MaxOverShortAmount" , 16)
oColumn.Name = "MaxOverShortAmount"
oColumn.Ordinal = 16
oColumn.Flags = 24
oColumn.Size = 21
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("MaxOverShortPercent" , 17)
oColumn.Name = "MaxOverShortPercent"
oColumn.Ordinal = 17
oColumn.Flags = 24
oColumn.Size = 17
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("OverShortLimitType" , 18)
oColumn.Name = "OverShortLimitType"
oColumn.Ordinal = 18
oColumn.Flags = 24
oColumn.Size = 12
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("AutoID" , 19)
oColumn.Name = "AutoID"
oColumn.Ordinal = 19
oColumn.Flags = 32784
oColumn.Size = 12
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("Telephone" , 20)
oColumn.Name = "Telephone"
oColumn.Ordinal = 20
oColumn.Flags = 8
oColumn.Size = 30
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oTransProps = oTransformation.TransformServerProperties
Set oTransProps = Nothing
oCustomTask1.Transformations.Add oTransformation
Set oTransformation = Nothing
End Sub
Lawrence
-
Try:
oConnection.ConnectionProperties.Item("Integrated Security") = "SSPI"
(etc)
Russel Loski, MCSE Business Intelligence, Data Platform
-
I get the following now:
Expression is a value and therefore cannot be part of an expression.
-
Is it giving a line number?
Russel Loski, MCSE Business Intelligence, Data Platform
-
It gives line 52.
Thanks Russel
-
Sorry, the error says:
Expression is a value and therefore cannot be the target of an assignment
-
I haven't seen that error before. Can you step through the code to find out what line the error is happening at?
Russel Loski, MCSE Business Intelligence, Data Platform
-
I found my issue Russell,
I needed to upgrade my code to vb.net. DTS package is running perfect now. I have another question. Do you know how to make the package so that the user selects where to save the file?
Lawrence
-
I don't have a .Net windows app open, so I may be off on certain details. But here is the gist of what I would do:
dim f as new FileDialog()
f.Show() ' I can't find the exact syntax or object for file dialog.
dim sTarget as String = f.Path
' Add the variable to the following line:
Set oConnection = goPackage.Connections.New("DTSFlatFile")
oConnection.ConnectionProperties("Data Source") = sTarget
Russel Loski, MCSE Business Intelligence, Data Platform
-
I found it, thanks. It does not work though. It brings up the dialog with no default save field (.txt or .csv)... After I entered all the info and hit save it does not save it. Any ideas?
Ps: Thank you so much for the help thus far. I might only answer on Monday again, but please don't forget me.
Lawrence
-
One of the properties of the filedialog returns the file name and path. I just can't put my finger on it right away.
Russel Loski, MCSE Business Intelligence, Data Platform
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply