February 20, 2013 at 5:07 pm
Hi!
I have a remote SQL 2012 server on which I am not allowed to create linked servers, run SSIS, or SSRS. We'll call that "DB Server" I have an applications serer on which I have installed and am running Visual Studio 2008 R2 and have created several SSIS packages to load and report data to that SQL server. We'll call that "App Server."
From within the SSIS designer on the App Server, I am able to execute these packages perfectly. The data is uploaded to the Db Server,
I need to create SQL Server jobs on the DB Server that will run these packages on the App Server.
I am not allowed to run any applications on the DB Server. I am, however, allowed to create SQL Server Agent jobs.
The person managing the App Server will have all necessary permissions to the DB Server to run Agent jobs, though my guess is that the jobs will run under a service account of some kind.
I'm uncertain how to execute jobs on a remote server when you don't have SQL installed on the App server, aside from what little bit of SQL is installed along with Visual Studio 2008 R2.
Any guidance would be helpful. The whole goal here is the separate the Application server from the DB Server. It's just that this requirement seems to make executing jobs on the DB server a little problematic, especially when what will be executed are SSIS projects located on the App Server,
So, the SSIS packages will need to reside on the App server but be executed from a scheduled job on the DB server. I want to use the SQL Server Agent to schedule these packages in large part because of the logging and the email notifications. I'll also be creating a job that will email out a set of reports to email addresses stored on the DB once the data is loaded.
In general, this project has two parts. The first is processing data into the database. I need to use scheduled SQL Server Agent jobs that will execute SSIS packages on the App Server to do that. By the way, they won't let me create linked servers. All they'll let me have on the DB Server is the database and SQL Agent jobs. I can have some stored procedures and views, but I cannot execute any command shell commands.
The second part is reporting and emailing out what will probably be Crystal Reports or perhaps SSRS reports. From what I can tell, they won't let me even install SSRS on the DB Server, so I'm not sure what to do about that. Can I create Crystal reports in Visual Studio 2008 R2 and have them executed and emailed out from a job on the DB Server? The app server cannot be licensed for SQL Server, but it can be licensed for Visual Studio 2008 R2.
The point is that this project will have no SQL or Application support from this company's IT department. They will have no one available to manage jobs, change or reschedule them, for example, so pretty much all of that needs to be integrated into the App Server. They will be in charge of the DB Server in terms of security and backups, but that's it. The person managing the App Server is NOT a DB Admin but knows some SQL, so I'm having to make things as easy as possible.
From the App Server, how can I execute SQL Server Agent jobs that reside on a remote DB Server, which execute Packages that reside on the App Server? That's my basic question. But the larger question is how you totally separate SQL Database and SSIS on different servers when you have SQL 2012 on one server and only Visual Studio 2008 R2 on the other?
February 20, 2013 at 5:20 pm
If you can create SQL Agent Jobs on the DB server, there is no logical reason why they should not let you load your SSIS package onto the DB server as it is after all just a big set of scripts at it's heart.
The DB server has to run the package, wherever it is stored and if you are forced to store it on the App server, then they will need to provide you with an account on the DB server that can access the app server over the network to read the package file.
If however, you explain to them that uploading the package into SSIS on the DB server is the alternative that means they do not need to provide and support the network account, they should say yes.
Either way, the job runs on the DB server, but the second way doesn't require network access privileges (unless the SSIS package requires them of course).
From the App Server, how can I execute SQL Server Agent jobs that reside on a remote DB Server
connect to SQL and EXEC sp_start_job passing it the job name.
which execute Packages that reside on the App Server?
Don't. Load them into SSIS on the DB server - they are just scripts, not applications...
But the larger question is how you totally separate SQL Database and SSIS on different servers when you have SQL 2012 on one server and only Visual Studio 2008 R2 on the other?
You don't. SSIS needs to be installed - you can't install it on your App server by your own comments....
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);February 20, 2013 at 8:43 pm
Hi! Thank you so much for responding!
I may not have been clear about a couple of things.
The company I'm doing this work for will not allow SSIS to be installed on the DB Server. They will not allow DTSX packages to be stored either within SQL or anywhere on the file system of the DB Server. As I read their requirements document further, they won't even allow SQL Server Agent jobs to run on their database servers. It looks as though the only way they allow data to be loaded is by processes developed through their ETL group.
The basic upshot is that I need to be able to install SQL Server on this little App Server in order to run jobs, data load processes, so some reporting, and allow this all to be maintained by the person running that server. At least that's what I'm thinking at this point. They're making it nearly impossible to deal with their different IT departments. I get it that this is a HUGE company, and the work I'm doing is just one small data and reporting project that their IT group doesn't want to do. Still, I wish they would have a special group specially designated to manage these small projects. All their IT group seems to care about are the enterprise applications, which is how this small project wound up being contracted out to me in the first place.
I'll come back with how we finally worked this out as soon as I have an answer. Thanks again!
Karen
February 21, 2013 at 2:20 am
Good luck. It sounds hellish!
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);February 21, 2013 at 3:04 am
Karen Grube,
a) You can configure SQL agent to run packages from file system. So if you can place the package in a fileshare and if the sql server agent has access to these files, you can succesfully configure the job.
b) Another aproach you can use is create an application (.net) to call these packages . schedule this with windows scheduler.
http://stackoverflow.com/questions/273751/how-to-execute-an-ssis-package-from-net
I havent tried the second option (I had been a DBA with all access most of my career), but I am confident it should work
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply