Post reply

Transferring data from one secure server to another -SSIS/SQL Server Agent Jobs

  • February 28, 2024 at 2:08 pm

    Go to Answer

    #4386399

    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.

     

    1. When I go back into SSIS/Visual Studio, I often find I have to re-enter the password of the secure website in the connection manager box because the data flow connection no longer seems to be working, even though I have clicked SAVE PASSWORD.
    2. I have set the job up in SQL Server Agent so that it runs the package automatically.  But the job always fails, which I assume is it to with this external password in the server, because the error message says something about not being able to login as USERX . I am assuming this may be related to issue 1 above if the package seems to have a problem when I go back into it.

    any ideas on how to solve this please?

    Happy to post screen shots or details of errors if need be.

     

    thank you so much

     

     

  • February 28, 2024 at 2:18 pm

    Answer

    #4386403

    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.

  • February 28, 2024 at 2:21 pm

    #4386404

    thank you. How do I do that please?

     

  • February 28, 2024 at 2:48 pm

    #4386413

    Please have a read of the following and post back with any questions:

    https://www.sqlservercentral.com/steps/ssis-catalog-environments-step-20-of-the-stairway-to-integration-services

  • February 29, 2024 at 10:41 am

    #4386956

    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

    #4386979

    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.

  • February 29, 2024 at 11:24 am

    #4386981

    Thank you so much. You have been very helpful.

     

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply