November 30, 2018 at 2:53 am
Hello there,
I hope you can help me to resolve my problem.
I am running a Windows Server 2016 Standard operating system with Microsoft SQL Server Management Studio 17 where the SSIS-Package runs daily. It contains a password. The DTSX-Data are developed in Microsoft Visual Studio 17 and also contains a Password. The DTSX is included in the package as file source.
When I debug the DTSX in visual 17, it succeeds. However, when I start the SSIS package, the following error is thrown:
"
Executed as user: AB\User. Microsoft (R) SQL Server Execute Package Utility Version 13.0.5026.0 for 32-bit Copyright (C) 2016 Microsoft. All rights reserved. Started: 03:00:01 Error: 2018-11-29 03:00:23.35 Code: 0x00000001 Source: Read Attachment Content Description: Exception has been thrown by the target of an invocation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 03:00:01 Finished: 03:00:23 Elapsed: 21.734 seconds. The package execution failed. The step failed.
"
I think it's a permission error, but I don't know where to look at. I have set the protection level of the Project properties to EncryptSensitiveWithPassword. In the control flow I have set the security properties to EncryptSensitiveWithPassword too. Everywhere the same Password is used. When I setting up the SSIS package I need to enter the password, wich I have distributed in visual 17. So far so good. I think... But now I have no idea to set the right permission. Can you help me?
If I need to give more information, say so...
Thank you in advance.
KR
ArneWW
December 3, 2018 at 11:59 am
How are you deploying the package to the server?
You should implement the Integration Services Catalog - and deploy your packages to the catalog. The packages can then be configured in the catalog - or the agent job - with the appropriate credentials and you don't have to worry about what is provided in the package itself.
If you are using package deployment - and deploying to a file share then you will run into these kinds of issues where you have to make sure the credentials are stored securely in a config file or table and populated at run time from the config file or table.
If you are deploying to either MSDB or the SSIS Package Store - then you can change the option when deploying to save the credentials securely in the database, but if you are going to do that it would be much easier to switch to project deployment and deploy to the catalog.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 4, 2018 at 6:25 am
Jeffrey Williams 3188 - Monday, December 3, 2018 11:59 AMHow are you deploying the package to the server?You should implement the Integration Services Catalog - and deploy your packages to the catalog. The packages can then be configured in the catalog - or the agent job - with the appropriate credentials and you don't have to worry about what is provided in the package itself.
If you are using package deployment - and deploying to a file share then you will run into these kinds of issues where you have to make sure the credentials are stored securely in a config file or table and populated at run time from the config file or table.
If you are deploying to either MSDB or the SSIS Package Store - then you can change the option when deploying to save the credentials securely in the database, but if you are going to do that it would be much easier to switch to project deployment and deploy to the catalog.
Hey Jeffrey Williams 3188,
I did what you wrote. It works! Thank you very much. I implemented the Integration Service Catalog and deployed the dtsx-file to the catalog. After that I created a job in which the Integration Services Catalogs-Package was implemented by me. If I click on "Start Job at Step..." it succeeded.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply