|
|
You're in the
sql server dts
group:Dynamic Properties Task
sql server dts:
Hello,
I've created a DTS package that works off a ini file on a server. The server is a development server. Now I need to move it to QA. I save the file for the QA folks. I've got an ActiveX script that takes the values out of the ini file and creates the global variables to run the rest of the package. The problem: The dynamic properties task is still looking at the ini file on the DEV server. Is there a way to program the dynamic properties to look at the ini file on the server where the package is being run? I could always change the values in the dynamic properties task, save it under a different name as myPackage_QA.dts and deploy this package. However, I would rather not have multiple copies of the package. Any thoughts or ideas on a better way? Thanks,
This is difficult but doable. I think that if you can just understand the
script below and adjust it to your needs it will work. You are going to manipulate the dynamic properties task programmatically. This script is reading an ini file that is called "test.ini" (this is specific to what you are doing). The path is in the global variable "LogPath." The dynamic property task assigns the value from test.ini to a global variable called Message. First you need to find out what DTS calls the assignment that reads the ini file. Create and the following script. Watch for the DestinationPropertyID that makes sense, and copy it verbatum (if you have only one assignment, you can simplify this script). There may be other ways to get the information you need. Be creative. '********************************************************************** ' Visual Basic ActiveX Script '************************************************************************ Function Main() dim file dim tsk dim assi set tsk = DTSGlobalVariables.Parent.Tasks("DTSTask_DTSDynamicPropertiesTask_1").CustomTask for each assi in tsk.Assignments msgbox assi.DestinationPropertyID next Main = DTSTaskExecResult_Success End Function ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ Now we are ready for the real script. Where I have "'Global Variables';'Message';'Properties';'Value'" put the value you found for the assignment you want to make VVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVV '********************************************************************** ' Visual Basic ActiveX Script '************************************************************************ Function Main() dim file dim tsk dim assi file = DTSGlobalVariables("LogPath").Value & "\test.ini" set tsk = DTSGlobalVariables.Parent.Tasks("DTSTask_DTSDynamicPropertiesTask_1").CustomTask for each assi in tsk.Assignments if assi.DestinationPropertyID = "'Global Variables';'Message';'Properties';'Value'" then assi.SourceIniFileFileName = file end if next Main = DTSTaskExecResult_Success End Function -- Russel Loski, MCSD.Net [quoted text, click to view] "Webtechie" wrote:
> Hello, > > I've created a DTS package that works off a ini file on a server. The > server is a development server. Now I need to move it to QA. I save the > file for the QA folks. I've got an ActiveX script that takes the values out > of the ini file and creates the global variables to run the rest of the > package. > > The problem: The dynamic properties task is still looking at the ini file > on the DEV server. > > Is there a way to program the dynamic properties to look at the ini file on > the server where the package is being run? > > I could always change the values in the dynamic properties task, save it > under a different name as myPackage_QA.dts and deploy this package. However, > I would rather not have multiple copies of the package. > > Any thoughts or ideas on a better way? > > Thanks, >
Fantastic!
You don't know how much I've wanted this, many, many thanks! If I may ask, how'd you find this out? Josh On Fri, 26 Jan 2007 06:16:03 -0800, RLoski [quoted text, click to view] <RLoski@discussions.microsoft.com> wrote:
>This is difficult but doable. I think that if you can just understand the >script below and adjust it to your needs it will work. You are going to >manipulate the dynamic properties task programmatically. This script is >reading an ini file that is called "test.ini" (this is specific to what you >are doing). The path is in the global variable "LogPath." The dynamic >property task assigns the value from test.ini to a global variable called >Message. > >First you need to find out what DTS calls the assignment that reads the ini >file. Create and the following script. Watch for the DestinationPropertyID >that makes sense, and copy it verbatum (if you have only one assignment, you >can simplify this script). There may be other ways to get the information >you need. Be creative. > >'********************************************************************** >' Visual Basic ActiveX Script >'************************************************************************ > >Function Main() >dim file >dim tsk >dim assi > >set tsk = >DTSGlobalVariables.Parent.Tasks("DTSTask_DTSDynamicPropertiesTask_1").CustomTask >for each assi in tsk.Assignments >msgbox assi.DestinationPropertyID >next > > Main = DTSTaskExecResult_Success >End Function > > >^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ >Now we are ready for the real script. Where I have "'Global >Variables';'Message';'Properties';'Value'" >put the value you found for the assignment you want to make > > >VVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVV > > >'********************************************************************** >' Visual Basic ActiveX Script >'************************************************************************ > >Function Main() >dim file >dim tsk >dim assi >file = DTSGlobalVariables("LogPath").Value & "\test.ini" > >set tsk = >DTSGlobalVariables.Parent.Tasks("DTSTask_DTSDynamicPropertiesTask_1").CustomTask >for each assi in tsk.Assignments > if assi.DestinationPropertyID = "'Global >Variables';'Message';'Properties';'Value'" then > assi.SourceIniFileFileName = file > end if >next > > Main = DTSTaskExecResult_Success >End Function
I did a google search about four months ago. I was too lazy to look for it
again, so I just adapted my code. I set a reference to the DTS libraries in Access, then use the object explorer to see what other properties I can manipulate. I believe that it was from SQLDTS ( this is close: http://www.sqldts.com/252.aspxwriten by Allan Mitchell). Also, http://sqlteamdev.cleardata.biz/item.asp?ItemID=12408. -- Russel Loski, MCSD.Net [quoted text, click to view] "JXStern" wrote:
> Fantastic! > > You don't know how much I've wanted this, many, many thanks! > > If I may ask, how'd you find this out? > > Josh > > > On Fri, 26 Jan 2007 06:16:03 -0800, RLoski > <RLoski@discussions.microsoft.com> wrote: > > >This is difficult but doable. I think that if you can just understand the > >script below and adjust it to your needs it will work. You are going to > >manipulate the dynamic properties task programmatically. This script is > >reading an ini file that is called "test.ini" (this is specific to what you > >are doing). The path is in the global variable "LogPath." The dynamic > >property task assigns the value from test.ini to a global variable called > >Message. > > > >First you need to find out what DTS calls the assignment that reads the ini > >file. Create and the following script. Watch for the DestinationPropertyID > >that makes sense, and copy it verbatum (if you have only one assignment, you > >can simplify this script). There may be other ways to get the information > >you need. Be creative. > > > >'********************************************************************** > >' Visual Basic ActiveX Script > >'************************************************************************ > > > >Function Main() > >dim file > >dim tsk > >dim assi > > > >set tsk = > >DTSGlobalVariables.Parent.Tasks("DTSTask_DTSDynamicPropertiesTask_1").CustomTask > >for each assi in tsk.Assignments > >msgbox assi.DestinationPropertyID > >next > > > > Main = DTSTaskExecResult_Success > >End Function > > > > > >^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ > >Now we are ready for the real script. Where I have "'Global > >Variables';'Message';'Properties';'Value'" > >put the value you found for the assignment you want to make > > > > > >VVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVV > > > > > >'********************************************************************** > >' Visual Basic ActiveX Script > >'************************************************************************ > > > >Function Main() > >dim file > >dim tsk > >dim assi > >file = DTSGlobalVariables("LogPath").Value & "\test.ini" > > > >set tsk = > >DTSGlobalVariables.Parent.Tasks("DTSTask_DTSDynamicPropertiesTask_1").CustomTask > >for each assi in tsk.Assignments > > if assi.DestinationPropertyID = "'Global > >Variables';'Message';'Properties';'Value'" then > > assi.SourceIniFileFileName = file > > end if > >next > > > > Main = DTSTaskExecResult_Success > >End Function >
Well, thank you Mr. Loski, and Mr. Mitchell!
Guess it's too late for Microsoft to, like, document this, DTS being obsolete and all, much less expose it to the DTS development environment. It's not click-accessible there, somewhere, is it? I've been in several DTS-heavy shops, and nobody knew this. Josh On Fri, 26 Jan 2007 20:05:00 -0800, RLoski [quoted text, click to view] <RLoski@discussions.microsoft.com> wrote:
>I did a google search about four months ago. I was too lazy to look for it >again, so I just adapted my code. I set a reference to the DTS libraries in >Access, then use the object explorer to see what other properties I can >manipulate. > >I believe that it was from SQLDTS ( this is close: >http://www.sqldts.com/252.aspxwriten by Allan Mitchell). Also, >http://sqlteamdev.cleardata.biz/item.asp?ItemID=12408.
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |