You’ve just completed building your SSIS package; your hopes are set high on your newly created project becoming the company's number one tool for data building, organization and formatting. Surely that unreachable career tier level will finally be reached with the successful execution of just this one package within SSIS. You save your work and immediately execute the newly created package just to be greeted by a series of ugly error messages.
You first see this error message detailing a Build related problem:
After you’ve read the message you decide to select “Yes” because you’re seeking to resolve the issue by running the last successful build; surely that would correct the issue! Unfortunately, without delay another error message immediately is generated:
Through the flames in your eyes and the smoke billowing from your ears, you’re able to focus long enough to read the new devil of an error message and see that it suggests building the project and trying again.
So, you clinch your mouse a little tighter and Select the “Build” tab at the top of your Visual Studios environment and select your solution from the drop down.
Surely, this time, the issue will be addressed and resolved! Maybe this time you will be greeted with applause and a celebratory song…Your package stutters, skips, almost as if it’s about to work! Your heart starts pounding and your suspense is greeted with this:
You’ve spent the past week building and engineering a way to make complex information more accessible for those in your company. Your gleeful demeanor and the “gung-ho” attitude with which you attacked the project seems all for naught.
After a quick burst of disappointment and frustration you decide to troubleshoot the package; to no avail. You try closing and reopening SSDT; yet, no such luck. Finally, after several unsuccessful attempts you throw in the towel for the day and promise yourself you will revisit the issue at a later time.
How to Properly Execute Your SSIS Package
The problem explained above is a VERY common problem within SSDT. If there is a “Build error” and you’re confident the setup of your package is completely, 100% correct, consider investigating the Security levels of your package and project. When working with a component that requires a secure connection type (i.e. SFTP, SalesForce Connection Manager, CRM Connection Manager, etc… or anything with which a Username and Password is required) the Protection Level must be set appropriately.
Details on how to set the appropriate Protection Level will be explained below:
Click anywhere within the design pane of the “Control Flow” and open the “Properties” tab to begin editing.
You will notice your “ProtectionLevel” is set to ‘Don’tSaveSensitive’. Believe it or not, this is the very reason behind the failure of your package. Why? Well, when saving sensitive information (i.e. Usernames, passwords, encrypted and compressed files) SSIS will not allow the user to simply save the information for the world to see; no, it wants to keep your information safe and requires a password level protection be set.
Select the drop down in the “ProtectionLevel” section and select “EncryptSensitiveWithPassword”. This will allow you to set a password at the package and project level. By following the directions below, your package will successfully execute. Let’s get started!
Select “EncryptSensitiveWithPassword” from the “ProtectionLevel” drop down:
After setting the encryption level, set the password you would like to use for access to your package by clicking on the ellipses button in the “PackagePassword” field, create and confirm password and select “OK”:
Now that we’ve changed your Package Protection Level, we will need to set the Project Level Protection. To accomplish this, simply right click on the Project Level within your Solution Explorer and select “Properties.” NOTE: There is another section under the properties tab seen below that states, “Convert to Package Deployment Model”, if your package is not set to project level deployment you will want to select this option and convert the Project level deployment model:
After selecting “Properties” a property page will appear, allowing the user to further set the ProjectLevel security. Set this protection level to “EncryptSensitiveWithPassword” and click on the ellipses button:
After Selecting the ellipses button, set the Project Protection Level to “Encrypt sensitive data with password” and set the same password you set for your package level; Click “OK”.
Click on Apply and select “OK” when the caution box appears indicating the ProtectionLevel of the project has changed. Click on “OK” and again select “OK” when the same caution box appears.
Save your package and try to execute once again. The error message should now be gone and the package should execute successfully!
Still having issues executing your package? Submit your questions in the comments section below for Ken to help your package.
-1.png)
Leave a comment