SSIS on Cluster also 2005 packages and 2008 db connections and jobs?
Hi, multipart questions. We have a SQL 2008 Cluster where we do not want to install SSIS. We have a SQL 2005 SSIS instance on another server for packages. here goes 1. Can we configure jobs on the SQL 2008 custer to point to the 2005 SSIS instance? and do we need SSIS installed on the SQL 2008 cluster to do this? 2. Can we run jobs on a SQL 2005 instance, pointing to a SQL 2005 SSIS instance/packages on a third server that connect to a SQL 2008 DB instance . I know that sounds crazy, but where exactly is the CPU and memory load in that design.. job on one server, package a seperate server and db on a third server.? 3. As mentioned, I have SQL 2005 SSIS instance. I have local admin on that server and can connect to the ssis instance from my sql 2005 sql client on my desktop. I added a new user to the server and added them to the SSIS and users group. I also made sure they could write to the folder where they will deploy packages. However, they get some kind of access denied while trying to connect, unless I give them local admin on the server. What more access do they need than to be in the users and SSIS group and have write access to an SSIS deployment folders? Many Thanks.
October 13th, 2010 6:05pm
Re #1, yes, assuming you have the package in a job on the SQL 2005 server like - http://blogs.technet.com/b/reeds/archive/2006/09/22/458074.aspx Re #2, to run the SSIS you need it installed on the target box Re #3: what is the error you are getting?Arthur My Blog
October 13th, 2010 7:02pm
1) Yes, you can. However, you do need SSIS installed on the SQL 2008 cluster to do this. 2) SSIS runs (uses CPU and memory) on the box where the job or package execution is initiated. If you are running it from SQL Agent, it uses the resources of the machine running SQL Agent. The machine with the package on it is treated as nothing more than a file server in that scenario. And yes, a 2005 package can connect to a 2008 SQL Server, assuming you have the right connectivity components installed. 3) If you search this forums for DCOM permissions for SSIS, you should find several posts with the steps to configure this without giving everyone admin privileges. John Welch | www.varigence.com | www.agilebi.com | ssisUnit.codeplex.com
October 13th, 2010 7:32pm
Thank you and sorry about delayed response. Part of the reason we do not want to install SSIS on the server is that it's clustered and we do not want to have to deploy packages to both nodes. What's the latest on SSIS on clustered environments. My hope was that we could simply point the job agent job to an SSIS instance on a stand alone server somewhere, but I do recall that SQL required SSIS to make the connect to a remote SSIS package.
October 25th, 2010 9:02pm