x

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

more ▼

asked Feb 12, 2010 at 12:30 PM in Default

avatar image

Joeypotter
3 1 1 2

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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 ...

more ▼

answered Feb 12, 2010 at 01:01 PM

avatar image

Fatherjack ♦♦
43.7k 79 98 117

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Feb 15, 2010 at 03:53 PM

avatar image

Fatherjack ♦♦
43.7k 79 98 117

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x502
x100
x71
x29
x23

asked: Feb 12, 2010 at 12:30 PM

Seen: 1969 times

Last Updated: Feb 12, 2010 at 12:47 PM

Copyright 2016 Redgate Software. Privacy Policy