Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 304
  • Last Modified:

Convert text file -> SQL 7 FAST??

I actually want to achieve this in VB, but this forum seems to have the most sql savvy types in it...

There is clearly a faster way to import a text file into a database than using the filesystemobject to iterate through the file, one line at a time, and do an INSERT each time.

I know this because to do this on a text file with 250,000 lines takes about 10-15 minutes, while the Import Data utility takes about 30 seconds!

NB. structure of the text file is one column, one row per line (separated by a LF).

I'm thinking maybe you could use the ODBC text file driver, but I can't find how to implement this.

Help?
0
AddamB
Asked:
AddamB
  • 11
  • 10
  • 4
  • +2
1 Solution
 
weesiongCommented:
AddamB,

Using SQL Server Import Data from txt

Regards,
Wee Siong
0
 
AddamBAuthor Commented:
Thanks Wee Siong,

I already know how to do that.

What I want to do is achieve this within an application I am writing, rather than use the Import Data utility.

:-)
0
 
thunderchickenCommented:
I parsed about 8 megs of text through DTS, it was faster than any VB or ASP app.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
AddamBAuthor Commented:
Can't you create DTS routines in VB though??
0
 
thunderchickenCommented:
Probably, never tried it.
0
 
puranik_pCommented:
You have to create an ODBC DSN to achiieve this.

Go to ODBC,
say ADD,
Select Microsoft text Driver from the list,
Give your DSN Name,
Uncheck the checkbox to 'Use Current Directory'
Select Directory to path of your text file.
Use the text file as a regular table in the DSN.
0
 
Michel SakrCommented:
I made ot thru fso readall, and split into an array.. then manipulate the data.. this was more faster than parsing line by line..like:

    Set objFSO = CreateObject("Scripting.FileSystemObject")
   
    Dim strRootFolder
    strRootFolder = Text1.Text
    'set our folder object to the web root folder
    Set objFolder = objFSO.GetFolder(strRootFolder)
   
    FileNo = 0
    For Each ObjFile In objFolder.Files
    If ObjFile.Size > 0 Then
    FileNo = FileNo + 1
    BatchID = BatchID + 1
    LogFile = ObjFile
    If LCase(Right(ObjFile.Name, 4)) = ".txt" Then
    Text2.Text = "Opening the log file..."
    DoEvents
    Set f = objFSO.GetFile(LogFile)
    Set ts = f.OpenAsTextStream(1, -2)
    Text2.Text = "Please wait..."
    DoEvents
     ' split here using your delimeter LF = chr(13) or chr(10)
    FilezArr = Split(ts.ReadAll, "/*")
    ts.Close
    'MsgBox UBound(FilezArr)
    cnt = UBound(FilezArr)
   
    For x = 0 To UBound(FilezArr) - 1
    Text2.Text = "Logging record: " & x & "/" & cnt
    LineParts = Split(FilezArr(x), " ")
    DoEvents
    '**********
    On Error Resume Next
    'Insert into database here
    '2 IP
    '3 TCP status
    '5 Method :get or post
    '6 URL visited
    'Host - resolved IP
    '**********
    If LineParts(2) <> "0.0.0.0" And InStr(1, LineParts(6), ".ida?") = 0 Then
    DbConn.Recordset.AddNew
    DbConn.Recordset!DestIP = LineParts(2)
    DbConn.Recordset!Method = LineParts(5)
    DbConn.Recordset!TCPResult = LineParts(3)
    DbConn.Recordset!TargetURL = LineParts(6)
    DbConn.Recordset!DirectUrl = LineParts(8)
    DbConn.Recordset!ResolvedIP = ""
    DbConn.Recordset!BatchID = BatchID
    End If
    If Err.Number <> 0 Then



a 4 meg file takes around 4 minutes to process and 2 minutes to log.. if there's a faster way that can be scheduled I'll award points to the expert too..

rgrds
0
 
Michel SakrCommented:
And the server specs are: Dual PII 450 Xeon processors, 512 MB ram..
0
 
AddamBAuthor Commented:
Hi Silvers5,

There is definitely a faster way :-)

I can do a 120Mb file on a single PIII-650, 256Mb server using the DTS wizard (Start->Microsoft SQL Server 7.0->Import and Export Data).


There is definitely a DTS API for VB (it's called "Microsoft DTSPackage Object Library")... I just don't have any code examples for using it.

0
 
AddamBAuthor Commented:
... re above... the 120Mb file took about 60 seconds
0
 
AddamBAuthor Commented:
PuranikP... I need to be able to select the text file at runtime. Can you create a DSN programmatically?
0
 
Michel SakrCommented:
hmm.. yes I knew about it.. but also my main problem is that I need to perform 2 splits before sending to the db.. otherwise I'll have to log to the database all the lines then parse each line again and split it to reenter only needed records..
0
 
AddamBAuthor Commented:
I believe DTS provides a lot of flexibility for the transformations.

eg. you could use a SQL query on the textfile datasource
0
 
Michel SakrCommented:
the only problem is the random filenames.. or there can be several.. what if:

you can make a DTS package and save it as a VB file, copy it and include it as a module in your application, now you can pass the module what file to read..

something similar:

    Set objFSO = CreateObject("Scripting.FileSystemObject")
   
    Dim strRootFolder
    strRootFolder = Text1.Text
    'set our folder object to the web root folder
    Set objFolder = objFSO.GetFolder(strRootFolder)
   
    FileNo = 0
    For Each ObjFile In objFolder.Files
    If ObjFile.Size > 0 Then
   'fire the module function or sub and pass it the file name , ObjFile.Name
    end if
    Next


didn't try it yet but I'm willing to do it now in a project I'm facing..
0
 
AddamBAuthor Commented:
How do you save the package as a .bas file?
0
 
Michel SakrCommented:
at the end of the DTS sched you have the option to save to a visual basic file.. try to install SQL2000 on your client and perform this.. on SQL 7 import export it's available but on the cd.. you'll have to install it as an extra option if I recall correctly.. it has som bugs in it..
0
 
Michel SakrCommented:
her's what I get.. once I include this module in my VB project I can easily pass it the variable text file name



'****************************************************************
'Microsoft SQL Server 2000
'Visual Basic file generated for DTS Package
'File Name: C:\Documents and Settings\msakr.HQDOMAIN1\Desktop\DTS1.bas
'Package Name: DTS1
'Package Description: DTS package description
'Generated Date: 8/28/2001
'Generated Time: 10:26:08 AM
'****************************************************************

Option Explicit
Public goPackageOld As New DTS.Package
Public goPackage As DTS.Package2
Private Sub Main()
        Set goPackage = goPackageOld

        goPackage.Name = "DTS1"
        goPackage.Description = "DTS package description"
        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("DTSFlatFile")

        oConnection.ConnectionProperties("Data Source") = "C:\Inetpub\ftproot\logsx\celog_172.16.0.10_20010828_020002.txt"
        oConnection.ConnectionProperties("Mode") = 1
        oConnection.ConnectionProperties("Row Delimiter") = "/*"
        oConnection.ConnectionProperties("File Format") = 1
        oConnection.ConnectionProperties("Column Delimiter") = " "
        oConnection.ConnectionProperties("File Type") = 1
        oConnection.ConnectionProperties("Skip Rows") = 0
        oConnection.ConnectionProperties("First Row Column Name") = False
       
        oConnection.Name = "Connection 1"
        oConnection.ID = 1
        oConnection.Reusable = True
        oConnection.ConnectImmediate = False
        oConnection.DataSource = "C:\Inetpub\ftproot\logsx\celog_172.16.0.10_20010828_020002.txt"
        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

'------------- 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") = "cachelogs"
        oConnection.ConnectionProperties("Data Source") = "INTRANET"
        oConnection.ConnectionProperties("Application Name") = "DTS  Import/Export Wizard"
       
        oConnection.Name = "Connection 2"
        oConnection.ID = 2
        oConnection.Reusable = True
        oConnection.ConnectImmediate = False
        oConnection.DataSource = "INTRANET"
        oConnection.ConnectionTimeout = 60
        oConnection.Catalog = "cachelogs"
        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

'---------------------------------------------------------------------------
' 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 celog_172 to [cachelogs].[dbo].[Failedlogs] Step"
        oStep.Description = "Copy Data from celog_172 to [cachelogs].[dbo].[Failedlogs] Step"
        oStep.ExecutionStatus = 1
        oStep.TaskName = "Copy Data from celog_172 to [cachelogs].[dbo].[Failedlogs] Task"
        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 Copy Data from celog_172 to [cachelogs].[dbo].[Failedlogs] Task (Copy Data from celog_172 to [cachelogs].[dbo].[Failedlogs] Task)
Call Task_Sub1(goPackage)

'---------------------------------------------------------------------------
' Save or execute package
'---------------------------------------------------------------------------

'goPackage.SaveToSQLServer "(local)", "sa", ""
goPackage.Execute
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


'------------- define Task_Sub1 for task Copy Data from celog_172 to [cachelogs].[dbo].[Failedlogs] Task (Copy Data from celog_172 to [cachelogs].[dbo].[Failedlogs] Task)
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")
Set oCustomTask1 = oTask.CustomTask

        oCustomTask1.Name = "Copy Data from celog_172 to [cachelogs].[dbo].[Failedlogs] Task"
        oCustomTask1.Description = "Copy Data from celog_172 to [cachelogs].[dbo].[Failedlogs] Task"
        oCustomTask1.SourceConnectionID = 1
        oCustomTask1.SourceObjectName = "C:\Inetpub\ftproot\logsx\celog_172.16.0.10_20010828_020002.txt"
        oCustomTask1.DestinationConnectionID = 2
        oCustomTask1.DestinationObjectName = "[cachelogs].[dbo].[Failedlogs]"
        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("Col003", 3)
                        oColumn.Name = "Col003"
                        oColumn.Ordinal = 3
                        oColumn.Flags = 32
                        oColumn.Size = 255
                        oColumn.DataType = 129
                        oColumn.Precision = 0
                        oColumn.NumericScale = 0
                        oColumn.Nullable = True
                       
                oTransformation.SourceColumns.Add oColumn
                Set oColumn = Nothing

                Set oColumn = oTransformation.SourceColumns.New("Col007", 7)
                        oColumn.Name = "Col007"
                        oColumn.Ordinal = 7
                        oColumn.Flags = 32
                        oColumn.Size = 255
                        oColumn.DataType = 129
                        oColumn.Precision = 0
                        oColumn.NumericScale = 0
                        oColumn.Nullable = True
                       
                oTransformation.SourceColumns.Add oColumn
                Set oColumn = Nothing

                Set oColumn = oTransformation.SourceColumns.New("Col006", 6)
                        oColumn.Name = "Col006"
                        oColumn.Ordinal = 6
                        oColumn.Flags = 32
                        oColumn.Size = 255
                        oColumn.DataType = 129
                        oColumn.Precision = 0
                        oColumn.NumericScale = 0
                        oColumn.Nullable = True
                       
                oTransformation.SourceColumns.Add oColumn
                Set oColumn = Nothing

                Set oColumn = oTransformation.SourceColumns.New("Col004", 4)
                        oColumn.Name = "Col004"
                        oColumn.Ordinal = 4
                        oColumn.Flags = 32
                        oColumn.Size = 255
                        oColumn.DataType = 129
                        oColumn.Precision = 0
                        oColumn.NumericScale = 0
                        oColumn.Nullable = True
                       
                oTransformation.SourceColumns.Add oColumn
                Set oColumn = Nothing

                Set oColumn = oTransformation.SourceColumns.New("Col009", 9)
                        oColumn.Name = "Col009"
                        oColumn.Ordinal = 9
                        oColumn.Flags = 32
                        oColumn.Size = 255
                        oColumn.DataType = 129
                        oColumn.Precision = 0
                        oColumn.NumericScale = 0
                        oColumn.Nullable = True
                       
                oTransformation.SourceColumns.Add oColumn
                Set oColumn = Nothing

                Set oColumn = oTransformation.DestinationColumns.New("DestIP", 2)
                        oColumn.Name = "DestIP"
                        oColumn.Ordinal = 2
                        oColumn.Flags = 104
                        oColumn.Size = 20
                        oColumn.DataType = 129
                        oColumn.Precision = 0
                        oColumn.NumericScale = 0
                        oColumn.Nullable = True
                       
                oTransformation.DestinationColumns.Add oColumn
                Set oColumn = Nothing

                Set oColumn = oTransformation.DestinationColumns.New("TargetURL", 3)
                        oColumn.Name = "TargetURL"
                        oColumn.Ordinal = 3
                        oColumn.Flags = 104
                        oColumn.Size = 500
                        oColumn.DataType = 129
                        oColumn.Precision = 0
                        oColumn.NumericScale = 0
                        oColumn.Nullable = True
                       
                oTransformation.DestinationColumns.Add oColumn
                Set oColumn = Nothing

                Set oColumn = oTransformation.DestinationColumns.New("Method", 4)
                        oColumn.Name = "Method"
                        oColumn.Ordinal = 4
                        oColumn.Flags = 104
                        oColumn.Size = 10
                        oColumn.DataType = 129
                        oColumn.Precision = 0
                        oColumn.NumericScale = 0
                        oColumn.Nullable = True
                       
                oTransformation.DestinationColumns.Add oColumn
                Set oColumn = Nothing

                Set oColumn = oTransformation.DestinationColumns.New("TCPResult", 5)
                        oColumn.Name = "TCPResult"
                        oColumn.Ordinal = 5
                        oColumn.Flags = 120
                        oColumn.Size = 50
                        oColumn.DataType = 129
                        oColumn.Precision = 0
                        oColumn.NumericScale = 0
                        oColumn.Nullable = True
                       
                oTransformation.DestinationColumns.Add oColumn
                Set oColumn = Nothing

                Set oColumn = oTransformation.DestinationColumns.New("DirectURL", 8)
                        oColumn.Name = "DirectURL"
                        oColumn.Ordinal = 8
                        oColumn.Flags = 104
                        oColumn.Size = 200
                        oColumn.DataType = 129
                        oColumn.Precision = 0
                        oColumn.NumericScale = 0
                        oColumn.Nullable = True
                       
                oTransformation.DestinationColumns.Add oColumn
                Set oColumn = Nothing

        Set oTransProps = oTransformation.TransformServerProperties

               
        Set oTransProps = Nothing

        oCustomTask1.Transformations.Add oTransformation
        Set oTransformation = Nothing

End Sub
0
 
Michel SakrCommented:
also don't forget to reference the Microsoft DTSpackage object to your project
0
 
Michel SakrCommented:
Here's what it finally looks like..
The Main is the main sub in the bas file created by the import wizard.. you add it in a module.. you set the logfile as a public variable.. so that you can pass its data to the module..

Private Sub Command1_Click()
    DoEvents
    Dim fs, f, s, ts
    Dim FilezArr, LineParts

    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Dim strRootFolder
    strRootFolder = Text1.Text
    'set our folder object to the web root folder
    Set objFolder = objFSO.GetFolder(strRootFolder)
   
    FileNo = 0
    For Each ObjFile In objFolder.Files
    If ObjFile.Size > 0 Then
    FileNo = FileNo + 1
    LogFile = ObjFile
    If LCase(Right(ObjFile.Name, 4)) = ".txt" Then
    Text2.Text = "Logging file .. Size = " & Fix(ObjFile.Size / 1024) & " Kb"
    DoEvents
    On Error Resume Next
    Call Main
    Else
    End If 'if file exists
    objFSO.CopyFile LogFile, strRootFolder & "\old\BatchOn" & Day(Now) & "-" & Month(Now) & "-" & Year(Now) & ".txt"
    objFSO.DeleteFile LogFile
    End If
    Next 'for each file
    Set objFSO = Nothing
    Timer1.Enabled = True
    Timer2.Enabled = True
End Sub


0
 
puranik_pCommented:
why are you bothering with FileSystemObject at all?
here's some code...

    Dim Con As New ADODB.Connection
    Dim Rs As New ADODB.Recordset
   
    Con.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=c:\yourdirectory;"
   
    Set Rs = Con.Execute("Select * from yourtextfile.txt")

    'Process the Recordset here.    

    Set Rs = Nothing
    Set Con = Nothing

Like this you can replace the yourtextfile.txt with any name you want (dynamically).

how'z that?
0
 
Michel SakrCommented:
>Like this you can replace the yourtextfile.txt with any name you want (dynamically).

you need fso to perform this action.. how are you going to get the file names? huh?
0
 
puranik_pCommented:
I believe, he's storing the filenames in a database or maybe i don't know where :o)
still, using FSO to just get the filename is very much better than using it to actually reading the text file which may be very large in size.
0
 
Michel SakrCommented:
>still, using FSO to just get the filename is very much better than using it to actually reading the
text file which may be very large in size.


If you read well the last codes I provided you can see that fso is used only to read the filename.. suppose that the files are dropped thru ftp.. you don't know the filename.. so you need to use fso to parse the filenames.. I implemented this today using dts and saw a performance boost of more than 3000% (30 times)


0
 
puranik_pCommented:
Silver5,
I overlooked the code u've posted for DTS.
still, I wish to try my code for the data on which u've got the performance boost of 3000%.
can you please check it using my code?
waiting...
0
 
AddamBAuthor Commented:
Puranik P... You don't need to use the method you've described if the DTS library is being invoked. If you read the first section of the DTS module Silvers5 has posted, you'll see that it sets up a text file connection within the DTS code... obviating the need for a second connection as you've described.

However, you've answered one of the questions I've had for a while... couldn't work out what the provider name was for text files (where DID you find it??)

0
 
AddamBAuthor Commented:
Silvers5...

Unfortunately, SQL 7 does NOT support saving from DTS to .bas; MSDN describes this as a new option in SQL 2000 (which I don't have, unfortunately).

Have a look at http://support.microsoft.com/support/kb/articles/Q293/1/64.ASP

Anyways, I will award points if you can re-post a .bas for DTS that ONLY does the following:

** Import text from a single-column text file, delineated by {LF} and separated by " ", into a new SQL 7 table.

Ideally, you should perform a transformation which names the column in the table - say, as "email", and adds a second "id" column (NOT NULL, identifier, auto-increment, start=1)

** you get 100 points for the main request. I'll award extra points if you get the transformation as well.

:-)
0
 
Michel SakrCommented:


'****************************************************************
'Microsoft SQL Server 2000
'Visual Basic file generated for DTS Package
'File Name: C:\Documents and Settings\msakr.HQDOMAIN1\Desktop\test.bas
'Package Name: test
'Package Description: DTS package description
'Generated Date: 8/29/2001
'Generated Time: 8:31:17 AM
'****************************************************************

Option Explicit
Public goPackageOld As New DTS.Package
Public goPackage As DTS.Package2
Private Sub Main()
        Set goPackage = goPackageOld

        goPackage.Name = "test"
        goPackage.Description = "DTS package description"
        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("DTSFlatFile")

        oConnection.ConnectionProperties("Data Source") = "C:\Documents and Settings\msakr.HQDOMAIN1\Desktop\test.txt"
        oConnection.ConnectionProperties("Mode") = 1
       
        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.Name = "Connection 1"
        oConnection.ID = 1
        oConnection.Reusable = True
        oConnection.ConnectImmediate = False
        oConnection.DataSource = "C:\Documents and Settings\msakr.HQDOMAIN1\Desktop\test.txt"
        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

'------------- 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") = "testlab"
        oConnection.ConnectionProperties("Data Source") = "INTRANET"
        oConnection.ConnectionProperties("Application Name") = "DTS  Import/Export Wizard"
       
        oConnection.Name = "Connection 2"
        oConnection.ID = 2
        oConnection.Reusable = True
        oConnection.ConnectImmediate = False
        oConnection.DataSource = "INTRANET"
        oConnection.ConnectionTimeout = 60
        oConnection.Catalog = "testlab"
        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

'---------------------------------------------------------------------------
' 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 = "Create Table [testlab].[dbo].[test] Step"
        oStep.Description = "Create Table [testlab].[dbo].[test] Step"
        oStep.ExecutionStatus = 1
        oStep.TaskName = "Create Table [testlab].[dbo].[test] Task"
        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

'------------- a new step defined below

Set oStep = goPackage.Steps.New

        oStep.Name = "Copy Data from test to [testlab].[dbo].[test] Step"
        oStep.Description = "Copy Data from test to [testlab].[dbo].[test] Step"
        oStep.ExecutionStatus = 1
        oStep.TaskName = "Copy Data from test to [testlab].[dbo].[test] Task"
        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

'------------- a precedence constraint for steps defined below

Set oStep = goPackage.Steps("Copy Data from test to [testlab].[dbo].[test] Step")
Set oPrecConstraint = oStep.precedenceConstraints.New("Create Table [testlab].[dbo].[test] Step")
        oPrecConstraint.StepName = "Create Table [testlab].[dbo].[test] Step"
        oPrecConstraint.PrecedenceBasis = 0
        oPrecConstraint.Value = 4
       
oStep.precedenceConstraints.Add oPrecConstraint
Set oPrecConstraint = Nothing

'---------------------------------------------------------------------------
' create package tasks information
'---------------------------------------------------------------------------

'------------- call Task_Sub1 for task Create Table [testlab].[dbo].[test] Task (Create Table [testlab].[dbo].[test] Task)
Call Task_Sub1(goPackage)

'------------- call Task_Sub2 for task Copy Data from test to [testlab].[dbo].[test] Task (Copy Data from test to [testlab].[dbo].[test] Task)
Call Task_Sub2(goPackage)

'---------------------------------------------------------------------------
' Save or execute package
'---------------------------------------------------------------------------

'goPackage.SaveToSQLServer "(local)", "sa", ""
goPackage.Execute
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


'------------- define Task_Sub1 for task Create Table [testlab].[dbo].[test] Task (Create Table [testlab].[dbo].[test] Task)
Public Sub Task_Sub1(ByVal goPackage As Object)

Dim oTask As DTS.Task
Dim oLookup As DTS.Lookup

Dim oCustomTask1 As DTS.ExecuteSQLTask2
Set oTask = goPackage.Tasks.New("DTSExecuteSQLTask")
Set oCustomTask1 = oTask.CustomTask

        oCustomTask1.Name = "Create Table [testlab].[dbo].[test] Task"
        oCustomTask1.Description = "Create Table [testlab].[dbo].[test] Task"
        oCustomTask1.SQLStatement = "CREATE TABLE [testlab].[dbo].[test] (" & vbCrLf
        oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "       [ID] [int] IDENTITY (1, 1) NOT NULL ," & vbCrLf
        oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "       [Email] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL " & vbCrLf
        oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & ") ON [PRIMARY]"
        oCustomTask1.ConnectionID = 2
        oCustomTask1.CommandTimeout = 0
        oCustomTask1.OutputAsRecordset = False
       
goPackage.Tasks.Add oTask
Set oCustomTask1 = Nothing
Set oTask = Nothing

End Sub

'------------- define Task_Sub2 for task Copy Data from test to [testlab].[dbo].[test] Task (Copy Data from test to [testlab].[dbo].[test] Task)
Public Sub Task_Sub2(ByVal goPackage As Object)

Dim oTask As DTS.Task
Dim oLookup As DTS.Lookup

Dim oCustomTask2 As DTS.DataPumpTask2
Set oTask = goPackage.Tasks.New("DTSDataPumpTask")
Set oCustomTask2 = oTask.CustomTask

        oCustomTask2.Name = "Copy Data from test to [testlab].[dbo].[test] Task"
        oCustomTask2.Description = "Copy Data from test to [testlab].[dbo].[test] Task"
        oCustomTask2.SourceConnectionID = 1
        oCustomTask2.SourceObjectName = "C:\Documents and Settings\msakr.HQDOMAIN1\Desktop\test.txt"
        oCustomTask2.DestinationConnectionID = 2
        oCustomTask2.DestinationObjectName = "[testlab].[dbo].[test]"
        oCustomTask2.ProgressRowCount = 1000
        oCustomTask2.MaximumErrorCount = 0
        oCustomTask2.FetchBufferSize = 1
        oCustomTask2.UseFastLoad = True
        oCustomTask2.InsertCommitSize = 0
        oCustomTask2.ExceptionFileColumnDelimiter = "|"
        oCustomTask2.ExceptionFileRowDelimiter = vbCrLf
        oCustomTask2.AllowIdentityInserts = False
        oCustomTask2.FirstRow = 0
        oCustomTask2.LastRow = 0
        oCustomTask2.FastLoadOptions = 2
        oCustomTask2.ExceptionFileOptions = 1
        oCustomTask2.DataPumpOptions = 0
       
Call oCustomTask2_Trans_Sub1(oCustomTask2)
               
               
goPackage.Tasks.Add oTask
Set oCustomTask2 = Nothing
Set oTask = Nothing

End Sub

Public Sub oCustomTask2_Trans_Sub1(ByVal oCustomTask2 As Object)

        Dim oTransformation As DTS.Transformation2
        Dim oTransProps As DTS.Properties
        Dim oColumn As DTS.Column
        Set oTransformation = oCustomTask2.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("Col001", 1)
                        oColumn.Name = "Col001"
                        oColumn.Ordinal = 1
                        oColumn.Flags = 32
                        oColumn.Size = 255
                        oColumn.DataType = 129
                        oColumn.Precision = 0
                        oColumn.NumericScale = 0
                        oColumn.Nullable = True
                       
                oTransformation.SourceColumns.Add oColumn
                Set oColumn = Nothing

                Set oColumn = oTransformation.DestinationColumns.New("Email", 1)
                        oColumn.Name = "Email"
                        oColumn.Ordinal = 1
                        oColumn.Flags = 32
                        oColumn.Size = 255
                        oColumn.DataType = 129
                        oColumn.Precision = 0
                        oColumn.NumericScale = 0
                        oColumn.Nullable = True
                       
                oTransformation.DestinationColumns.Add oColumn
                Set oColumn = Nothing

        Set oTransProps = oTransformation.TransformServerProperties

               
        Set oTransProps = Nothing

        oCustomTask2.Transformations.Add oTransformation
        Set oTransformation = Nothing

End Sub

this will create the table and insert in it the records.. now if you want to append to the table later you'll need to skip the creation part of it...

rgrds
0
 
AddamBAuthor Commented:
Hey Silvers5... you ROCK!
(guess SQL2000 is a handy upgrade!)

I had to make a few changes to the code (comment-out the properties that didn't apply to SQL7, as per the MSDN article above suggests).... the VB debugger picks them up nicely for you.


Also noticed that the code had a few "errors" in it...
eg.
Public goPackage As DTS.Package2

... instead of
Public goPackage As DTS.Package
(DTS.Package2 is undefined)

Also:

I had to comment-out the line...
Set oTransProps = oTransformation.TransformServerProperties

because it produced the error:
"The Transformation server does not have properties that can be set through Automation".
(couldn't find this one in MSDN)

And:

I had to change one line in the CREATE TABLE... it didn't like the COLLATE statement in the line that creates the Email column.

Finally:

I had to add...
   oConnection.ConnectionProperties("Row Delimiter") = vbLf
... in the DTSFlatFile Connection properties.


*** Handy way to debug this stuff is to use the goPackage.SaveToSQLServer line instead of the goPackage.Execute line (as per the MS comments).

... this saves the package to DTS/LocalPackages in the Enterprise Manager, and you can run the package from there to see what errors it produces.

0

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

  • 11
  • 10
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now