Monday, March 19, 2012

Generating DTS flat file connection

I'm trying to generate a DTS Package with VB.Net using the Microsoft DTSPackage Object Library and
and the Microsoft DTSDataPump Scripting Object Library
I have to load csv files into SQL tables.
I could generate both a SQL connection and a FlatFile connection and the transformationtask.

When I look at the transformationtask and click on the transformation tab I get this error

"Incomple file format information"

The problem is I don't find where I could set the FlatFile connection properties like "Text Qualifier" and
"row delimiter"

I tried this but it still shows CRLF as row delimiter when I look at the generated DTS Package

Dim oConnection As DTS.Connection2
Dim package As DTS.Package2
Dim filename As String
filename = "myfilename.csv"

oConnection = package.Connections.New("DTSFlatFile")
oConnection.Name = filename
oConnection.ConnectionProperties.Item("Row Delimiter").Value = vbTabWhy not just bcp the file out using a stored porcedure?

No comments:

Post a Comment