SSIS Script Task update on a table

Question

SSIS Script Task update on a table

fecker

SSC-Addicted

Points: 428
More actions
February 5, 2013 at 9:03 am

#288804

Hi, I created a SSIS package to prepare data for analysis. The data comming from a phone system and for one call attempt, there a up to 20 different records. The script combines now all 20 records and allocate a "attempt id" to each.
Now the question: I have a SQL Task to select the dataset into a package variable (object).
Then a loop container with a Script Task will work record by record to allocate the attempt id.
that exists always in the first record of a set. This works easy and fine on SQL Server 2000 and
DTS with a ActiveX task. There are over 10mio records and this takes a few minutes to run. While
I try this as described above on SSIS, this will not update the records and I think the problem is the
Script Task (see code below). Maybe this does not work like I did it under DTS, but how can I get
record by record to update? I also tried a SQL statement that worked but took endless to run.
Here is the code I use within the Script Task (in a Foreach Loop Container):
Dim vars As Variables
Dts.VariableDispenser.LockOneForWrite("User::var_csid", vars)
vars(0).Value = 99
vars.Unlock()
This is only a dummy to set each value to 99 - but it does not update when I run the package in SSIS.
Thanks for your help
Frank

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 247202 More actions · Answer 1

I tried understanding what you are trying to do, but it confused me as it's not written very clearly.

But then you have said that your script task to update a variable did not, in fact, update it. If that is the extent of the question, may I ask how you traced the value of the variable before and after update? Were you using breakpoints and debugging?

fecker SSC-Addicted Points: 428 More actions · Answer 2

First: thanks for your feedback. Description may be strange while I am german.

I did some screenshots (attached) - for this dummy - maybe this will explain more. I run

this package in Development inside the Visual Studio.

Thanks,

Frank

Phil Parkin SSC Guru Points: 247202 More actions · Answer 3

fecker (2/6/2013)
First: thanks for your feedback. Description may be strange while I am german.
I did some screenshots (attached) - for this dummy - maybe this will explain more. I run
this package in Development inside the Visual Studio.
Thanks,
Frank

OK, that certainly provides helpful information. But I asked you what method you used to check the values of the variables, before and after update?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

What version of SQL Server are you using? Please will you post a sample of data from your tbl_SI_Var_CS1 table?

John

fecker SSC-Addicted Points: 428 More actions · Answer 5

I used a SQL statement on the table. I have not a lot of experiance with SQL Server 2008R2 but with DTS on SQL Server 2000. Here it was easy to run

VB ActiveX programs to modify data record by record on a table. This is what I tried here as well.

The package runs and there is also no error message, but the values on the table did not change.

fecker SSC-Addicted Points: 428 More actions · Answer 6

SQL Server 2008R2 - I attached some records. What I will do is easy. In the column

attempt is for record #1 the value 1. If the next record #2 is 0, then copy the 1 into

the attempt field. For record #3 the value in field attempt is 2. This does not change

and I will copy now the value 2 to record #4 to #10...

I did this in SQL but for 12mio records, it does not work. I copied the table into ACCESS

and wrote a short program and it took a few minutes to run.

cs_idProject_idid_externalid_contid_stateattempt

1481120001

2487304120010

34818886120002

44818925120010

548NULL110000

648NULL110030

74818955120020

84818953120130

94818954120140

104819001120150

114819085120090

12482220003

13487305220010

Here is the VB code I used in ACCESS to modify:

Public Function Attempt_id()

Dim db As DAO.Database

Dim rst As DAO.Recordset

Dim i As Integer

Dim att As Integer

'set warnings off

DoCmd.SetWarnings False

Set db = CurrentDb()

Set rst = db![dbo_tbl_SI_Var_CS].OpenRecordset

rst.MoveFirst

Do While rst.EOF = False

If rst![cs_id] = 1 Then

act_attempt = rst![attempt]

End If

If rst![attempt] > 0 Then

act_attempt = rst![attempt]

Else

rst.Edit

rst![attempt] = act_attempt

rst.Update

End If

rst.MoveNext

Loop

End Function

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

You don't need to use a Script Task. A (relatively) simple UPDATE statement will do the trick. I haven't tested this, so you may need to tweak it:

WITH Attempts AS (

SELECT

cs_id

,id_state_attempt

FROM

tbl_SI_Var_CS1

WHERE

id_state_attempt > 0

)

UPDATE

c

SET

id_state_attempt = a.id_state_attempt

FROM

Attempts a1

JOIN

tbl_SI_Var_CS1 c ON a1.cs_id > c.cs_id

JOIN

Attempts a2 ON a2.cs_id < c.cs_id

Disclaimer: some purists caution against the use of UPDATE...FROM because of the possibility of unreported cardinality errors. As long as your cs_id column is unique, it won't be an issue for you here. However, you can use the MERGE statement instead if you want to be sure. I didn't because UPDATE...FROM is simpler to write.

John