February 5, 2013 at 9:03 am
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
February 5, 2013 at 10:26 am
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?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 6, 2013 at 2:06 am
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
February 6, 2013 at 2:15 am
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?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 6, 2013 at 2:20 am
What version of SQL Server are you using? Please will you post a sample of data from your tbl_SI_Var_CS1 table?
John
February 6, 2013 at 3:53 am
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.
February 6, 2013 at 4:00 am
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
February 6, 2013 at 9:48 am
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply