August 16, 2012 at 4:06 am
Hi All,
I would be great full on suggestions on how to implement the following the SQL Statement in SSIS:
[font="Arial Black"]DECLARE @num AS INT, @TEXT AS VARCHAR(50)
UPDATE dbo.TableA SET ColumnA=NULL;
SET @TEXT = (SELECT MIN(Text) FROM dbo.TableA WHERE ColumnA IS NULL);
SET @num=0
WHILE (@TEXT IS NOT NULL)
BEGIN
UPDATE dbo.TableA SET ColumnA=@num WHERE AText=@TEXT;
SET @num=@num + 1
SET @TEXT = (SELECT MIN(Text) FROM dbo.TableA WHERE ColumnA IS NULL);
END
--------------------------------------------------------
UPDATE dbo.TableA_With SET ColumnA=NULL;
SET @TEXT = (SELECT MIN(Text) FROM dbo.TableB_With WHERE ColumnA IS NULL);
SET @num=0
WHILE (@TEXT IS NOT NULL)
BEGIN
UPDATE dbo.TableA_With SET ColumnA=@num WHERE AText=@TEXT;
SET @num=@num + 1
SET @TEXT = (SELECT MIN(Text) FROM dbo.TableA_With WHERE ColumnA IS NULL);
END[/font]
If there are any suggestions on how to be best do this please.
Thanks,
August 16, 2012 at 7:09 am
Use Execute sql take
August 16, 2012 at 7:55 am
Thank you for your reply.
Would it not be better to use a For Loop task with an Execute SQL task inside it?
Would this not make use of the SSIS in memory processing? I need to make this as efficient as possible as the package is quite large.
I am not sure how to implement the queries in the For Loop. I have done the following in the 'For Loop Editor':
InitExpression: @[NUM] = 0
EvalExpression: ISNULL(@[ATEXT] ) == TRUE
'SQL Task Editor':
I have entered the following SQL statement -
UPDATE dbo.Activity SET Ranking=NULL
UPDATE dbo.Activity
SET Ranking= @[User::NUM] WHERE AText= @[User::ATEXT]
SET @[User::NUM] = @[User::NUM] + 1
SET @[User::ATEXT] = (SELECT MIN(AText) FROM dbo.Activity WHERE Ranking IS NULL)
BUt I can't get this to work. Any guidance would be appreciated.
Thanks
August 16, 2012 at 7:59 am
I relaise it is still an Execute task so the SQL will be run against the database.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply