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.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • 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

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • 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.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • February 29, 2024 at 11:24 am

    #4386981

    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