February 10, 2009 at 7:12 pm
Hi, I'm posting this under SSIS because it is relevant to running an SSIS Package.
Here's the quick rundown.
I need to basically run an SSIS Package which tried to copy a file, from our helpdesk app, which is written in MS Access.
The easiest thing to do from an Access standpoint is to use ADO to send the SQL Server an EXEC command to run an stored procedure.
From this point, the Stored Procedure can either launch the SSIS Package or can call a job which will launch the SSIS package.
In one way I like the idea of calling a job, because this will give me a history of when the job is actually run, which will give me access to some stats, although I can find another way to do this from the SP if needed, so either will still work.
Can anyone point me in a direction to show me how to launch either of these items from an SP? Can I just use a command line call from the SP to launch the package?
Any help is appreciated.
February 11, 2009 at 1:16 am
To Execute a job from a stored proc:
--Start Job
EXEC msdb.dbo.sp_start_job N'Weekly Accounts Payable Transaction History'
--Stop Job
EXEC msdb.dbo.sp_stop_job N'NightlyInventoryFeed'
--Info abt Job
EXEC msdb.dbo.sp_help_job
@job_name = N'FinanceBackup',
@job_aspect = N'ALL' ;
GO
February 11, 2009 at 4:05 pm
Thanks a bunch!
March 10, 2009 at 4:06 pm
how do i call an ssis package which is stored in the msdb database from a stored procedure... note: i cannot use a sql job in here ...
March 10, 2009 at 4:33 pm
That's my exact problem at the moment too.
I'm calling one from a job, but I hadn't first considered that the SP will be called in a Windows Authentication Mode only environment by users who do not have SQL Agent rights... therefore the SP executes, but the job calling the SSIS Package fails.
So I'm in the same spot. It would be handy to find another way to call it which would not require agent access, as I'm clearly not opening that up to all my users!
August 12, 2009 at 12:04 pm
Did you get an answer to this? I'm having a similar issue. I need to call an SSIS package from Microsoft Access 2007.
August 12, 2009 at 2:48 pm
Have you tried using xp_cmdshell to run the dtexec utility?
Your SQL Server will have to be configured by an admin to use xp_cmdshell. If that's a possibility, you could execute a sql statement similar to this:
EXEC xp_cmdshell 'dtexec /f "c:\PathToYourFile.dtsx"'
August 12, 2009 at 6:03 pm
Hi ssexton.
I think I probably have some bad news for you about launching an SSIS Package from Access.
I'm using Access2003, so it may be different, but from my experience, security gets TIGHTER on each iteration of Access, so the same probably holds true for Access2007... especially since it's more of a Windows network issue anyway.
The problem I found was that we are using Windows Authentication on our SQL Servers. For security reasons, the managers won't let us move it to a mixed mode situation, and this presents problems when trying to launch SSIS from Access.
Basically, SSIS packages can only be run by certain roles, which are admin type roles. If you are using Windows Authentication, then it doesn't matter what ODBC string you pass to the SQL Server from Access, it will ALWAYS default to trying to execute the command with the credentials of the logged in user from the calling computer. Period.
My problem there is that the majority of our users (aside from myself and the other IT people who have admin access) don't have Windows Level permissions to run SSIS Packages, and this includes launching them from within an SP. If you try to launch from an SP and the Windows Authenticated User doesn't have admin rights, then when the SP tries to launch the SSIS Package, it will automatically grab the Windows User's default permissions, and will fail.
In my case, since I was not allowed to put the SQL Server in Mixed Mode and create a login for this purpose, I was forced to approach the whole situation from a different angle that didn't involve trying to launch a package from within access, but from a job overnight from the SQL Server.
If your SQL Server is in mixed mode, or you are able to put your SQL Server in mixed mode, then I would suggest creating a proxy user who has permissions to launch SSIS Packages, and then open an ODBC connection with that user's credentials from within Access, and send the EXEC SQL command to launch the SSIS package through that connection, as the previous poster has shown.
If you need help sorting out your syntax in access to open the ODBC connection and making the call, let me know and I'll send you a sample.
Also, when sending the execute command, if your SQL Server is 64-bit, be sure to use the 32-bit .exe to launch the SSIS package from the command line, as you will have less dramas. The 64-bit .exe is very buggy and on the majority of occasions will throw up weird errors, rather than doing as it's told, and this situation is resolved by using the 32-bit exe.
Good luck! I hope you have permission to put your SQL Server in Mixed Mode. It will save you many dramas!
If you find a way to use Windows Authentication Mode and somehow find a way to get permissions to launch SSIS packages from within Access, do let me know. I worked on it for two weeks, and finally gave up after reading that the Windows default will ALWAYS be used in EVERY situation. I tried even switching users when I was launching the package from an SP, and it always defaulted back to Windows permissions. Argh!
August 14, 2009 at 8:18 am
Thank you soooo much...this is without a doubt the most detailed response I've received.
You are correct it doest sound like good news, but it helps to know what my options are.
I'm spending a good deal of time working on an ACCESS user interface for the end user (with basically read only rights to the database) to write the necessary parameters to a table. This part I've managed to do.
Then I planned to script a command button to execute the package which would read the parameters it needs from the table.
Just to confirm are you saying that I won't be able to script the execute command without the end user having permissions to run SSIS packages (File System and Server Storage)?
Thanks,
Sabrina
August 16, 2009 at 5:45 pm
I'm not sure.
I know that you won't be able to execute an SSIS package if you are in Windows Authentication Mode unless your users have pretty high access rights... like administrator... on the SQL Server.
However, I wouldn't go about it that way unless you have to. From the sound of it, couldn't you simply call a stored procedure with the parameters that need updating, and have the SP execute the update or insert to the table?
The only necessary uses I've found for SSIS so far are: 1) Import/Export of data to/from our SQL Server. 2) situations where you need to generate a file in which the filename changes per time increment. I find it's useful for things outside the scope of normal database insertion/update, but shouldn't be necessary for simple database tasks.
To call a stored procedure from within access, you will need to do something similar to the following:
Dim dbs As New ADODB.Connection
Dim rst As ADODB.Recordset
Dim strSQL As String
strSQL = "EXEC spName '" & me.Param1Text & "', " & Me.Param2Int
Set dbs = CreateObject("ADODB.Connection")
dbs.Open (strConnection)
Set rst = dbs.Execute(strSQL)
NOTE: If you want to return a variable from the SP to see if the values, for example, have processed, you can surround them with a try/catch in the SP and then return a boolean (bit) to indicate this. Also, you may want to check that some records have been returned. If you don't need to check return values, no need to set the execute statement to rst, the line above would simply look like this: dbs.execute(strSQL) which would execute the sp without returning any value.
The strConnection in the above will be something similar to as follows. Your easiest way to derive the right connection here is to make a SQL Pass-through query in access, setting a connection to the appropriate database, and find out what it is, and then use this connection to do the job. You could hard code, or what we do is to derive the appropriate connection on our menu form in access and let it reside there hidden once a user logs in. Then, we just call the value of that field to strConnection when we need it.
ODBC;DSN=db1;Description=db1 Database;UID=user;DATABASE=db1;Trusted_Connection=Yes
You should be able to pass anything you want to an SP and perform an insert/update using the new values you pass. If the users don't have access to SPs by default, it is possible to put them in a group that will have that access.
I hope that helps. Perhaps I've misunderstood and you need to use SSIS for what you're doing, but if not, keep it simple and just write some stored procedures to do the work.
August 17, 2009 at 4:12 am
ssexton,
It would really help if you could explain what exactly are you planning to achieve through the script.
What do you want your SSIS package to do?
Thanks
Pankaj
August 17, 2009 at 8:32 am
Sharon,
You're rigt, if I had my 'druthers' I wouldn't go about it this way either. Unfortunately, I'm severely limited by my "development rights" and tools.
The end user "needs" an interface and the only tool I have the ability to create one in is ACCESS 2007. We really need to turn this functionality over to the end user rather than me (or somone on my team) being tasked to run this at their discretion.
The SSIS package works beautifully. It does exactly what it's intended to do, and without going into detail (to prevent sympathies 🙂 ) it allows us to do what needs to be done, again with our limited development rights (ie. we don't even have linked servers). The SSIS package is not the problem.
Kicking off the SSIS package from command line is the problem. It's not enough for me to be able to kick off the package, the end user has to be able to kick it off. And they have less rights than I do.
Since the end user only has "read only access" to the tables that need to be updated/written to, the ACCESS 2007 front end (form) has built into it the user name and password of a user with my permissions. Unfortunately, I don't even have rights to exec xp_cmdshell to be able to do:
EXEC xp_cmdshell 'dtexec /f "g:\filepath.dtsx'"
If you or anyone reading this post has ideas on how I might be able to put the "go" behind my "go" button, where "go" simply executes a package, it would be greatly appreciated.
Thanks,
Sabrina
August 17, 2009 at 5:40 pm
I feel your pain. I had a similar situation, but had to dump the idea and head off in another direction, since I wasn't allowed to put our server into Mixed Mode.
Good luck with that. I don't think you'll probably find a way to do it under Windows Authentication Mode only.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply