Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I'm a complete newbie and I'm just learning how to administrate SQL Server Integration Services (SSIS). I'm thinking of running SQL Server 2005 with SSIS install. Can an SSIS package run on a seperate SQL server but process data thats located on another sql server? I'd like to know this before I attempt to install SSIS and require a possible reboot on another server.

Why would i want to do this? Because Microsoft recommends not to run SSIS on a Clustered environment. To avoid issues, I installed another instance of SQL Server 2005 with SSIS to run packages provided for our accounting system.

thanks in advance

share|improve this question

4 Answers 4

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.

share|improve this answer
    
awesome! since i am new at this, could you please suggest a link to an example or tutorial on how to do this? –  phill Jun 17 '09 at 15:22
    
No problem, added a link to the answer. –  squillman Jun 18 '09 at 14:32

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.

share|improve this answer

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)

share|improve this answer
    
Yea I realized this error earlier on so I just installed it on a single node of the cluster. Now i'm having issues with which server name/virtual name and which login to use on different apps. –  phill Jul 27 '09 at 22:10

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.

share|improve this answer
    
could you please suggest any sites or tutorials on how to do this? –  phill Jun 18 '09 at 19:02
    
Did you read the MSDN article I linked to? –  Nick Kavadias Jun 19 '09 at 0:05

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.