August 29, 2016 at 12:02 pm
Right now, the output of my script looks this:
Microsoft (R) SQL Server Execute Package Utility
Version 11.0.6020.0 for 32-bit
Copyright (C) Microsoft Corporation. All rights reserved.
Started: 11:07:26 AM
Error: 2016-08-26 11:07:27.14
Code: 0xC1140004
Source: Process Item Sales Data Pond Cube Analysis Services Execute DDL Task
Description: Either the database with the ID of 'Item Sales Data Pond' does not exist in the server with the ID of 'COMISDBDEV01\OPREPORTINGDEV', or the user does not have permissions to access the object.
End Error
Warning: 2016-08-26 11:07:27.14
Code: 0x80019002
Source: ProcessSSAS_Cube
Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
End Warning
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 11:07:26 AM
Finished: 11:07:27 AM
Elapsed: 0.297 seconds
But I want this same information to be in a .CSV format. How would I do that?
August 29, 2016 at 12:16 pm
You need to add logging to your package and define a file destination for the log.
August 29, 2016 at 2:42 pm
Hi,
I do not understand the idea behind exporting that output to a Comma Separated Values file type, I will not look behind this requirement, I will just provide you with the answer.
I suppose you are running a SSIS Package using DTEXEC, when running applications from the command line you can redirect the stdout stream to a file.
e.g. C:\DTEXEC -f test.dtsx > C:\folder\output.csv
This will create the output.csv file under the C:\folder folder containing all the information what would appear if you have run the application as you were doing it.
In my opinion, this would be the easiest approach to what you are trying to achieve - no PoSh required.
August 29, 2016 at 4:31 pm
Thanks, here is some more information.
We are trying to run an SSIS package that processes an SSAS Tabular model. Also, the SSIS package calls a dtsconfig file. Can the command you presented support all of that?
August 30, 2016 at 5:14 am
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.
August 30, 2016 at 7:49 am
Nice post, Frederico. Good points, well made.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 30, 2016 at 12:43 pm
The company wants to use Control-M, which is our standard scheduler. Control-M is Unix based. Here are the planned steps:
1. Control-M calls a PowerShell script.
2. PowerShell script calls SSIS package.
3. SSIS package processes SSAS Tabular model.
4. Status of steps 2 and 3 somehow go to a log file AND get reported back to Control-M.
A .CSV isn't totally necessary per se. However, the log file does need to be parse-able.
I created a PowerShell script that can take some parameters, call the SSIS package, tell the SSIS package which dtsconfig file to use, and what SSAS Tabular model to process. However, your approach looks a LOT cleaner and more efficient. I'll give it a try.
Thank you so much!
August 30, 2016 at 1:25 pm
By the way, I have three questions about the code
C:\DTEXEC -f test.dtsx > C:\folder\output.csv
1. PowerShell won't allow ">". Is that a typo?
2. How do I pass the dtsconfig file into the statement?
3. Why does this statement use "-f" instead of "/file"?
August 30, 2016 at 1:42 pm
imani_technology (8/30/2016)
By the way, I have three questions about the codeC:\DTEXEC -f test.dtsx > C:\folder\output.csv
1. PowerShell won't allow ">". Is that a typo?
2. How do I pass the dtsconfig file into the statement?
3. Why does this statement use "-f" instead of "/file"?
-f or /file are the same (you did read the manual didn't you?)
passing the dtsconfig file is also a parameter of the dtexec command
as for output redirection.
dtexec ... |Tee-Object mylog.log
dtexec ... |out-file mylog.log
standard Powershell redirections (again in the manuals although I agree these are a lot harder to read)
August 30, 2016 at 2:14 pm
imani_technology (8/30/2016)
By the way, I have three questions about the codeC:\DTEXEC -f test.dtsx > C:\folder\output.csv
1. PowerShell won't allow ">". Is that a typo?
2. How do I pass the dtsconfig file into the statement?
3. Why does this statement use "-f" instead of "/file"?
User frederico_fonseca has provided us with some valuable information in the post ABOVE, I would just like to add some annotations to your 1st question, the > symbol will work just fine, all commands that are valid in the old command line processor are supported in PoSh, in order to provide you with some directions regarding this topic, open a new PowerShell console and type this:
Get-Help about_Redirection
I am not sure if you have heard about the "about topics" in PowerShell, if you want to gather more knowledge in PoSh, I recommend you to read about it. (More about the About Topics HERE)
As you run that command you will get a lot of information, but the most important is this:
The Windows PowerShell redirection operators are as follows.
Operator Description Example
-------- ---------------------- ------------------------------
> Sends output to the Get-Process > Process.txt
specified file.
>> Appends the output to dir *.ps1 >> Scripts.txt
the contents of the
specified file.
Regards.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply