Using Data Transformation Services: Using Global Variables with the DTS Designer - Second Example: Update Records
(Page 4 of 5 )
In this example, I will be using the same table, Orders, imported into my test bed database, TestWiz. The update query will update the RequiredDate and ShipName for the customer with CustomerID HANAR. It is assumed that all the RequiredDates have been postponed to 7/10/2005, and the shipping company changed the ship's name to Hanari Banari from their existing values shown in the Orders table.
To implement this update procedure, we create a package as shown in the next picture, GlobalUpdate.
Since we are changing the value of three columns at the same time, we need three parameters which we specify in the Global Variables tab of the package as shown. Using the global variable sname we change the ship's name. We also use SD, which now represents the RequiredDate and CID set equal to HANAR.
In the Logging tab we specify a error file with location, and also choose to write to the Event Log. You need only one of these, as it is essentially the same information.
The Update query will be fashioned using the DTS Designer. Create an instance of the Execute SQL Task by dropping it into the design pane. This brings up the next window shown in the picture. The SQL Statement text area will be empty to start with. What you see here is the result after building the query as described.
By clicking the Build Query, bring up the Query designer as shown in the next picture.
Click on the orders table in the listing on the left and drop it into the query design pane. By right clicking in the empty region of the query designer, you can pop up a menu from which you can change the query type from the default Select to the type of your choice. Here an Update type is chosen.
Then you can click the check box in the table definition in the query designer and create the query. You may have to add the ? marks shown as these are not added.
The query can now be tested in-situ by supplying the appropriate parameters as shown in this picture.
Once the query is created, click on the Parameters... button to bring up the Parameter Mapping window. Here, you make an association between preset Global variables with the parameters needed for the query as shown in the next picture. If the parameters are not set beforehand, you can create them using the Create Global Variables... button in this window. Since the Input Global variables were created in an earlier step, they should be available in the Input Global Variables tab in the Parameters Mapping window. Now the package is complete and it can be executed as before going to Package->Execute step.
Next: Execution again >>
More MS SQL Server Articles
More By Jayaram Krishnaswamy
/ 13 
