Introduction
Most of the times, there are two factors that give us a lot of pain when moving
an SSIS package from one server to another. The first one is the directory structure
and the second is the database connection string.
Assume that you have dozens of SSIS packages which take files from the FTP folder
and process them. The FTP location of your server is c:\inetpub\ftproot\. When
you move these packages to another server, which has FTP folder configured at d:\inetpub\ftproot\localuser,
the SSIS packages will fail. Similarly, you may be connecting to the database on
SERVER1 currently. However, when moving to the new server, you may need to connect to SERVER2\SQL2005 named instance. To fix this, you will have to modify each of
the packages on the new server.
Well, we do not move servers very often, right? I agree. Usually this is not a problem
at all. But if you have dozens of SSIS packages then it will be a tedious job
and you will have to work overnight to fix the issues when you move to a new server.
Most of the times people keep identical copy of the SSIS package on the backup servers
so that if the primary server fails, the backup server can be put online. This requires
that the backup sever has EXACTLY the same copy of the SSIS packages as the primary
server. Every time a package is updated on the primary server, the backup servers
need to be updated too.
Such problems can be handled to a good extend by using SSIS package variables. I
am presenting a simpler solution to the above problem in this article.
Handling the differences in Drive/Folder stricture
If you are some one who entered into programming during the MS-DOS era,
you must be familiar with the cute little command: SUBST. It adds an alias to a disk drive or to a specific folder. Here
is an example.
The first line tries to access drive J. Drive J does not exist on my laptop hence the system throws an error. The next statement maps C:\TEMP to
drive J. Once the mapping is done, I can access the file C:\TEMP\rdf.xml
by referring as J:\rdf.xml.
Using this approach, we can create a virtual drive mapping for each folder
that our SSIS package needs to access. Assume that the FTP folder on SERVER1 is
located at c:\inetpub\ftproot\mycorporatedomainname and the FTP folder
of SERVER 2 is located at d:\inetpub\ftproot\localuser. We could create
a virtual drive alias on each server which points to the respective folders. Here
is an example:
SERVER 1:
SUBST R: C:\Inetpub\ftproot\domainname
SERVER 2: SUBST R: D:\Inetpub\ftproot\localuser
The above command creates drive R on both the servers. Note that they are pointing
to different locations. Your package does not need to worry about the differences
in the folder structure any more. You can
refer to a fixed drive name. You can copy the SSIS package from SERVER1 to SERVER2
and can run it without the fear of breaking anything due to the differences in the
folder/drive structure.
Handling the differences in Database Server/instance name
Next, let us see how to handle the differences in database server or instance name.
The best way to handle it is by creating a server alias.
To create a server alias, open SQL
Server Configuration Manager and go to SQL Native
Client Configuration.
Right click on Aliases and select New Alias. Here you can provide
an alias name to the database server and enter the connection information.
Once this is done, your SSIS packages can refer to the name VirtualServerForMySSIS
which points to the correct database server. You can create an alias with the same
name on the second server too. Your SSIS package can then refer to the database
server by alias name. This way, we can make sure
that the same version of SSIS package will work on both the servers without any
change.
Once you have a server alias configured, you could use it to connect to the correct
server from the management studio. When you pull down a server and bring another
online, you could simply change the server alias to point to the new server. Your
team members will not shout at you for not informing them about the change, because
the change is absorbed at the server alias level.
Even the application development team need not bother about the server movements,
if they are using the server alias in their app.config or web.config.
It is a good idea to use the server alias in the report data sources too.
I guess most of you out there are not prone to frequent server changes or switching.
But there are some poor souls like us, who regularly come across cases where we
need to take an application or service offline from one server and put it online
from another server. Drive aliases and Server aliases were found to be very handy
in such cases.
Conclusions
SSIS packages which access files from a pre-defined folder structure tend
to break when the package is moved to a new server. This happens if the new server
has a different drive or folder structure. This article shows a simple solution
to that by using drive mapping. Drive mapping is done by using the DOS SUBST
command.
Some times, when we move an SSIS package to a new location, we might need to change the database connection properties. If you have dozens of SSIS
packages, updating all the connection strings will be a tedious job. This
can be avoided by creating a server alias from SQL Server Configuration manager.
