Solved

Convert text file -> SQL 7 FAST??

Posted on 2001-08-27
28
292 Views
Last Modified: 2007-12-19
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
Comment
Question by:AddamB
  • 11
  • 10
  • 4
  • +2
28 Comments
 
LVL 7

Expert Comment

by:weesiong
ID: 6430635
AddamB,

Using SQL Server Import Data from txt

Regards,
Wee Siong
0
 

Author Comment

by:AddamB
ID: 6430646
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
 
LVL 11

Expert Comment

by:thunderchicken
ID: 6430657
I parsed about 8 megs of text through DTS, it was faster than any VB or ASP app.
0
 

Author Comment

by:AddamB
ID: 6430665
Can't you create DTS routines in VB though??
0
 
LVL 11

Expert Comment

by:thunderchicken
ID: 6430688
Probably, never tried it.
0
 
LVL 14

Expert Comment

by:puranik_p
ID: 6430730
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
 
LVL 20

Expert Comment

by:Silvers5
ID: 6430789
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
 
LVL 20

Expert Comment

by:Silvers5
ID: 6430804
And the server specs are: Dual PII 450 Xeon processors, 512 MB ram..
0
 

Author Comment

by:AddamB
ID: 6430809
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
 

Author Comment

by:AddamB
ID: 6430812
... re above... the 120Mb file took about 60 seconds
0
 

Author Comment

by:AddamB
ID: 6430824
PuranikP... I need to be able to select the text file at runtime. Can you create a DSN programmatically?
0
 
LVL 20

Expert Comment

by:Silvers5
ID: 6430828
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
 

Author Comment

by:AddamB
ID: 6430835
I believe DTS provides a lot of flexibility for the transformations.

eg. you could use a SQL query on the textfile datasource
0
 
LVL 20

Expert Comment

by:Silvers5
ID: 6430976
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:AddamB
ID: 6431027
How do you save the package as a .bas file?
0
 
LVL 20

Expert Comment

by:Silvers5
ID: 6431062
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
 
LVL 20

Expert Comment

by:Silvers5
ID: 6431115
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
 
LVL 20

Expert Comment

by:Silvers5
ID: 6431128
also don't forget to reference the Microsoft DTSpackage object to your project
0
 
LVL 20

Expert Comment

by:Silvers5
ID: 6431307
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
 
LVL 14

Expert Comment

by:puranik_p
ID: 6431448
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
 
LVL 20

Expert Comment

by:Silvers5
ID: 6431612
>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
 
LVL 14

Expert Comment

by:puranik_p
ID: 6431767
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
 
LVL 20

Expert Comment

by:Silvers5
ID: 6431988
>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
 
LVL 14

Expert Comment

by:puranik_p
ID: 6432066
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
 

Author Comment

by:AddamB
ID: 6434363
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
 

Author Comment

by:AddamB
ID: 6434378
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
 
LVL 20

Accepted Solution

by:
Silvers5 earned 125 total points
ID: 6435252


'****************************************************************
'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
 

Author Comment

by:AddamB
ID: 6435386
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now