Calling SSIS Package from MsAccess form Button Event

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Integration Services » Calling SSIS Package from MsAccess form...

Calling SSIS Package from MsAccess form Button Event

Rate Topic

Display Mode

Topic Options

Author

Message

rangu

Posted Wednesday, July 25, 2012 4:17 PM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, October 17, 2012 2:33 PM
Points: 19, Visits: 89

Hi,
I have a SSIS package which imports data from a source excel to the Sql Server database.
I want this package to be triggered from Ms Access form button event.
For this I have created a job and calling this job from a store proc, which has to be executed from button event.
Am using following in my SP

EXEC msdb.dbo.sp_start_job N'SSIS_FFIEC101';

Not sure if this is correct!

My requirement is:
TO create a SP which executes the job and this SP has to be called from MS Access.
I here also want to know whether the job has been executed completely.

Any other suggestions, pls let me know.
Any ideas pls help.

Thanks,
Srini

Post #1335478

Lynn Pettis

Posted Wednesday, July 25, 2012 4:28 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 3:18 AM
Points: 21,588, Visits: 27,375

rangu (7/25/2012)

This, EXEC msdb.dbo.sp_start_job N'SSIS_FFIEC101';, is asyncronous. It starts the job and returns. If you need to know if the job completes successfully, you are going to have to monitor the processing.

Based on past expereince, the EXEC command does look correct.

Cool

Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1335482

rangu

Posted Wednesday, July 25, 2012 5:07 PM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, October 17, 2012 2:33 PM
Points: 19, Visits: 89

HI Thanks for your response, Can you please suggest how to track whether the job is ran completely or not,
Do I need to handle this in the proc, please suggest.

Post #1335499

rangu

Posted Thursday, July 26, 2012 11:00 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, October 17, 2012 2:33 PM
Points: 19, Visits: 89

Hi Lynn,

So here is the good news.
I could create a Sp which executes the Job and also wait untill the job is run success. I have executes this proc in Sql Server DB and it is working awesome.

I am now trying to execute this SP from Ms access VBA code connection thru ADO.

When I excute this SP, its showing an error Query Timeout.
I havent seen this before.
In real my Job takes 3-4 mins to run.

Any ideas how to over come this problem.

I have also tried change the setting in Access Options/Advanced ==> OLE\DDE time out to 240Secs from default 60 sec.

Am stuck at this last step, please get through me with this.

Thanks.
Srini

Post #1335993

harsh.warikoo

Posted Friday, July 27, 2012 5:01 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, March 05, 2013 7:02 AM
Points: 2, Visits: 29

Hi,

You mentioned that you are using ADO in VBA to pass the command to execute the SP on the Server.
the time out issue is due to the command timing out on the server (This is a setting that has to be configured).
to resolve this, when you are creating your command object to pass to the server, mention the CommandTimeout value too over there.

For Example(Example Code that works) in the VBA module of Access (This is only in case of Access ADP file) :

Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Dim RstOut As ADODB.Recordset If in case your SP returns any records then creat a recordsect object to hold them

Set RstOut = New ADODB.Recordset

Set cmd = New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = [Name of your procedure]
cmd.CommandType = adCmdStoredProc
cmd.CommandTimeout = 0 <- mention your time out value/setting here, 0 means no time-out

Incase if you are passing Input parameters to your SP
Set prm = New ADODB.Parameter
prm.Type = adChar Set Parameter type
prm.Size = Len([parameter]) size of your parameter(Length of it in case of String)
prm.Direction = adParamInput
prm.Value = [parameter] value of the parameter
cmd.Parameters.Append prm append it to the command object to be passed to the server

Finally call/execute the Stored Procedure present on the server, assign the returned records if any, to the recordset object

Set RstOut = cmd.Execute()

After you are done, also remember to
Set RstOut = Nothing
Set cmd = Nothing
Set prm = Nothing

Post #1336387

harsh.warikoo

Posted Friday, July 27, 2012 5:03 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, March 05, 2013 7:02 AM
Points: 2, Visits: 29

Hope this helps, you can also refer the following links for further reference :

http://www.functionx.com/vbaccess/

http://icodeguru.com/database/Programming.Microsoft.Office.Access.2003/8368final/LiB0091.html

Post #1336392

rangu

Posted Friday, July 27, 2012 7:49 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, October 17, 2012 2:33 PM
Points: 19, Visits: 89

Hi Harsh,
Thanks for your note..
Untill yest i was trying to give connectiontimeout not commandtimout.
I have set the cmd.Commandtimout to 300 and its working gr88...

All,
Thanks for your help. I could at last call SSIS Job from MsAccess VBA.

Post #1336514

« Prev Topic | Next Topic »

Permissions