Hello all,
I am struggling around defining a logging mechanism for my packages. I have 2 questions concerning that matter:
I have used event handlers for my loggings (as defined here: http://blogs.conchango.com/jamiethomson/archive/2005/06/11/1593.aspx ), but the problem is with packages that failed validation. I cannot find log entry for these cases since no "onerror event" doesn't trigger (for instance when the table I'm loading to doesn't exsist).
And the second question: many of my packages are executed using execute process task (using dtexec command line). I am trying to capture the result of the execution as a log file by using the ">" in the command line in order to output the execution to a log file in the following format:
dtexec /FILE "MyPackage.dtsx" > " MyPackageLog.log"
This works fine when executed by myself but when using the Execute Process task (defined: Executable: DTExec.exe, Arguments: /FILE "MyPackage.dtsx" > " MyPackageLog.log") I get execution error…
Thanks,
Liran
 Liran R wrote: |
|
| ... I am trying to capture the result of the execution as a log file by using the ">" in the command line in order to output the execution to a log file in the following format:
dtexec /FILE "MyPackage.dtsx" > " MyPackageLog.log"
This works fine when executed by myself but when using the Execute Process task (defined: Executable: DTExec.exe, Arguments: /FILE "MyPackage.dtsx" > " MyPackageLog.log") I get execution error… |
Regarding the second issue: output redirection (">") is feature of command shell (cmd.exe). So it works "when executed by myself" because it is executed by cmd.exe, not by itself - cmd.exe intercepts ">" symbol and does the redirection.
When you specify DTExec.exe as your executable you don't get this feature, ">" is simply passed as argument to DTExec.exe and DTExec reports an error as it does not recognize it as valid argument. You need to specify cmd.exe as executable and use /C DTEXEC.EXE /F "MyPackage.dtsx" >MyLog.log as arguments.
|||
Thanks.
Does anybody else misses the DTS2000 Logging feature, or is it just me...
|||
Why not use the Execute Package tasks. All events will get passed up to the parent.
I guess your error is prior to execution, so before logging starts.
For the Execute Process task, could you not capture what you want via the StandardOutput/Error variables?
|||
I cannot use the Execute Package task because I would like to create the source where the package is dynamic. By using the execute process task, I'm passing the package path as a variable in the dtexec utility args.
By using the Execute Package task, I will need to create a connection manager to every package.
|||
| Liran R wrote: |
|
| I cannot use the Execute Package task because I would like to create the source where the package is dynamic. By using the execute process task, I'm passing the package path as a variable in the dtexec utility args. By using the Execute Package task, I will need to create a connection manager to every package. |
You can do the same with the Execute Package Task i.e. Make it dynamic.
You do this using expressions. This may help:
Setting expressions
(http://blogs.conchango.com/jamiethomson/archive/2006/03/11/3063.aspx)
In answer to your other earlier question: No, I don't miss DTS logging at all. It was cumbersome, incomplete and unextensible to the extreme.
-Jamie
|||
| Liran R wrote: |
|
| I cannot use the Execute Package task because I would like to create the source where the package is dynamic. By using the execute process task, I'm passing the package path as a variable in the dtexec utility args. By using the Execute Package task, I will need to create a connection manager to every package. |
Besides to expressions I would look at package configurations as well.
Rafael Salas
|||
| Jamie Thomson wrote: |
|
| Liran R wrote: | | | | I cannot use the Execute Package task because I would like to create the source where the package is dynamic. By using the execute process task, I'm passing the package path as a variable in the dtexec utility args. By using the Execute Package task, I will need to create a connection manager to every package. | |
You can do the same with the Execute Package Task i.e. Make it dynamic.
You do this using expressions. This may help:
Setting expressions
(http://blogs.conchango.com/jamiethomson/archive/2006/03/11/3063.aspx)
In answer to your other earlier question: No, I don't miss DTS logging at all. It was cumbersome, incomplete and unextensible to the extreme.
-Jamie
Yes, I thought about that, but the problem is I want to execute my packages in parallel, not in a loop.
|||
| Liran R wrote: |
|
| Jamie Thomson wrote: | | | | Liran R wrote: | | | | I cannot use the Execute Package task because I would like to create the source where the package is dynamic. By using the execute process task, I'm passing the package path as a variable in the dtexec utility args. By using the Execute Package task, I will need to create a connection manager to every package. | | You can do the same with the Execute Package Task i.e. Make it dynamic. You do this using expressions. This may help: Setting expressions
(http://blogs.conchango.com/jamiethomson/archive/2006/03/11/3063.aspx) In answer to your other earlier question: No, I don't miss DTS logging at all. It was cumbersome, incomplete and unextensible to the extreme. -Jamie |
Yes, I thought about that, but the problem is I want to execute my packages in parallel, not in a loop.
So what? All I'm saying is to use an Execute Package Task rather than an Execute Process Task. I'm not advocating use ofa Foreach loop.
-Jamie
|||
I must understand something:
If I have many Execute Package tasks running in Parrallel, all using the same connection manager (to a specific package file) how can I use a variable to set the connection string using expression if I'm not using a loop container?
Thanks,
Liran
|||
If using the Execute Package Task, how does one pass additional command line values for the package to be executed?
I'm calling my child packges that reside in FileSystem (folder) dynamically but I need to pass them the /CONFIGFILE so that they know where to grab the dtsConfig file. I can't hardcode the dtsConfig because the location of the file/folder is different from environment to env.
These are so far my findings:
1- I can use Parent Configuration Variable in the child but I didn't see an option where I can specify the dtsConfig itself and rather have to create one variable for different config values that I have in my XML.
2- Hardcode the dtsConfig location and force the DBA to let me have a fixed folder for all the environments
3- Instead of using File System, switch to SQL Server and use the manifest to install the child packages and therefore inherit the dtsConfig from the manifest. Change the parent to use SQL Server instead of file system. Limits me now to install every time instead of just having the dba to drop/replace the dtsx in a folder
4- Use alternative methods such as DOS Batch to call dtexec, SQL Agent, SQL stored proc, etc.
5- other workarounds but it start to get out of the child/parent solution where it would great to run the child by itself when needed and also have the parent run the child without touching any code.
I searched your blog as well as others for a solution to pass the command line via Execute Package task and I posted a separate thread with no response.
I've got a little closer by having my Connection Manager concatenating the /CONFIGFILE for my dynamic PackageToRun variable but I get a run time error that either the filename, directory name or volume lable syntax is incorrect. Both dtsx and dtsConfig are in the same folder and the syntax/string looks good (except perhaps maybe it doesn't like the double quotes).
Any suggestions? With so many people advocating Execute Package Task's flexibility and dtsConfig Files (in general), I don't see any solutions to my problem without a big workaround.
Anatole
MohitGupta wrote:
