I have created an SSIS package which moves data from one external server (accessible using a different username (eg USERX) and password from what I login to my PC) to an internal server (Window Authentication login). The package does run successfully but I have 2 issues.
any ideas on how to solve this please?
Happy to post screen shots or details of errors if need be.
thank you so much
If you have deployed the package to SSISDB, create an environment containing a 'sensitive' variable to store the password.
Then configure your project in SSISDB to reference the environment and map the variable to the connection's password.
This solves the server problem.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 28, 2024 at 2:21 pm
thank you. How do I do that please?
February 28, 2024 at 2:48 pm
Please have a read of the following and post back with any questions:
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 29, 2024 at 10:41 am
I have created the environment.
However I am still getting the following error.
The WAST-AGL-DW.DataAcademy db is the one which has a separate username and password, which I have added into the environment.
Message
Executed as user: CYMRU\CTM_SQLAgent_NCCU01. Microsoft (R) SQL Server Execute Package Utility Version 15.0.2000.5 for 32-bit Copyright (C) 2019 Microsoft. All rights reserved. Started: 10:37:24 Error: 2024-02-29 10:37:45.91 Code: 0xC0202009 Source: Package Connection manager "WAST-AGL-DW.DataAcademy.NCCU_Reader" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Unable to complete login process due to delay in opening server connection". End Error Error: 2024-02-29 10:37:45.93 Code: 0xC020801C Source: Data Flow Task OLE DB Source [183] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "WAST-AGL-DW.DataAcademy.NCCU_Reader" 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: 2024-02-29 10:37:45.93 Code: 0xC0047017 Source: Data Flow Task SSIS.Pipeline Description: OLE DB Source failed validation and returned error code 0xC020801C. End Error Error: 2024-02-29 10:37:45.93 Code: 0xC004700C Source: Data Flow Task SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2024-02-29 10:37:45.93 Code: 0xC0024107 Source: Data Flow Task Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 10:37:24 Finished: 10:37:45 Elapsed: 21.437 seconds. The package execution failed. The step failed.
February 29, 2024 at 11:12 am
I don't think that this is a SQL Server or SSIS issue.
The part of the error message which I would focus on is this: Unable to complete login process due to delay in opening server connection
Unfortunately, it seems that this is a somewhat generic message which could be the result of several different issues. But if you Google it, you will see that others have had it and found solutions.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 29, 2024 at 11:24 am
Thank you so much. You have been very helpful.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply