DTS

Could you help me with this problem: I have a DTS
executing .bat file and when try to run it in enterprise
management error occurs "The device is not
ready." Other DTSs with SQL queries work fine.
I'd guess that DTS is trying to access a physical drive letter that
doesn't exist or isn't mapped. Use UNC paths instead of drive letters
and see if the problem goes away.
David Portas
SQL Server MVP
|||I am using the UNC path
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1121875027.700950.158000@.g43g2000cwa.googlegr oups.com...
> I'd guess that DTS is trying to access a physical drive letter that
> doesn't exist or isn't mapped. Use UNC paths instead of drive letters
> and see if the problem goes away.
> --
> David Portas
> SQL Server MVP
> --
>
|||docsql wrote:[vbcol=seagreen]
> I am using the UNC path
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in
> message news:1121875027.700950.158000@.g43g2000cwa.googlegr oups.com...
Are you sure the account running the DTS script has access to the UNC
you provide?
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||yes
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:%23At6ZwUjFHA.3540@.TK2MSFTNGP14.phx.gbl...
> docsql wrote:
> Are you sure the account running the DTS script has access to the UNC you
> provide?
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
|||Hello,
Similar issue will occur when using the DTS import/Export wizard, select
the option "Copy Objects and data between SQL server databases" and the
script file directory pointing to a wrong drive.
If this is the case, to narrow down the issue, copy the script file to the
local hard drive and recreate a new DTS package to test.
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

DTS

Could you help me with this problem: I have a DTS
executing .bat file and when try to run it in enterprise
management error occurs "The device is not
ready." Other DTSs with SQL queries work fine.I'd guess that DTS is trying to access a physical drive letter that
doesn't exist or isn't mapped. Use UNC paths instead of drive letters
and see if the problem goes away.
--
David Portas
SQL Server MVP
--|||I am using the UNC path
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1121875027.700950.158000@.g43g2000cwa.googlegroups.com...
> I'd guess that DTS is trying to access a physical drive letter that
> doesn't exist or isn't mapped. Use UNC paths instead of drive letters
> and see if the problem goes away.
> --
> David Portas
> SQL Server MVP
> --
>|||docsql wrote:
> I am using the UNC path
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in
> message news:1121875027.700950.158000@.g43g2000cwa.googlegroups.com...
>> I'd guess that DTS is trying to access a physical drive letter that
>> doesn't exist or isn't mapped. Use UNC paths instead of drive letters
>> and see if the problem goes away.
>> --
>> David Portas
>> SQL Server MVP
>> --
Are you sure the account running the DTS script has access to the UNC
you provide?
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||yes
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:%23At6ZwUjFHA.3540@.TK2MSFTNGP14.phx.gbl...
> docsql wrote:
>> I am using the UNC path
>> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in
>> message news:1121875027.700950.158000@.g43g2000cwa.googlegroups.com...
>> I'd guess that DTS is trying to access a physical drive letter that
>> doesn't exist or isn't mapped. Use UNC paths instead of drive letters
>> and see if the problem goes away.
>> --
>> David Portas
>> SQL Server MVP
>> --
> Are you sure the account running the DTS script has access to the UNC you
> provide?
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||Hello,
Similar issue will occur when using the DTS import/Export wizard, select
the option "Copy Objects and data between SQL server databases" and the
script file directory pointing to a wrong drive.
If this is the case, to narrow down the issue, copy the script file to the
local hard drive and recreate a new DTS package to test.
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.

DTS

Could you help me with this problem: I have a DTS
executing .bat file and when try to run it in enterprise
management error occurs "The device is not
ready." Other DTSs with SQL queries work fine.I'd guess that DTS is trying to access a physical drive letter that
doesn't exist or isn't mapped. Use UNC paths instead of drive letters
and see if the problem goes away.
David Portas
SQL Server MVP
--|||I am using the UNC path
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1121875027.700950.158000@.g43g2000cwa.googlegroups.com...
> I'd guess that DTS is trying to access a physical drive letter that
> doesn't exist or isn't mapped. Use UNC paths instead of drive letters
> and see if the problem goes away.
> --
> David Portas
> SQL Server MVP
> --
>|||docsql wrote:[vbcol=seagreen]
> I am using the UNC path
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in
> message news:1121875027.700950.158000@.g43g2000cwa.googlegroups.com...
Are you sure the account running the DTS script has access to the UNC
you provide?
David Gugick
Quest Software
www.imceda.com
www.quest.com|||yes
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:%23At6ZwUjFHA.3540@.TK2MSFTNGP14.phx.gbl...
> docsql wrote:
> Are you sure the account running the DTS script has access to the UNC you
> provide?
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||Hello,
Similar issue will occur when using the DTS import/Export wizard, select
the option "Copy Objects and data between SQL server databases" and the
script file directory pointing to a wrong drive.
If this is the case, to narrow down the issue, copy the script file to the
local hard drive and recreate a new DTS package to test.
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

DTExec Reporting Options

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.

|||

jwelch wrote:

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

The vbs looks for files and then kicks off the ETL. The .bat file has the commands for the ETL.|||

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.

DTExec Reporting Options

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.

|||

jwelch wrote:

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

The vbs looks for files and then kicks off the ETL. The .bat file has the commands for the ETL.|||

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.

DTExec Can this thing run in the background?

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!

DTExec and Pasword Prompting

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.

SSIS Amateur wrote:

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?

|||

SSIS Amateur wrote:

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.

SSIS Amateur wrote:

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.

|||

SSIS Amateur wrote:

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.

|||

SSIS Amateur wrote:

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.