DTS Dynamic Import Question

[Cheryl3D]

I have a DTS package that I created that takes an Excel spreadsheet file and imports it into a SQL Server 2000 database, as a table.

First, it drops the existing table, then creates the table based on a T-SQL script, then it imports the Excel spreadsheet fields and data into the newly created table.

I used the DTS Import/Export Wizard to create the import tasks from Connection 1 (Excel spreadsheet) to Connection 2 (specified SQL Server database). Then, I used the DTS Designer in Enterprise Manager to add the Execute SQL Tasks for drop table and create table, and applied the workflows.

It all works fine. My issue is that for Connection 1’s File Name property (specifying the database path and file name holding the data for import), I hard coded a path/file name, but what I would like to do is take the File Name property and add end-user flexibility by allowing the File Name to be based on a path and file specified during run-time by the end user via a common dialog box (like in VBA/VB). Is there any way to accomplish this using a DTS package? Using an ActiveX Script?


What I want is dynamic configuration of a DTS package object, in this case a Connection object. Can I pass a variable to a Connection object? Any ideas?


Any assistance would be greatly appreciated.
Thanks,

CherylDixon

 

[tlbroadbent]

The SQLDTS.com website has several examples of setting a dynamic file path.

http://www.sqldts.com/default.aspx?6,101,101,5,1

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[Cheryl3D]

Hi:

Sorry but I need a little extra help on this one. This is my first time being exposed to the power of DTS in SQL Server. I'm cutting my teeth doing the following ActiveX Script in a DTS package.


'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************

Option Explicit

DTSglobalvariables("PullFile&quot = "C:\Documents and Settings\TriAnnual_Review\Bolling-DM-Work-Nov02.xls"

Function Main()

dim oFile

set oFile = CreateObject("Scripting.Filesystemobject&quot

If oFile.FileExists(DTSglobalvariables("PullFile&quot) Then

Msgbox "File " & DTSglobalvariables("PullFile&quot.Value & " exists."
Main = DTSTaskExecResult_Success
Else
Msgbox "File " & DTSglobalvariables("PullFile&quot.Value & " does not exists."
Main = DTSTaskExecResult_Failure

End If

End Function
'************************************************************************

I got it to work. When I execute it from SQL Server it goes out there and finds my C: drive and determines if the file I specified exists, where I designated in the string.

What I need to understand is how does the process for DTS packages work? Like if you have a connection where does the ActiveX Script fit in? Would it go a step before I have my Connection1 (which is an Excel spreadsheet connection)?

I went to the SQLDTS.com website as you suggested and tried the following code from the sample entitled "Working with files and the FileSystemObject":

Using Global Variables
To try and make our packages more flexible we should avoid using hard coded filenames as much as possible. One good method of doing this is to store all parameters required in global variables. In this way, no matter how many times we use a filename or file path inside our package, we only have to make one change. This sample is a move operation again, and uses two global variables. The source filename is held in one global variable, and the destination path or folder is held in a second. We are only supplying the destination folder in this example, so the existing filename will be preserved. It is important that the path is qualified with a backslash, so we use a helper function QualifyPath to ensure this is the case.

' Global Variable Move

'************************************************************************
Option Explicit

Function Main()

Dim oFSO
Dim sSourceFile
Dim sDestinationFile

Set oFSO = CreateObject("Scripting.FileSystemObject&quot

sSourceFile = DTSGlobalVariables("SourceFileName&quot.Value
sDestinationFile = QualifyPath(DTSGlobalVariables("DestinationPath&quot.Value)

MsgBox sSourceFile
MsgBox sDestinationFile
oFSO.MoveFile sSourceFile, sDestinationFile

' Clean Up
Set oFSO = Nothing

Main = DTSTaskExecResult_Success
End Function

Function QualifyPath(ByVal sPath)
If Right(sPath, 1) = "\" Then
QualifyPath = sPath
Else
QualifyPath = sPath & "\"
End If
End Function
'************************************************************************

I tried the code and received the following error:

Error Code: 0
Error Source= Microsoft VBScript runtime error
Error Description: Invalid procedure call or argument

Error on line 19

What I am doing wrong? How do I set Global Variables? Is there a dialog box?

Please advise.

Thanks,
Cheryl 3D

 

[tlbroadbent]

You define Global Variables for the package by right-clicking the package and selecting Package Properties. Then click on the Global Variables tab. Create the SourceFileName and DestinationPath variables as strings.

You can set the values in the properties or to make it more dynamic you can use code like the following that asks the user for input.

'***********************************************************' Visual Basic ActiveX Script
'***********************************************************
Function Main()
DTSGlobalVariables("FileLocation&quot.Value = InputBox("Type File Location", "File Location" , "C:\temp&quot
Msgbox DTSGlobalVariables("FileLocation&quot
Main = DTSTaskExecResult_Success
End Function

You can download a custom task from SQLDTS.com that allows you to display a file dialog so a global variable can be set to the chosen file name.

Open File Dialog Custom Task

http://www.sqldts.com/default.aspx?6,101,226,0,1

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[Cheryl3D]

Hi tbroadbent:

I tried your code using the Inputbox and it worked fine. However, I want something more flexible like the Open File Dialog Custom Task, which would be perfect. That is, if I could get it to work.

First there are no directions for how to code the .dll file after you download it and register it.

I use the Common Dialog box in the past with code like:

Dim ocxDialog As Object
Dim strImportFileName As String

Set ocxDialog = Screen.ActiveForm!ocxDialog.Object

With ocxDialog
.DialogTitle = "Please locate the Excel spreadsheet file for import"
.Filter = "All Files (*.*)|*.*|Excel files (*.xls)|*.xls|"
.FilterIndex = 1
.FileName = strImportFileName
On Error GoTo DoNothing
.CancelError = True
.ShowOpen
End With

This code works just fine. The common dialog box appears once it gets to the .ShowOpen method.

I tried to use similar code with this Open File Dialog Custom Task that I downloaded.

My code looks like this:

Dim fileD As FileDialog
Dim SourceFileName As GlobalVariable
Dim DestinationName As GlobalVariable
Dim strImportFileName As String

' Set fileD = Screen.ActiveForm!FileDialog.Object

With fileD
.DialogTitle = "Please locate the Excel spreadsheet file for import"
.Filters = "All Files (*.*)|*.*|Excel files (*.xls)|*.xls|"
.DefaultFilter = "All Files (*.*)|*.*"
.Name = strImportFileName
End With

It does not work. First after I registered the .dll file that I downloaded (DTSFileDialog.dll). No object ever appeared on my Access 2000 form in design mode. Even though I can clearly see that the Reference was set/registered and in the library.

I could not load the .dll component.

Is it me? Or is it the dowloaded .dll file may be flawed?

See if you can try it in either an Access 2000 (.adp) and VB project and get it to work -- preferrably a .adp file.

Thanks for your help so far,
Cheryl3D

 

[bstark]

Hi Cheryl3D,

If you haven't figured this out by now, I think I may have your answer here.

The .dll file is a DTS task that is registered in your DTS designer -- Not Access. Navigate to Task / Register Custom Task and this will register this FileDialog task to be available as another task.

Be sure to create a global variable by using the Global Variables tab in Package Properties. After you have done that, the Global Variable will show up in the Global Variable drop-down list in the FileDialog task.

Reference the Global variable in your ActiveX script task like so:

'Put the name into the datasource
	oConn.DataSource =  DTSGlobalVariables("sFileName").Value

When you execute the package, the File Dialog box will ask for a filename and put it into the global variable.

So far, this has worked really well. Good luck!