SSIS Script Task update on a table

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Data Warehousing » Integration Services » SSIS Script Task update on a table

SSIS Script Task update on a table

Rate Topic

Display Mode

Topic Options

Author

Message

fecker

Posted Tuesday, February 05, 2013 9:03 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, February 07, 2013 2:29 AM
Points: 4, Visits: 8

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

Post #1415923

Phil Parkin

Posted Tuesday, February 05, 2013 10:26 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:15 AM
Points: 4,242, Visits: 9,489

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?

____________________________________________________________________________________________

Help us to help you. For better, quicker and more focused answers to your questions, consider following the advice in this link:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.

Post #1415985

fecker

Posted Wednesday, February 06, 2013 2:06 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, February 07, 2013 2:29 AM
Points: 4, Visits: 8

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

Post Attachments

pic1.JPG (9 views, 18.77 KB)

pic2.JPG (9 views, 70.40 KB)

pic3.JPG (5 views, 52.00 KB)

pic4.JPG (7 views, 36.52 KB)

pic5.JPG (8 views, 64.58 KB)

Post #1416316

Phil Parkin

Posted Wednesday, February 06, 2013 2:15 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:15 AM
Points: 4,242, Visits: 9,489

fecker (2/6/2013)

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?

____________________________________________________________________________________________

Help us to help you. For better, quicker and more focused answers to your questions, consider following the advice in this link:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.

Post #1416327

John Mitchell-245523

Posted Wednesday, February 06, 2013 2:20 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:33 AM
Points: 4,428, Visits: 7,198

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

John

Post #1416330

fecker

Posted Wednesday, February 06, 2013 3:53 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, February 07, 2013 2:29 AM
Points: 4, Visits: 8

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.

Post #1416380

fecker

Posted Wednesday, February 06, 2013 4:00 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, February 07, 2013 2:29 AM
Points: 4, Visits: 8

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_id Project_id id_external id_cont id_state attempt
1 48 1 1 2000 1
2 48 7304 1 2001 0
3 48 18886 1 2000 2
4 48 18925 1 2001 0
5 48 NULL 1 1000 0
6 48 NULL 1 1003 0
7 48 18955 1 2002 0
8 48 18953 1 2013 0
9 48 18954 1 2014 0
10 48 19001 1 2015 0
11 48 19085 1 2009 0
12 48 2 2 2000 3
13 48 7305 2 2001 0

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

[color=#8B0000]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
[/color]

Post #1416388

John Mitchell-245523

Posted Wednesday, February 06, 2013 9:48 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:33 AM
Points: 4,428, Visits: 7,198

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

Post #1416607

« Prev Topic | Next Topic »

Permissions