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 Ms Access form button event Expand / Collapse
Author
Message
Posted Wednesday, July 25, 2012 3:21 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 09, 2013 11:41 AM
Points: 19, Visits: 92
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 #1335447
Posted Wednesday, July 25, 2012 4:55 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, March 23, 2014 9:42 PM
Points: 48, Visits: 9,454
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
Post #1335490
Posted Wednesday, July 25, 2012 5:01 PM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 3:22 PM
Points: 22,510, Visits: 30,232
Duplicate post. Please post all further responses here

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



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 #1335494
Posted Wednesday, July 25, 2012 5:15 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 09, 2013 11:41 AM
Points: 19, Visits: 92
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
Post #1335503
Posted Thursday, July 26, 2012 10:08 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 09, 2013 11:41 AM
Points: 19, Visits: 92
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.
Post #1335959
Posted Friday, July 27, 2012 9:00 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, March 23, 2014 9:42 PM
Points: 48, Visits: 9,454
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
Post #1336571
Posted Friday, July 27, 2012 9:23 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 09, 2013 11:41 AM
Points: 19, Visits: 92
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.
Post #1336584
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse