Calling SSIS Package from MsAccess form Button Event

Question

Calling SSIS Package from MsAccess form Button Event

rangu

Default port

Points: 1407
More actions
July 25, 2012 at 4:17 pm

#278488

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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

rangu (7/25/2012)
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

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.

rangu Default port Points: 1407 More actions · Answer 2

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.

rangu Default port Points: 1407 More actions · Answer 3

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

harsh.warikoo SSC Rookie Points: 36 More actions · Answer 4

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

harsh.warikoo SSC Rookie Points: 36 More actions · Answer 5

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

rangu Default port Points: 1407 More actions · Answer 6

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.