DTS ActiveX BCP file manipulation

Does anyone know the best way to handle this type of situation?

A file coming into a directory based on the date filename042707
I use the fileexist stored procedure to check for the existence.
I use xp_cmdshell(sp)... stored procedure to rename the file so that
it just has filename instead of the date... I can not use
variables within the xp_cmdshell to replace the date...
everyday the filename would change to filename042807,filename042907 etc...

Basically I know how to copy the file to another directory if it exists...
then I want to strip the right 8 characters off... rename just to that
but with the rename i have to know what the file will be named for that day ...
excuse the grammar just somewhat tired...
any suggestions... please thanks time for sleep"DTS ActiveX BCP file manipulation"

what on earth are you doing with ActiveX in SQL Server?|||not sure where r u facing problem

I can not use variables within the xp_cmdshell to replace the date...

u can use variables withing xp_cmdshell like below

declare @.cmd varchar(100)
set @.cmd = 'ren d:\file042807.xls file' + replace(convert(varchar ,getdate(),1),'/','') + '.xls'
exec master..xp_cmdshell @.cmd

what on earth are you doing with ActiveX in SQL Server?

talking about ActiveX of DTS...

DTS 2K and Hang with Ingres import

Hi All,

I have a problem with a DTS package to import data from one of our Ingres based Databases.

I've recently moved the DTS package from one server to another and recreated the Ingres ODBC Settings, which are all OK.

When I run the package through EM and the CMD line, it runs OK, but not as a scheduled task.

I've had a look at a KB Article Q318819 and followed the steps about package threads on 3rd party drivers, but this doesent help, nor does making sure DTSRun isnt in the current task list.

Could anyone help me out please?

TIA.

Hi,

Can anyone help me?

Thanks

|||

Steve,

This isn't a DTS forum. Check out the microsoft.public.sqlserver.dts newsgroup

-Jamie

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