Is it possible to pass parameter inside With Clause in SQL Server SSIS Job? -


i want pass parameter (@date1) in ssis ole db source created variable , tried pass parameter using '?' it's showing 'syntax error, permission violation or other non-specific error'

i tried this:

select dateadd(second, 1, @date1=?) starttime, --selecting calls next second of last processed time.  convert(datetime, convert(char(19), dateadd(minute, -1, current_timestamp), 120)) endtime --trim seconds.

but know can pass parameter in condition want pass in clause. possible pass parameter in condition of clause?

full query:

alter procedure [dbo].[get_call_level_details] ( @date1 datetime ) back_log_pick(starttime, endtime) ( select dateadd(second, 1, @date1) starttime, --selecting calls next second of last processed time.  convert(datetime, convert(char(19), dateadd(minute, -1, current_timestamp), 120)) endtime --trim seconds. --15 mins considered max call time. calls before 15 mins backloged , selected.  --select '18-mar-2014 18:52:00' starttime, --'18-mar-2014 18:54:00' endtime ), tcd(routercallkeyday,routercallkey,callstarttime,callendtime) ( select routercallkeyday,routercallkey ,min(datetime) callstarttime, max(datetime) callendtime termination_call_detail digitsdialed in('30013900', '30013901') group routercallkeyday,routercallkey having min(datetime)>=(select starttime back_log_pick)  , min(datetime) < (select endtime back_log_pick) --any call started between our interested time selected, if call 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) ( 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 termination_call_detail , tcd b    a.routercallkey = b.routercallkey  , a.routercallkeyday = b.routercallkeyday    --and min(datetime)>=(select starttime back_log_pick)  --and max(datetime) < (select endtime back_log_pick)  ), calldisposition(routercallkey, routercallkeyday, hangup_flag) ( select routercallkey, routercallkeyday,  ( case  when max(calldisposition) = 52 'ad'  else      case      when max(calldisposition) = 13 'cd'      else 'sd'      end  end) hangup_flag  tcdrecords group routercallkey, routercallkeyday ), calltype1prepare(routercallkeyday,routercallkey,duration, localqtime) ( select distinct routercallkeyday,routercallkey, max(duration), sum(localqtime) tcdrecords  peripheralcalltype =1   group routercallkeyday,routercallkey ), calltype1preparedistinct(rownumber,routercallkeyday,routercallkey,datetime, digitsdialed, dnis,   ani,calldisposition,networktime,duration,ringtime,delaytime,timetoaband,holdtime,   worktime,localqtime,callsegmenttime,conferencetime,networkskillgroupqtime,enterprisequeuetime) ( select row_number() over(partition b.routercallkeyday,b.routercallkey order b.routercallkeyday,b.routercallkey   desc) rownumber,         b.routercallkeyday,b.routercallkey, datetime, digitsdialed, dnis, ani,calldisposition,networktime,   b.duration,             ringtime,delaytime,timetoaband,             holdtime,worktime,b.localqtime,             callsegmenttime,conferencetime conferencetime,             networkskillgroupqtime,enterprisequeuetime tcdrecords a, calltype1prepare b   a.routercallkeyday = b.routercallkeyday   , a.routercallkey = b.routercallkey   , a.duration = b.duration  ), calltype1(routercallkeyday,routercallkey,datetime, digitsdialed, dnis,   ani,calldisposition,networktime,duration,ringtime,delaytime,timetoaband,holdtime,   worktime,localqtime,callsegmenttime,conferencetime,networkskillgroupqtime,enterprisequeuetime) (              select routercallkeyday,routercallkey,datetime, digitsdialed, dnis, ani,calldisposition,networktime,             duration,             ringtime,delaytime,timetoaband,             holdtime,worktime,localqtime,             callsegmenttime,conferencetime conferencetime,             networkskillgroupqtime,enterprisequeuetime calltype1preparedistinct rownumber = 1 ), calltype2 (routercallkeyday,routercallkey,calldisposition,agentskilltargetid,skillgroupskilltargetid, agentperipheralnumber,holdtime,talktime,worktime, datetime, digitsdialed, dnis, ani) (            select   routercallkeyday,routercallkey,calldisposition,agentskilltargetid,skillgroupskilltargetid,agentperipheralnumber,holdtime  ,talktime,worktime,            datetime, digitsdialed, dnis, ani  tcdrecords  peripheralcalltype =2 ) select  p1.routercallkey routercallkey, p1.routercallkeyday routercallkeyday,  p1.datetime , agentperipheralnumber, agentskilltargetid, skillgroupskilltargetid, p1.digitsdialed dnis, p1.ani, p2.talktime, p2.holdtime, p2.worktime, duration, p2.dnis extension, p1.localqtime localqtime, cd.hangup_flag calltype1 p1  left outer join calltype2 p2  on p1.routercallkey = p2.routercallkey  , p1.routercallkeyday = p2.routercallkeyday left outer join calldisposition cd on p1.routercallkey = cd.routercallkey  , p1.routercallkeyday = cd.routercallkeyday;

i write this:

select dateadd(second, 1, ?) starttime, convert(datetime, convert(char(19), dateadd(minute, -1, current_timestamp), 120)) endtime

or 

declare @date1 datetime set @date1 = ? select dateadd(second, 1, @date1), convert(datetime, convert(char(19), dateadd(minute, -1, current_timestamp), 120)) endtime

the latter syntax easier check in mgt studio


Comments

Post a Comment

Popular posts from this blog

c# - Validate object ID from GET to POST -

i have method allows user access comment have made on site. have post method lets them update comment. my normal solution send entire comment model through, let them update it, update on database when post back. involve sending commentid through in hiddenfor , can manipulated. how can verify commentid sent in get method same i'm getting in post , not able alter comment wish? how can verify commentid sent in method same i'm getting in post? basically have validate following things in post - user logged-in user. authenticated users post comments. commentid in post , should valid commentid , should present in database. userid associated logged-in user should same userid associated comment . comment should contain userid column, can check @ time of update. to make sure update happens comment has been sent in get - hold commentid in session , in post action compare commentid value in session .
Read more

node.js - Custom Model Validator SailsJS -

when using custom model validator checks record within model. return within spread doesn't seem end control flow. //post.js model /** * custom validator **/ types: { isuservalid: function(user_id) { var promise = require('bluebird'); promise.all([ user.findone({id: user_id}) ]) .spread(function(user) { console.log(user); if (user === null || user === undefined) { console.log('failed'); return false; }else{ console.log('passed'); return true; } }); } }, my response standard validation failed response. { "error": "e_validation", "status": 400, "summary": "1 attribute invalid", "model": "post", "invalidattributes": { "owner": [ { "rule": "isuservalid", ...
Read more

php - Find a regex to take part of Email -

i have following emails need extract part from: bestellnummer xxx: 1 von xxx, 1er pack ------------- anfang der nachricht ------------- foo bar baz foo bar baz // <<<<< need text here ------------- ende der nachricht ------------- ------------- anfang der nachricht ------------- foo bar baz foo bar baz ------------- ende der nachricht ------------- there 0 unlimited occurences of ------------- anfang der nachricht ------------- ------------- ende der nachricht ------------- and i'm able extract first part regex: $re = "/------------- .*? -------------.?(.*?).?------------- .*? -------------/s"; but, i'm quite new on learning regex, i'm pretty sure there must better regex extract part (foo bar baz foo bar baz) of text between ------------- anfang der nachricht ------------- ------------- ende der nachricht ------------- as can in different languages, i'm using .? to match between hyphens. i...
Read more