Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 SSIS and Import/Export (2005)
 SSIS package fails when run as Agent Job

Author  Topic 

igress
Starting Member

2 Posts

Posted - 2008-11-28 : 07:28:29
Here is my problem,

Please note that this is targeted only for advance SSIS developers.

Description:
I have 13 SQL servers whose login information is maintained at a sql server database.
When the SSIS package starts I iterate through those information from a FOR EACH LOOP container and dynamically create an OLEDB connection named OLEDBDynamicSource (By binding variables to expression , connection strings etc)



There is a Data Flow task inside the FOR EACH LOOP, and its task is to create that dynamic connection and count the number of rows return from a select statement and save it in a table as ( this is a much reduced version of actual problem, but for the sake of explanation this would do)
[ServerName],[RowCount]



To make sure that the For each loop continues even after a server login failure (which in terms cause data flow task to fail) so that SSIS package will try to connect to next server from the next iteration I have correctly set PROPOGATE, MAX ERROR COUNT, ForceExecutionResult at appropriate locations. (As explained in so many articles on the net).


Everything works perfect when runs under VS 2008. (I mean For each loop continues even when there is a login failure to those dynamic connection)
Look at the image below



Ever thing works perfect when the package is executed directly from SQL server Package folder. (I mean For each loop continues even when there is a login failure to those dynamic connection)

For example



Everything works perfect when Agent Job runs. (Note that I have correctly set all the credentials and service accounts. Job Step’s “Run as” is correctly set with right credentials). ProtectionLevel=Don’t Save Sensitive.



There is no security critical info in the package as I have set S
But…….
Suppose at least one login fails for those 13 SQL servers during For each loop iteration the entire Agent Job fails. The intended behavior is failing only the Data Flow task inside for each loop and continues from the next server.


If the logins succeed for those 13 SQL servers even the Agent Jobs runs perfectly.
Plat form:
SQL server 2008
2008 BIDS
.net 3.5

The error message that pops out is

Started: 1:55:45 PM Error: 2008-11-28 13:56:03.99 Code: 0xC0202009 Source: TranMaster Connection manager "OLEDBDynamicSource" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E4D Description: "Login failed for user 'test123'.". End Error Error: 2008-11-28 13:56:04.08 Code: 0xC020801C Source: Data Flow Task OLE DB Dynamic Source [1] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "OLEDBDynamicSource" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2008-11-28 13:56:04.15 Code: 0xC0047017 Source: Data Flow Task SSIS.Pipeline Description: component "OLE DB Dynamic Source" (1) failed validation


Please help as this is a kind of urgent project


Sriwantha Sri Aravinda

sachin.kale
Starting Member

6 Posts

Posted - 2008-11-29 : 03:16:10
Check whether the user name has sufficient previlages check the user roles whether they are same as 'sa' user.
Go to Top of Page

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-11-29 : 10:56:01
Maybe solution is in Jobstep Execution Options Tab:

Execution Options Tab
View or change the execution options for the package on this tab.

Fail package on validation warnings
Select this option for package execution to fail if validation warnings occur.



Webfred


No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page
   

- Advertisement -