a visual basic script to DTS

Hello I am trying to convert a VB script someone wrote for my company into a DTS package that will fire automatically whenever the file is updated on the server. I am using vb.net for the firing of the DTS package, but I am having trouble writting the DTS package itself. This is what the code looks like now

If fso.FileExists(cSource) Then
With cne
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & cSource & ";" _
& "Extended Properties=Excel 8.0"
.Open
cSQL = "SELECT * FROM " & sSheet
Set rse = .Execute(cSQL)
End With

While Not rse.EOF
i = i + 1
Me.Label1.Caption = "Updating Sold: " & i
Me.Refresh
If Not IsNull(rse("VEHICLE-STOCK-NO###")) Then
sSearch = "'" & rse("VEHICLE-STOCK-NO###") & "'"
cSQL = "UPDATE Inventory SET SoldDate = '" & rse("FNLZ-DT") & "', "
cSQL = cSQL & "LastUpdate = '" & Now & "' WHERE StockNo = " & sSearch
cSQL = cSQL & " AND SoldDate IS NULL"
cn.Execute (cSQL)
End If
rse.MoveNext
Wend
rse.Close
cne.Close
End If


So basically I have an excel file that is downloaded once a week and then I run this program, well I am having some toubles with making it a DTS

I need to convert this into an active X transfermation code I think
If Not IsNull(rse("VEHICLE-STOCK-NO###")) Then
sSearch = "'" & rse("VEHICLE-STOCK-NO###") & "'"
cSQL = "UPDATE Inventory SET SoldDate = '" & rse("FNLZ-DT") & "', "
cSQL = cSQL & "LastUpdate = '" & Now & "' WHERE StockNo = " & sSearch
cSQL = cSQL & " AND SoldDate IS NULL"
end if

Thanks a lot

DamianSearch SQLDTS (http://www.sqldts.com) website for code examples and more information.

HTHsql

a short question ?

how can i convert the varchar value to a column of data type int?

i am trying to do this

declare @.w varchar(50)
select @.w = col1 from myTable
select colname from mtable
where mtableID in (@.w)

but the mtableID is a int

and i always got

Syntax error converting the varchar value to a column of data type int.

thanks for everyone for helpingtry this

where mtableID in CAST(@.w AS int)

not sure if you want to swap your "in" for an "=" or not...

a related question - how to convert from sqlexpress to sql server 2005

how does one convert from sqlexpress (mdf files) to sql server 2005 and vise-versa, ie from SQL Server 2005 to SQLExpress mdf files?Attach & detach is the easiest way to go :-)|||

What if you don't have the ldf for the SQLExpress database?

|||Look in Books Online for the usage of [sp_attach_single_file_db]

a related question - how to convert from sqlexpress to sql server 2005

how does one convert from sqlexpress (mdf files) to sql server 2005 and vise-versa, ie from SQL Server 2005 to SQLExpress mdf files?Attach & detach is the easiest way to go :-)|||

What if you don't have the ldf for the SQLExpress database?

|||Look in Books Online for the usage of [sp_attach_single_file_db]

A quicker way of performing this XML query?

Hi all

I have the following query that makes up part of a table-value-function i've written in SQL 2K5.

XML Query

SELECT CONVERT(XML, objectdata).value('(/xmlData/IsTrue)[1]', 'bit') as IsTrue
from myTable
where idfield = [@.FunctionTable].ID

Until adding this query, the function ran in under 60 seconds. Adding this query has added an extra 120 seconds to the function execution time.

This query is called around 200 times in the function as part of an update:

UPDATE @.FunctionTable set ...

....

, FieldValue = (SELECT CONVERT(XML, objectdata).value('(/xmlData/IsTrue)[1]', 'bit') as IsTrue
from myTable
where idfield = [@.FunctionTable].ID)

Is there a more performant way to do the same XML lookup?

Many Thanks

Are there any XML indexes created?

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||Probabaly not, would this index be placed upon the source field?
|||

You can create an index on the field where you do the XQuery.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

A quicker way of performing this XML query?

Hi all

I have the following query that makes up part of a table-value-function i've written in SQL 2K5.

XML Query

SELECT CONVERT(XML, objectdata).value('(/xmlData/IsTrue)[1]', 'bit') as IsTrue
from myTable
where idfield = [@.FunctionTable].ID

Until adding this query, the function ran in under 60 seconds. Adding this query has added an extra 120 seconds to the function execution time.

This query is called around 200 times in the function as part of an update:

UPDATE @.FunctionTable set ...

....

, FieldValue = (SELECT CONVERT(XML, objectdata).value('(/xmlData/IsTrue)[1]', 'bit') as IsTrue
from myTable
where idfield = [@.FunctionTable].ID)

Is there a more performant way to do the same XML lookup?

Many Thanks

Are there any XML indexes created?

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||Probabaly not, would this index be placed upon the source field?
|||

You can create an index on the field where you do the XQuery.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com