Replace CRLF with LF to Rescue My Packages –Powershell

Get-ChildItem -Path d:\mypackages\* -recurse |  Select-String -Pattern "myString" | Out-File c:\temp\filesContainsMyString.txt  

get-childitem d:\mypackages\*.dtsx -recurse |  
 select -expand fullname |
  foreach {
 $mycontent = Get-Content $_

if ($mycontent  -like '*myString*' )
{
(Get-Content $_) -replace "myString", "myOtherString"  | Set-Content $_}
}

#######################################################

get-childitem d:\mypackages\*.dtsx -recurse | 
 select -expand fullname |
foreach { 
 $mycontent = Get-Content $_

if ($mycontent  -like '*myOtherString*') 
{
 $text1 = [IO.File]::ReadAllText($_) -replace "`r`n", "`n"  
[IO.File]::WriteAllText($_, $text1)

}
}

Get-ChildItem -Path d:\mypackages\* -recurse |  Select-String -Pattern "myOtherString" | Out-File c:\temp\filesContainsMyOtherString.txt  

 


########## use encoding switch for Set-Content should be better option

##### http://stackoverflow.com/questions/28123279/powershell-set-content-seems-to-corrupt-my-ssis-dtsx-files
#####       Set-Content $_ -Enc Utf8    

get-childitem d:\mypackages\*.dtsx -recurse |  
 select -expand fullname |
  foreach {
 $mycontent = Get-Content $_

if ($mycontent  -like '*myString*' )
{
(Get-Content $_) -replace "myString", "myOtherString"  | Set-Content $_ -Enc Utf8}
}

Calculating Date Difference for Multiple Events

  

  create table tableA (PersonID int, EventDate date, EventLabel varchar(10))

Insert into tableA values(1,'2010-07-17','e1'),
(1,'2011-02-05','e2'),
(1,'2012-01-11','e3'),
(7,'2009-05-13','e1'),
(7,'2009-09-18','e2'),
(7,'2010-01-14','e3'),
(7,'2010-05-07','e4'),
(7,'2011-02-23','e5'),
(7,'2012-05-15','e6'),
(7,'2013-05-08','e7'),
(7,'2013-06-15','e8'),
(7,'2013-06-22','e9'),
(19,'2013-02-12','e1'),
(20,'2011-12-05','e1'),
(29,'2009-05-16','e1'),
(37,'2009-05-11','e1'),
(37,'2009-05-18','e2'),
(61,'2009-07-13','e1'),
(84,'2013-09-14','e1'),
(84,'2009-09-24','e2') 

create table tableB (PersonID int, FollowupDate date)
Insert into tableB values(1,'2010-09-09')
,(1,'2012-03-19')
,(1,'2012-06-01')
,(7,'2009-10-10')
,(7,'2009-11-18')
,(7,'2010-01-18')
,(7,'2011-02-28')
,(7,'2013-06-19')
,(7,'2013-06-26')
,(7,'2013-06-28')
,(19,'2013-04-11')
,(20,'2012-02-01')
,(37,'2009-07-03')
,(37,'2009-08-05')
,(61,'2009-12-12')
,(84,'2009-09-17')
,(84,'2009-09-29')

;With mycte as (
select PersonID, EventDate, EventLabel , 1 as grp from tableA
Union all
select PersonID, FollowupDate,
'e'+ Cast(row_number() Over(Partition by PersonID Order by FollowupDate) as varchar(10)) +'-followup' as EventLabel
, 0 as grp from tableB
)

,mycte1 as (
Select *, sum(grp) Over(Partition by PersonID Order by EventDate) grp2 from mycte )


,mycte2 as (
Select PersonID, min(EventDate) Over(Partition by PersonID, grp2) as EventDate
--, min(EventDate) Over(Partition by PersonID, grp2,grp) minFollowupDate
,datediff(day, min(EventDate) Over(Partition by PersonID, grp2),min(EventDate) Over(Partition by PersonID, grp2,grp)) waitDate
,row_number() Over(Partition by PersonID, grp2 Order by grp) rn
From mycte1
)

Select PersonID,EventDate, waitDate from mycte2
Where rn=1
--
order by PersonID,EventDate
Drop table   tableA, tableB

 

https://social.msdn.microsoft.com/Forums/en-US/a388d555-26a3-4bd7-9b8c-2ea2d2f3551e/calculating-date-difference-for-multiple-events-per-person?forum=transactsql

Update table date column from default value to NULL

ALTER PROCEDURE [dbo].[Update_dt_value]
@TableName NVARCHAR(128)
AS
BEGIN
SET NOCOUNT ON;

Declare @Sql nvarchar(max)=”

;with mycte as (SELECT [TABLE_NAME], [COLUMN_NAME], ‘update ‘ + QUOTENAME([TABLE_NAME])
+’ Set ‘+ [COLUMN_NAME] +’= null WHERE ‘ + [COLUMN_NAME] +’=”1900-01-01”’ as sqlstr

FROM [mydb1].[INFORMATION_SCHEMA].[COLUMNS]
where data_type=’date’ and [TABLE_NAME]=@TableName
)

SELECT @sql= ( SELECT Cast(t2.sqlstr as varchar(2000)) +’;’
FROM mycte t2
WHERE t2.[TABLE_NAME] = t1.[TABLE_NAME]

FOR XML PATH(”), TYPE).value(‘.’, ‘varchar(max)’)
FROM mycte t1
Group by [TABLE_NAME]
;

EXECUTE sp_executesql @Sql
–print @Sql

END

Check Next Two Wednsedays With an Exclusion Table (T-SQL Code snippet)

--date of Wednseday of the job run week
declare @rundate date=Dateadd(day,2,dateadd(week,datediff(week,0,getdate()),0) )
declare @firstrun date,@lastrun date
--,@lastrunExt6 date

---Option 1

declare @NumOfNoRundate int
;with mycte as

(select norundate from [dbo].[exc_norun]
where norundate=dateadd(week,1,@rundate)
union all
select dateadd(week,1,m.norundate)
from mycte m join [dbo].[norunDates] n on n.norundate=dateadd(week,1,m.norundate))

select @NumOfNoRundate=count(*) from mycte

select @firstrun=dateadd(week,1+@NumOfNoRundate,@rundate),
@lastrun=dateadd(week,2+@NumOfNoRundate,@rundate )
--,@lastrunExt6=Dateadd(day,-1,dateadd(week,3+@NumOfNoRundate,@rundate ))

print @NumOfNoRundate
print @firstrun
print @lastrun
--print @lastrunExt6

--Option 2

;with mycte1 as

(select 1 as i, dateadd(week,1,@rundate) dt
union all
select m.i+1 as i, dateadd(week,m.i,m.dt)
from mycte1 m where m.i<100
and Not exists (select 1 from [dbo].[norunDates] n where n.norundate=m.dt)

)

Select @firstrun=min(dt)
,@lastrun= dateadd(week,1,min(dt))
--,@lastrunExt6=Dateadd(day,-1,dateadd(week,2,min(dt) ))
from mycte1
print @firstrun
print @lastrun
--print @lastrunExt6