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

Have you tried debugging the Script Task to see whether that gives you any clues about what is happening?

So what do Email_Start and Email_Success do?

Surely your e-mail task needs to be outside of the Sequence container, after the Collect All Errors task?

Great stuff - glad to help & thanks for posting back.