Using Global Variables to Open a Database and Form

Question

question

Joeypotter asked Feb 12, '10

Using Global Variables to Open a Database and Form

Hello, I have created a job which I pass two variables through as follows:

DTSRUN
/Sqh-sql2
/N"UnisoftVB"
/AgvAccessDatabase :8="Y:\InformationCopy.mdb"
/AgvAccessForm :8= "frmImportDataBackup"
/E

The "UnisoftVB" DTS contains the following ActiveX script:

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

Dim varAccessDatabase
Dim varAccessForm


Function PressButton()

varAccessDatabase = DTSGlobalVariables ("gvAccessDatabase").Value
varAccessForm = DTSGlobalVariables ("gvAccessForm").Value


 Dim objDB
 Set objDB = CreateObject("Access.Application")
 objDB.OpenCurrentDatabase(varAccessDatabase)
objDB.OpenForm(varAccessForm)
 objDB.CloseCurrentDatabase
 objDB.Quit
 PressButton = DTSTaskExecResult_Success

End Function

The form in the access database when opened runs a lot of VB (which I didn't write) and ultimately updates a table with up to the most recent data.

When I run the job it is successful but it happens far too quickly which made me suspicious that it wasn't working. The table doesn't contain the most up to date data.

I have checked my DTS by chaning the global variables and declaring them there instead and it does work.

I wondered if anyone could tell me why my Job doesn't seem to run my DTS correctly????

Please help Jo

sql-server-2000 job dts variable vb

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-02-12T13:01:47Z

Fatherjack answered Feb 12, '10

Its a stab in the dark but you say the code runs when you open a form but the script above is called PressButton I know its only its name and it doesnt necessarily have to be associated with a button press but ...

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2010-02-15T15:53:22Z

Fatherjack answered Feb 15, '10

OK, in a new SSIS package add a script task to the Control Flow tab. Set the language to Visual Basic (it may be Visual C# by default). Edit the task. Click the Edit script button. In there you will find a section of code that is Sub Main. Paste this after the comments but before the DTS.TaskResult .. line

Dim myVar As String 
        myVar = "My Varaible has a value"
        MessageBox.Show("Hello World" & vbCrLf & myVar)

Click File|Exit and then OK the dialogs until you get to the Control Flow editor. Press F5. You should get a message box come up with a message.

Now you need to get your Globalvariable value into a similar message box and you can check how your script is working.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question