Site Sponsored By: SQLDSC - SQL Server Desired State Configuration
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.
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 belowEver 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 exampleEverything 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 SBut…….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.5The 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 projectSriwantha 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.
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 TabView 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.
WebfredNo, you're never too old to Yak'n'Roll if you're too young to die.