Recordset in ActiveX Script Task

[rmchung]

I'm attempting to parse out a column of data in a table(Table A) into another table(TableB) that has all the fields. In total there are 127 fields that are going to be populated. Can I use a standard SQL INSERT statement to do this or will I reach a max limit on the size of my SQL statement? I was thinking this would happen so I tried inserting records into TableB by way of a recordset. Ithe ActiveX Script Task window, I keep getting an error message saying my ADODB.Recordset does not support updating. Am I missing a piece of logic here?

Dim...

strConnection = "Provider=SQLOLEDB;Integrated Security=SSPI;Initial Catalog=SPA_Dev;Data Source=(local)"

Set rsSource = createobject("ADODB.Recordset&quot
rsSource.Open "TableA", strConnection, adOpenDynamic

Set rsDest = CreateObject ("ADODB.Recordset&quot
rsDest.Open "TableB", strConnection, adOpenDynamic
rsSource.movefirst

Do...
strLine = rsSource.fields(0).value
rsdest.addnew
rsDest.fields(0) = mid(strLine,961,1)
rsdest.update
rsSource.movenext
End

 

[tb]

First : you are not specifying the Lock type of the recordset ...

Second: why don't you rather use something like this

conn.execute "Insert into TableB Select SubString (FieldName,961,1) From TableA "

Hope this helps.
{flowerface]



I was standing in the park, wondering why frisbees got bigger as they came closer... then it hit me!

 

[tb]

First : you are not specifying the Lock type of the recordset ...

Second: why don't you rather use something like this

conn.execute "Insert into TableB Select SubString (FieldName,961,1) From TableA "

Hope this helps.




I was standing in the park, wondering why frisbees got bigger as they came closer... then it hit me!

 

[rmchung]

I was thinking of just using an INSERT statement, but again, I need to insert 127 fields. Can an SQL statement be that long?

In other words, my INSERT statement would look like:

INSERT INTO TableB (FieldName1, FieldName2, FieldName3...FieldName127) SELECT SUBSTRING(FieldName1,2,6), SUBSTRING(FieldName2,300,19), SUBSTRING(FieldName3,7653,5)...SUBSTRING(FieldName127,8114,1) FROM TableA

 

[JVZ]

I'm pretty sure that not a problem... I have a select-insert statement using substrings that insert 78 fields. Another way to do what you want may be to use Transform Data Task and create a ActiveX Script inside the task, here is a example:

Function Main()
    DTSDestination("RecordID") = mid(DTSSource("cRecord"), 1, 6)
    DTSDestination("Date_of_Record") = mid(DTSSource("cRecord"), 7, 6)
    DTSDestination("FromNumberLength") = mid(DTSSource("cRecord"), 13, 2)
    ....
    ....
    Main = DTSTransformStat_OK
End Function