question

BrianKudera-2522 avatar image
0 Votes"
BrianKudera-2522 asked BrianKudera-2522 answered

SSIS Not Replacing Variable Name with Variable Value

Every once in awhile our ETL jobs in SSIS fail. The error message looks like this:

Login failed for user 'TEAMASP\SQLAdmin'. Reason: Failed to open the explicitly specified database '$Project::BICfg_Svr'

As you can see, I have a parm at the project level. But SSIS is not pulling in the parm value, and instead is literally trying to connect to a database named what my parm name is?

sql-server-integration-services
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@BrianKudera-2522,

What is your SQL Server/SSIS run-time environment version?
Please issue the following statement, and share its result:

 SELECT @@VERSION;
0 Votes 0 ·

Microsoft SQL Server 2017 (RTM-CU18) (KB4527377) - 14.0.3257.3 (X64)
Nov 16 2019 01:14:50
Copyright (C) 2017 Microsoft Corporation
Enterprise Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)

I should have mentioned that this only happens intermittently so there's got to be a correlation to something else -- high cpu, slow network, etc?

0 Votes 0 ·
BrianKudera-2522 avatar image
0 Votes"
BrianKudera-2522 answered

The consensus is that our SQL Server that produced this error has not been patched since 2019. The suggestion is to patch it to current, and then test the behavior to see if the issue still exists in the latest CU, and then if so, raise the issue.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CarrinWu-MSFT avatar image
0 Votes"
CarrinWu-MSFT answered BrianKudera-2522 commented

Hi @BrianKudera-2522,

Welcome to Microsoft Q&A!

Have you check the SQL Server error log before? And find some error like below. If yes, this error had been fixed by KB4486936.
DateTime Logon Error: 18456, Severity: 14, State: 8.
DateTime Logon Login failed for user 'UserName'. Reason: Password did not match that for the login provided. [CLIENT: <LocalMachine>]


Best regards,
Carrin


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Yes, I checked the SQL Server error log. The error from the log is what I pasted when I opened this Q&A.
The error you pasted is a different error that we are not receiving.

0 Votes 0 ·

Hi @BrianKudera-2522, I read the discussion about you and YitzhakKhabinsky-0887. Good to know that you are going to update the SQL Server. Could you please do "Accept Answer" for this thread? By doing so, it will benefit for community members who have this similar issue. Your contribution is highly appreciated. Thank you!

0 Votes 0 ·

The only two answers refer to KB4486936, which is not the correct answer and is completely unrelated to my question. Marking this as answered will mislead other community members with the same issue as mine.

0 Votes 0 ·
YitzhakKhabinsky-0887 avatar image
0 Votes"
YitzhakKhabinsky-0887 answered BrianKudera-2522 commented

Hi @BrianKudera-2522,

This bug was already fixed by Microsoft.
As @CarrinWu-MSFT mentioned, it is documented here: kb4486936-fix-login-failed-for-user-error-occurs-when-you-run-maintenance-plan-with-sql-login-account-in-sql-server

Your current CU18 is dated back to 2019.
Please update your SQL Server/SSIS run-time environment with the latest CU, which is CU24.
You can see it here: Microsoft SQL Server Versions List


· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

That KB is unrelated to this issue. Different error messages, and we're not using a sql login account.

0 Votes 0 ·

@BrianKudera-2522,

Still, you instance was last patched in 2019.
It is a good idea to install the latest CU.

0 Votes 0 ·

Thanks for the input @YitzhakKhabinsky-0887
Running a production farm with 90 servers makes it a bit hard to ask infrastructure to install the latest CU because it seems like a good idea.
If this was fixed in a patch then there should be a KB.

0 Votes 0 ·
Show more comments