Please explain what you mean by "manually executed through SQL Server"

I guess you mean as part of a job. Are you logged in to SQL Server as the same user...

da-zero (5/5/2010)

---

To give a little more explanation:

DTS is the old version of SSIS. DTS uses .dts files, SSIS uses .DTSX files.

You can compare it with .xls for Excel 2003 and...

You cannot open dtsx files in DTS.

Maybe this will help:

http://msdn.microsoft.com/en-us/library/dkkx7f79%28v=VS.71%29.aspx

Use the connection you defined in Connection Manager in the Script Task - that is the way to work.

I am assuming that you tried creating a new connection using Connection Manager?

What happened when you selected New Connection / ODBC? Were you not able to see your system DSN...

Are you using a 64-bit machine by any chance?

Put a data viewer after your source to check that the 'bad' records make it into the pipeline OK. Look for 'special' characters - commas, quotes, empty fields/NULLs, tabs, CRs.

Maybe...

Do your backups and restores using Execute SQL tasks.

After backing up on server 1 you'll need to transfer the file to server 2 before it is restored. I would probably...

Are both these databases on the same server?

You are writing data from a DW back into an application database and want to mimic the application's IDENTITY logic?

Leaving aside the fact that the requirement sounds a little strange,...

If all of DB2's data has been copied from DB1, what is the reason for Step 2?

How complex is the db? Have you considered BACKUP / RESTORE / ALTER TABLE...

Without knowing the specifics, I can't get very detailed.

I would be tempted to create all of the Excel layouts as new tables in Access (with PK/FK Referential Integrity applied across...

In the part where you specify what the field will contain, just add some literal text as your expression, eg

"Server1"

This will then be available to you in the data pipeline,...

Hey, you're not that new! I told you how to add derived columns yesterday - see here

http://www.sqlservercentral.com/Forums/Topic912502-148-1.aspx

Phil