it always prompt the path is not valid and error is 0x80070057
below is what i input
dtexec /dts c:\ssis\***\***.dtsx
what's wrong? thanks
dtexec /f "C:\ssis\**.dtsx" can working
so sorry
|||try this, it should generate a log for you so you can view detailed errors:
dtexec /f "C:\SSIS\YourPackage.dtsx">>C:\SSIS\YourPackage.LOG
hi!
i am trying to execute through command line the following:\
dtexecui /SQL "\ArchiveMain" /SERVER "SE413695\AASQL2005" /WARNASERROR /MAXCONCURRENT " -1 " /CHECKPOINTING OFF
I am actaully creating an archiving package.
1. All it does is get some data from the Table.
2. creating directory and set usedirectoryifexist = true
shows some warning when i run that directory already exists cos i don't want to create new one i want to use the existing one.
3. actual archiving to get call to another package for archiving - warning while running the package that parent variable is missing
The pacakge seems to run while i execute it thorugh command prompt but i get this message
DTExec: The package execution returned DTSER_SUCCESS (0) but had warnings, with
warnings being treated as errors!
Thanks,
Jas
Your description in line 2 indicates you are creating and setting the directory, is that correct? I see the set command but I don't see the create command. If you are using both a create and set command you should remove the create command because the directory already exists, and then try running it.
You could also include some sort of "if not exist then create" command for your directory in case it was accidentally deleted somehow, then it would be recreated before being set.
Hi there.
I'd like to call dtexec with something like this:
dtexec /f myPackage.dtsx /Set \package.variables[User::connStr].Value;Source=localhost;Provider=blah;Integrated Security=SSPI;
I get an error along the lines of
Option "Source=localhost;Provider=blah;Integrated" is not valid".
How do I pass in a property containing spaces? I've tried all of the usual quote-encasing patterns I can think of.
Thanks,
Jon
JonB_QRM wrote:
Hi there.
I'd like to call dtexec with something like this:
dtexec /f myPackage.dtsx /Set \package.variables[User::connStr].Value;Source=localhost;Provider=blah;Integrated Security=SSPI;
I get an error along the lines of
Option "Source=localhost;Provider=blah;Integrated" is not valid".
How do I pass in a property containing spaces? I've tried all of the usual quote-encasing patterns I can think of.
Thanks,
Jon
Double quotes.
dtexec /f myPackage.dtsx /Set \package.variables[User::connStr].Value;"Source=localhost;Provider=blah;Integrated Security=SSPI;"|||
Thanks for getting back so quickly. Your suggestion worked for a date string passed in as a property, but not a connection string.
When I run
dtexec /f package.dtsx /Set \package.variables[User::effectiveDate].Value;"2007-04-30 09:00" /Set \package.variables[User::connStr].Value;"Data Source=localhost\2005;Initial Catalog=QRMDB1_MRKT_SVC;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;"
I get the following complaint:
Argument ""\package.variables[User::connStr].Value;Data Source=localhost\2005;Initial Catalog=QRMDB1_MRKT_SVC;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;"" for option "set" is not valid
It doesn't complain about the quotes around the date value.
Thoughts?
|||As an aside, if I write these props to a file and specify it using the /Com <filename> switch, dtexec takes them.|||you may need a backslash to escape the double quotes.
Try this:
/Set \package.variables[User::connStr].Value;\"Data Source=localhost\2005;Initial Catalog=QRMDB1_MRKT_SVC;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;\"
In my previous post here: https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1044739&SiteID=1 Michael Entin provides a number of responses to my questions regarding programatic execution of remote SSIS packages.
Having experienced some significant reliability problems with the Microsoft.SqlServer.Dts.Runtime components from an ASP.NET process (the page either times out, or inevitably just stops responding), I have been prototyping the DTExec command option which Michael suggests as being a better approach to remote programability.
So, off I've been prototyping this all day today...
I have a stored procedure that wraps a call to xp_cmdshell which takes the DTS (DTEXEC) params as a big long argument. This scenario would hopefully allow me to call the sproc from an ASP.NET application.
The proc is deployed to a SQL 2005 machine running SSIS (which I now understand is a REQUIREMENT for targetting SSIS "remotely"). The package targets a seperate SQL 2000 machine and includes two connection managers which are set to use SSPI. I use configuration option files to allow for configurable connection manager target/sources.
In this scenario, it does not seem that the DTEXEC command runs in the same context as the caller. and as a result, a peculiar account called MACHINENAME$ is used (where MachineName is literally the name of the SQL 2005 machine). The account authentication fails (obviously) when the package tries to establish a connection to any of the connection managers because MACHINENAME$ does not exist on the connection manager servers.
Based on the following excerpt from the MSDN doc on xp_cmdshell, it would seem that MACHINENAME$ is probably the LOCAL SYSTEM, which is the process tha the SQL Server Service is running under:
When xp_cmdshell is invoked by a user who is a member of the sysadmin fixed server role, xp_cmdshell will be executed under the security context in which the SQL Server service is running. When the user is not a member of the sysadmin group, xp_cmdshell will impersonate the SQL Server Agent proxy account, which is specified using xp_sqlagent_proxy_account. If the proxy account is not available, xp_cmdshell will fail. This is true only for Microsoft? Windows NT? 4.0 and Windows 2000. On Windows 9.x, there is no impersonation and xp_cmdshell is always executed under the security context of the Windows 9.x user who started SQL Server.
Obviously, settting the ENTIRE SQL Server service to run as a fixed account or even a domain account is probably not appropriate for client sites. Any opinion to the contrary is welcome.
In reading Kirk Haselden's walkthrough for setting up a SQL Agent Proxy, this seems incredibly involved. Before I go through this exercise, can anyone validate that this is the way to go for doing SSPI?
A work-around is to use SQL Server Auth for the connection managers and use configuration files to try to obfuscate these details, but my preference would be SSPI/Windows Integrated.
Thanks.
SSIS was designed to interact with SQL Server Agent because it permits packages to be executed within a very secure environment. There are a variety stored procedures specific to SQL Server Agent.
I strongly recommend using SQL Server Agent stored procedures to execute SSIS packages programatically.
|||Thanks.
Is sp_start_job the way to go then? Will this work for packages stored on the file system or package store, or only MSDB?
Also, do SQL Server Agent proxies have to be setup?
Any additional direction appreciated.
Thanks,
Rick
|||Follow up- I got the agent proxy and credentials configured on my local dev machine and can call the job using sp_start_job, but it appears that the proc works asynchronously.
Is there anyway to either block or determine the progress or outcome of the job?
Thanks,
Rick
|||Alerts can be defined to fire when a job starts, is in progress, ends, etc. Then, the sp_add_notification stored procedure can be used to configure the notification type of the alert.Assert.True wrote:
Follow up- I got the agent proxy and credentials configured on my local dev machine and can call the job using sp_start_job, but it appears that the proc works asynchronously.
Is there anyway to either block or determine the progress or outcome of the job?
Thanks,
Rick
To run a legacy DTS package, continue to use dtsrun, which is also installed with SQL Server 2005. Dtexec has no provision for running a 2000 package.
Thanks,
jkh
This application starts a database job in SQL Server, which in turn starts a dtsx package. This package loops over the contents of a table and starts the packages that are marked to be executed.
This all work well most of the time. Sometimes though, DTExec, the process running the packages, just stops running. There are no entries in the event viewer or anywhere else.
I run Windows Server 2003 with SQL Server 2005 SE SP1. The packages are run from the filesystem, not the database.
Has anyone encountered the same problem and know a solution? I can't find any info on the internet.
- Matthijs
Do you have package logging turned on? Enable all of the logging events and see if you can catch something that way. Log to a text file or to SQL Server.|||No logging was active. I'll turn it on and see if the problem occurs again. One of the problems is that it stops randomly so it's not easily reproducable.
Thanks for the quick response. I'll post again when it happens again.
- Matthijs
|||The problem occured again. I checked the log and the last thing in there is the OnPostValidate event of one of the script tasks. Again no error in the windows eventlogger and nop apperant reason why it stopped.
There have been some other problems though. It have been brought to my attention that the server on which the process runs has the tendancy to overheat. It gives a message that it will be shut down due to overheating or the loss of cooling and right after that a message that shutdown was cancelled by the administrator.
Because of this it occured to me that the problems might come from damaged memory. I'm going to have the client's IT support department check the systems memory and see where that gets me. Do you think that this is a possiblity? I would say that when memory is damaged it should't effect just one process, but that other processes would suffer from the same damage. This does not seem to be the case however. So it's a longshot but i'll have them check it anyway.
- Matthijs
|||
If the fault is suspected with DTEXEC, package logging may not help, but the console output from DTEEXEC itself may be of use. Ensure you have set the job step log, in teh Advanced tab of the job step. If DTEXEC itself errors I would expect this to capture it.
If a shutdown started and was then cancelled, it could have started to kill off processes? If it is running hot then you could get all sorts of strange corruptions, but they would probably not manifest themselves under normal conditions.
|||The cancelled shutdown scenario has not happened lately. That was mostly last summer. I thought it might be result of the damage done back then. I will enable logging in the job step and wait until it happens again.- Matthijs
|||There seems to be no logging option in the advanced tab of the job step. In the general tab there is a loggin tab, but i think this just logs the package log entries, just like the package itself does.
This application starts a database job in SQL Server, which in turn starts a dtsx package. This package loops over the contents of a table and starts the packages that are marked to be executed.
This all work well most of the time. Sometimes though, DTExec, the process running the packages, just stops running. There are no entries in the event viewer or anywhere else.
I run Windows Server 2003 with SQL Server 2005 SE SP1. The packages are run from the filesystem, not the database.
Has anyone encountered the same problem and know a solution? I can't find any info on the internet.
- Matthijs
Do you have package logging turned on? Enable all of the logging events and see if you can catch something that way. Log to a text file or to SQL Server.|||No logging was active. I'll turn it on and see if the problem occurs again. One of the problems is that it stops randomly so it's not easily reproducable.
Thanks for the quick response. I'll post again when it happens again.
- Matthijs
|||The problem occured again. I checked the log and the last thing in there is the OnPostValidate event of one of the script tasks. Again no error in the windows eventlogger and nop apperant reason why it stopped.
There have been some other problems though. It have been brought to my attention that the server on which the process runs has the tendancy to overheat. It gives a message that it will be shut down due to overheating or the loss of cooling and right after that a message that shutdown was cancelled by the administrator.
Because of this it occured to me that the problems might come from damaged memory. I'm going to have the client's IT support department check the systems memory and see where that gets me. Do you think that this is a possiblity? I would say that when memory is damaged it should't effect just one process, but that other processes would suffer from the same damage. This does not seem to be the case however. So it's a longshot but i'll have them check it anyway.
- Matthijs
|||
If the fault is suspected with DTEXEC, package logging may not help, but the console output from DTEEXEC itself may be of use. Ensure you have set the job step log, in teh Advanced tab of the job step. If DTEXEC itself errors I would expect this to capture it.
If a shutdown started and was then cancelled, it could have started to kill off processes? If it is running hot then you could get all sorts of strange corruptions, but they would probably not manifest themselves under normal conditions.
|||The cancelled shutdown scenario has not happened lately. That was mostly last summer. I thought it might be result of the damage done back then. I will enable logging in the job step and wait until it happens again.- Matthijs
|||There seems to be no logging option in the advanced tab of the job step. In the general tab there is a loggin tab, but i think this just logs the package log entries, just like the package itself does.
This application starts a database job in SQL Server, which in turn starts a dtsx package. This package loops over the contents of a table and starts the packages that are marked to be executed.
This all work well most of the time. Sometimes though, DTExec, the process running the packages, just stops running. There are no entries in the event viewer or anywhere else.
I run Windows Server 2003 with SQL Server 2005 SE SP1. The packages are run from the filesystem, not the database.
Has anyone encountered the same problem and know a solution? I can't find any info on the internet.
- Matthijs
Do you have package logging turned on? Enable all of the logging events and see if you can catch something that way. Log to a text file or to SQL Server.|||No logging was active. I'll turn it on and see if the problem occurs again. One of the problems is that it stops randomly so it's not easily reproducable.
Thanks for the quick response. I'll post again when it happens again.
- Matthijs
|||The problem occured again. I checked the log and the last thing in there is the OnPostValidate event of one of the script tasks. Again no error in the windows eventlogger and nop apperant reason why it stopped.
There have been some other problems though. It have been brought to my attention that the server on which the process runs has the tendancy to overheat. It gives a message that it will be shut down due to overheating or the loss of cooling and right after that a message that shutdown was cancelled by the administrator.
Because of this it occured to me that the problems might come from damaged memory. I'm going to have the client's IT support department check the systems memory and see where that gets me. Do you think that this is a possiblity? I would say that when memory is damaged it should't effect just one process, but that other processes would suffer from the same damage. This does not seem to be the case however. So it's a longshot but i'll have them check it anyway.
- Matthijs
|||
If the fault is suspected with DTEXEC, package logging may not help, but the console output from DTEEXEC itself may be of use. Ensure you have set the job step log, in teh Advanced tab of the job step. If DTEXEC itself errors I would expect this to capture it.
If a shutdown started and was then cancelled, it could have started to kill off processes? If it is running hot then you could get all sorts of strange corruptions, but they would probably not manifest themselves under normal conditions.
|||The cancelled shutdown scenario has not happened lately. That was mostly last summer. I thought it might be result of the damage done back then. I will enable logging in the job step and wait until it happens again.- Matthijs
|||There seems to be no logging option in the advanced tab of the job step. In the general tab there is a loggin tab, but i think this just logs the package log entries, just like the package itself does.
Hi,
The DTEXEC Utility has the capability of returning ReturnCodes which have specific meanings e.g.
ReturnCode=3 means: The package was canceled by the user.
Is it possible to set my own ReturnCode values?
For example, my Package contains a Script Task which contains code to search a folder for a file. If the file cannot be found then I make the Script Task fail and hence the Package fails. But, when the Package is invoked by the DTEXEC utility, then the ReturnCode is always set to 1. Is it posssible to set the ReturnCode from within the Package (in, say, a Script Task in the Event Handler) to a different value?
It is necessary to be able to set custom application-specific return codes because these return codes are passed to the Batch Scheduler which alerts Operations (Support) staff in the event of failure. Meaningful (custom) ReturnCodes expedite problem solving and are (usually) mandatory in large production environments.
If it is not possible to make a Package return my own returncodes via DTEXEC then can you suggest alternative solutions, please?
Thanks.
The /CONS option is useful but not the answer. Any Ideas?The problem is that it reports that it executed the Maintenance Plan but it never actually executes the Plan. The data isn't updated and the CPU and hard drive I/O reports nothing happening.
I've checked the argument lists but I don't think I've missed anything...?
On the Sql Machine itself, I run the command line as follows:
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Microsoft Windows [Version 5.2.3790]
(C) Copyright 1985-2003 Microsoft Corp.
C:\Documents and Settings\V2Admin>cd \
C:\>dtexec /SQL "\Maintenance Plans\GPI Update" /Server V2SQL\VC2 /User sa /Pass
word XxXxXxX
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.3042.00 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 11:29:43 AM
DTExec: The package execution returned DTSER_SUCCESS (0).
Started: 11:29:43 AM
Finished: 11:29:44 AM
Elapsed: 0.375 seconds
C:\>
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>I don't understand why you are running using DTEXEC, have you tried to schedule the package that is created using SSIS?|||We are running DTExec as we want chain together and run other processes outside as Sql Server as well, but want to use the History tracker of Sql Server's Maintenance Plans.
The Sql Server Maintenance Plan tasks are kept with Sql Server and the external processes are kept with their relevant tools.
We need to run both together which is why we need to call DtExec.
At the worst case scenario, we can simply run SqlCmd and execute the desired functionality, but that is asking for problems as there would then be two locations to maintain the Maintenance Tasks Sub-Plans.
Not a smart move in any operations manual!
|||
Schedule a maintenance plan and look at the arguments to dtexec in the agent job step. You will find that the subplan that contains your job steps is enabled from that command line. If you just run a maintenance plan without so enabling the subplans, all are disabled, and nothing runs, as you have confirmed in your scenario.
jkh
Currently, we are running a Master Package with sub-Packages that are executed as a result. We run multiple days by executing a .bat file of DTExec commands. For Example:
Code Snippet
DTExec /FILE E:\ETL\FinancialDataMart\Master.dtsx /SET \Package.Variables[ReportingDate].Value;"1/02/2007" > etl_20070102.log
mkdir E:\ETL\ErrorLogs\Archive\20070102
copy E:\ETL\ErrorLogs\Processing\*.txt E:\ETL\ErrorLogs\Archive\20070102
Date values are incremented for as many days as we want to run. The log gives progress information and the Started, Finished, Elapsed time for the the Master package.
We are interested in manipulating the script entries to get the Start, Finished, Elapsed time for the sub-Packages that are initiated by this script. I think that I could use the Reporting option:
Code Snippet
/Rep[orting] level [;event_guid_or_name[;event_guid_or_name[...]]
Of course I can't find a good example to model the script. Is there anyone else using DTExec to get the run time statistics for each and every package? If so, can you forward that part of the script that accomplishes this task? BTW, we are going to implement run-time auditing to a table at some point but we are not there yet. Of course, my manager would like statistics now.
Thanks in advance.
You could get all those details by enabling package logging on each package; that is just a few clicks away. You can choose where the log information is going to be: file, table etc.
Package logging is enable/disable at the package level, so you need to edit each package.
|||If my package itself fails to load for some reason, the logging will not happen (this is my assumption), so how do I capture that. The DTEXEC return codes just says the package failed to load. For eg when I executing one of the packages I got the return code as 1, but the console displayed the below error.
Description: The LoadFromSQLServer method has encountered OLE DB error code 0x80040E14
(Only the owner of DTS Package 'kk-test' or a member of the sysadmin role may create new versions of it.).
The SQL statement that was issued has failed.
I dont think there is any way for me to capture this error.
Thanks
You could try redirecting the output from DTEXEC to a file.
Code Snippet
DTEXEC [your package params] > log.txt|||
Visual SSIS package execution stats (e.g. # executions, most recent execution timestamp, avg runtime , # failures, # successes, and all by machine) is available for "free" via SQL Server Reporting services, provided one uses the stock SQL Server log provider in each package.
These run time stats can be had via the following BI project: SSIS Log Provider Reports .
The stock SQL Server log provider writes to table named sysdtslog90 via the stored procedure sp_dts_addlogentry in whatever server instance/database is pointed to by the log provider's connection manager.
Now, since the report does hit the table directly, its probably a more reasonable approach to push the logging data into a cube periodically, reporting from there, but that feature is not included.
|||
You could try redirecting the output from DTEXEC to a file.
Code Snippet
DTEXEC [your package params] > log.txt
This works if I execute the DTEXEC directly in the command prompt, but when I execute using WScript shell in vbscript it fails with return value 6 saying "The utility encountered an internal error of syntactic or semantic errors in the command line"
Code Snippet
strShellCommand = "DTEXEC /SQL "\pkg-1 " /SERVER MBIXDEV1 /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E > \\serv1\logs\test.tmp"
Set objWshShell = WScript.CreateObject("WScript.Shell")
lngReturnValue = objWshShell.Run(strShellCommand , vbNormalFocus, True)
So how do I make this work? If I copy the strShellCommand value to command prompt and run it works.
Thanks
|||See Michael's post here: http://blogs.msdn.com/michen/archive/2007/08/02/redirecting-output-of-execute-process-task.aspx
You probably need to run it as CMD.EXE /C DTEXEC [rest of your commandline] > log.txt
|||
Karunakaran,
We invoke a .bat file from the vbs script:
Code Snippet
IF (colFiles1.Count = 1 _
AND colFiles2.Count = 1 _
AND colFiles3.Count = 1 _
AND colFiles4.Count = 1 _
AND colFiles5.Count = 1 _
AND colFiles6.Count = 1 _
AND colFiles7.Count = 1) THEN
Set WshShell = WScript.CreateObject("WScript.Shell")
WshShell.Run "E:\\ETL\\daily_import_and_etl_with_cmd_input.bat 0807 8/7"
Wscript.Quit
END IF
The .bat file looks like this basically:
Code Snippet
DTExec /FILE E:\ETL\FinancialDataMart\Master.dtsx /DECRYPT masterpwd /SET \Package.Variables[ReportingDate].Value;"%2/2007" > E:\ETL\ErrorLogs\Processing\etl_2007%1log.txt
IF NOT %ERRORLEVEL%==0 GOTO ERROR%ERRORLEVEL%
MKDIR E:\ETL\ErrorLogs\Archive\2007%1
MOVE E:\ETL\ErrorLogs\Processing\*.txt E:\ETL\ErrorLogs\Archive\2007%1
Thanks John, cmd did the trick.
Unfortunately I cannot take the batch file approach because my sys admins will not allow, thanks for suggesting that though.
Currently, we are running a Master Package with sub-Packages that are executed as a result. We run multiple days by executing a .bat file of DTExec commands. For Example:
Code Snippet
DTExec /FILE E:\ETL\FinancialDataMart\Master.dtsx /SET \Package.Variables[ReportingDate].Value;"1/02/2007" > etl_20070102.log
mkdir E:\ETL\ErrorLogs\Archive\20070102
copy E:\ETL\ErrorLogs\Processing\*.txt E:\ETL\ErrorLogs\Archive\20070102
Date values are incremented for as many days as we want to run. The log gives progress information and the Started, Finished, Elapsed time for the the Master package.
We are interested in manipulating the script entries to get the Start, Finished, Elapsed time for the sub-Packages that are initiated by this script. I think that I could use the Reporting option:
Code Snippet
/Rep[orting] level [;event_guid_or_name[;event_guid_or_name[...]]
Of course I can't find a good example to model the script. Is there anyone else using DTExec to get the run time statistics for each and every package? If so, can you forward that part of the script that accomplishes this task? BTW, we are going to implement run-time auditing to a table at some point but we are not there yet. Of course, my manager would like statistics now.
Thanks in advance.
You could get all those details by enabling package logging on each package; that is just a few clicks away. You can choose where the log information is going to be: file, table etc.
Package logging is enable/disable at the package level, so you need to edit each package.
|||If my package itself fails to load for some reason, the logging will not happen (this is my assumption), so how do I capture that. The DTEXEC return codes just says the package failed to load. For eg when I executing one of the packages I got the return code as 1, but the console displayed the below error.
Description: The LoadFromSQLServer method has encountered OLE DB error code 0x80040E14
(Only the owner of DTS Package 'kk-test' or a member of the sysadmin role may create new versions of it.).
The SQL statement that was issued has failed.
I dont think there is any way for me to capture this error.
Thanks
You could try redirecting the output from DTEXEC to a file.
Code Snippet
DTEXEC [your package params] > log.txt|||
Visual SSIS package execution stats (e.g. # executions, most recent execution timestamp, avg runtime , # failures, # successes, and all by machine) is available for "free" via SQL Server Reporting services, provided one uses the stock SQL Server log provider in each package.
These run time stats can be had via the following BI project: SSIS Log Provider Reports .
The stock SQL Server log provider writes to table named sysdtslog90 via the stored procedure sp_dts_addlogentry in whatever server instance/database is pointed to by the log provider's connection manager.
Now, since the report does hit the table directly, its probably a more reasonable approach to push the logging data into a cube periodically, reporting from there, but that feature is not included.
|||
You could try redirecting the output from DTEXEC to a file.
Code Snippet
DTEXEC [your package params] > log.txt
This works if I execute the DTEXEC directly in the command prompt, but when I execute using WScript shell in vbscript it fails with return value 6 saying "The utility encountered an internal error of syntactic or semantic errors in the command line"
Code Snippet
strShellCommand = "DTEXEC /SQL "\pkg-1 " /SERVER MBIXDEV1 /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E > \\serv1\logs\test.tmp"
Set objWshShell = WScript.CreateObject("WScript.Shell")
lngReturnValue = objWshShell.Run(strShellCommand , vbNormalFocus, True)
So how do I make this work? If I copy the strShellCommand value to command prompt and run it works.
Thanks
|||See Michael's post here: http://blogs.msdn.com/michen/archive/2007/08/02/redirecting-output-of-execute-process-task.aspx
You probably need to run it as CMD.EXE /C DTEXEC [rest of your commandline] > log.txt
|||
Karunakaran,
We invoke a .bat file from the vbs script:
Code Snippet
IF (colFiles1.Count = 1 _
AND colFiles2.Count = 1 _
AND colFiles3.Count = 1 _
AND colFiles4.Count = 1 _
AND colFiles5.Count = 1 _
AND colFiles6.Count = 1 _
AND colFiles7.Count = 1) THEN
Set WshShell = WScript.CreateObject("WScript.Shell")
WshShell.Run "E:\\ETL\\daily_import_and_etl_with_cmd_input.bat 0807 8/7"
Wscript.Quit
END IF
The .bat file looks like this basically:
Code Snippet
DTExec /FILE E:\ETL\FinancialDataMart\Master.dtsx /DECRYPT masterpwd /SET \Package.Variables[ReportingDate].Value;"%2/2007" > E:\ETL\ErrorLogs\Processing\etl_2007%1log.txt
IF NOT %ERRORLEVEL%==0 GOTO ERROR%ERRORLEVEL%
MKDIR E:\ETL\ErrorLogs\Archive\2007%1
MOVE E:\ETL\ErrorLogs\Processing\*.txt E:\ETL\ErrorLogs\Archive\2007%1
Thanks John, cmd did the trick.
Unfortunately I cannot take the batch file approach because my sys admins will not allow, thanks for suggesting that though.
Hi All,
I'm trying to execute DTExec from a workstation and I got some help from a different group without luck maybe someone here can help me.
This is what I try so far.
1. My package run in command line from my sql box using dtexec with
parameters.
2. Set the package with DontSaveSensitive and import into IS under MSDB with
the same option setup.
3. Set package role to public.
4. Share DTS folder with everyone permission just for testing.
5. Execute the package from a workstation using
//sqlServerbox/DTS/BINN/dtexec /dts "msdb/mypackage" /SER "MySQLServer" /set
\package.variables[myvariable].Value;"myvalue1,myvalue2"
(myvariable is string and I can pass multiple values separate by commas)
6. Still getting error:
Error: 2007-02-09 10:31:34.31
Code: 0xC0010018
Source: Execute DTS 2000 Package Task
Description: Error loading a task. The contact information for the task
is "E
xecute DTS 2000 Package Task;Microsoft Corporation; Microsoft SQL Server v9;
? 2
004 Microsoft Corporation; All Rights
Reserved;http://www.microsoft.com/sql/supp
ort/default.asp;1". This happens when loading a task fails.
End Error
Anyone has any ideas?
Any help will be appreciate it. Tks in advance...
Rgds
Johnny
You can't just run DTEXEC without installing it. You need to install SSIS locally.|||That is not true... I can run a package from a workstation without installing IS, I share the dts folder and it's working...
Anybody else try this?
Tks
JFB
|||I don't understand. You start a thread by asking why it does not work. Now you claim it is working.
Do you have Client Components installed on the workstation? If you do, you can simply run your own DTEXEC from Program Files instead of running it from network share.
|||Tks for you reply. If you read my first post with careful you will see that the package run without installing IS on the workstation. I have and error and thats my problem, I can't figure out the error ... I believe is a security issue but I don't know how to fix this.JFB|||
Tks for you reply. If you read my first post with careful you will see that the package run without installing IS on the workstation. I have and error and thats my problem, I can't figure out the error ... I believe is a security issue but I don't know how to fix this.
I will keep trying to get more info about it or if anybody try this or know how to solve this problem.
RgdsJFB
Right, but I think what Michael is saying is that even though dtexec executes, you are still missing libraries and such that will be required to execute that. You aren't executing dtexec on the remote box, you are executing a remote copy of dtexec on your box. Hence, you will need to install the SSIS client tools.
trying to execute SSIS package "Package.dtsx" from another machine. What are the requirements to do this without installing full SSIS?
I presume that we need .Net framework 2.0, but what are the minimum component or files I need to run a package?
thanks
HorseshoeMy understaning is that SSIS is not redistributable as DTS was. Either way there are more files than just dtexec. All the tasks and components are spread accross several assemblies for example. If it is redistributable, then it will be covered in redist.txt or the equivalent if that has changed from SQL 2000. I believe it is now licencsed as server component, so you need a server licence for any machine you install it on.|||Darren's absolutely right. For a headless install (without UI) on a server, you can select to not install the Tools, but you still need a license and the SQL Server setup to install SSIS on another machine.
regards
ash
I have created a SSIS package that reads 500 text files splits them into 4 raw files then reads them again and writes then to 4 database tables different Tables.
The reason form this is that my raw files have multiple types of records in them and it is only 1 Coolum. I split this out into the different types of records and load whole rows into the database.
ie input 1 txt file
<T6>
1:1000178
3:18148821-00
5:40204043
6:1
17:EX201036259NZ
25:0000304862
</T6>
<T1>
1:18148821-00
</T1>
<T5>
1:1511313
4:18126485-00
8:2006032510230300
17:EX201033399NZ
</T5>
<T6>
1:1511158
3:18084863-00
5:40617044
6:1
17:EX201033969NZ
25:0000302981
</T6>
End up begin rows in the T6 Table
1000178 18148821-00 40204043 1 EX201036259NZ 0000304862
1511158 18084863-00 40617044 1 EX201033969NZ 0000302981
T5 Table gets a new record
1511313 18126485-00 2006032510230300 EX201033399NZ
and T1 Table get a record
18148821-00
Anyway all this works find but I find that the DTExec process work fine until it has used up all the memory in the laptop in general it take 400megs to run this SSIS. I'm wondering am I missing something like don't run in a transaction. I know in the old DTS you could commit on each package and how do I turn all logging off eg what you see in the DOS box (can I do this?) would love some help on this and if anyone want a copy of this ssis package ie your trying to do the same then I'm more than happy to email it.
Check the /Rep option of DTExec to reduce the output.
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqlcmpt9/html/89edab2d-fb38-4e86-a61e-38621a214154.htm
-Jamie
|||Thanks Jamie,
This has help the logging (if you know can I set this in the enviroment IDE).
Now bigger problems is the memory, at the moment it fails after 700 files due to luck in memory.
Any ideas on how to find where the memory leak is in the SSIS Package?
|||Just to let other people kno, after some more searching I found that there is a memory leak in the Forloop object, that should be fixed in SP1.
I have download SP1 and tested it and it does appear that there is still a memory leak but only about a 10th of what it was, which now allows me in import 5000 files in one go.
|||Thanks for the info John. That's an important one to know. I certainly wasn't aware of it.
-Jamie
Hi All,
When I was trying to execute an SSIS package from DTExec using xp_cmdShell, it is giving an error message saying "unrecognized command,...". This error I am getting only in my Staging Environment. But at the same time, it is working fine with Development and Production servers.
I suspect the issue should be with config or access issues. So if anyone of you faced the same problem or if anyone have any solution, please share with me.
Thanks in advance for your help.
Thanks & Regards,
Prakash Srinivasan
Two things:I have noticed a strange behaviour when running some of my packages with dtexec. The packages use EventHandlers to log information to a database using the Execute SQL Task with the query stored in a variable. This variable is further part of the package configuration, so that I can change the query without changing the package itself. Now, this all works fine, until I made a typo in the query causing the syntax to be invalid, ie changing SELECT to SELCET. Now, one would expect the package to fail, and it does, if I run it through the debugger in Visual Studio, but when I run it using dtexec it just hangs and I have to kill the process using the Task Manager.
Peculiarly, I tried doing the same thing with a task that was not contained in an EventHandler, and then the package fails as expected, both when running it in the debugger and using dtexec. This is not a major problem, but what I am afraid of is that one day the database server will be down and cause an error in the Execute SQL Task in the EventHandler, causing dtexec to hang indefinitely. Has anyone else had problems like these?
Regards,
Lars
That's a really interesting observation Lars. I'd certainly be very worried about this happening as well. It might be worth logging as a bug.
I've certainly noticed dtexec appearing to hang but have never managed to track down why. Perhaps this was the reason.
-Jamie
|||I finally managed to track down the problem. It is described here: http://msdn2.microsoft.com/en-us/library/system.diagnostics.process.standardoutput.aspx and it is the deadlock that arises when reading both standard input and standard error after eachoter. In other words, this was related to my poor .NET skills and not a problem within SSIS.
Regards,
Lars
Ok, I was wondering if there was a more efficient way of executing the SSIS package from the remote server without having to go via a stored procedure.
Search this forum for the two words "dtexec" and "remote."
I have a DTS Package that I am running from a command line via .bat file. Does anyone know if there is a command to have the command window minimized or running in the background? I used the /Rep N command but that still leaves the window open until the package has executed.
Thanks!
Try the start command it may have some options that are useful for you.
If using a bath file, then you cannot get away from having a command console session open, that is the nature of what you are doing, unlress you use a separate user session, such as you would get with a service.
|||Thank you! The Start helped at least minimize the window! Thanks again!
A colleague of mine is having problems when trying to schedule package execution via a .bat file that executes a .dtsx on a sql server (not file system). The scheduling system that is employed by the company requires a .bat file to execute the package. The packages themselves move data from AS400 servers to SQL Servers. Occasionally (very randomly) when the scheduling system runs the statement in the bat files, the package prompts for password information (for connectivity).
We've tried a number of solutions to this, mostly on the "ProtectionLevel" property of the package itself. We did some research and it seems as though there are a few options.What is the best solution to eliminate this from happening? We certainly don't want to have people checking to make sure the packages don't prompt for passwords.
Thanks.
A colleague of mine is having problems when trying to schedule package execution via a .bat file that executes a .dtsx on a sql server (not file system). The scheduling system that is employed by the company requires a .bat file to execute the package. The packages themselves move data from AS400 servers to SQL Servers. Occasionally (very randomly) when the scheduling system runs the statement in the bat files, the package prompts for password information (for connectivity).
We've tried a number of solutions to this, mostly on the "ProtectionLevel" property of the package itself. We did some research and it seems as though there are a few options.What is the best solution to eliminate this from happening? We certainly don't want to have people checking to make sure the packages don't prompt for passwords.
Thanks.
I recommend setting ProtectionLevel=DontSaveSensitive and then using configurations to store your conenction strings. This is the "least hassle" choice in my experience.
-Jamie
|||
OK, we'll try that.
Couple more things, in a whole series of packages, it only prompts for a password on one or another, and it's not always the same packages, even though they always run in the same order, but they all hit AS400 data. Just wondering if you had any input on that? They are all set up the same security wise.
Also, we just came across the connection manager property "ProtectionLevel". I didn't research it yet, but if we changed the setting from the default "0", could this have an impact?
|||OK, we'll try that.
Couple more things, in a whole series of packages, it only prompts for a password on one or another, and it's not always the same packages, even though they always run in the same order, but they all hit AS400 data. Just wondering if you had any input on that? They are all set up the same security wise.
Seems strange but I can't explain it. Sorry.
Also, we just came across the connection manager property "ProtectionLevel". I didn't research it yet, but if we changed the setting from the default "0", could this have an impact?
Yes, it could. You should read the BOL topic on it.
-Jamie
|||
Jamie,
We're currently trying to implement your suggestion to set the Package Level Security "ProtectionLevel" to "DontSaveSensitive" and create a configuration to hold the connection strings. I seem to not be able to even get it to run in debug mode (let alone deploying and testing), because a login is failing. I noticed that the connection string does not store the password, and it's not possible to put it in there (just an observation - maybe not relevant).
It runs fine when I changed the ProtectionLevel back to the default setting. I'm know I'm missing something really stupid here. Your help is much appreciated, you know your stuff.
|||Jamie,
We're currently trying to implement your suggestion to set the Package Level Security "ProtectionLevel" to "DontSaveSensitive" and create a configuration to hold the connection strings. I seem to not be able to even get it to run in debug mode (let alone deploying and testing), because a login is failing. I noticed that the connection string does not store the password, and it's not possible to put it in there (just an observation - maybe not relevant).
It runs fine when I changed the ProtectionLevel back to the default setting. I'm know I'm missing something really stupid here. Your help is much appreciated, you know your stuff.
Good observation about the password. You have to manually edit the connection string yourself and put the password in there.
As you may know there was a big push in Microsoft not long ago around tightening security holes in its products and this is one of things that came out of that - it is considered a security risk for SSIS to store a password for you. Microsoft want you to responsible for creating security risks, not them.
-Jamie
|||Jamie, when you say "manually edit the connection string", where are you talking about? Actually opening the .dtsConfig file and editing? Because I can't seem to be able to successfully store the password in the Package editor.|||
OK, I have seemed to have figured my problem out. For those of you who may be a little new to SSIS or new to configuration files, here's a run down on my problem and how I fixed it.
My company scheduling software was getting prompted for a password when a .bat file containing a DTExec command was initiated, this was due to connecting to AS400 servers while transferring data. So we had to look into a way to prevent the scheduled .bat file executions from prompting for a password. Here is the solution, in detail for those of you who are lacking in SSIS experience like myself, and have a similar situation. Some of it is very obvious to most, but was not to me, so I included everything I could think of. Thanks once again to Jamie for all the help.
1. In the package, change protection level to "DontSaveSensitive" (click anywhere in the control flow, off of a task and look at properties).
2. Create a package configuration to store connection string (there are many examples on the web, here is one).
http://msdn2.microsoft.com/en-us/library/ms140213.aspx
3. You will have to manually edit the resulting .dtsConfig to include passwords. Open in notepad and add passwords to each connection. It will be stored in the project folder.
4. Build the package and deploy it, save the configuration file where you feel best. I saved on folder a sql server itself (this is an option when deploying).
5. Change (or create) your .bat file to include the /CONF option, this will be the fully qualified path each of your .dtsConfig files.
6. Rerun you .bat to make sure it works properly.
Jamie, when you say "manually edit the connection string", where are you talking about? Actually opening the .dtsConfig file and editing?
Yep. That's exactly what I mean.
