Calling SSIS package from Ms Access form button event

Question

Calling SSIS package from Ms Access form button event

rangu

Default port

Points: 1407
More actions
July 25, 2012 at 3:21 pm

#74325

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

ProofOfLife Ten Centuries Points: 1332 More actions · Answer 1

Hi Srini

Your exec command looks correct and I assume you have tested it to make sure it works. To execute it from within Access I think you have the right idea to put the job execution inside a stored procedure.

Calling a stored procedure from Access ranges in difficulty from easy to a little challenging, dependent upon your VBA skills. I think in your case you have a simple job in that you don't appear to want to be able to pass stored procedure parameter values from the form. If I am wrong in this assumption just reply and I can guide you though using QueryDefs and values from controls on your form.

But based in the assumption we have the simplest scenario, you firstly need to set up an Access pass-through query. Access Help gives details on how to do this and there will be plenty of info available via google if you have any difficulty. Basically, you create a new query, don't add tables, and open SQL view in the query editor. There will be an option for setting the query as a pass-through type - where this option is to be found, varies according to the version of Access you are using. You will have to add a connection to the database - having an ODBC connection already set up helps if you have to connect that way. Save the password in the connection (an option you will be prompted for) and set the returns records property to false.

Getting the connection working is possibly the most challenging part and using an established ODBC connection will help to simplify it if you run into difficulties. Getting an ODBC connection working is one way of learning the connection requirements of your SQL Server db. NB. Make sure that the user profile under which the Access db is to be used has appropriate permissions on the database to which your stored procedure has been added.

Save the query and test it by double-clicking. Having satisfied yourself that the job runs, you can move on to the next step which is to configure your button. You will need to create an "OnClick" event and use a simple DoCmd.OpenQuery command eg DoCmd.OpenQuery "qry_ExecSQLJob".

The above is all fairly brief and over-simplified but gives you the outline of the steps required. Just post back if you need any help with any of the details.

Cheers

Rowan

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

Duplicate post. Please post all further responses here

Please, do not cross post as it just fragments any responses you get from members.

rangu Default port Points: 1407 More actions · Answer 3

Hi Rowan

Thanks for your response,

I have no issues in connecting the SQL Server Db from VBA, I have done that part by connecting through a DSN setupn on my machine.

I could execute few other SQl Procs from VBA.

My challenge here is to run the SSIS package throguh a SP.

I need complete syntax for the SP to call the job already created. When I run the command above its just returning 0, means success..

he actual time for the SSIS package to run is 3.00 Mins

I need a Proc which tells whether the SSIS Job has been run completely and successfully.

Please suggest. If the full SP is created, ican call this from Access VBA with ease.

Thanks

Srini

rangu Default port Points: 1407 More actions · Answer 4

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.

ProofOfLife Ten Centuries Points: 1332 More actions · Answer 5

Good news that you have a suitable stored procedure working.

I think I have run across the timeout issue myself and I think all I have done is to increase the timeout to 600.

A pass-through query is named this because that is what it does. It passes the SQL statement through to the database server which executes the statement. Nothing is being done by Access other than to wait for a response. This is the great value of this type of query when dealing with datasets as all the work is done on the db server with only the results being passed back to Access. This is contrary to an Access query which pulls all the data from all the tables in the query back to the local machine before it does any processing of that data - one of the reasons IT dislikes Access apps.

So don't be afraid to experiment with the timeout option. There have been occassions when I have set it as large as 1200. It is also possible that it takes longer for the sp to process when initiated from Access as opposed to running from Management Studio so you need to allow for that. The main goal however, is to get it working. We can explore ways in fine tuning performance as another issue.

Cheers

rangu Default port Points: 1407 More actions · Answer 6

Hi,

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.