July 25, 2012 at 4:17 pm
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
July 25, 2012 at 4:28 pm
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.
July 25, 2012 at 5:07 pm
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.
July 26, 2012 at 11:00 am
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
July 27, 2012 at 5:01 am
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
July 27, 2012 at 5:03 am
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
July 27, 2012 at 7:50 am
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.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy