Using Data Transformation Services: Package Execution Using SQL Server Agent, Introduction - Create a Transform Data Task

(Page 3 of 4 )

The data transformation task is very simple, copying from one table to another. The source table is the SQL Server database table and the destination table is the MS Access table. Herein, only the Transformations tab of the Transform Data Task is shown:

Create an Execute SQL Task

The business objective requires that the MS Access table is deleted and a new one is created before the data from SQL Server is added. In order to carry this out, a Execute SQL Task is added. This task should drop the table and recreate the table with the same design features. The next picture shows the features of such a task.

Add Workflow to Package

The workflow is needed because the MS Access table should exist before the data can be pumped from the SQL Server. Hence only when the Execute SQL task succeeds will the data be transferred from SQL Server to MS Access, and not otherwise. The Workflow properties page (obtained by right clicking the workflow) shows the information in the next picture.

Save the package

This completes the design of the package that deletes a table, recreates the table, and then populates it with data from SQL Server. This is saved to the SQL 2000 server, and the next two pictures show the details of this package.

Next: Part B: Scheduling the Package: Create a SQL Job >>
 

More MS SQL Server Articles
More By Jayaram Krishnaswamy

Comments On: Using Data Transformation Services: Package Execution Using SQL Server Agent, Introduction

· I thank you for reading through the article, assuming you already have. Do you have...    · Hi JayaramThe article is excellent - it gives the step by step guide for creating...    · I do not have an immediate, complete answer. I can think of two possibilities,...    · Read your document..it is really nice as you have given step-by-step execution.I...    · Can you make your question a little more specific, please? ThanksJ    · Ok I will explain in detail.I have one intranet(Offline) application for some...    · I think if you use DTS for the transfer and schedule the job nightly you may be able...    · Thanks for your reply!One question...Will DTS work (best to use?) if my live Sql...    · My best guess is that it depends on what you can, and cannot do on hosting...   >>> Post your comment now!