SurendraThotaMSBIProfessional

In many cases for incremental load we will consider datetime column . when we want to implement incremental load based on datetime column in SSIS we should be very careful because the milliseconds will be truncated in SSIS datetime variable . In This case we will face issue if the data is in milliseconds also . Workaround  :  To avoid this milliseconds issue  create variable sof string instead of Datetime and load the value using the fallowing query select CONVERT(VARCHAR(23), max(LastUpdated), 121) as MaxLastUpdated from [Schema].[OPS_CDR_Scrapper_Data] and use that varible in  OLEDB source  like below select FileName, Folder, LastUpdated from scraper_log_file_details where LastUpdated > CONVERT(DATETIME, ?, 121) and map the parameter .

In one of our requirement we need  to convert the rows into columns in sql server for that we used pivot keyword . here is the example : we need get the number of counts for each status on date basis . SELECT DATEADD ( dd , 0 , DATEDIFF ( dd , 0 , getdate ()- 1 )) AS theDate , [Active] , [On-Order] , [Scrapped] , [Stock] FROM ( SELECT SIMStatus , simid FROM tbl_sims ) AS SourceTable PIVOT ( count ( simid ) FOR SIMStatus IN ( [Active] , [On-Order] , [Scrapped] , [Stock] ) ) AS PivotTable ;

To get the date format in European style that is dd/MM/yyyy   then we need use Cdate function Example: MEMBER MyPeriod1 as CASE @Granularity      WHEN  "M" THEN  ([Date].[Hierarchy].currentmember.parent.member_caption+"/"+[Date].[Hierarchy].currentmember.member_caption)                                else Format(CDate([Date].[Hierarchy].currentmember.member_caption),"dd/MM/yyyy")           end

Hi All we are using the SFTP task from codeplex . we have configured the task and when we try to run we got the allowing error [SSISSFTTask] Error: Error : Access to the path 'DRA\Dev\Source\Server1' is denied. It is local Path we given in SFTP task . we have access to that path . after strugling for some time we came to know that we need to use the complete path including filename .

if you want calculate cummulative sum from the starting till date . Then you need to use SUM({NULL:[Date].[Hierarchy].currentmember},[Measures].[SIMID]) The above query gives from  from starting SIMID availble to till the end of the date dimension but  to restrict the cummulative value to only from start to latest date then use below query . iif([Measures].[SIMID]>0,SUM({NULL:[Date].[Hierarchy].currentmember},[Measures].[SIMID]),null)