Can an SSIS package run on one SQL Server instance but process data that is located on another SQL Server instance? I'd like to know this before I attempt to install SSIS and require a possible reboot on another server.
I want to do because Microsoft recommends not to run SSIS in a Clustered environment. To avoid issues, I installed another instance of SQL Server 2005 with SSIS to run packages provided for our accounting system.
Yes, it is very much possible. You can define connections to a myriad of external datasources from within an SSIS package. SQL Server, Oracle, Excel, flat files, etc etc.
Just remembered one gotcha when dealing connecting to Excel via SSIS in a 64-bit environment. You need the 32-bit client tools installed on your 64-bit SQL box in order to interface with Excel. This has been somewhat painful for us...
Here's a link to an MSDN article that describes how to add connection managers to your SSIS package and also provides links to various types of supported connection managers.
Seeing as SSIS is an Extract, Transform and Load tool, you would hope it could access data on another server!
Check out the SSIS book by Kirk Hasselden. There is a great walkthrough in it. You could probably find some other tutorials online.
Once you get the basics, check out this package, it's totally awesome:
http://www.sqlmag.com/Articles/ArticleID/95385/pg/3/3.html
Where does ms say to not run SSIS on a clustered server? It says not to cluster the service, but I hadn't seen the recommendation against it.
Phill, I suppose that you are wrong about that MS doesn't recommend to run SSIS in clustered environment. In Fact, they don't recommend it to be configured as a cluster resource, but it is OK to run several separate instances of SSIS on several cluster nodes
http://msdn.microsoft.com/en-us/library/ms345193.aspx
The Integration Services service is not a clustered or cluster-aware service, and does not support failover from one cluster node to another. Therefore, in a clustered environment, Integration Services should be installed and started as a stand-alone service on each node in the cluster.
...
However, if high availability is your goal in establishing a clustered hardware environment, you can achieve this goal without configuring the Integration Services service as a cluster resource. To manage your packages on any node in the cluster from any other node in the cluster, modify the configuration file for the Integration Services service on each node in the cluster. You modify each of these configuration files to point to all available instances of SQL Server on which packages are stored. This solution provides the high availability that most customers need, without the potential problems encountered when the Integration Services service is configured as a cluster resource.
So for your question, the answer is Yes, of course you can do this, but you also have a better choice to install stand-alone services on your cluster node and you will not need an additional box (and additional administrative expenses for backups, monitoring, updating, if you already established all these procedures on you cluster)
IMHO its overkill to install another version of SQL Server if all your going to do is use it to store SSIS packages.
You can 'connect' to a SQL Server environment in the sense that packages can run that utilize other databases as part of their process.
You can connect your SSIS instance to any SQL Server instance to use MSDB as the package repository by modifying the MsDtsSrvr.ini.xml file. By default this is configured to point to the local instance.
You can install SSIS in clustered environment. MS recommends not running it as a clustered resource. The trick with running SSIS on a clustered db is you need to point <ServerName>.</ServerName> in the above mentioned config file to to your virtual SQL Server name.
