Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

DTS Question

Status
Not open for further replies.

dgillz

Instructor
Mar 2, 2001
10,038
US
I have an Excel file that is periodically placed at a certain location on the network. I want to make MS SQL Server to read this file, and update a MS SQL table based on the contents of the Excel file.

Is this a job for DTS?

If so does someone have a quick cheat sheet on hoe to configure DTS to do things like this? I am pretty comfortable with SQL Server but have no experience with DTS.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports
 
Sure DTS can perform that task.

Here is a quick how to.

1. Open Enterprise Manager
2. Select Data Transformation Services and right click on Local Packages and Select New Package.
3. Click Connection on the menu and select Microsoft Excel.
4. Specify the File name (and location) and click OK.
5. Click on Connection again and select Microsoft OLE DB Provider for Sql Server
6. Specify the server name and connection info and ensure what ever database you are using is selected as the target database and click ok.
7. Click Tasks on the menu and select Transform Data Task.
Click the source connection (Excel) and then the destination connection (SQL Server. An arrow is created that links the two connections. Double-click the arrow,
On source tab select the sheet you want. Then select the Destination tab, here you will select the table where the data from the Excel Spreadsheet will be inserted. Now click the Transformations tab. Notice the arrows correctly map the source and destination columns.
Click OK to close the dialog.

8. Click on Package and select execute.


Here are some website to help you learn more about DTS.

Importing a Named Range From Microsoft Excel

Hope by Helps.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top