修正SQLSERVER package连接

 

drop table #temp_mt;
 --WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' AS DTS)
 SELECT ROW_NUMBER() OVER ( ORDER BY id ) AS idx ,
        id ,
        name ,
        packageXML = CAST(CAST(packagedata AS VARBINARY(MAX)) AS XML)
 INTO   #temp_mt
 FROM   dbo.sysdtspackages90


DECLARE @idx INT,@id UNIQUEIDENTIFIER,@xml XML,@maxIdx INT;
SELECT @maxIdx = MAX(idx) FROM #temp_mt;
SELECT @idx = 1;
WHILE @idx <= @maxIdx
BEGIN    
    SELECT @xml = packageXML,@id = id FROM #temp_mt WHERE idx = @idx;            
    --SELECT @xml,@id;
    
    SET @xml.modify('declare namespace DTS="www.microsoft.com/SqlServer/Dts"; 
                        replace value of (/DTS:Executable/DTS:ConnectionManager/DTS:ObjectData/DTS:ConnectionManager/DTS:Property/text())[2]
                        with 
                        "server=''.'';Trusted_Connection=true;Application Name=''Microsoft SQL Server Management Studio'';Pooling=false;Packet Size=4096;multipleactiveresultsets=false;"');    
--    SELECT @xml,@id;
    update dbo.sysdtspackages90 
    set packagedata = CAST(@xml as VARBINARY(MAX))
    WHERE id = @id;
    SET @idx = @idx+1;
END