November 19, 2007 at 1:07 pm
Hello,
I have a Master Package and several Child Packages.
The Child Packages transform some excel files into the SQL server Database.
For each Child Package there is a Task created in the Master Package. So It goes executing one after another.
The Child Packages have the globalVariable "gv_source_path" set which points to the Source Folder of the Files.
Now, we want to be able to change the Souce Folder dynamically.
We store this Folder name into some Table and read the Value at the Execution time using an ActiveX Script as the First Step in the Master Package. Which would update all the Child packages' GlobalVariables to reflect to the New Location (Folder) as Source Folder.
so if currently the Folder which contains the excel files, is C:\ProjectA\
and later if we want to change the Folder to C:\ProjectB in the Database table and that would reflect in all the Child Packages' Global Variables with this new ActiveX Script.
I wrote the script. It updates the GlobalVariables of each child Package. And at the run time if i put MsgBox to see the value of that Package with
oPkg.GlobalVariables("gv_source_path").value
that will correctly show the right value.
but later when the child Package executes it still looks into the OLDER Source Path.
I realised that I will have to Save the Child Package after updating its GlobalVariable using SaveToSqLServer Method.
But i get following Error.
Only the owner of DTS Package or a member of the sysadmin role may create new versions of it.
one more thing.
When i update the globalVariables of the MasterPackage through code, it reflects it correctly when i go into design mode and look at the Properties of the Master package.
But Child Package does not.
what am i doing wrong?
thanks
Deepak
November 20, 2007 at 1:50 pm
Hello Deepak,
Let me suggest an alternative approach.
Perhaps a Dynamic Properties Task would help here. The file path is already in the database, so us it to set the global variable or connection in each child package.
The dynamic property task can be configured to get the data from a query.
Create a connection to the database with the filepath.
Create a dynamic properties task and make it the first thing the package does.
Double click
Click Add on the Dynamic Properties Task Properties Dialog
Expand the Global Variables on the left pane of the Dynamic Properties Task: Package Properties dialog
Click on the global variable of interest in the left pane.
Click on Value in the right pane
Click on the Set
On the Add/Edit Assignment dialog...
Choose Query in the Source: drop down
Chose the connection to the file path in the Connection: drop down
Enter the query in the box
Click refresh to test the the correct result is returned.
Now the package will grab the correct path every time it runs.
Norman
DTS Package Search
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply