Yet another way to include portability on DTS packages

  • alfreitas

    SSC Enthusiast

    Points: 109

    Comments posted to this topic are about the item Yet another way to include portability on DTS packages

  • Anipaul

    SSC-Insane

    Points: 24681

    The article is cool. But I am not clear with one thing.

    Try to break the INI in two new files

    Can you explain me this little bit elaborately? Why is the advantage of doing this way.

    🙂

  • Bill Whitman

    SSC Rookie

    Points: 38

    You can use separate INI files that follow with the DTS package, or you can utilize global variables that are fed by the dtsrun command using the /A switch, or a combination of both. The simplest way we found is to feed the location of the INI file to the package using the /A switch, and then reprogram the Dynamic Properties objects to pick their values from there. Also, here is a more efficient snippet that handles multiple Dynamic Properties objects:

    ' Loop through package tasks, looking for dynamic

    ' properties task(s). If the task assignment uses an

    ' INI file, change the INI file specified to the value

    ' ASSIGNED IN THE "gvINIpath" GLOBAL VARIABLE.

    Option Explicit

    Function Main()

    dim objPackage

    dim objTask

    dim objDynTask

    dim objAssignment

    set objPackage = DTSGlobalVariables.Parent

    for each objTask in objPackage.Tasks

    if objTask.CustomTaskID = "DTSDynamicPropertiesTask" then

    set objDynTask = objTask.CustomTask

    for each objAssignment in objDynTask.Assignments

    if objAssignment.SourceType = 0 then 'Zero is INI file type

    objAssignment.SourceIniFileFilename = DTSGlobalVariables("gvINIpath").Value

    end if

    next

    end if

    next

    set objPackage = Nothing

    set objTask = Nothing

    set objDynTask = Nothing

    set objAssignment = Nothing

    Main = DTSTaskExecResult_Success

    End Function

    Code is compliments of Chris Brannigan

  • taulpall

    SSC Journeyman

    Points: 99

    This is good information. I wish I would have known about this technique before having hardcode the DTS packages that I use now.

    Are there any particular, known issues with converting INI enabled DTS packages to SSIS packages in SQL 2005 and/or 2008?

  • Bill Whitman

    SSC Rookie

    Points: 38

    taulpall,

    Unfortunately, there "ain't no way", easily. The simplistic DTS package will convert, but I haven't seen but a handful that would -- most take advantage of activex and other obscure features of DTS that will not convert. There may be some 3rd party tools out there (there's surely a market!) but I haven't found one. I would welcome a correction on this because there are hundreds of installations of DTS that could use it. Time to get some SSIS training!

  • taulpall

    SSC Journeyman

    Points: 99

    I will certainly be keen to uncover the softcoding (INI like configuration) capabilities as I experiment with SSIS.:)

  • Alexander-449406

    SSC Enthusiast

    Points: 153

    This is something that I prefer to implement using an environment variable, or actually reading the environment variable COMPUTERNAME, the good thing with SSIS is that you won't have to code this we will be using Expressions to solve this problem in SSIS...

  • alfreitas

    SSC Enthusiast

    Points: 109

    Anirban Paul (4/10/2008)


    The article is cool. But I am not clear with one thing.

    Try to break the INI in two new files

    Can you explain me this little bit elaborately? Why is the advantage of doing this way.

    🙂

    The idea is that your DTS package uses two INIs: one with technical information (like connection parameters and paths in the file server) and another one with business rules (if necessary). If you do like this, you just need to create a new INI file with business rules when you develop a new DTS package and don't have to worry about using invalid connection parameters, for example.

  • Yelena Varshal

    SSC-Dedicated

    Points: 34207

    This is a very good article. The ideas fully apply to 2000, 2005 and will to 2008. And also to any application development: Do not hardcode the changable stuff.

    One comment: I have heard from developers that they use LocalHost in order to avoid specifying the server name if their application will work on the same server as a database server. Not a good idea if you later decide to move the application or the database to another server or to use a named instance. The configuration files will do better.

    Regards,Yelena Varsha

  • Anipaul

    SSC-Insane

    Points: 24681

    alfreitas (4/10/2008)


    Anirban Paul (4/10/2008)


    The article is cool. But I am not clear with one thing.

    Try to break the INI in two new files

    Can you explain me this little bit elaborately? Why is the advantage of doing this way.

    🙂

    The idea is that your DTS package uses two INIs: one with technical information (like connection parameters and paths in the file server) and another one with business rules (if necessary). If you do like this, you just need to create a new INI file with business rules when you develop a new DTS package and don't have to worry about using invalid connection parameters, for example.

    Thanks alfreitas. I got it now. Also thanks others to share their thoughts.

    🙂

  • Jags2001

    SSCommitted

    Points: 1529

    If you install SQL Server Accelator for BI (SSABI) -- if it is still available - the implementation of its generated DTS packages are all driven by variables, including INI files.

  • John Palmer-427485

    Newbie

    Points: 5

    Speaking of DTS package portability, we use the SQL Server Client Network Utility (cliconfg.exe, installed with Windows) to create an "alias" for our DEV/TEST/PROD SQL Servers on all servers and workstations. For example, the alias we use is "CorporateSqlServer01", and on DEV servers and developer workstations it points to our DEV SQL Server; on TEST servers it points to our TEST SQL Server; and on PROD servers it points to our PROD SQL Server.

    This has been a great help to us for deploying and migrating applications as well as DTS packages because the connection string never has to change:

    "Data Source=CorporateSqlServer01; Initial Catalog=our_app_database; Integrated Security=SSPI"

    Using the same concept, we create aliases for the file servers and what-nots in the HOSTS file, so UNC paths can be \\localhost\share or \\CorporateFileServer01\share and so forth. Again, so code and DTS packages can be migrated from server to server regardless of their DEV/TEST/PROD environment. Similarly, you can create a HOSTS alias for CorporateStateServer01, for example, that points to your ASP.NET state server (if you use one).

  • taulpall

    SSC Journeyman

    Points: 99

    John,

    I'm very thankful that you posted this info about aliasing server names. I am about to setup a testing environment so I have an immediate use for this.

    Thanks again,

    taulpall

  • Yelena Varshal

    SSC-Dedicated

    Points: 34207

    This is a GREAT idea with alises.

    The reason I don't use it is that we try to make dev, test and production environment with as different names as possible including different database names. The reason for that is that the superusers who test the SW would not accidentally confuse test and production and would not enter production data into the test database.

    Regards,Yelena Varsha

  • mosaic-263591

    Right there with Babe

    Points: 727

    Bill Whitman,

    This is a useful solution that allows the production pakage to run while at the same time working on the development of the next version of the package - thanks!

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply