I'm using the SSIS wizard to import data from an SQL2000 machine across the state and import to an sql2005 machine here where I am a member of the domain administrators group. I select the .net oledb provider for sql server via the drop-down menu on the second screen titled "Source". I then choose my "Destination" information and log on locally as sa. I then enter my query for the "Source" data, select the table into which I wish to put the data, and then the mappings to determine which "Source" columns go into which "Destination" columns. I preview and get a glimpse of the data fine. Click finish. Execute the job and everything runs fine.
I exit the BIDS 2005 on myMachine, remote desktop to myServer, open SQL Server Management Studio, Integration Services, and import the dtsx file from the "File" drop-down menu (the file is physically located on the SQL Server the whole time). I run the file here and it stops with the login error. I haven't changed anything at this point and only relied on BIDS' drop-down menus. I open SQL Server Management Services, Database Engine, and try to schedule the job with the job scheduler and it also won't run. It only runs when I'm in BIDS 2005. I've successfully created dtsx packages before, but they've been local packages to import Access database files or export Excel spreadsheets, etc. Pretty sure the backwards compatibility works because I've imported and used some old 2000 dts packages in SQL 2005.
I've never been able to run DTEXEC on any machine - I said I tried and couldn't get it to work. I remoted to myServer, opened cmd, went to the \Program files\Microsoft SQL Server\90\dts\binn and tried to execute dtexec.exe, but couldn't get it to go. But, I'm unfamiliar with DTEXEC and am probably doing it incorrectly.
The error seems to indicate that it's getting to the remote server, but the password isn't being saved and given? Could that be it? Any help is appreciate - I've been fighting it for a week with no results. Here's the exact error message:
Executed as user: Domain\Administrator. ....00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005.
All rights reserved.
Started: 8:46:45 AM Progress: 2010-03-03 08:46:47.84
Source: Data Flow Task
Validating: 0% complete
Error: 2010-03-03 08:46:48.55
Source: Data Flow Task Source - Query 
Description: System.Data.SqlClient.SqlException: Login failed for user 'remote_sup'.
at System.Data.SqlClient.SqlInternalConnection.OnErro r(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser****n(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlInternalConnectionTds.Com pleteLogin(Boolean enlistOK) at System.Data.SqlClient.SqlInternalConnectionTd...
The package execution fa... The step failed.
Not quite sure what was happening - I'm a domain administrator using sa account, but using my local machine to develop a BIDS was NOT working. And, I couldn't install BIDS on the server cause it kept telling me it was already installed. Finally found a post telling me how to get around that. Installed BIDS on the server, did an rdc to the server and used BIDS on the server and everything works fine.