June 7, 2012 at 3:57 am
Hi Friends,
is there any way to get the isdate function in ssis? If we can through script component, then could you give me sample codes please...?
Thanks,
Charmer
June 7, 2012 at 6:52 am
There isn't one built in, but a date-check regex should be easy enough to build into a script component. Bing/Google/whatever, ".net date regex", and you'll find several samples pretty easily.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 7, 2012 at 7:17 am
GSquared (6/7/2012)
There isn't one built in, but a date-check regex should be easy enough to build into a script component. Bing/Google/whatever, ".net date regex", and you'll find several samples pretty easily.
Thanks for response and more doubt..
how can we use row_number partition in ssis?
do we have any component for that?
Thanks,
Charmer
June 7, 2012 at 9:10 am
Charmer (6/7/2012)
GSquared (6/7/2012)
There isn't one built in, but a date-check regex should be easy enough to build into a script component. Bing/Google/whatever, ".net date regex", and you'll find several samples pretty easily.Thanks for response and more doubt..
how can we use row_number partition in ssis?
do we have any component for that?
You really need to explain in better detail what you are trying to accomplish.
The simple answer is that you use it the same way you would in a T-SQL script, view, stored procedure, function, etc.
June 7, 2012 at 10:12 am
select
i.IncidentIDRef
, (row_Number() over (partition by sc.PnxCodeValue ,a.IAREF# order by a.IAREF#) + 2 ) seqno
, 'Type: ' + IATYPE
+ ' | Last Name: ' + IALNAM + ' First Name: ' + IAFNAM
+ ' | Address: ' + ISNULL(Location, 'No Address Available') + ' | Phone: ' + Case when IAPHN1 = 0 then '' else cast(IAPHN1 as nvarchar) end as comment
,dbo.GetDateTimeAdd(INCALD,(row_Number() over (partition by sc.PnxCodeValue, a.IAREF# order by a.IAREF#) + (select MAX(seqno) from GloucMidPolice.dbo.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
this is my T-SQL statement.......when i run this one on sql server, it is taking more time than ssis to execute...
so i am trying to achieve this through ssis without using this statement in oledb source directly..
Thanks,
Charmer
June 7, 2012 at 10:16 am
Lynn Pettis (6/7/2012)
Charmer (6/7/2012)
GSquared (6/7/2012)
There isn't one built in, but a date-check regex should be easy enough to build into a script component. Bing/Google/whatever, ".net date regex", and you'll find several samples pretty easily.Thanks for response and more doubt..
how can we use row_number partition in ssis?
do we have any component for that?
You really need to explain in better detail what you are trying to accomplish.
The simple answer is that you use it the same way you would in a T-SQL script, view, stored procedure, function, etc.
Lynn, if you need DDL and DMl, let me know...
Thanks,
Charmer
June 7, 2012 at 10:44 am
Here is your code formatted:
SELECT i.IncidentIDRef
,(
row_Number() OVER (
PARTITION BY sc.PnxCodeValue
,a.IAREF# ORDER BY a.IAREF#
) + 2
) seqno
,'Type: ' + IATYPE + ' | Last Name: ' + IALNAM + ' First Name: ' + IAFNAM + ' | Address: ' + ISNULL(Location, 'No Address Available') + ' | Phone: ' + CASE
WHEN IAPHN1 = 0
THEN ''
ELSE cast(IAPHN1 AS NVARCHAR)
END AS comment
,dbo.GetDateTimeAdd(INCALD, (
row_Number() OVER (
PARTITION BY sc.PnxCodeValue
,a.IAREF# ORDER BY a.IAREF#
) + (
SELECT MAX(seqno)
FROM GloucMidPolice.dbo.SrcCADCmt
)
)) AS EndDttm
,sc.PnxCodeValue
FROM PLNADR a
INNER JOIN SrcCodeMap sc ON LTRIM(RTRIM(IAORI#)) = sc.SrcCodeValueRef
AND sc.CodeID = 100
INNER JOIN PLINCD i ON a.IAREF# = i.IncidentIDRef
AND LTRIM(rtrim(iaori#)) = LTRIM(rtrim(inori#))
AND a.IAREF# = i.ININC#
LEFT JOIN PSGADR ON IAADR# = GAADR#
WHERE IAPID# = 0
How many rows of data in each table? I can see table scans involved in the query from just looking at the code.
June 7, 2012 at 10:50 am
Lynn Pettis (6/7/2012)
Here is your code formatted:
SELECT i.IncidentIDRef
,(
row_Number() OVER (
PARTITION BY sc.PnxCodeValue
,a.IAREF# ORDER BY a.IAREF#
) + 2
) seqno
,'Type: ' + IATYPE + ' | Last Name: ' + IALNAM + ' First Name: ' + IAFNAM + ' | Address: ' + ISNULL(Location, 'No Address Available') + ' | Phone: ' + CASE
WHEN IAPHN1 = 0
THEN ''
ELSE cast(IAPHN1 AS NVARCHAR)
END AS comment
,dbo.GetDateTimeAdd(INCALD, (
row_Number() OVER (
PARTITION BY sc.PnxCodeValue
,a.IAREF# ORDER BY a.IAREF#
) + (
SELECT MAX(seqno)
FROM GloucMidPolice.dbo.SrcCADCmt
)
)) AS EndDttm
,sc.PnxCodeValue
FROM PLNADR a
INNER JOIN SrcCodeMap sc ON LTRIM(RTRIM(IAORI#)) = sc.SrcCodeValueRef
AND sc.CodeID = 100
INNER JOIN PLINCD i ON a.IAREF# = i.IncidentIDRef
AND LTRIM(rtrim(iaori#)) = LTRIM(rtrim(inori#))
AND a.IAREF# = i.ININC#
LEFT JOIN PSGADR ON IAADR# = GAADR#
WHERE IAPID# = 0
How many rows of data in each table? I can see table scans involved in the query from just looking at the code.
PLINCD= around 4.7 millions, PLNADR and PSGADR has 1 million...
Thanks,
Charmer
June 8, 2012 at 12:28 am
Try to get those functions out of your join columns, if you want to use indexes.
This also seems problematic:
,dbo.GetDateTimeAdd(INCALD, (
row_Number() OVER (
PARTITION BY sc.PnxCodeValue
,a.IAREF# ORDER BY a.IAREF#
) + (
SELECT MAX(seqno)
FROM GloucMidPolice.dbo.SrcCADCmt
)
)) AS EndDttm
I guess GetDateTimeAdd is a function? So you have a function call and a subquery for each row.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 8, 2012 at 1:40 am
Koen Verbeeck (6/8/2012)
Try to get those functions out of your join columns, if you want to use indexes.This also seems problematic:
,dbo.GetDateTimeAdd(INCALD, (row_Number() OVER (
PARTITION BY sc.PnxCodeValue
,a.IAREF# ORDER BY a.IAREF#
) + (
SELECT MAX(seqno)
FROM GloucMidPolice.dbo.SrcCADCmt
)
)) AS EndDttm
I guess GetDateTimeAdd is a function? So you have a function call and a subquery for each row.
yes Koen, it is a function....yes i have subquery for each row....some times i will be having same incidentidref for multiple times...so i want to distinguish it through data and time....that's why i use function and incrementing the time with seconds....better let me post my function also...
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;
here, INCALD column is a numeric datatype and it has rows like "20000105","19990503"....so i am passing this column to the function along with row_number() partition + max(SeqNo) to get datatime column incremented by seconds to distinguish the same incidentid's with data and time.....
Thanks,
Charmer
June 8, 2012 at 1:44 am
Why don't you just assign a sequencenumber to the same incident references, instead of this overly complicated datetime function?
If you would get rid of the function and the subquery, tune your joins and index, this query will be a lot faster.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 8, 2012 at 1:48 am
Koen Verbeeck (6/8/2012)
Why don't you just assign a sequencenumber to the same incident references, instead of this overly complicated datetime function?If you would get rid of the function and the subquery, tune your joins and index, this query will be a lot faster.
i explained my concept in the previous post...Please take a look at it Koen...
Customer wants to distinguish through data and time only...so i have no chances...
if you have any idea on your mind, let me know Koen.....i would work on that one and discuss with my managers...
Thanks,
Charmer
June 8, 2012 at 7:48 am
First, if I remember correctly from previous threads, you said the data in the text columns was dirty, requiring you to do the ltrim(rtrim()) tricks in your comparisions.
With this, the first thing that you really need to do is clean up the data, period. Using ltrim(rtrim(a.somecolumn)) = b.anothercolumn keeps SQL Server from using an index that may be defined on a.somecolumn. It has to apply the functions to every row to determine if it matches b.anothercolumn. (Quick thought, SQL may still be able to use a covering index on the column a.somecolumn but I would have to test this to be sure so I would not rely on this at this time.)
Your user defined scalar functions in the select list can also be a performance killer.
If we were to tune this SQL code, we would need the DDL for the tables including indexes defined (NO extended properties please, just clutters up everything), sample data for the tables (series of INSERT statements), the current actual execution plan for the SQL, and the expected results from the query based on the sample data.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply