Hi All, I have few questions about SSIS logging . Below is my scenaario: 1.I have configured my ssis package to log to a text file on post execute events. When manually running the package on the sql 2008 dev server using execute pkg utility, the package logs all the detailed information about each task in the package on an postexecute event. But when it runs as a sql server agent job, the package does not log the information about each task in the package. It just log package start and end. Why is it not logging about each task? 2.In package properties tab, under security/protectionlevel we have a default setting as “EncryptSensitive with user Key”. Do we have to change the default value to “DontSaveSensitive” before deploying on to the dev/ prod server? I did not change this property, but the package is running fine on the server. But is it causing the logging issue with the sql server agent?

You should not deploy with "EncryptSensitive with user Key". If you did, my expectation the package will fail if you used secure/password protected connections in it. The "Do not save sensitive" does not make sense either because a message box will pop up asking for a password. So make the right arrangements as discussed here: http://www.sqlshare.com/introduction-to-deploying-ssis-packages_28.aspx and re-deploy then see if the package now works as designed.Arthur My Blog

HI Arthur, I am not encrypting my package with any password. I had used "EncryptSensitivewithUserKey"(which was by defualt) and I have successfully deployed the package on the dev server and executed it as a SQL Agent Job. It did not give me any error as it was only me who has executed it. May be if other user execute it it will give an error. So, do we need to change the default value of "EncryptSensitivewithUserKey" every time we create a new package or it does not matter if I am not encrypting the pkg with a password. In my packages, I do connect to SQl server database using windows authentication to execute a set of stored procedure. Please let me know what i need to do in such a situaion with this default value. Thanks a lot.

It is recommended to don't use EncryptSensitiveWithUserKey, and as answer to your question: Sensitive Data in SSIS are : The password part of a connection string. However, if you select an option that encrypts everything, the whole connection string will be considered sensitive. The task-generated XML nodes that are tagged as sensitive. The tagging of XML nodes is controlled by Integration Services and cannot by changed by users. Any variable that is marked as sensitive. The marking of variables is controlled by Integration Services. as you see this is not just connection passwords, you better to use sensitive data encrypted ( for example with password )http://www.rad.pasfu.com

Hi Reza, Thank you for the rpely. I have a question, if I use windows integrated security will the password be considered as sensitive? This is because I am currently using windows integrated security and I dont see the password as sensitive in the xml code of the package. Please let me knowwwhich protection level is generally preferred to be used?

Hi Reza, Thank you for the rpely. I have a question, if I use windows integrated security will the password be considered as sensitive? This is because I am currently using windows integrated security and I dont see the password as sensitive in the xml code of the package. Please let me knowwwhich protection level is generally preferred to be used? There is no password stored when the connection string is using the Windows Integrated security. How you encrypt/protect the package depends on whether you run it using the Agent off the MSDB/Package Store - in this case use RelyOnServer setting, and if you run it off a file system then it depends if you want to conceal the entire package design (e.g. when there is a potential when a package can be inadvertently modified) or you only want to hid the passwords or make it impossible to connect to a data source/target - use EncryptSensitiveWithPassword then.Arthur My Blog

HI Arthur, Here is what I have done so far. In my package I am only connecting to SQL server database using windows integrated security. I changed the protection level to "EncryptSensitivewithPassword" and then I have deployed it to File system. I Executed my package using the Sql Agent Job, but it didnot prompt for any password? Is it because it is windows integrated security and there is no sensitive data being stored? In such a case is it fine if we just use the deafult for the package( encryptsensitivewithuserkey). Also as suggested, I changed the protection level to "EncryptSensitivewithPassword" and ran using agent job, I checked the log file but I do not see any "onpostexecuteevents" being logged (i do see them when I run the pkg manually). I only see the package start and package end events being logged when using the sql agent job to run the package?

How did you set up the logging? My thinking the account used to run the Agent's job has no right to write to that directory.Arthur My Blog

I have setup logging to a text file. Changing the protectionlevel did not help. When I run the packages manually, it logs all the postexecute events. But using SQL Agent, it is able to create a file and also log the package start and package end but doesnot not log any post execute events. I used the servcice-sql account to run the package using SQL Agent. I also thought that it could be a permission issue, but realized it is not the permission issue as it is able to create a file in to the directory and log package start and package end into the log file but not the "post execute events". Do I need to select any options in BIDS so that the SQL agent logs the detailed information? Thanks a lot.

Is the incomplete logging of SSIS task using sql agent a bug in SQl 2008? I have found a similar information here. Has anyone tried the workaround for it? https://connect.microsoft.com/SQLServer/feedback/details/491508/logging-of-ssis-tasks-are-incomplete-when-run-by-sql-agent-job