March 17, 2011 at 12:22 pm
You're right, my bad. I meant the dtexec or dtexecui utilities.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 17, 2011 at 12:24 pm
miquel deblanco (3/17/2011)
I didnt think I could run a package using dtutil. It looks like it's just for copying/moving etc. on the local server? Just so you know I'm working from a client machine, not on the server and don't currently have access to it's file system.I assume you mean the "login failed for" account thats on the other server. It is not a domain account. The server roles are sysadmin and serveradmin.
Thanks again.
Does this account exist on the SQL box? Is the SQL box on a domain?
Are you able to login to SSMS using this other account?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 17, 2011 at 12:46 pm
I use win auth to log in (does that mean its on a domain?) but I was able to login to SSMS also with this account.
March 17, 2011 at 12:57 pm
Failed also when using dtexec.
March 17, 2011 at 1:03 pm
Windows Auth does not mean that you are on a domain.
My understanding
SSIS to be executed on ServerA with a windows user account on ServerB.
You have added the ServerB\WindowsAccount as an account in SQL Server?
The job is failing trying to use this account.
You are able to login to ServerA with ServerB\WindowsAccount
You created a proxy account for ServerB\WindowsAccount and it did not work
What is the account being used to run the SQL Agent Service?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 17, 2011 at 3:25 pm
SSIS to be executed on ServerA with a windows user account on ServerB.
You have added the ServerB\WindowsAccount as an account in SQL Server?
ServerB account is not a windows account but just a login and user I created in SSMS
The job is failing trying to use this account.
Yes
You are able to login to ServerA with ServerB\WindowsAccount
No, sorry if I wasnt very clear on that. I logged into ServerB with the login I created there. That login does not exist on ServerA.
You created a proxy account for ServerB\WindowsAccount and it did not work
The proxy I created was for ServerA. I don't know if I can create a cred on ServerA based on a login on ServerB? Right now the cred is based on my windows login to ServerA. I've tried running it with both a proxy and using SQL Server Agent Service Account and they have the same result.
What is the account being used to run the SQL Agent Service?
If you mean on the Agent General tab, I'm logging into the server using Windows Auth.
One thing I think I misspoke on yesterday. The package is not saved to the file system, but is saved to SQL Server.
March 17, 2011 at 3:51 pm
Try creating that same account/pw combination on ServerA and then try it again.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 17, 2011 at 4:26 pm
Didnt work. I'm trying a bunch of combos with no luck.
March 17, 2011 at 4:39 pm
In the services control panel, what is the account that is running the SQL Agent Service?
Can you script out the job and post it?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 17, 2011 at 5:44 pm
CirquedeSQLeil (3/17/2011)
In the services control panel, what is the account that is running the SQL Agent Service?
On the server, right? I have a call in to the server admin.
Can you script out the job and post it?
USE [msdb]
GO
/****** Object: Job [CamsImportToDOTTime2011New] Script Date: 03/17/2011 16:38:26 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 03/17/2011 16:38:26 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'CamsImportToDOTTime2011New',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'DOTTimeUser', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Run SSIS Package] Script Date: 03/17/2011 16:38:27 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Run SSIS Package',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'SSIS',
@command=N'/SQL "\CamsImportToDOTTime2011" /SERVER itas23 /CHECKPOINTING OFF /REPORTING E',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
April 5, 2011 at 12:17 pm
Sorry for the delay. I've been meaning to update you. After banging my head on the wall for weeks on this I noticed that there is a command in BIDS to run a DTS2000 package. So I created a new SSIS package which contains just that command and calls the old DTS package that ran fine in SQL 2000 but would not run (from SQL Agent) when I imported it into 2008.
Well it ran just fine from SQL Agent when called in the SSIS package. I really don't know why and never figured out why the exact same actions would not run when done in SSIS. The SSIS package was created from importing the DTS package so there was no difference.
Anyway I can't waste any more time on this than I already have so since it is running I'm calling it good but wanted to say thanks for all the help. Much appreciated!
Mike
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply