2

I want to pass a parameter (@date1) in my SSIS OLE DB Source so I created a variable and I tried to pass the parameter using '?' but it's showing 'Syntax error, permission violation or other non-specific error'

I tried like this:

select dateAdd(second, 1, @date1=?) StartTime, --Selecting calls from the next second of last processed time. 
convert(datetime, convert(char(19), dateAdd(minute, -1, CURRENT_TIMESTAMP), 120)) EndTime --TRIM to seconds.

But I know I can pass parameter in where condition but I want to Pass it in select.

Exact With Query:

select dateAdd(second, 1, @date1) StartTime, --Selecting calls from the next second of last processed time. 
convert(datetime, convert(char(19), dateAdd(minute, -1, CURRENT_TIMESTAMP), 120)) EndTime --TRIM to seconds.

Full Query:

ALTER PROCEDURE [dbo].[GET_CAll_LEVEL_DETAILS] ( @date1 DateTime )
As
With BACK_LOG_PICK(StartTime, ENDTIME) as
(
select dateAdd(second, 1, @date1) StartTime, --Selecting calls from the next second of last processed time. 
convert(datetime, convert(char(19), dateAdd(minute, -1, CURRENT_TIMESTAMP), 120)) EndTime --TRIM to seconds.
--15 mins is considered as max call time. So calls before 15 mins are backloged and selected.

--select '18-mar-2014 18:52:00' StartTime,
--'18-mar-2014 18:54:00' EndTime
),
TCD(RouterCallKeyDay,RouterCallKey,CallStartTime,CallEndTime)
As
(
select RouterCallKeyDay,RouterCallKey
,Min(DateTime) as CallStartTime, Max(DateTime) as CallEndTime from Termination_Call_Detail
where DigitsDialed in('30013900', '30013901')
group by RouterCallKeyDay,RouterCallKey
having Min(DateTime)>=(Select StartTime from BACK_LOG_PICK) 
and Min(DateTime) < (Select ENDTIME from BACK_LOG_PICK)
--Any Call started between our interested time is selected, even if the call is not ended in our time interval.
),
TCDRecords (AgentSkillTargetID,SkillGroupSkillTargetID,ServiceSkillTargetID,PeripheralID,RouteID,
  RouterCallKeyDay,RouterCallKey,DateTime,PeripheralCallType,DigitsDialed,PeripheralCallKey,
  CallDisposition,NetworkTime,Duration,RingTime,DelayTime,TimeToAband,HoldTime,TalkTime,
  WorkTime,LocalQTime,CallSegmentTime,ConferenceTime,NetworkTargetID,TrunkGroupID,DNIS,
  InstrumentPortNumber,AgentPeripheralNumber,ICRCallKey,ICRCallKeyParent,ICRCallKeyChild,
  ANI,AnsweredWithinServiceLevel,Priority,Trunk,CallDispositionFlag,RouterCallKeySequenceNumber,
  CED,CallTypeID,BadCallTag,ApplicationTaskDisposition,ApplicationData,NetQTime,CallTypeReportingDateTime,
       NetworkSkillGroupQTime,EnterpriseQueueTime) as
(
select AgentSkillTargetID,SkillGroupSkillTargetID,ServiceSkillTargetID,PeripheralID,RouteID,
  b.RouterCallKeyDay,b.RouterCallKey,DateTime,PeripheralCallType,DigitsDialed,PeripheralCallKey,
  CallDisposition,NetworkTime,Duration,RingTime,DelayTime,TimeToAband,HoldTime,TalkTime,
  WorkTime,LocalQTime,CallSegmentTime,ConferenceTime,NetworkTargetID,TrunkGroupID,DNIS,
  InstrumentPortNumber,AgentPeripheralNumber,ICRCallKey,ICRCallKeyParent,ICRCallKeyChild,
  ANI,AnsweredWithinServiceLevel,Priority,Trunk,CallDispositionFlag,RouterCallKeySequenceNumber,
  CED,CallTypeID,BadCallTag,ApplicationTaskDisposition,ApplicationData,NetQTime,CallTypeReportingDateTime,
  NetworkSkillGroupQTime,EnterpriseQueueTime from Termination_Call_Detail a , TCD b 
  where a.RouterCallKey = b.RouterCallKey  and a.RouterCallKeyDay = b.RouterCallKeyDay 
  --and Min(DateTime)>=(Select StartTime from BACK_LOG_PICK) 
--and Max(DateTime) < (Select ENDTIME from BACK_LOG_PICK) 
),
CallDisposition(RouterCallKey, RouterCallKeyDay, Hangup_Flag) as
(
select RouterCallKey, RouterCallKeyDay, 
(
case 
when max(CallDisposition) = 52 then 'AD' 
else 
    case 
    when max(CallDisposition) = 13 then 'CD' 
    else 'SD' 
    end 
end) as Hangup_Flag 
from TCDRecords group by RouterCallKey, RouterCallKeyDay
),
callType1prepare(RouterCallKeyDay,RouterCallKey,Duration, LocalQTime)
As
(
select distinct RouterCallKeyDay,RouterCallKey, MAX(Duration), SUM(LocalQTime) from TCDRecords 
WHERE PeripheralCallType =1 
 group by RouterCallKeyDay,RouterCallKey
),
CallType1PrepareDistinct(RowNumber,RouterCallKeyDay,RouterCallKey,DateTime, DigitsDialed, DNIS, 

ANI,CallDisposition,NetworkTime,Duration,RingTime,DelayTime,TimeToAband,HoldTime,
  WorkTime,LocalQTime,CallSegmentTime,ConferenceTime,NetworkSkillGroupQTime,EnterpriseQueueTime) as
(
select Row_Number() Over(Partition by b.RouterCallKeyDay,b.RouterCallKey Order By b.RouterCallKeyDay,b.RouterCallKey 

desc) As RowNumber,
        b.RouterCallKeyDay,b.RouterCallKey, DateTime, DigitsDialed, DNIS, ANI,CallDisposition,NetworkTime, 

b.Duration,
            RingTime,DelayTime,TimeToAband,
            HoldTime,WorkTime,b.LocalQTime,
            CallSegmentTime,ConferenceTime as ConferenceTime,
            NetworkSkillGroupQTime,EnterpriseQueueTime FROM TCDRecords a, callType1prepare b
  WHERE a.RouterCallKeyDay = b.RouterCallKeyDay
  and a.RouterCallKey = b.RouterCallKey
  and a.Duration = b.Duration 
),
CallType1(RouterCallKeyDay,RouterCallKey,DateTime, DigitsDialed, DNIS, 

ANI,CallDisposition,NetworkTime,Duration,RingTime,DelayTime,TimeToAband,HoldTime,
  WorkTime,LocalQTime,CallSegmentTime,ConferenceTime,NetworkSkillGroupQTime,EnterpriseQueueTime)
AS
(

            SELECT RouterCallKeyDay,RouterCallKey,DateTime, DigitsDialed, DNIS, ANI,CallDisposition,NetworkTime,
            Duration,
            RingTime,DelayTime,TimeToAband,
            HoldTime,WorkTime,LocalQTime,
            CallSegmentTime,ConferenceTime as ConferenceTime,
            NetworkSkillGroupQTime,EnterpriseQueueTime FROM CallType1PrepareDistinct WHERE RowNumber = 1
),
CallType2
(RouterCallKeyDay,RouterCallKey,CallDisposition,AgentSkillTargetID,SkillGroupSkillTargetID,
AgentPeripheralNumber,HoldTime,TalkTime,WorkTime,
DateTime, DigitsDialed, DNIS, ANI)
As
(
           SELECT 

RouterCallKeyDay,RouterCallKey,CallDisposition,AgentSkillTargetID,SkillGroupSkillTargetID,AgentPeripheralNumber,HoldTime

,TalkTime,WorkTime,
           DateTime, DigitsDialed, DNIS, ANI
 from TCDRecords  WHERE PeripheralCallType =2
)
select 
p1.RouterCallKey as RouterCallkey,
p1.RouterCallKeyDay as RouterCallKeyDay, 
p1.DateTime ,
AgentPeripheralNumber,
AgentSkillTargetID,
SkillGroupSkillTargetID,
p1.DigitsDialed as DNIS,
p1.ANI,
p2.TalkTime,
p2.HoldTime,
p2.WorkTime,
Duration,
p2.DNIS as Extension,
p1.LocalQTime as LocalQTime,
CD.Hangup_Flag
from CallType1 p1 
left outer join CallType2 p2 
on p1.RouterCallKey = p2.RouterCallKey 
and p1.RouterCallKeyDay = p2.RouterCallKeyDay
left outer join CallDisposition CD
on p1.RouterCallKey = CD.RouterCallKey 
and p1.RouterCallKeyDay = CD.RouterCallKeyDay;
2

It's not possible to bind column names to variables, but you can achieve similar results by using dynamic SQL.

DECLARE @datetxt varchar(10) = '2015-01-01'
DECLARE @sqltxt nvarchar(1000); 

SET @sqltxt = N'SELECT dateAdd(second, 1, ' + @datetxt + ') AS StartTime FROM Termination_Call_Detail'
Exec (@sqltxt)
2

If I'm understanding you correctly what you're looking to do should be quite simple.

I'm not entirely sure how you're setting @date1 but for this to work you will need to set that as a SSIS variable and for this example I'll assum it's a date. Then you need to create your query as a variable and then set it as an expression like so.

Expression

Then you set your expression and replace your question mark with your variable and which you type cast to a string like so.

"select dateAdd(second, 1,@date1= "+ (DT_STR, 50, 1252) @[User::Date1] + " ) StartTime, convert(datetime, convert(char(19), dateAdd(minute, -1, CURRENT_TIMESTAMP), 120)) EndTime"

enter image description here

Then in your OLEDB task simply select query from variable and you're good to go.

SQLFromVariable

FreeHand

  • missing complementary @dis1 variable – swasheck Jun 16 '15 at 20:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.