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 activex task to query soap web service ?

Hello there
I wanted to know if anybody has an example of an activex task in a dts that
will query a soap service ?
I never queries a soap service from vbscript so I guess i need to see an
example to start ? please
ThanksSimo Sentissi wrote:
> Hello there
> I wanted to know if anybody has an example of an activex task in a dts tha
t
> will query a soap service ?
> I never queries a soap service from vbscript so I guess i need to see an
> example to start ? please
> Thanks
>
There have been various toolkits released and the .Net tools have good
support for SOAP. Trying to write that by hand in VBS would seem like
very hard work. I would look to develop something in .Net that does the
work and wrap it either as a custom task or as a COM DLL for use from
the ActiveX Script.
Darren
http://www.sqldts.com
http://www.sqlis.com

DTS activex task to query soap web service ?

Hello there
I wanted to know if anybody has an example of an activex task in a dts that
will query a soap service ?
I never queries a soap service from vbscript so I guess i need to see an
example to start ? please
Thanks
Simo Sentissi wrote:
> Hello there
> I wanted to know if anybody has an example of an activex task in a dts that
> will query a soap service ?
> I never queries a soap service from vbscript so I guess i need to see an
> example to start ? please
> Thanks
>
There have been various toolkits released and the .Net tools have good
support for SOAP. Trying to write that by hand in VBS would seem like
very hard work. I would look to develop something in .Net that does the
work and wrap it either as a custom task or as a COM DLL for use from
the ActiveX Script.
Darren
http://www.sqldts.com
http://www.sqlis.com

DTS activex task to query soap web service ?

Hello there
I wanted to know if anybody has an example of an activex task in a dts that
will query a soap service ?
I never queries a soap service from vbscript so I guess i need to see an
example to start ? please
ThanksSimo Sentissi wrote:
> Hello there
> I wanted to know if anybody has an example of an activex task in a dts tha
t
> will query a soap service ?
> I never queries a soap service from vbscript so I guess i need to see an
> example to start ? please
> Thanks
>
There have been various toolkits released and the .Net tools have good
support for SOAP. Trying to write that by hand in VBS would seem like
very hard work. I would look to develop something in .Net that does the
work and wrap it either as a custom task or as a COM DLL for use from
the ActiveX Script.
Darren
http://www.sqldts.com
http://www.sqlis.com

DTS activex task to query soap web service ?

Hello there
I wanted to know if anybody has an example of an activex task in a dts that
will query a soap service ?
I never queries a soap service from vbscript so I guess i need to see an
example to start ? please
ThanksSimo Sentissi wrote:
> Hello there
> I wanted to know if anybody has an example of an activex task in a dts that
> will query a soap service ?
> I never queries a soap service from vbscript so I guess i need to see an
> example to start ? please
> Thanks
>
There have been various toolkits released and the .Net tools have good
support for SOAP. Trying to write that by hand in VBS would seem like
very hard work. I would look to develop something in .Net that does the
work and wrap it either as a custom task or as a COM DLL for use from
the ActiveX Script.
--
Darren
http://www.sqldts.com
http://www.sqlis.com

DSO

I was using DSO to make atree based on this example. But my Question is where could i put code for complexity_penalty or minimum_leaf_case in my code below?

And what's complexity_penalty or minimum_leaf_case use for? are they important in our coding?

Public Sub CreateRelMiningModel()
Dim dsoServer As New DSO.Server
Dim dsoDB As DSO.MDStore
Dim dsoDS As DSO.DataSource
Dim dsoDMM As DSO.MiningModel
Dim dsoColumn As DSO.Column
Dim dsoRole As DSO.Role

Dim strLQuote As String, strRQuote As String
Dim strFromClause As String

' Constants used for DataType property
' of the DSO.Column object.
' Note that these constants are identical to
' those used in ADO in the DataTypeEnum enumeration.
Const adInteger = 3
Const adWChar = 130

' Connect to the server on this computer.
dsoServer.Connect "LocalHost"

' Select the FoodMart 2000 database.
Set dsoDB = dsoServer.MDStores("FoodMart 2000")

' Retrieve the open and close quote characters for
' the FoodMart data source.
strLQuote = dsoDB.DataSources("FoodMart").OpenQuoteChar
strRQuote = dsoDB.DataSources("FoodMart").CloseQuoteChar

' The Customer table is the fact table for this
' relational data mining model; this variable will
' make it easier to understand the code that
' follows.
strFromClause = strLQuote & "customer" & strRQuote

' Check for the existence of the model on this computer.
If Not dsoDB.MiningModels("CustSalesModelRel") Is Nothing Then
' If this model exists, delete it.
dsoDB.MiningModels.Remove "CustSalesModelRel"
End If

' Create a new relational mining model
' called CustSalesModelRel.
Set dsoDMM = dsoDB.MiningModels.AddNew("CustSalesModelRel", _
sbclsRelational)

' Create a new mining model role called All Users
Set dsoRole = dsoDMM.Roles.AddNew("All Users")

' Set the needed properties for the new mining model.
With dsoDMM
.DataSources.AddNew "FoodMart", sbclsRegular
' Set the description of the model.
.Description = "Analyzes the salaries " & _
"of customers"
' Set the case table for the model to the
' Customer table.
.FromClause = strFromClause
' Select the algorithm provider for the model.
.MiningAlgorithm = "Microsoft_Decision_Trees"
' Let DSO define the training query.
.TrainingQuery = ""
' Save the existing structure.
.Update
End With

' Create the columns pertinent to the new model.

' Create the CustomerID column as a key column.
Set dsoColumn = dsoDMM.Columns.AddNew("CustomerID", _
sbclsRegular)
' Set the column properties for the new column.
With dsoColumn
' Set the source field from the case table for
' the column.
.SourceColumn = strFromClause & "." & strLQuote & _
"customer_id" & strRQuote
.DataType = adInteger
.IsKey = True
.IsDisabled = False
End With

' Create the Gender column as an attribute column.
Set dsoColumn = dsoDMM.Columns.AddNew("Gender", _
sbclsRegular)
With dsoColumn
.ContentType = "DISCRETE"
.SourceColumn = strFromClause & "." & strLQuote & _
"gender" & strRQuote
.DataType = adWChar
.IsDisabled = False
End With

' Create the Marital Status column as an attribute column.
Set dsoColumn = dsoDMM.Columns.AddNew("Marital Status", _
sbclsRegular)
With dsoColumn
.ContentType = "DISCRETE"
.SourceColumn = strFromClause & "." & strLQuote & _
"marital_status" & strRQuote
.DataType = adWChar
.IsDisabled = False
End With

' Create the Education column as an attribute column.
Set dsoColumn = dsoDMM.Columns.AddNew("Education", _
sbclsRegular)
With dsoColumn
.ContentType = "DISCRETE"
.SourceColumn = strFromClause & "." & strLQuote & _
"education" & strRQuote
.DataType = adWChar
.IsDisabled = False
End With

' Create the Yearly Income column as an predictable column.
Set dsoColumn = dsoDMM.Columns.AddNew("Yearly Income", _
sbclsRegular)
With dsoColumn
.ContentType = "DISCRETE"
.SourceColumn = strFromClause & "." & strLQuote & _
"yearly_income" & strRQuote
.DataType = adWChar
.IsInput = False
.IsPredictable = True
.IsDisabled = False
End With

' Save the data mining model.
With dsoDMM
' Set the LastUpdated property of the new mining model
' to the present date and time.
.LastUpdated = Now
' Save the model definition.
.Update
End With

' Process the data mining model.
With dsoDMM
' Lock the mining model for processing
.LockObject olapLockProcess, _
"Processing the data mining model in sample code"
' Fully process the new mining model.
' This may take up to several minutes.
.Process processFull
' Unlock the model after processing is complete.
.UnlockObject
End With

' Clean up objects and close server connection
Set dsoRole = Nothing
Set dsoColumn = Nothing
Set dsoDMM = Nothing

dsoServer.CloseServer
Set dsoServer = Nothing

End Sub

thank's a lot

COMPLEXITY_PENALTY and MINIMUM_LEAF_CASES control the growth of decision trees - a higher complexity_penalty lowers the likelihood of splits; increasing minimum_leaf_cases prevents generation of leaf nodes with low support (number of cases that fall into that node).

You can add training parameters using the MiningModel object's Parameters collection. This download includes the source code for an Analysis Mgr Add-in that edits mining model parameters using DSO: http://www.sqlserverdatamining.com/DMCommunity/SQLServer2000/Links_LinkRedirector.aspx?id=97