Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Calling SSIS Package from MsAccess form Button Event Expand / Collapse
Author
Message
Posted Wednesday, July 25, 2012 4:17 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 5, 2014 8:21 AM
Points: 41, Visits: 175
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
Posted Wednesday, July 25, 2012 4:28 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 6:47 PM
Points: 23,396, Visits: 32,229
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.



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
Posted Wednesday, July 25, 2012 5:07 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 5, 2014 8:21 AM
Points: 41, Visits: 175
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
Posted Thursday, July 26, 2012 11:00 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 5, 2014 8:21 AM
Points: 41, Visits: 175
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
Posted Friday, July 27, 2012 5:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 4, 2014 5:31 AM
Points: 2, Visits: 30
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
Posted Friday, July 27, 2012 5:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 4, 2014 5:31 AM
Points: 2, Visits: 30
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
Posted Friday, July 27, 2012 7:49 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 5, 2014 8:21 AM
Points: 41, Visits: 175
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 »

Add to briefcase

Permissions Expand / Collapse