asked
SSIS Package Fails to Run Under Proxy
I have a BIDS 2005 SSIS package that I've deployed to the SQL Server SSIS Package subsystem (also 2005). The package is designed to connect to a source database and transfer tables and data to a target database. Both source and target are dynamic, with connection strings generated from metadata and returned to the package connection managers via a stored procedure call. DelayValidation is set to true for the package, to prevent it falling over on load. The package works as expected when run from BIDS (running on local machine under personal domain account). I have deployed the package to SQL Server and created a SQL Agent job to execute it. Because the service account on the deployment server has no rights on other servers, I have also created a proxy account using credentials of a domain account that has the required access to other servers. When attempting to execute this job, it just hangs. The package processing is not started as far as I can see (I have extensive logging in place and no log records are generated). Although possibly it may well be launched, but displaying a message and waiting for input? Attempting to run the agent job using the service account does launch the package, but the package fails as it is unable to connect to the source or target servers. So the problem can't be the package, it must be the interaction with the proxy. Why won't it use the proxy? Am I missing something? I've also tried running the package via dtexec (run via xp_cmdshell from SSMS whilst connected to the server with a domain account that has rights to all servers). This produces a similar connection error. I had thought that this might inherit my domain account credentails, but clearly not. Interestingly, attempting to execute via a
vb.net app call using Microsoft.SqlServer.Dts.Runtime fails also, but on attemting to load the package to a new instance of the app. I get an Assertion Failed error: at STrace.ReadTraceValues() at STrace..cctor() at STrace.Trace(String strComponentName, String strLine) at ManagedHelper.GetNextManagedInfo(DTS_MANAGED_INFO& nextManagedInfo) I've added references to Microsoft.SQLServer.DTS.ManagedDTS/RuntimeWrap/PipelineWrap/PipelineHost Can anyone suggest where to go next? I'm a bit stumped at the minute. Apologies for the novella of a post.
Shot in the dark, but can you tell us about how you set up the proxy itself (not the domain account, the proxy object in SQL Server). Specifically, has the proxy been given access to the SSIS package execution subsystem? EXEC dbo.sp_grant_proxy_to_subsystem @proxy_name = N'NameOfProxy', @subsystem_id = 11 ;
I created the proxy via sp_add_proxy, adding access to SSIS via the same procedure call you've added here. I've just also tried adding the three SQL Agent roles as principals, no improvement. I failed to add that when running a trace as the agent job shows as executing (when manually started via the UI) I just see this sequence of events repeating constantly: SELECT sv.name AS [Name] FROM msdb.dbo.sysjobs_view AS sv WHERE (sv.name=N'RunDatabaseTransfer') SELECT sv.name AS [Name] FROM msdb.dbo.sysjobs_view AS sv WHERE (sv.name=N'RunDatabaseTransfer') SELECT null AS [CurrentRunStatus], CAST(sv.job_id AS nvarchar(100)) AS [job_id] FROM msdb.dbo.sysjobs_view AS sv WHERE (sv.name=N'RunDatabaseTransfer') SELECT null AS [CurrentRunStatus], CAST(sv.job_id AS nvarchar(100)) AS [job_id] FROM msdb.dbo.sysjobs_view AS sv WHERE (sv.name=N'RunDatabaseTransfer') exec msdb.dbo.sp_help_job exec msdb.dbo.sp_help_job Might be relevant?
15 People are following this question.
