DTS error not configured for data access

I just had to rebuild master and msdb in Sql 2000. Now any DTS package accessing that machine fails with the message 'Server __ is not configured for DATA ACCESS.' The message seems to be coming from OLE. Everything else, including applications, scheduled jobs, etc. seems to work fine.

How can I restore data access?You may need to allow other SQL servers to connect. In enterprise manager, right-click on the server and select properties. On the Connections tab, check the box in the Remote Servers frame "Allow other SQL Servers to connect remotely to this SQL Server using RPC".|||No good. Properties showed remote connection OK. I unchecked, rechecked and rebooted to make sure. same problem.|||Can you create a connection to the server in a new DTS package?

It may be that you have to recreate the Server Connections in the existing package(s).|||No. Fails for both new and existing packages. The rebuild was apparently caused by converting databases from another machine from Sql 6.5. After the conversion we saw some odd things showing in msdb and lost alerts, operators etc. I also saw some table structure changes. Even though we didn't include msdb it looked like stuff from 6.5's was showing up. This looks to be a repercussion related to that.

Waht I can't seem to find anywhere is any discussion of the error. DTS's error window says the source is OLE but gives no eror number|||Just to add to the fun, I ignored the error message and built the queries without the point & click junk. Package ran fine. So it looks like all I've lost the visual references to that server.sql

DTS data transfer and Update

My DTS package transfers data mostly codes from temp to master table. The master table has 10 description fields for each of the codes. These description fields are then populated using update statements joined with 10 description tables. However, the update process is so inefficient since a single update statement takes several hours to finish.

The second option is to populate description fields as codes are transferred from temp to master table using 10 left outer joins with description tables. But the result was unexpected. It's giving me more than twice as many records as there are in the temp table.

What would be the best (efficient) approach for this situation? Would greately appreciate any help/thoughts.

thanks.I think I would create a view, unload the vie and bcp the data into the table in native format

Do the code tables contain keys?|||a single update statement takes several hours to finish

Then you must be doing something wrong...

The master table has 10 description fields

This sounds like awful design! Fancy posting the DDL of the table in question?sql

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.

DTC problem

I have a master package that executes a series of sub packages. The sub packages run in a series with OnSuccess precedence constraints between them. The master package is run from a SQL Agent job.

When I run the master package without setting TransactionOption = Required at any level they all work fine. However when I set TransactionOption = Required at the master package level (and Supported from there downwards) the first package in the series of sub packages executes (as reported in the Event Log), but the next package does not seem to start . . . however the SQL Agent job keeps running indefinitely.

If I disable the second package and set the precedence constraint between the first and third packages, they both execute fine. In all there are 19 packages and 4 of them have the same problem. Disable them and remove them from the precedence chain and it all works.

I tried re-enabling the 4 problem packages and setting the TransactionOption on the Execute Package Task to Not Supported for them but it still 'hangs' at the first one. If I set the TransactionOption to Required for one of the problem packages and execute it on its own, then it runs fine.

I am currently running a SQL Profile trace which isn't telling me anything except that the package isn't executing any SQL (though there are loads of SQL Transaction entries with no text data) and I've got logging to the Event Log turned on, but that just shows that the last package finished and the next one didn't start.

I'm at a loss as to how to proceed so any ideas on what to look for or what might be causing the problem would be much appreciated.

TIA . . . Ed

I just tried swapping the order of the first and second package and the same situation occurs - it runs the first but not the second. Which tells me the problem is not related to the package as such, but some resource used within the package.

The packages only access two databases residing on the same instance of SQL Server - the same machine as the packages are being executed on. They don't access the file system or any other resources.

Could there be a locking problem and if so how can I find out where it is? (As you can probably tell my d/b admin skills aren't tip-top, I'm a web developer in my day job).

Thanks . . . Ed

|||

Ed,

I'm no DBA myself but it does sound as though you have a locking problem. My first port of call in these situations is to execute sp_who and sp_lock

These return information about processes (i.e. SPIDs) that are blocked by other processes and which resources are locked respectively.

BOL has information on sp_who and sp_lock.

Note that there is an undocumented sproc called sp_who2 which returns the same info as sp_who....plus a little bit more! The 'BlkBy' column is interesting as that lists the SPID that is blocking. If BlkBy=-2 then read this article: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_wa-wz_3v8v.asp

-Jamie

|||

Jamie - Thank you very much, you set us on the right path. The queries above showed that there was locking and this:

select * from sys.dm_tran_locks a
inner join sys.objects b on
a.resource_associated_entity_id = b.object_id
where a.request_session_id = <spid>

. . . showed the objects which were being locked.

The problem was due to our schema . . . not all tables had PKs (something I'd been meaning to address) so presumably there were table scans deadlocking each other.

Adding some missing PKs has fixed the problem.

Thanks again . . . Ed

DTC - Transaction context in use by another session

Hi

I have a master package that executes a series of sub packages. The master package is run from a SQL Agent job. The packages are reading from and writing to two databases on the same instance of SQL Server 2005.

If I set the Execute Package Tasks for the sub packages without any precedence constraints between them and set TransactionOption = Required at the master package level (and supported from there downwards) I get the following errors.

The event log shows me:

The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D00A "Unable to enlist in the transaction.".

Running a SQL Profiler trace shows me:

Error: 3910, Severity: 16, State: 2
Transaction context in use by another session.

This problem is well documented and seems to go back to DTS in SQL Server 7.0 . . . see http://support.microsoft.com/?scid=kb;en-us;279857&spid=2852&sid=150

I can get round it by setting precedence between the sub packages - making them run one at a time solves the problem. But then we don't get the performance benefits of running the packages concurrently. Does anyone have any other solutions.

TIA . . . Ed

Ed,

are using the RTM or SP1 version?

There was a fix in SP1 that might address your problem.

Thanks.

|||

Hi Bob

We tried SP1 but reverted because there were some issues with it and we couldn't afford the time to play around with it.

I can leave the packages running in series for now and try running them in parallel with SP1 installed when I have time to experiment.

Thanks . . . Ed

|||

I'm getting the same error messages in almost the same scenario, but I have SP1 applied. In my case, I have a sub-package that starts its own distributed transaction and it has multiple data flows that run concurrently within the same container component. Sometimes it runs through just fine. Other times, one or another of the data flows will die with this error. It's unpredictable which one or if it will fail at all. If I sequence the data flows, the problem goes away.

Is there an alternative solution besides the one in SP1? What was the one in SP1?

Thanks,

Joe

|||

Are you using a configured common connection between the two (or more) packages? As a guess, try to make the connection string slightly different for each by appending the ";Application Name=?" property with the package name where the ? is.

Under a slightly different scenario I was able to get the same thing happening and this corrected my problem. I was using a shared config file to configure the connections and when I added the Application Name part it went away.

|||

None of my packages are using configured connections. They each have their own connection manager defined within their package. I do intend to make them configured in the future, so I'll keep that suggestion in mind.

Also, I can get the error to occur when executing only one of the subpackages at a time. They each are starting their own transaction. It's the data flows that have problems running concurrently.

DTC - Transaction context in use by another session

Hi

I have a master package that executes a series of sub packages. The master package is run from a SQL Agent job. The packages are reading from and writing to two databases on the same instance of SQL Server 2005.

If I set the Execute Package Tasks for the sub packages without any precedence constraints between them and set TransactionOption = Required at the master package level (and supported from there downwards) I get the following errors.

The event log shows me:

The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D00A "Unable to enlist in the transaction.".

Running a SQL Profiler trace shows me:

Error: 3910, Severity: 16, State: 2
Transaction context in use by another session.

This problem is well documented and seems to go back to DTS in SQL Server 7.0 . . . see http://support.microsoft.com/?scid=kb;en-us;279857&spid=2852&sid=150

I can get round it by setting precedence between the sub packages - making them run one at a time solves the problem. But then we don't get the performance benefits of running the packages concurrently. Does anyone have any other solutions.

TIA . . . Ed

Ed,

are using the RTM or SP1 version?

There was a fix in SP1 that might address your problem.

Thanks.

|||

Hi Bob

We tried SP1 but reverted because there were some issues with it and we couldn't afford the time to play around with it.

I can leave the packages running in series for now and try running them in parallel with SP1 installed when I have time to experiment.

Thanks . . . Ed

|||

I'm getting the same error messages in almost the same scenario, but I have SP1 applied. In my case, I have a sub-package that starts its own distributed transaction and it has multiple data flows that run concurrently within the same container component. Sometimes it runs through just fine. Other times, one or another of the data flows will die with this error. It's unpredictable which one or if it will fail at all. If I sequence the data flows, the problem goes away.

Is there an alternative solution besides the one in SP1? What was the one in SP1?

Thanks,

Joe

|||

Are you using a configured common connection between the two (or more) packages? As a guess, try to make the connection string slightly different for each by appending the ";Application Name=?" property with the package name where the ? is.

Under a slightly different scenario I was able to get the same thing happening and this corrected my problem. I was using a shared config file to configure the connections and when I added the Application Name part it went away.

|||

None of my packages are using configured connections. They each have their own connection manager defined within their package. I do intend to make them configured in the future, so I'll keep that suggestion in mind.

Also, I can get the error to occur when executing only one of the subpackages at a time. They each are starting their own transaction. It's the data flows that have problems running concurrently.

dt_addtosourcecontrol

In the master database is a stored procedure named dt_addtosourcecontrol.
Does this imply that if you have Visual Source Safe, there is a slick way of
quickly checking files in and out or similar without using VSS? Thanks in
advance.
Mark--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
These are created by the Visual Studio tools but yes I have seen some
scripts that allow you to use them from QA, try searching google.
I'd suggest you take a look at mssqlXpress from
http://www.xpressapps.com/ a nice QA replacement with VSS
integration.
"Mark" <mfield@.idonotlikespam.cce.umn.edu> wrote in message
news:ePeHGAokDHA.2432@.TK2MSFTNGP10.phx.gbl...
> In the master database is a stored procedure named
> dt_addtosourcecontrol. Does this imply that if you have Visual
> Source Safe, there is a slick way of quickly checking files in and
> out or similar without using VSS? Thanks in advance.
> Mark
>
--BEGIN PGP SIGNATURE--
Version: PGP 8.0
iQA/AwUBP40IFlOULLw5tFTpEQIfWgCgppxFmwNPI3a7iFGZHkeYd9mTQvEAoIQl
MSRdisM+fik3DPtKwYVBWLYt
=GZKR
--END PGP SIGNATURE--