DTS package using SQL Server 2005

To get started I would go into management studio and use the import wizard.

right click database
all tasks
import data

Follow the wizard through, and before you finish the last step you would tick the option to save as a package.  Save this as a file, and then you can open the saved pakage in Business Intelligence Studio

This will be the first part of your package.

If you want to get the output part, then simply run an export, and save this.
Then combine the packages in the studio.

DTS in SQL2005 is now referred to as SSIS - SQL Server Integration Services.

Where does the saved package get saved to?

Do you want to copy the entire Excel file across servers, or do you want to grab data from the source spreadsheet and append it to the destination one?

To copy a file you just issue a COPY command. You can do that within a SQL Agent job (no need for SSIS)

You can choose to save the package in the server, or as a file.

If you save as a filename this is easier, as you can then just open up the file directly in BIDS

I want to grab data from the source spreadsheet and insert the data into the destination one. The destination one will need to be cleared daily, and then the new data will be added daily.

I have saved the package as a file, but now when I try and open/retrieve the file, I can not find it anywhere. Any idea where the file is saved to?

When you ticket the box to "Save SSIS Package" did you choose file system?

If you did then it asks you for the path.
If you left the default which is "SQL Server" then it is saved within the SQL Installation and you can't find the file.

I think the confusion here is between packages deployed to the server and packages saved as a dtsx file.  It is possible to save SSIS as a dtsx file and run directly from the file.  But it is also possible to "save" the file into the SQL Server, in this case it is stored in the database and can only be accessed through management studio.

Is there a way I can use the Import Data function of SQL Server 2005, and specify the table that I want the data to be imported into? Right now it is creating a new table every time called Data$. I want the data to be imported into an existing table, so no create statement would be used.

>> I want to grab data from the source spreadsheet and insert the data into the destination one. The destination one will need to be cleared daily, and then the new data will be added daily.

So why can't you just copy the entire spreadsheet file over? (i.e. no ETL required) Is there other data in other worksheets that remains static?

I need to copy over a folder with Excel spreadsheets, then copy the data that are in those spreadsheets into another spreadsheet. I do not need the data to be copied over into SQL Server. Can I just copy the spreadsheets over to the server using XCopy? If so, is there a way to write a SQL Job Agent to XCopy the files from one server location to another server location? If so, how would I write the DTS package to XCopy the files?