DontSaveSensitive protection level in SSIS

It means this is not going to encrypt anything in the package and so our sensitive information would be blank like password or configuration manager. We would have to then supply our password using SSIS configuration.

Click here to read Package configuration in SSIS

http://bageshkumarbagi-msbi.blogspot.com/2014/12/package-configuration.html

Click here to read: Package Parameters and Project parameters in SSIS

http://bageshkumarbagi-msbi.blogspot.com/2016/07/package-parameters-and-project.html

Let’s see the example

Here I am taking the data flow task and my aim is to extract the data in the flat file.

Taking data flow task

    

In dataflow pan, I am taking OLEDB source

Creating a connection

   

Using sql server authentication. Click ok.

Select the source

     

Click ok.

Now we need to set the protection level to DontSaveSensitive on the package.

Click on the package property

 

Set Protection level property to DontSaveSensitive.

After that, we need to set the protection level on the package level.

  

And click on the properties

   

Click ok.

Now I am taking destination flat file.

    

Now the package is ready to run.

Running the package.

Ooooooooooo my package get failed

  

Let’s see the reason why my package gets failed.

To see the error go to progress tab

    

Error see that login failed.

[OLE DB Source [13]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. 

The AcquireConnection method call to the connection manager "DESKTOP-SGHGLJT.AdventureWorks2012.bagi" failed with error code 0xC0202009. 

There may be error messages posted before this with more information on why the AcquireConnection method call failed.

[Connection manager "DESKTOP-SGHGLJT.AdventureWorks2012.bagi"] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E4D.

An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80040E4D  Description: "Login failed for user 'bagi'.".

Now see the OLEDB connection manager

Password is blank

Although I save the password here showing the blank.

Let’s see the package source code.

Here also password is blank.

If we set protection level DontSaveSensitive then the package will not store sensitive information. This is the reason our package failed.