hi,

In terms of getting the output of the DTEXEC command onto a file how it is done depends on how you execute it

- if executed from a SQL Server agent job integration services step then you need to

define a output file on the step itself and the output will go to the desired file.

- if you execute it from a command line (and powershell for this matter is a command line) using the dtexec.exe command then you just redirect the output to a file as mentioned before with the normal redirector operator.

- if executing a package on the SSISDB catalog then there won't be any meaningful output and probably you can ignore this. Reason for this is that all process flow gets logged onto a sql server table which can be queried directly

now making the output a CSV file that can not be done.

Only way is to have a parser that splits this file onto the individual components (I have one that can supply if asked).

Issue with this is that the output does not tell you which .dts package the output is from

for example suppose you have a master package that calls 2 other packages, both to process a cube

if the component names do not have a identifier of the steps that include the package name then the output could be something like this

Source: ProcessSSAS_Cube

Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution ...

End Warning

Source: ProcessSSAS_Cube

Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution ...

End Warning

So you will not know which block refers to which package.

As for internal SSIS logging, needs to be configured and again in order to identify which package it relates to fields sourceID and executionID must be included on the logs. This logging can be made to several destinations - if done to a file again it needs to be parsed as it is not a fully compliant CSV file (I also have a parser). Best is to log it to a SQL Table - but in this case part of the information may get lost if greater than 4k I think.

If you are using SQL 2012+ the best way to get full logging without much work is to run the packages on SSISDB Catalog. This raises other issues though mainly related to configuration files/parameters and how it is executed. Personally I do not like the lack of automation that I can have with filesystem packages.