December 8, 2007 at 12:01 am
Comments posted to this topic are about the item Creating Location Independent SSIS packages
.
December 12, 2007 at 1:00 am
It is good idea, but usually we and also our customers have test environment on the same server and we have to deal with different database name. In this case SUBST is also not useful because it should point to another directory. It will be good to mention also another possibility how to configure SSIS package (e.g. XML file, DB Table, environment Variable, registry) or rename article to Creating Machine Independent SSIS packages.
Kind Regards,
Otakar
Otakar
December 12, 2007 at 2:30 am
Neat Trick but check out win Server 2008 - It does not have SUBST available so you might have to rethink your strategy.
The best way I have found to make SSIS Packages location independant is to use Package Configurations.
These are available in the SSIS Designer Frame under SSIS / Packagage Configurations.
I Prefer to store all Connection strings in an XML File which is then stored in the same place on all servers. Simmilarly any File Locations & Wildcards are stored in the XML File.
e.g. C:\Program Files\Company Name\XMLConfigs.xml
This way Package Deployment comes to me with 3 Config files
UAT
Prod
Alternate Prod
and all I have to do is run windiff or Visual SourceSafe to compare them before implementation.
Paul
December 12, 2007 at 2:41 am
Welle written, and neat tricks, but i agree with Paul and rather use configuration file.
The use of a server alias might come in handy though for a different scenario as you already mentioned: a migration to another server without having to telle users and apps that a new server is being used. I certainly will keep this in mind with migration scenario's.
Peter Rijs
BI Consultant, The Netherlands
December 12, 2007 at 3:24 am
I have to agree with the previous comments, I'd use package configurations before ever considering subst etc for production data loads... what happens when you have a wide range of ETL packages running, one of them hooks a wrong drive up and fails, or worse pulls in incorrect data?!
It's an alternative way of thinking but I don't think articles with recommendations such as this should be published and definitely not added into the SSC mail shot! Newby sys admins/dbas might think it's a good way to go without the potential implications and without going for package configurations.
They were added to SSIS for this very reason, with the capability of SQL Agent being able to configure jobs with package-x, configuration-y according to the environment you're running on.
December 12, 2007 at 4:03 am
I like the idea, but it seems like we end up with a server alias that points to two different servers. How is that resolved? For example if on my development server I created the alias MyVirtualServer and developed my SSIS package, and then moved my SSIS package to production I now create a server alias again called MyVirtualServer. Now I have one name that points to two servers, who is that resolved? This certainly seems like a nice solution when retiring a server and its contents get moved to a new server.
December 12, 2007 at 4:20 am
The scope of a server alias is limited to the local computer. Hence if two different computers have the same alias defined, there will be no conflict. Each computer will read its local alias name and connect to the server pointed by the alias name. If you create an alias 'abc' in X machine, you will not be able to access it from 'Y' machine.
It looks like the alias information is stored in the local registry. This post has some details. http://blogs.msdn.com/sql_protocols/archive/2007/01/07/connection-alias.aspx
.
December 12, 2007 at 5:59 am
What's wrong with simply using a set of package variables and reading them in at init time from an XML file?
December 12, 2007 at 6:29 am
There is nothing wrong with package variables and xml configuration files. I think this article sent a wrong message to many of the readers. I agree that a better way of doing is by using configuration files. However, this was kind of a quick trick that helps in some of the cases.
The following incident prompted me to write this short article. A friend of mine called me up recently and said that he has a problem with one of the servers and moved everything to a new server. He had dozens of SSIS packages on the old server. These packages were directly developed on the server. (usually people develop in the development server and deploy on server. at that time they make use of the configuration file and the package variables). The packages were depending on various 'machine-specific-settings' like drive structure and database server instance names.
What he wanted was a way to get out of the problem. He wanted to run the packages on the new server. The quick fix that we found after some discussion was what is mentioned in this article. I strongly reccommend using package configuration and package variables. But there will be times when we need quick fixes to get things moving. The intention of writing this was to show a 'dirty-quick-fix' for anyone who is facing a similar problem mentioned above.
.
December 12, 2007 at 6:41 am
jacob sebastian (12/12/2007)
The scope of a server alias is limited to the local computer. Hence if two different computers have the same alias defined, there will be no conflict. Each computer will read its local alias name and connect to the server pointed by the alias name. If you create an alias 'abc' in X machine, you will not be able to access it from 'Y' machine.It looks like the alias information is stored in the local registry. This post has some details. http://blogs.msdn.com/sql_protocols/archive/2007/01/07/connection-alias.aspx
Thanks for the clarification Jacob. The comments in the referenced blog also point out that you could create an alias on each client PC as well if you wanted. Not that I would recommend it, but its nice to know the capability is there if I need a quick and dirty solution.
December 12, 2007 at 7:05 am
Jacob,
The proper technique is to use package configurations by either using an XML configuration file, or SQL Server based configurations. If using an XML configuration file, you can use Environment variables to specify the location of the XML config file, or heck, use an environment variable to specify an SSIS variable directly.
With that said, your SUBST technique has severe limitations, namely the fact that the new drive letter is VIRTUAL and like mapped network drives are a user session concept. Windows service accounts, much like the one that runs SQL Server Agent, has no knowledge of SUBST drives and as such will not be able to pick up files from those locations.
So, the moral? If you're always running packages through the GUI - SUBST/mapped drives will work. If you're running jobs through a service, like SQL Server Agent, they will not work as those drives don't technically exist -- they aren't physical drives.
There are many articles on this site and others talking about package configurations. Using the SUBST command may work in a pinch for some folks, but for better long-term portability and pre-setup, package configurations are the appropriate solution.
--
Phil Brammer
SQL Server MVP
December 12, 2007 at 11:52 am
I do think that the config files are the best, but a lot of people prefer DNS aliases for the server names. This way you have to make a change in one place when replacing the server with a new one.
As for the SUBST, it is really a cool stuff. If it does not work in Windows 2008 then you can implement the similar approach with the local DFS (Distributed File System) - does the same, maps a drive letter to a folder.
Regards,Yelena Varsha
December 12, 2007 at 11:57 am
Yelena Varshal (12/12/2007)
I do think that the config files are the best, but a lot of people prefer DNS aliases for the server names. This way you have to make a change in one place when replacing the server with a new one.As for the SUBST, it is really a cool stuff. If it does not work in Windows 2008 then you can implement the similar approach with the local DFS (Distributed File System) - does the same, maps a drive letter to a folder.
Right, DNS/SQL Server aliases all work for server name anonymizing, but for file locations you're pretty limited when using service accounts. If you're using network file paths, using UNC is the preferred way to reference that file system location because mapped drives don't work/exist under service accounts.
December 12, 2007 at 1:06 pm
Not sure if this has already been said by someone, but the method we use here for portability of file system references is a DNS alias for the server and a share name for the target directory (or possibly a share for a directory somewhere in the tree above the target if the structure is guaranteed to be the same from the share downwards), then reference the directory as \\Myhost\MyShare. Works fine and of course in testing you can set up a host file entry to mimic the DNS alias (just remember to delete it when you've finished with it!) I use SQL aliases pretty much as described, possibly with a configuration override for the database name if that varies between environments.
Then, as long as the installers follow the instructions and set up the required SQL and DNS aliases, the packages move between environments without hassle.
Note, there are a couple of gotchas around resolution of multiple names resolving to the same server which mean you may need to alter a registry key. See http://kbalertz.com/926642/Error-message-access-server-locally-using-CNAME-alias-after-install-Windows-Server-Service-network-provider-accepted.aspx
December 12, 2007 at 1:47 pm
I am disappointed that an article that discusses how to manage database connections and file locations in SSIS, without even mentioning package configurations, can score an "average" rating on this site.
It takes time to set up configurations, but it's a far more powerful and flexible way to do it.
Aliasing is very useful but for another purpose. Perhaps it's good enough to suit some people in place of configurations. But to not even mention it? I'd hoped for better from this author and this site.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply