DTS Errors with Access?

Hi,

I have a DTS package that when executed through Enterprise Manager works just fine.
I have an Access app on a different computer that executes the DTS package via VB using the syntax below. This process causes the DTS package to return an error of 'SQL Server Does Not Exist or Access Denied'.
I have tried both authentication types, not making a difference. I have used the SA login from Access and still get the error. All the necessary DTS drivers are installed on the second box and I can connect to the SQL tables succesfully also...
Any ideas? Thanks!

Private Sub cmd_MPSDTS_Click()

Dim oPackage As New DTS.Package
On Error GoTo eh

oPackage.LoadFromSQLServer "SVRName", "sa", "pwd", DTSSQLStgFlag_Default, "", "", "", "Pkg_Name", 0
'Execute the Package

oPackage.Execute
'MsgBox oPackage.Description, vbInformation, _
"Re-import Excel sheet."

'Clean up.
MsgBox ("Ran DTS Package")
Set oPackage = Nothing
Exit Sub
eh:
MsgBox Err.Description, vbCritical, _
"Error Running Package"

End SubI assume you have tried pinging the server from the machine with the access db? or rather from the machine that opens the access db.|||You should also check that the user you are executing as from Access is the same as the user that is executing from Enterprise Manager or at least has the same privileges.|||Not sure why that would make a difference (Not saying it wouldn't, I'm just not sure why it would). The error they are receiving makes it look as though they can't reach the server at all...|||Found the problem,

The DTS package imported information from an Informix database. The necessary drivers weren't installed on the second machine so the job failed even though I could connect to the SQL server just fine. I got around it by changing the code to execute a SQL Job which in turn executed the DTS package. This way it ran on the SQL server.

Thanks!

DTS Error Handling

Hi folks,

I would like to know -

-) Is there a way to continue the DTS Package execution even if there are some errors? for example if there is a primary key violation error, I would like to continue with my transformation.

Note:
I found a KB (#240221) in MSDN "HOW TO: Handle Errors in Data Transformation Services "Package" and "Step" Objects". At the bottom of this article MS suggests to use DTSErrorMode object. But I could not find that object.

DB version : SQL Server 2000You can handle exceptions in DTS tasks and continue processing...

The example you gave: PRIMARY KEY Violation would most likely occur in an Execute SQL Task or possibly an ActiveX Script.

When an error occurs in a task you can add an On Failure Workflow to an ActiveX Script that restarts the process (loops back to) where it left off, i.e. performing the next INSERT etc...

See the sample below:

Function Main()

dim pkg
dim stp
'Rerun the ActiveXScriptstep
set pkg = DTSGlobalVariables.Parent 'Get a reference to the package
set stp = pkg.Steps("DTSStep_DTSActiveScriptTask_1")
stp.ExecutionStatus = DTSStepExecStat_Waiting

'Release resources
set stp = nothing
set pkg = nothing

main = DTSTaskExecResult_Success
End Function

Regards,
CPN

DTS Error handling

Hi,

I'm trying to decide on the best method for dealing with errors in a DTS package. It is sufficient to retrieve Step failure information after package execution but I have tried both methods. Specifically, these methods are detailed in http://support.microsoft.com/kb/240221. Another article - http://support.microsoft.com/default.aspx?scid=kb;en-us;321525 - details the need to establish a single event sink to avoid "unexpected behaviour". I have used the code as described but noticed very little difference between post execution checking using GetExecutionErrorInfo and the PackageEventSink Interface. Using the Event Sink does retrieve one extra type of error, ie.

"Error at Destination for Row number 2. Errors encountered so far in this task: 1". This error will always give the row number as the last row in the file, in this case an excel file source. This is not really useful, but I don't want to detach the Event Sink because of the possibility of "unexpected behaviour". Could somebody advise? First is it possible to retrieve the line number, and secondly can anyone detail what an example of this unexpected behaviour might be? Finally, is there a way to retrieve a unique key constraint violation from a package if it occurs? It is only caught in the generalised way and produced as "Error at Destination...".

Thanks in advance

Please see the DTS group: http://groups.google.com/group/microsoft.public.sqlserver.dts?lnk=srg
|||This forum is specific to SSIS, the replacement for DTS in 2005. You might have better success posting here: http://groups.google.com/groups?as_q=Html+mail&as_ugroup=microsoft.public.sqlserver.dts

Dts Error

Hi,
Am having errors like this one:
The numner of failling rows exceeds the maximun specified
Could not allocate space for onject 'asdsadsad' in database 'xyz'
because the 'PRIMARY' filegroup is full
Any ideas? :)Sounds like the database is full.
Either increase the size or allow it to automatically increase.

If the disk is full then you have learnt to always put a limit on file sizes.

DTS Across Network Errors

I have a database and web server (physically different boxes) and I am
trying to get DTS to read in a file from the web server and insert the
data into the database. I get permissions errors when I try to execute
the job, so I am thinking if I could some how get the file over from
the web server on to the database server, then it would run fine.
This is a job, and it will happen every day.
Any suggestions would be very appreciated.
Check your security on that folder
If anything write a batch file that brings the file to you server
Schedule to run before DTS
Paul Zanbaka
Sharepoint Adminstrator and DBA
www.mycodekb.com
"michael.esposito@.gmail.com" wrote:

> I have a database and web server (physically different boxes) and I am
> trying to get DTS to read in a file from the web server and insert the
> data into the database. I get permissions errors when I try to execute
> the job, so I am thinking if I could some how get the file over from
> the web server on to the database server, then it would run fine.
> This is a job, and it will happen every day.
> Any suggestions would be very appreciated.
>
|||Mr. Sanbaka,
Here is the exact error I am getting...maybe this can help. I gave
Everyone full rights to the folder and it still doesnt work right.
Should I try creating a batch file to copy it to the DB server?
Error string: The Microsoft Jet database engine cannot open the file
'\\abc123\d$\mdb\WVL2000Mike.mdb'. It is already opened exclusively by
another user, or you need permission to view its data. Error
source: Microsoft JET Database Engine Help file:... Process Exit
Code 7. The step failed.
|||Michael
Make sure the databsae is not open by a user at the time DTS is trying to
access it.
If you were to use a batch file yes copy it to the server
Paul Zanbaka
Sharepoint Adminstrator and DBA
www.mycodekb.com
"michael.esposito@.gmail.com" wrote:

> Mr. Sanbaka,
> Here is the exact error I am getting...maybe this can help. I gave
> Everyone full rights to the folder and it still doesnt work right.
> Should I try creating a batch file to copy it to the DB server?
> Error string: The Microsoft Jet database engine cannot open the file
> '\\abc123\d$\mdb\WVL2000Mike.mdb'. It is already opened exclusively by
> another user, or you need permission to view its data. Error
> source: Microsoft JET Database Engine Help file:... Process Exit
> Code 7. The step failed.
>
|||Thats the problem I am having, I get permissions errors when I try to
use a batch file to move it over

DTS Across Network Errors

I have a database and web server (physically different boxes) and I am
trying to get DTS to read in a file from the web server and insert the
data into the database. I get permissions errors when I try to execute
the job, so I am thinking if I could some how get the file over from
the web server on to the database server, then it would run fine.
This is a job, and it will happen every day.
Any suggestions would be very appreciated.Check your security on that folder
If anything write a batch file that brings the file to you server
Schedule to run before DTS
--
Paul Zanbaka
Sharepoint Adminstrator and DBA
www.mycodekb.com
"michael.esposito@.gmail.com" wrote:
> I have a database and web server (physically different boxes) and I am
> trying to get DTS to read in a file from the web server and insert the
> data into the database. I get permissions errors when I try to execute
> the job, so I am thinking if I could some how get the file over from
> the web server on to the database server, then it would run fine.
> This is a job, and it will happen every day.
> Any suggestions would be very appreciated.
>|||Mr. Sanbaka,
Here is the exact error I am getting...maybe this can help. I gave
Everyone full rights to the folder and it still doesnt work right.
Should I try creating a batch file to copy it to the DB server?
Error string: The Microsoft Jet database engine cannot open the file
'\\abc123\d$\mdb\WVL2000Mike.mdb'. It is already opened exclusively by
another user, or you need permission to view its data. Error
source: Microsoft JET Database Engine Help file:... Process Exit
Code 7. The step failed.|||Michael
Make sure the databsae is not open by a user at the time DTS is trying to
access it.
If you were to use a batch file yes copy it to the server
Paul Zanbaka
Sharepoint Adminstrator and DBA
www.mycodekb.com
"michael.esposito@.gmail.com" wrote:
> Mr. Sanbaka,
> Here is the exact error I am getting...maybe this can help. I gave
> Everyone full rights to the folder and it still doesnt work right.
> Should I try creating a batch file to copy it to the DB server?
> Error string: The Microsoft Jet database engine cannot open the file
> '\\abc123\d$\mdb\WVL2000Mike.mdb'. It is already opened exclusively by
> another user, or you need permission to view its data. Error
> source: Microsoft JET Database Engine Help file:... Process Exit
> Code 7. The step failed.
>|||Thats the problem I am having, I get permissions errors when I try to
use a batch file to move it over

DTS Across Network Errors

I have a database and web server (physically different boxes) and I am
trying to get DTS to read in a file from the web server and insert the
data into the database. I get permissions errors when I try to execute
the job, so I am thinking if I could some how get the file over from
the web server on to the database server, then it would run fine.
This is a job, and it will happen every day.
Any suggestions would be very appreciated.Check your security on that folder
If anything write a batch file that brings the file to you server
Schedule to run before DTS
--
Paul Zanbaka
Sharepoint Adminstrator and DBA
www.mycodekb.com
"michael.esposito@.gmail.com" wrote:

> I have a database and web server (physically different boxes) and I am
> trying to get DTS to read in a file from the web server and insert the
> data into the database. I get permissions errors when I try to execute
> the job, so I am thinking if I could some how get the file over from
> the web server on to the database server, then it would run fine.
> This is a job, and it will happen every day.
> Any suggestions would be very appreciated.
>|||Mr. Sanbaka,
Here is the exact error I am getting...maybe this can help. I gave
Everyone full rights to the folder and it still doesnt work right.
Should I try creating a batch file to copy it to the DB server?
Error string: The Microsoft Jet database engine cannot open the file
'\\abc123\d$\mdb\WVL2000Mike.mdb'. It is already opened exclusively by
another user, or you need permission to view its data. Error
source: Microsoft JET Database Engine Help file:... Process Exit
Code 7. The step failed.|||Michael
Make sure the databsae is not open by a user at the time DTS is trying to
access it.
If you were to use a batch file yes copy it to the server
Paul Zanbaka
Sharepoint Adminstrator and DBA
www.mycodekb.com
"michael.esposito@.gmail.com" wrote:

> Mr. Sanbaka,
> Here is the exact error I am getting...maybe this can help. I gave
> Everyone full rights to the folder and it still doesnt work right.
> Should I try creating a batch file to copy it to the DB server?
> Error string: The Microsoft Jet database engine cannot open the file
> '\\abc123\d$\mdb\WVL2000Mike.mdb'. It is already opened exclusively by
> another user, or you need permission to view its data. Error
> source: Microsoft JET Database Engine Help file:... Process Exit
> Code 7. The step failed.
>|||Thats the problem I am having, I get permissions errors when I try to
use a batch file to move it over

DTC Transaction Abort error

We are using MSDTC to handle our transactions and some clients seem to be getting these errors more and more when we went to framework 2.0. Any ideas what could be causing this?

Before in framework 1.1 we would ask our clients to re-register their computer into the domain and/or rename their computer and this worked but it seems like in framework 2.0 it doesn't do the trick. Also, the wrong dns setup causes this too but after trying to fix both things it still doesn't work. I believe this error means either the server has finished the transaction and can't find the client to return the result or the active directory account/computer account is corrupted. Any ideas?

The transaction has already been implicitly or explicitly committed or aborted.

Type: TransactionException
StackTrace:
Server stack trace:
at System.Transactions.Oletx.OletxTransactionManager.ProxyException(COMException comException)
at System.Transactions.TransactionInterop.GetExportCookie(Transaction transaction, Byte[] whereabouts)
at System.Data.SqlClient.SqlInternalConnection.EnlistNonNull(Transaction tx)
at System.Data.SqlClient.SqlInternalConnection.Enlist(Transaction tx)
at System.Data.SqlClient.SqlInternalConnectionTds.Activate(Transaction transaction)
at System.Data.ProviderBase.DbConnectionInternal.ActivateConnection(Transaction transaction)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()

Previously

For DTC issues, you should try posting on SQL Server Integration Services.

Thanks
Laurentiu

DSV and Cube do not match - generates Key Errors

Hi All,

This is strange behaviour, hopefully I can resolve it without rebuilding the cube and all the dimensions from scratch.

I have a cube with a fact table, and a number of dimensions, including an EventType and Event Date. (Event Type is "Sale", "Return", etc.etc, Event Date is the date it occurred)

When I created the DSV for this I accidently joined the fact table EventTypeID field to the EventTimeID on the Event Date dimensions. Not suprisingly this gave me a key error, as my EventTypeID on the fact table has values from 1-12, and the EventTimeID records start at 10000 and go upwards.

Having seen the error I went into the DSV and changed the relationship so that the Event Date dimension table was joined to the fact table on the correct fields. I then checked using SQL that there were no missing keys or other oddities on the base tables. I then manually did FULL process on all the dimensions, then tried to process the cube.

No dice. The error still occurs, it still claims that there is a missing key on the Event Date dimension, and a little further investigation shows that it is still using EventTypeID as the joining key. I have manually re-processed all dimensions etc, but to no avail.

How do I get the cube to pick up the changes in the DSV? What I don't want to have to do is throw it all away, as there are a fair number of hierarchies etc I would have to re-create.

Any help appreciated.

Richard R.

The Error reported back is:

Errors in the OLAP storage engine:
The attribute key cannot be found:
Table: dbo_tbl_Sales_FACT_LOAD, Column: EventTypeID, Value: 1.
Errors in the OLAP storage engine:
The attribute key was converted to an unknown member because the attribute key was not found.
Attribute Tbl Time DIM of Dimension:
Event Date from Database: ProtoType Cubes,
Cube: Sales And Mailings, Measure Group: Tbl Sales And Mailing FACT LOAD,
Partition: Tbl Sales And Mailing FACT LOAD, Record: 1.

Unfortunately, the metadata is pulled from the DSV and embedded in the higher level objects (dimensions, cubes, measure groups, etc.) when those objects are created. You need to recreate the objects for it to pickup the revised metadata in the DSV. Just updating the DSV doesn't do it alone.

Sometimes if it is a simple change, you can script out the objects into an XMLA script and then recreate it from the script rather than taking the time to drag & drop new objects around the system. However, you have to be careful and be knowledgeable. It is straightforward to do -- you are just editing a flat file, but if there are lots of references and changes I wouldn't go down that path.

Sorry to give you the bad news.

_-_-_ Dave

|||

As the late, great, Kenny Everett would have said

"Oh Bum!"

Thanks Dave,

Richard

|||

I hope somebody releases an XML refresh & validation tool for this issue. I have been troubleshooting this one and other datatype & DSV errors for awhile, and it affects multiple dimensions.

I have found that by correcting the relationship, then going into the cube's dimensions and clicking on the ... next to the dimension, you can reselect the key field (the same field, just click it again) and it seems to do the trick.

Otherwise, it's 'view code' and manually changing the DSV & the objects affected.

|||Wow, I just sank a few hours of time because of this issue...has anyone seen an refresh/validation tool like Andrew indicated? I could easily see how it could save tons of time!

DSV and Cube do not match - generates Key Errors

Hi All,

This is strange behaviour, hopefully I can resolve it without rebuilding the cube and all the dimensions from scratch.

I have a cube with a fact table, and a number of dimensions, including an EventType and Event Date. (Event Type is "Sale", "Return", etc.etc, Event Date is the date it occurred)

When I created the DSV for this I accidently joined the fact table EventTypeID field to the EventTimeID on the Event Date dimensions. Not suprisingly this gave me a key error, as my EventTypeID on the fact table has values from 1-12, and the EventTimeID records start at 10000 and go upwards.

Having seen the error I went into the DSV and changed the relationship so that the Event Date dimension table was joined to the fact table on the correct fields. I then checked using SQL that there were no missing keys or other oddities on the base tables. I then manually did FULL process on all the dimensions, then tried to process the cube.

No dice. The error still occurs, it still claims that there is a missing key on the Event Date dimension, and a little further investigation shows that it is still using EventTypeID as the joining key. I have manually re-processed all dimensions etc, but to no avail.

How do I get the cube to pick up the changes in the DSV? What I don't want to have to do is throw it all away, as there are a fair number of hierarchies etc I would have to re-create.

Any help appreciated.

Richard R.

The Error reported back is:

Errors in the OLAP storage engine:
The attribute key cannot be found:
Table: dbo_tbl_Sales_FACT_LOAD, Column: EventTypeID, Value: 1.
Errors in the OLAP storage engine:
The attribute key was converted to an unknown member because the attribute key was not found.
Attribute Tbl Time DIM of Dimension:
Event Date from Database: ProtoType Cubes,
Cube: Sales And Mailings, Measure Group: Tbl Sales And Mailing FACT LOAD,
Partition: Tbl Sales And Mailing FACT LOAD, Record: 1.

Unfortunately, the metadata is pulled from the DSV and embedded in the higher level objects (dimensions, cubes, measure groups, etc.) when those objects are created. You need to recreate the objects for it to pickup the revised metadata in the DSV. Just updating the DSV doesn't do it alone.

Sometimes if it is a simple change, you can script out the objects into an XMLA script and then recreate it from the script rather than taking the time to drag & drop new objects around the system. However, you have to be careful and be knowledgeable. It is straightforward to do -- you are just editing a flat file, but if there are lots of references and changes I wouldn't go down that path.

Sorry to give you the bad news.

_-_-_ Dave

|||

As the late, great, Kenny Everett would have said

"Oh Bum!"

Thanks Dave,

Richard

|||

I hope somebody releases an XML refresh & validation tool for this issue. I have been troubleshooting this one and other datatype & DSV errors for awhile, and it affects multiple dimensions.

I have found that by correcting the relationship, then going into the cube's dimensions and clicking on the ... next to the dimension, you can reselect the key field (the same field, just click it again) and it seems to do the trick.

Otherwise, it's 'view code' and manually changing the DSV & the objects affected.

|||Wow, I just sank a few hours of time because of this issue...has anyone seen an refresh/validation tool like Andrew indicated? I could easily see how it could save tons of time!

DSV and Cube do not match - generates Key Errors

Hi All,

This is strange behaviour, hopefully I can resolve it without rebuilding the cube and all the dimensions from scratch.

I have a cube with a fact table, and a number of dimensions, including an EventType and Event Date. (Event Type is "Sale", "Return", etc.etc, Event Date is the date it occurred)

When I created the DSV for this I accidently joined the fact table EventTypeID field to the EventTimeID on the Event Date dimensions. Not suprisingly this gave me a key error, as my EventTypeID on the fact table has values from 1-12, and the EventTimeID records start at 10000 and go upwards.

Having seen the error I went into the DSV and changed the relationship so that the Event Date dimension table was joined to the fact table on the correct fields. I then checked using SQL that there were no missing keys or other oddities on the base tables. I then manually did FULL process on all the dimensions, then tried to process the cube.

No dice. The error still occurs, it still claims that there is a missing key on the Event Date dimension, and a little further investigation shows that it is still using EventTypeID as the joining key. I have manually re-processed all dimensions etc, but to no avail.

How do I get the cube to pick up the changes in the DSV? What I don't want to have to do is throw it all away, as there are a fair number of hierarchies etc I would have to re-create.

Any help appreciated.

Richard R.

The Error reported back is:

Errors in the OLAP storage engine:
The attribute key cannot be found:
Table: dbo_tbl_Sales_FACT_LOAD, Column: EventTypeID, Value: 1.
Errors in the OLAP storage engine:
The attribute key was converted to an unknown member because the attribute key was not found.
Attribute Tbl Time DIM of Dimension:
Event Date from Database: ProtoType Cubes,
Cube: Sales And Mailings, Measure Group: Tbl Sales And Mailing FACT LOAD,
Partition: Tbl Sales And Mailing FACT LOAD, Record: 1.

Unfortunately, the metadata is pulled from the DSV and embedded in the higher level objects (dimensions, cubes, measure groups, etc.) when those objects are created. You need to recreate the objects for it to pickup the revised metadata in the DSV. Just updating the DSV doesn't do it alone.

Sometimes if it is a simple change, you can script out the objects into an XMLA script and then recreate it from the script rather than taking the time to drag & drop new objects around the system. However, you have to be careful and be knowledgeable. It is straightforward to do -- you are just editing a flat file, but if there are lots of references and changes I wouldn't go down that path.

Sorry to give you the bad news.

_-_-_ Dave

|||

As the late, great, Kenny Everett would have said

"Oh Bum!"

Thanks Dave,

Richard

|||

I hope somebody releases an XML refresh & validation tool for this issue. I have been troubleshooting this one and other datatype & DSV errors for awhile, and it affects multiple dimensions.

I have found that by correcting the relationship, then going into the cube's dimensions and clicking on the ... next to the dimension, you can reselect the key field (the same field, just click it again) and it seems to do the trick.

Otherwise, it's 'view code' and manually changing the DSV & the objects affected.

|||Wow, I just sank a few hours of time because of this issue...has anyone seen an refresh/validation tool like Andrew indicated? I could easily see how it could save tons of time!