SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Calling SSIS Package from MsAccess form Button Event


Calling SSIS Package from MsAccess form Button Event

Author
Message
rangu
rangu
SSC Journeyman
SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)

Group: General Forum Members
Points: 99 Visits: 208
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
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39305 Visits: 38529
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.

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)
rangu
rangu
SSC Journeyman
SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)

Group: General Forum Members
Points: 99 Visits: 208
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
rangu
SSC Journeyman
SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)

Group: General Forum Members
Points: 99 Visits: 208
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
harsh.warikoo
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 31
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
harsh.warikoo
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 31
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
rangu
SSC Journeyman
SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)

Group: General Forum Members
Points: 99 Visits: 208
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search