I would like to create DTS for Importing from CSV using store procedure, in which I would like to update fields conditionally.

Anyone can help in this matter or let me know url/tutorial on this.

Thanks in advance


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.699 / Virus Database: 456 - Release Date: 06/04/2004

No real need here for DTS other then using it is a workflow engine.

Look up BULK INSERT in BOL.

Stage the data into a working entity and then use TSQL Statements to do your
UPDATEs

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Ashish Kanoongo" <ashishk (AT) armour (DOT) com> wrote

I would like to create DTS for Importing from CSV using store procedure, in
which I would like to update fields conditionally.

Anyone can help in this matter or let me know url/tutorial on this.

Thanks in advance


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.699 / Virus Database: 456 - Release Date: 06/04/2004

I have following logic , how do use this logic in DTS. Here is my logic

*------------------------------------------------
Dim reccount As Double
'On Error Resume Next
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H1
Set objConnection = CreateObject("ADODB.Connection")
Set objConn = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")

strPathtoTextFile = "C:\"

objConn.Open ("Provider=SQLOLEDB.1;Persist Security Info=False;User
ID=sa;Initial Catalog=stocks;Data Source=webserver1")

objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strPathtoTextFile & ";" & _
"Extended Properties=""text;HDR=YES;FMT=FixedLength"""

objRecordset.Open "SELECT * FROM accounts.csv", _
objConnection, adOpenStatic, adLockOptimistic, adCmdText

Do Until objRecordset.EOF
strCSV = "update accounts set closed = 0 where accountid=" &
objRecordset.Fields.Item("AccountID")
objConn.Execute strCSV
objRecordset.MoveNext
Loop
objRecordset.Close
objRecordset.Open "select count(*) from accounts where closed=0", objConn
MsgBox objRecordset(0)

*-------------------------------
"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.700 / Virus Database: 457 - Release Date: 06/06/2004

OK

So you want to update a table in SQL Server from values in a text field. I
ALWAYS import text files into staging tables if I am going to do something
like this and then use TSQL to do the sexy bits

--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Ashish Kanoongo" <ashishk (AT) armour (DOT) com> wrote

Hi

This is untested but you may want to try opening the datasource within the
update statement therefore there would be no need to step through the record
set.

strCSV = "update a set closed = 0
from accounts a JOIN
OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source=""" & strPathtoTextFile & """;" & _
"Extended
Properties=""text;HDR=YES;FMT=FixedLength""')...ac counts.csv c
ON a.accountid= C.accountid"

objConn.Execute strCSV

John

"Ashish Kanoongo" <ashishk (AT) armour (DOT) com> wrote

Thanks john. But I wouldlike to use this in DTS and I am new to DTS, so I am
looking for a guidance for all sql Gurus.
"John Bell" <jbellnewsposts (AT) hotmail (DOT) com> wrote


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.700 / Virus Database: 457 - Release Date: 06/06/2004

Hi

The code would fit into your existing script.

John

"Ashish Kanoongo" <ashishk (AT) armour (DOT) com> wrote