DTS in 2000 to SSIS in 2005 transformation

[juliane26]

I have a little problem with our old DTS packages.
We changed them to SSIS packages.
They work as long executed in a way that all connections (i.e. file sources) are local and our sqlagent(local system user) has no problem accessing them.

I would like to access a remote file system and changed the job owner to a domain user. Still the package is run under the sqlagent id, and I have no option to change that.

any help, anything I somehow missed here ??

The error message I get is ,,Executed as user: ....\svcsqlagent. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 16:41:01 Error: 2008-01-31 16:41:02.17 Code: 0xC001401E Source: .... Connection manager "..." Description: The file name "\\...\...$\...\....txt" specified in the connection was not valid

the job owner is a domain user with sysadmin role. this user can access the file.

the sqlagent cannot, since it is not a domain user and we actually cannot change that.

thanks

Juliane

 

[mrdenny]

Job ownership credentials are used for what rights you have within SQL, not the OS. You might be able to use credentials to get around this. Setup a credential as the domain user, and give the login rights to use the credential. Then setup the SSIS job step to run under that credential.

If that doesn't work, then changing the Account which the SQL Agent runs under is probably the only option.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog