Using Data Transformation Services: Package Execution Using SQL Server Agent, Introduction - Part A: Creating the Package

(Page 2 of 4 )

A package called DeleteAnAcessTable is created and the necessary connections, tasks, and workflow are configured. We highlight a couple of essential steps and skip the detailed description. The description is available in earlier tutorials. Screen shots are shown for the essential steps.

Creating the package: Details of the two connections

There are two connections, one to SQL Server 2000 and the other to the MS Access database in the package DeleteAnAcessTable. All that is needed to set up a connection is to drag the connection control from the connection collection and drop it on the designer.

The next two pictures show the two connections. The first one is the source (SQL 2000 Server), and the second is the destination (MS Access database at its default location).

Creating an SQLConnection

Creating an MS Access Connection

The table in the SQL 2000 Server whose content populates the MS Access table has the following structure.

The design view of the destination table in MS Access has the following information:

Next: Create a Transform Data Task >>
 

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!