SSIS Package Error in SSMS, works in Visual Studio

Question

SSIS Package Error in SSMS, works in Visual Studio

Isabella Sturm 116

I have a package that has three OLEDB connection managers to three different SQL Servers.
The first server connection is to the server I am deploying on which is SQL Server 2016 (OLTP)
The second server is another SQL Server 2016 (OLAP)
The third server is SQL Server 2008 R2 (this is the source database).

I can run this package without any problems on Visual Studio. I can also run this package without any problems connecting to these production databases from our dev server which hosts a 2016 instance of SQL Server as well.

For some reason, when I run on the production server I get this error for the 2008 R2 server error :

I've checked that TLS 1.2 is enabled. I've tried comparing the differences between the prod and the dev server and database configurations. The main difference I can find is that our dev server is 13.0.4001.0 while prod is 13.0.5865.1 - I wasn't sure there were changes that would have discontinued support to the order SQL Server versions or something - and that the prod is part of high availability cluster/group. I have also quadruple checked that the values I am passing for the connections are correct.

I am really at a loss. Any ideas for why the package wouldn't work on one 2016 instance but does on another?

2 answers

Your answer

Answer 1

ZoeHui-MSFT 41,491

Hi @Isabella Sturm ,

Have you check if the account have the required permissions to make connections or not?

You can check SQL Server Agent’s activity logs, Windows Event logs and SSIS logs to get more details.

You may follow this blog to do some troubleshooting to see if it is helpful.

Regards，

Zoe

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.
Hot issues October

Isabella Sturm 116 Reputation points

2021-10-08T14:41:55.45+00:00

Hi @ZoeHui-MSFT ,

I am using the same connection parameters for all three places I've tested running this package and I am executing as the same user on the SQL Servers.

Also, I did put it in a SQL Agent job because I saw one post that said it worked in SQL Agent but not when Executed manually but it failed in both places for the same reason I posted above

Answer 2

Yitzhak Khabinsky 26,586

Hi @Isabella Sturm ,

The error you are getting seems coming from two things:

TLS 1.2
Cipher Suites

Both of them should match on servers that are communicating with each other.

You can use a free utility IIS Crypto GUI to check both on the problematic server vs. working servers.

Its download link: IISCrypto

**** UPDATE ****
TLS 1.2 Support for SQL Server 2008, 2008 R2, 2012 and 2014

Isabella Sturm 116 Reputation points

2021-10-08T21:37:40.977+00:00

I think this isn't the issue because it works from my dev server and not the prod server but they are configured the same, as far as TLS and Cipher Suites go.
Yitzhak Khabinsky 26,586 Reputation points

2021-10-11T20:31:35.217+00:00

@Isabella Sturm ,

Please check the UPDATE section in my answer.

Share via

SSIS Package Error in SSMS, works in Visual Studio

2 answers

Your answer