Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Yet another way to include portability on DTS packages Expand / Collapse
Author
Message
Posted Wednesday, April 9, 2008 11:35 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 5, 2010 9:31 AM
Points: 7, Visits: 25
Comments posted to this topic are about the item Yet another way to include portability on DTS packages
Post #482774
Posted Thursday, April 10, 2008 1:53 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 3:19 AM
Points: 5,368, Visits: 1,391
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.


:)



Post #482816
Posted Thursday, April 10, 2008 7:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 28, 2009 7:35 AM
Points: 6, Visits: 13
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
Post #482990
Posted Thursday, April 10, 2008 7:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 16, 2013 3:16 PM
Points: 4, Visits: 19
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?
Post #483000
Posted Thursday, April 10, 2008 9:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 28, 2009 7:35 AM
Points: 6, Visits: 13
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!
Post #483125
Posted Thursday, April 10, 2008 10:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 16, 2013 3:16 PM
Points: 4, Visits: 19
I will certainly be keen to uncover the softcoding (INI like configuration) capabilities as I experiment with SSIS.:)
Post #483160
Posted Thursday, April 10, 2008 10:47 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 3:37 PM
Points: 23, Visits: 184
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...
Post #483175
Posted Thursday, April 10, 2008 10:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 5, 2010 9:31 AM
Points: 7, Visits: 25
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.
Post #483182
Posted Thursday, April 10, 2008 11:40 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, September 15, 2014 4:12 PM
Points: 3,475, Visits: 582
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 Varshal

Post #483207
Posted Thursday, April 10, 2008 9:59 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 3:19 AM
Points: 5,368, Visits: 1,391
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.
:)



Post #483397
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse