How to write T-SQL in Scrip Component?

Question

How to write T-SQL in Scrip Component?

Charmer

SSChampion

Points: 14903
More actions
June 6, 2012 at 5:47 am

#257465

Hi friends,
I need to write this statement "Select dbo.Getdate(INCALD,0) from PLINCD " in Script component..
I don't have knowledge with C# codings... i googled but i get scripts for how to use variables in script task...
So could you guys show me how to write this one in there please?
Thanks,
Charmer
June 6, 2012 at 6:35 am

This was removed by the editor as SPAM
June 6, 2012 at 8:14 am

This was removed by the editor as SPAM
June 6, 2012 at 8:56 am

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

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

Charmer SSChampion Points: 14903 More actions · Answer 1

Stewart "Arturius" Campbell (6/6/2012)
I presume you are using SSIS?
Why must this be done using a script component?
If it is to populate a variable, consider using a SQL task, returning a single row, mapped to the relevant variable in the Result Set tag of the component.

Sorry Campbell, i forgotto mention...

yes, in ssis only....

i used Execute SQL task and it worked fine...

how can we use row_number partitioning in ssis...?

Could you help me?

Thanks,
Charmer

Charmer SSChampion Points: 14903 More actions · Answer 2

Stewart "Arturius" Campbell (6/6/2012)
Please clarify why you wish to do row_number partitioning in SSIS?

select

i.IncidentIDRef

, (row_Number() over (partition by sc.PnxCodeValue ,a.IAREF# order by a.IAREF#) + 2 ) seqno

,dbo.GetDateTimeAdd(INCALD,(row_Number() over (partition by sc.PnxCodeValue, a.IAREF# order by a.IAREF#) + (select MAX(seqno) from SrcCADCmt))) as EndDttm

, sc.PnxCodeValue

from PLNADR a

JOIN SrcCodeMap sc

ON LTRIM(RTRIM(IAORI#)) = sc.SrcCodeValueRef and sc.CodeID = 100

join PLINCD i on a.IAREF# = i.IncidentIDRef and LTRIM(rtrim(iaori#)) = LTRIM(rtrim(inori#)) and a.IAREF# = i.ININC#

left outer join PSGADR

on IAADR# = GAADR#

where IAPID# = 0

Above is my SQL statement...i need to do exactly the same way in ssis....

and the function that i used in the query is posted below....

CREATE FUNCTION [dbo].[GetDateTimeAdd](@Date varchar(8), @sec smallint)

RETURNS datetime

WITH EXECUTE as CALLER

as

BEGIN

DECLARE @FullDttm datetime

If

@Date = '0'

or

ISDATE(substring(@Date, 5,2) + '/' + substring(@Date, 7,2) + '/' + left(@Date,4)) = 0

Set @FullDttm = '1/1/1900'

Else

set @FullDttm =

substring(@Date, 5,2) + '/' + substring(@Date, 7,2) + '/' + left(@Date,4)

SET @FullDttm = dateadd(SECOND, @sec, @FullDttm)

RETURN (@FullDttm)

END;

Thanks,
Charmer