DTS Transformation Data Task Errors

Hi people,

I have VB.NET 2005 application that creates a DTS package based on
criteria read from an ini file. If I open and execute the created DTS
package in Enterpise Manager I receive the following error for each
Transform Data Task.

Step Error Source: Microsoft Data Transformation Services Flat File
Rowset Provider
Step Error Description:Incomplete file format information - file cannot
be opened.
Step Error code: 80004005
Step Error Help File:DTSFFile.hlp
Step Error Help Context ID:0

If I then open the Transform Data Tasks and define colmns on the
Destination tab, everything works 100%. Could someone please help me
solve this problem.

Thanks in advance.
Hank

He is my entire vb.net file:
Imports System.Windows.Forms

Public Class BuildDTSPackage

Public goPackageOld As New DTS.Package
Public goPackage As DTS.Package2

Private Sub btnLoadConfigurationFileLocation_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
btnLoadConfigurationFileLocation.Click
If dlgConfigurationFile.ShowDialog = Windows.Forms.DialogResult.OK
Then
txtbxConfigurationFileLocation.Text = dlgConfigurationFile.FileName
End If
End Sub

Private Sub btnLoadDestinationDTSFileLocation_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
btnLoadDestinationDTSFileLocation.Click
If dlgDestinationDTSFileLocation.ShowDialog =
Windows.Forms.DialogResult.OK Then
txtbxDestinationDTSFileLocation.Text =
dlgDestinationDTSFileLocation.FileName
End If
End Sub

Private Sub btnGenerateDTSPackage_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles btnGenerateDTSPackage.Click

'Load configuration from ini file
Dim oIniFile As New IniFile(txtbxConfigurationFileLocation.Text)

'Miscellaneous
Dim sDtsFullPathName As String = txtbxDestinationDTSFileLocation.Text

'Source Connection
Dim sServerName As String = oIniFile.GetString("Source Connection",
"ServerName", "")
Dim sDatabaseName As String = oIniFile.GetString("Source Connection",
"DatabaseName", "")
Dim bTrusted As Boolean = oIniFile.GetString("Source Connection",
"Trusted", True)
Dim sDBUserName As String = oIniFile.GetString("Source Connection",
"UserName", "")
Dim sPassword As String = oIniFile.GetString("Source Connection",
"Password", "")

'Destination Connection
Dim sExportLocation As String = oIniFile.GetString("Destination
Connection", "ExportLocation", "")
Dim sRowDelimiter As String = oIniFile.GetString("Destination
Connection", "RowDelimiter", "")
If sRowDelimiter = "{Cr}{Lf}" Then
sRowDelimiter = vbCrLf
ElseIf sRowDelimiter = "{Cr}" Then
sRowDelimiter = vbCr
ElseIf sRowDelimiter = "{Lf}" Then
sRowDelimiter = vbLf
End If
Dim sColumnDelimiter As String = oIniFile.GetString("Destination
Connection", "ColumnDelimiter", "")
Dim sTextQualifier As String = oIniFile.GetString("Destination
Connection", "TextQualifier", "")
Dim bColumnNames As Boolean = oIniFile.GetString("Destination
Connection", "ColumnNames", True)

'Objects Configuration
Dim sObjectNames As String = oIniFile.GetString("Objects
Configuration", "ObjectNames", "")

'Create DTS Package
goPackage = goPackageOld

goPackage.Name = "Export DTS Package"
goPackage.WriteCompletionStatusToNTEventLog = True
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.LogServerName = "(local)"
goPackage.LogServerFlags = 256
goPackage.FailPackageOnLogFailure = False
goPackage.ExplicitGlobalVariables = False
goPackage.PackageType = 0

'Create Source Connection
CreateSourceConnection(sDatabaseName, sServerName, bTrusted,
sDBUserName, sPassword)

'Create Destination Connections
CreateDestinationConnections(sExportLocation, sObjectNames,
sRowDelimiter, sColumnDelimiter, sTextQualifier, bColumnNames,
oIniFile)

'Save DTS package
If FileIO.FileSystem.FileExists(sDtsFullPathName) Then
FileIO.FileSystem.DeleteFile(sDtsFullPathName)
End If

goPackage.SaveToStorageFile(sDtsFullPathName)

'clear objects
oIniFile = Nothing
goPackage = Nothing

MessageBox.Show("Complete.", "Information", MessageBoxButtons.OK,
MessageBoxIcon.Information)
End Sub

Private Sub CreateSourceConnection(ByVal sDatabaseName As String,
ByVal sServerName As String, ByVal bTrusted As Boolean, ByVal
sDBUserName As String, ByVal sPassword As String)
Dim oConnection As DTS.Connection2

oConnection = goPackage.Connections.New("SQLOLEDB")

With oConnection

.ConnectionProperties.Item("Integrated Security").Value = "SSPI"
.ConnectionProperties.Item("Persist Security Info").Value = True
.ConnectionProperties.Item("Initial Catalog").Value = sDatabaseName
.ConnectionProperties.Item("Data Source").Value = sServerName
.ConnectionProperties.Item("Application Name").Value = "DTS
Designer"
.ConnectionProperties.Item("User ID").Value = sDBUserName

.Name = "Source Connection"
.ID = 1
.Reusable = True
.ConnectImmediate = False
.DataSource = sServerName
.UserID = sDBUserName
.ConnectionTimeout = 60
.Catalog = sDatabaseName
.UseTrustedConnection = bTrusted
.UseDSL = False
.Password = sPassword

goPackage.Connections.Add(oConnection)
oConnection = Nothing
End With
End Sub

Private Sub CreateDestinationConnections(ByVal sExportLocation As
String, ByVal sObjectNames As String, ByVal sRowDelimiter As String,
ByVal sColumnDelimiter As String, ByVal sTextQualifier As String, ByVal
bColumnNames As Boolean, ByVal oIniFile As Object)

Dim arrObjectNames As Array = Split(sObjectNames, ",")

Dim iObjectIndex As Integer = 0
Dim sSectionName As String = ""
Dim sDestinationFileName As String = ""
Dim sSourceObjectName As String = ""
Dim sTaskName As String = ""
Dim sStepName As String = ""
Dim sTransformationName As String = ""

For iObjectIndex = LBound(arrObjectNames) To UBound(arrObjectNames)

'Create Destination Connection
Dim oConnection As DTS.Connection2 =
goPackage.Connections.New("DTSFlatFile")

sSectionName = Trim(arrObjectNames(iObjectIndex))
sDestinationFileName =
FileIO.FileSystem.CombinePath(sExportLocation, sSectionName & ".csv")

With oConnection

.ConnectionProperties.Item("Data Source").Value =
sDestinationFileName
.ConnectionProperties.Item("Mode").Value = 3
.ConnectionProperties.Item("File Format").Value = 1
.ConnectionProperties.Item("Row Delimiter").Value = vbCrLf
.ConnectionProperties.Item("Column Delimiter").Value = ","
.ConnectionProperties.Item("File Type").Value = 1
.ConnectionProperties.Item("Skip Rows").Value = 0
.ConnectionProperties.Item("Text Qualifier").Value = sTextQualifier
.ConnectionProperties.Item("First Row Column Name").Value =
bColumnNames
.ConnectionProperties.Item("Max characters per delimited
column").Value = 8000

.Name = sSectionName & " Destination Column"
.ID = iObjectIndex + 2
.Reusable = True
.ConnectImmediate = False
.DataSource = sDestinationFileName
.ConnectionTimeout = 60
.UseTrustedConnection = False
.UseDSL = False

goPackage.Connections.Add(oConnection)
oConnection = Nothing

End With

'Create Data Pump Task
sSourceObjectName = oIniFile.GetString(sSectionName,
"SourceObjectName", "")
sTaskName = "Export " & sSectionName & " Task"

Dim oTask As DTS.Task

Dim oCustomTask As DTS.DataPumpTask2
oTask = goPackage.Tasks.New("DTSDataPumpTask")
oTask.Name = sTaskName
oCustomTask = oTask.CustomTask

With oCustomTask
.Name = sTaskName
.Description = sTaskName
.SourceConnectionID = 1
.SourceObjectName = sSourceObjectName
.DestinationConnectionID = iObjectIndex + 2
.DestinationObjectName = sDestinationFileName
.ProgressRowCount = 1000
.MaximumErrorCount = 0
.FetchBufferSize = 1
.UseFastLoad = True
.InsertCommitSize = 0
.ExceptionFileColumnDelimiter = sColumnDelimiter
.ExceptionFileRowDelimiter = vbCrLf
.ExceptionFileTextQualifier = ","
.AllowIdentityInserts = False
.FirstRow = 0
.LastRow = 0
.FastLoadOptions = 2
.ExceptionFileOptions = 1
.DataPumpOptions = 0
End With

goPackage.Tasks.Add(oTask)

'Create Step
sStepName = "Export " & sSectionName & " Step"

Dim oStep As DTS.Step2

oStep = goPackage.Steps.New

With oStep

.Name = sStepName
.Description = sStepName
.ExecutionStatus = 4
.TaskName = sTaskName
.CommitSuccess = False
.RollbackFailure = False
.ScriptLanguage = "VBScript"
.AddGlobalVariables = True
.RelativePriority = 3
.CloseConnection = True
.ExecuteInMainThread = False
.IsPackageDSORowset = False
.JoinTransactionIfPresent = False
.DisableStep = False
.FailPackageOnError = False
End With

goPackage.Steps.Add(oStep)

'Create Transformation
sTransformationName = sSectionName & " Copy Columns"

Dim oTransformation As DTS.Transformation2
oTransformation =
oCustomTask.Transformations.New("DTSPump.DataPumpTransformCopy")

With oTransformation
.Name = sTransformationName
.TransformFlags = 512
.ForceSourceBlobsBuffered = 0
.ForceBlobsInMemory = False
.InMemoryBlobSize = 1048576
.TransformPhases = 4
End With

'Add transformation columns

Dim sColumnNames As String = oIniFile.GetString(sSectionName,
"ColumnNames", "")
Dim arrColumnNames As Array = Split(sColumnNames, ",")
Dim sColumnName As String = ""
Dim iColumnIndex As Integer = 0

Dim oSourceColumn As DTS.Column
Dim oDestinationColumn As DTS.Column

For iColumnIndex = LBound(arrColumnNames) To UBound(arrColumnNames)
sColumnName = Trim(arrColumnNames(iColumnIndex))

oSourceColumn = oTransformation.SourceColumns.New(sColumnName,
iColumnIndex + 1)

With oSourceColumn
.Name = sColumnName
.Ordinal = iColumnIndex + 1
.Nullable = True
End With

oTransformation.SourceColumns.Add(oSourceColumn)

oDestinationColumn =
oTransformation.DestinationColumns.New(sColumnName, 1)

With oDestinationColumn
.Name = sColumnName
.Ordinal = iColumnIndex + 1
.Nullable = True
End With

oTransformation.DestinationColumns.Add(oDestinationColumn)
Next

oCustomTask.Transformations.Add(oTransformation)

'Clear Objects
oConnection = Nothing
oCustomTask = Nothing
oTask = Nothing
oStep = Nothing
oTransformation = Nothing
oSourceColumn = Nothing
oDestinationColumn = Nothing
Next
End Sub

Private Sub btnExit_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnExit.Click
Me.Close()
End Sub
End Class

Here is my ini file:

[Source Connection]
;This section is used to specify source connection information
;* - means that the field is required.
;ServerName* - String value representing the name or ip address of the
server running sql. E.g. (local) or SQL_SERVER or 127.0.0.1.
;DatabaseName* - String value representing the name of the database to
be exported from. E.g. MyDatabase or LoasDB.
;Trusted* - Boolean value specifying if the windows user account
should be used. E.g. True or False.
;UserName - String value representing a SQL user account. E.g. sa or
myuser. This value is required if "Trusted" is set to "False".
;Password - String value representing the password for the SQL user
account specified for "UserName". E.g. MyPassword. This value is
required if "Trusted" is set to "False".
ServerName=(local)
DatabaseName=pubs
Trusted=True
UserName=bob
Password=bob

[Destination Connection]
;This section is used to specify destination connection information
;* - means that the field is required.
; ExportLocation* - String value representing the location where the
data is to be exported to. E.g. C:\Export\ or D:\Export.
; RowDelimiter* - String value representing the delimiter for new rows.
E.g. vbCrLf. (Recommended - vbCrLf),
;For more information please visit the msdn library
(http://windowssdk.msdn.microsoft.com/library/default.asp?url=/library/en-us/script56/html/dada61a7-ec5f-4686-8ccf-f3a293b281c8.asp).
; ColumnDelimiter* - String value representing the column delimiter for
each column. E.g. , or ;.
; TextQualifier* - String value representing the text qualifier for
each column. E.g. ' or ".
;ColumnNames* - Boolean value representing if the first row should
contain column headings. E.g. True or False.
ExportLocation=D:\Export
RowDelimiter={Cr}{Lf}
ColumnDelimiter=,
TextQualifier="
ColumnNames=True

;####################################################################################################
;PLEASE NOT THAT THE BELOW SECTION SHOULD BE GENERATED USING THE
"GenerateINI.sql" SCRIPT OR BY ADVANCED USERS.
;Sample below:
; [authors]
; This section is used to specify the object to be exported and
destination file name of the records.
; * - means that the field is required.
; SourceObjectName* - Specify the database object (table or view) to
exported. E.g. MyView or vwDT_GB_SITE.
; DestinationFileName* - Specify the destination file that the results
from the source object will be save to. E.g. MyView.csv or GB_SITE.csv
; SourceObjectName=authors
; DestinationFileName=authors.csv
;####################################################################################################

[Objects Configuration]
;This section is used to specify sections used for export
configuration.
;* - means that the field is required.
;ObjectNames* - Unique string value representing the section name to be
referenced in this ini file seperated by a colon. e.g.
Section1,Section2, Section 3. Please note that each section name needs
to be unique.
ObjectNames=authors, titleauthor

[authors]
SourceObjectName=pubs.dbo.authors
DestinationFileName=authors.csv
ColumnNames=au_id,au_lname,au_fname,phone,address,city,state,zip,contract

[titleauthor]
SourceObjectName=pubs.dbo.titleauthor
DestinationFileName=titleauthor.csv
ColumnNames=au_id,title_id,au_ord,royaltyper

.