October 22, 2007 at 7:33 am
Someone has provided me the code for a DTS package I need to install on our server. I was expecting a file with a .dts extension, but instead I've gotten a .txt file, the first portion of which is below.
I feel really foolish asking this, but what do I do with this? Do I copy the code into a Visual Basic editor, and execute it to create the DTS package, and then modify it as I see fit? Or do I modify the code, and turn it into an executable? Or can I do something with it directly in the DTS designer?
Thanks for your help on this. If you need more of the package to see what else it's doing, let me know.
Mattie
'*****************************************'Microsoft SQL Server 2000
'Visual Basic file generated for DTS Package
'File Name: DCSS_IMPORT_FILE.bas
'Package Name: DCSS_IMPORT_FILE
'Package Description: Daily Processing of Paymentech files
'Generated Date: 10/19/2007
'Generated Time: 12:22:07 PM
'*****************************************
And the first several lines of the file are
Option Explicit
Public goPackageOld As New DTS.Package
Public goPackage As DTS.Package2
Private Sub Main()
set goPackage = goPackageOld
goPackage.Name = "DCSS_IMPORT_FILE"
goPackage.Description = "Daily Processing of Paymentech files"
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.LogServerName = "(local)"
goPackage.LogServerFlags = 256
goPackage.FailPackageOnLogFailure = False
goPackage.ExplicitGlobalVariables = False
goPackage.PackageType = 0
' begin to write package global variables information
Dim oGlobal As DTS.GlobalVariable
Set oGlobal = goPackage.GlobalVariables.New("server_name")
oGlobal = ***************
goPackage.GlobalVariables.Add oGlobal
set oGlobal = Nothing
Set oGlobal = goPackage.GlobalVariables.New("error_msg")
oGlobal = " "
goPackage.GlobalVariables.Add oGlobal
set oGlobal = Nothing
Set oGlobal = goPackage.GlobalVariables.New("FileName")
oGlobal = " "
goPackage.GlobalVariables.Add oGlobal
set oGlobal = Nothing
'---------------------------------------------------------------------------
' create package connection information
'---------------------------------------------------------------------------
Dim oConnection as DTS.Connection2
'------------- a new connection defined below.
'For security purposes, the password is never scripted
Set oConnection = goPackage.Connections.New("SQLOLEDB")
oConnection.ConnectionProperties("Integrated Security") = "SSPI"
oConnection.ConnectionProperties("Persist Security Info") = True
oConnection.ConnectionProperties("Initial Catalog") = ***************
oConnection.ConnectionProperties("Data Source") = ***************
oConnection.ConnectionProperties("Application Name") = "DTS Designer"
oConnection.Name = "Microsoft OLE DB Provider for SQL Server 2 Copy"
oConnection.ID = 6
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = ***************
oConnection.ConnectionTimeout = 60
oConnection.Catalog = ***************
oConnection.UseTrustedConnection = True
oConnection.UseDSL = False
'If you have a password for this connection, please uncomment and add your password below.
'oConnection.Password = " "
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") = ***************
oConnection.ConnectionProperties("Data Source") = ***************
oConnection.ConnectionProperties("Application Name") = "DTS Designer"
oConnection.Name = "Microsoft OLE DB Provider for SQL Server 2"
oConnection.ID = 3
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = ***************
oConnection.ConnectionTimeout = 60
oConnection.Catalog =***************
oConnection.UseTrustedConnection = True
oConnection.UseDSL = False
'If you have a password for this connection, please uncomment and add your password below.
'oConnection.Password = " "
goPackage.Connections.Add oConnection
Set oConnection = Nothing
'------------- a new connection defined below.
'For security purposes, the password is never scripted
Set oConnection = goPackage.Connections.NewDataLink(***************)
oConnection.Name = "***************"
oConnection.ID = 4
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.ConnectionTimeout = 60
oConnection.UseTrustedConnection = False
oConnection.UseDSL = True
'If you have a password for this connection, please uncomment and add your password below.
'oConnection.Password = " "
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("Persist Security Info") = True
oConnection.ConnectionProperties("User ID") = "***************"
oConnection.ConnectionProperties("Initial Catalog") = ***************
oConnection.ConnectionProperties("Data Source") = ***************
oConnection.ConnectionProperties("Application Name") = "DTS Designer"
oConnection.Name = "Microsoft OLE DB Provider for SQL Server"
oConnection.ID = 9
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = ***************
oConnection.UserID = ***************
oConnection.ConnectionTimeout = 60
oConnection.Catalog = ***************
oConnection.UseTrustedConnection = False
oConnection.UseDSL = False
'If you have a password for this connection, please uncomment and add your password below.
'oConnection.Password = " "
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") =***************
oConnection.ConnectionProperties("Data Source") =***************
oConnection.ConnectionProperties("Application Name") = "DTS Designer"
oConnection.Name = "DBConnection"
oConnection.ID = 7
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = ***************
oConnection.ConnectionTimeout = 60
oConnection.Catalog = ***************
oConnection.UseTrustedConnection = True
oConnection.UseDSL = False
'If you have a password for this connection, please uncomment and add your password below.
'oConnection.Password = " "
goPackage.Connections.Add oConnection
Set oConnection = Nothing
'------------- a new connection defined below.
'For security purposes, the password is never scripted
Set oConnection = goPackage.Connections.New("DTSFlatFile")
oConnection.ConnectionProperties("Data Source") = ***************
oConnection.ConnectionProperties("Mode") = 1
oConnection.ConnectionProperties("Row Delimiter") = vbCrLf
oConnection.ConnectionProperties("File Format") = 2
oConnection.ConnectionProperties("Column Lengths") = "8,1,9,1,20,1,15,1,1,1,9,1,8,1,1,1,2,1,1,1,8,1"
oConnection.ConnectionProperties("File Type") = 1
oConnection.ConnectionProperties("Skip Rows") = 0
oConnection.ConnectionProperties("Text Qualifier") = """"
oConnection.ConnectionProperties("First Row Column Name") = False
oConnection.ConnectionProperties("Number of Column") = 22
oConnection.ConnectionProperties("Max characters per delimited column") = 255
oConnection.Name = "Text File (Source)"
oConnection.ID = 8
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = ***************
oConnection.ConnectionTimeout = 60
oConnection.UseTrustedConnection = False
oConnection.UseDSL = False
'If you have a password for this connection, please uncomment and add your password below.
'oConnection.Password = " "
goPackage.Connections.Add oConnection
Set oConnection = Nothing
' create package steps information
October 23, 2007 at 2:19 am
Hi,
I am sorry i dont have the URL for the following microsoft KB. But I have copy pasted it. Hope this helps. The material below ********** can be usefull later on if u encounter any error related to that.
DTS Programming (SQL Server 2000)
Running a DTS Package Saved as a Visual Basic File
Running a DTS Package Saved as a Visual Basic File
You can run a Data Transformation Services (DTS) package that has been saved by one of the DTS tools as a Microsoft® Visual Basic® file. The saved module, a Visual Basic .bas file, consists of declarations and a Sub Main and may contain other Subs called by Sub Main. The Subs contain all the logic of the DTS package.
Here are the basic steps for incorporating a Visual Basic module file into a Visual Basic project and executing it on a computer running the Microsoft SQL Server™ client tools:
In Visual Basic, create a new Standard EXE project.
On the Project menu, click References, and then select the Microsoft DTSDataPump Scripting Object Library, Microsoft DTSPackage Object Library, and Microsoft DTS Custom Tasks Object Library check boxes.
Not all DTS programs will require all three of these libraries.
On the Project menu, click Add File, and then add the Visual Basic file produced by the DTS Import/Export Wizard or DTS Designer.
In the Project Explorer, select Form1 and then on the Project menu, click Remove Form1 to remove the blank form from the Project.
Run the project.
No indication of completion will be given other than the Visual Basic Development Environment will go back to design mode.
You may want to add completion notification and error handling and controls to allow the user to invoke the transformation.
Using the Visual Basic File to Save to SQL Server
The Visual Basic project you created from the generated Visual Basic file can be used to save the DTS package to SQL Server.
Here are the basic steps for saving Visual Basic files to SQL Server:
Go to the end of the Sub Main and uncomment the line
'objPackage.SaveToSQLServer ...
Comment out the following line
objPackage.Execute
Run the project.
When the Visual Basic Development Environment goes back to design mode, the package is saved to SQL Server.
The package can now be edited, maintained, and run from DTS Designer. It can be saved again as a Visual Basic file from DTS Designer.
*******************************************************
PRB: A DTS Package That You Save as a Visual Basic File May Fail to Import Tab-Delimited Text Data
View products that this article applies to.
Article ID : 320304
Last Review : February 22, 2007
Revision : 3.1
This article was previously published under Q320304
BUG #: 357022 (SHILOH_BUGS)
SYMPTOMS
If you save a Data Transformation Services (DTS) package as a Microsoft Visual Basic file, it may fail to import tab-delimited text data when you run the package from Microsoft Visual Basic, and you receive the following error message:
Copy data from 'transformation task name' Step failed. Microsoft Data Transformation Services (DTS) Data Pump Column Name 'column_name' was not found.
NOTE: The column name in the error message may vary depending on whether the first row of the input file contains column headings.
This error occurs on SQL Server 2000 Service Pack 1 (SP1) and SQL Server 2000 Service Pack 2 (SP2).
If you save the same DTS package as a Visual Basic file while running SQL Server 2000 RTM, the package runs from Visual Basic without errors; however, if the input file contains only one row, the row may fail to import, but no error is generated.
Back to the top
CAUSE
This behavior occurs because the column delimiter for the file is changed to spaces (opening quotation marks followed by eight spaces and then closing quotation marks): oConnection.ConnectionProperties("Column Delimiter") = " "
Back to the top
WORKAROUND
To work around this behavior, modify the Visual Basic file and change the ConnectionProperties("Column Delimiter") property of the text file to vbTab.
For example: oConnection.ConnectionProperties("Column Delimiter") = vbTab
Back to the top
STATUS
Microsoft is researching this problem and will post more information in this article when the information becomes available.
Regards
Prachin
October 23, 2007 at 6:29 am
Prachin,
Thank you so much. While waiting for a response, I experimented with the various ways you could save a DTS package, and figured out that the text file was a renamed .bas file, but having the directions you provided will make this much easier to add to our system.
Mattie:)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply