Using the value of Global Variable in a Trans. Data Task
-
I have 3 thing in my DTS window.
Step One
Global variable has been created: PracticeID
I then use Dynamic Prop. Task to polulate that variable. This is done by specifying a query like so:
SELECT PRACTICEID FROM PRACTICE
WHERE PRACTICENAME = 'My Practice'
The Global variable has now been set.
Step Two
Specify text file as a source file with data.
Step Three
Create a DB Connection to SQL Server where the table lives (PracticeData) and where the text data will be moved to.
Step Four
Create a Transformation Data Task between the text file and DB Connection.
The text file does not contain the PracticeID so I am one column short when I go and use the transformation tab.
How do I now use the Global Variable that has been set by my Dynamic Properties Task as the value that will be used in the transformtion for each record to be inserted?
-
I believe that the only way is to create an activx transformation. Select only the output column.
Then in the code assign the output column to the value of the global variable. Just use the browser on the left to select the output column type = then select the global.
DTSDestination("RecordOut") = DTSGlobalVariables("$OPCODE").Value
-
Well I have tried the following and the package will insert the data but it will not assign the Practice ID column according to what the global variable is. I have also added a SQL task to delete the records where the Column PracticeID = the global variable. That too does not work. Here is what I have from start to finsh:
STEP ONE:
Assign the package with a global variable named = PracticeID
STEP TWO
Create Dynamic Property Task that performs a query. The results of the query will then be assigned to the global variable:
SELECT PracticeID
FROM Practice
WHERE PracticeName = 'Ridgeview Medical Group'
STEP THREE
Create a SQL task that deletes all records according to the Variable assigned from the previous step
STEP FOUR
On success the next step is to perform the transformation. Within the transformation I create a ActiveX script for the specific column that will use the Global variable value to populate the PracticeID column in my table. The firts illustartion shows the transformation view:
The next illustration shows the exact way the ActiveX script is written in order to again use the global variable to populate my column PracticeID:
Here is what the package looks like:
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply