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

(Page 1 of 4 )

The scenario is one of creating a DTS package that populates a table in an MS Access database from an SQL Server database on a schedule. The scheduling is to be carried out by creating an SQL Job. MS Access is often used for creating reports, and this scheduling makes it easier to create updated reports using data from an SQL Server. This question of scheduling dts packages often shows up in SQL forums.

In previous tutorials, step-by-step methods of creating and executing DTS packages with ImportExport Wizards as well as using the DTS Designer has been described in considerable detail. Executing the DTS Packages designed and saved on the SQL Server from ASP.NET using the Visual Studio 2003 has also been treated in an earlier article.

This main focus of this tutorial is to guide the user in scheduling the execution of the DTS Packages by creating an SQL Job. In order to complete the tasks in this tutorial the reader is advised to review the previous tutorials on this topic, available online here at ASP Free.

We will follow the steps indicated here:

Part A: Creating the Package

• Create a dts package
• Create an SQL Connection
• Create an MS Access Connection
• Create a Transform Data Task
  This task populates a table in MS Access with data in a table in the SQL 2000 Server database.
• Create an Execute SQL Task
• Add Workflow to Package
• Save the Package

Part B: Scheduling the Package

• Create a SQL Job
• Configure the Job for Scheduling
• Save the Job

Next: Part A: Creating the Package >>
 

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!