SQLDBA - Need to download specific package from integration services.

 

Below code will help us down load a specific packages from integration services. 

$SqlServer   = "SSIS2025DB,81117"

$OutputFolder = "C:\SSISBackup"

if (!(Test-Path $OutputFolder))

{

    New-Item -ItemType Directory -Path $OutputFolder -Force | Out-Null

}

$query = @"

;WITH FolderHierarchy AS

(

    SELECT

        folderid,

        parentfolderid,

        foldername,

        CAST(foldername AS VARCHAR(MAX)) AS FolderPath

    FROM msdb.dbo.sysssispackagefolders

    WHERE parentfolderid IS NULL

    UNION ALL

    SELECT

        f.folderid,

        f.parentfolderid,

        f.foldername,

        CAST(h.FolderPath + '\' + f.foldername AS VARCHAR(MAX)) AS FolderPath

    FROM msdb.dbo.sysssispackagefolders f

    INNER JOIN FolderHierarchy h

        ON f.parentfolderid = h.folderid

)

SELECT

      p.name AS PackageName

    , ISNULL(h.FolderPath,'Root') AS FolderPath

    , CAST(CAST(p.packagedata AS VARBINARY(MAX)) AS XML) AS PackageXML

FROM msdb.dbo.sysssispackages p

LEFT JOIN FolderHierarchy h

    ON p.folderid = h.folderid

WHERE p.name IN (

'HMR_Breakeven_Pfo_Load'

, 'HMR_Cache_Refresh_VACOil'

, 'HMR_Cache_Refresh_WasteOil'

, 'HMR_Customer_Profile_Refresh_VACOil'

, 'HMR_Customer_Profile_Refresh_WasteOil_BranchView'

, 'HMR_Customer_Profile_Refresh_WasteOil_TankView'

, 'HMR_Inter_Company_Rate'

, 'HMR_UMO_Refresh'

)

ORDER BY FolderPath, PackageName

"@

$packages = Invoke-Sqlcmd `

    -ServerInstance $SqlServer `

    -Database msdb `

    -Query $query

foreach ($pkg in $packages)

{

    # Create local folder structure matching MSDB hierarchy

    if ($pkg.FolderPath -eq "Root")

    {

        $LocalFolder = $OutputFolder

        $PackagePath = "\" + $pkg.PackageName

    }

    else

    {

        $LocalFolder = Join-Path $OutputFolder $pkg.FolderPath

        $PackagePath = "\" + $pkg.FolderPath + "\" + $pkg.PackageName

    }

    if (!(Test-Path $LocalFolder))

    {

        New-Item -ItemType Directory -Path $LocalFolder -Force | Out-Null

    }

    $FileName = Join-Path $LocalFolder ($pkg.PackageName + ".dtsx")

    $pkg.PackageXML.OuterXml | Out-File `

        -FilePath $FileName `

        -Encoding utf8

    Write-Host "Package Path : $PackagePath"

    Write-Host "Exported To  : $FileName"

    Write-Host "--------------------------------------"

}

Write-Host "Export completed." 

SQLDBA - Log activity

 to track the data base log activity. 

USE dba

GO

CREATE TRIGGER [chi_ddl_log_trigger] 

ON DATABASE 

FOR DDL_DATABASE_LEVEL_EVENTS 

AS

SET ARITHABORT ON

SET QUOTED_IDENTIFIER ON

DECLARE @data XML

SET @data = EVENTDATA()

exec dba.dbo.sp_chi_database_logactivity @data;

SET ARITHABORT OFF

SET QUOTED_IDENTIFIER OFF

GO

ENABLE TRIGGER [chi_ddl_log_trigger] ON DATABASE

GO

USE dba

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TRIGGER chi_ddl_log_trigger 

ON DATABASE 

FOR DDL_DATABASE_LEVEL_EVENTS 

AS

SET ARITHABORT ON

SET QUOTED_IDENTIFIER ON

DECLARE @data XML

SET @data = EVENTDATA()

INSERT DBA.dbo.chi_ddl_log 

( PostTime, 

ServerName, 

DBName, 

DBUser, 

Event, 

TSQL

) 

VALUES 

(

GETDATE(), 

@data.value('(/EVENT_INSTANCE/ServerName)[1]','nvarchar(100)'),

@data.value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(100)'),

@data.value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(100)'),

@data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'), 

@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(4000)') 

) ;

SET ARITHABORT OFF

SET QUOTED_IDENTIFIER OFF

GO

SET QUOTED_IDENTIFIER OFF

GO

  

CREATE PROCEDURE [dbo].[sp_chi_database_logactivity] (@data XML)  

AS  

BEGIN  

 SET ARITHABORT ON  

 SET QUOTED_IDENTIFIER ON  

  

 --DECLARE @data XML  

 DECLARE @text NVARCHAR(max)  

  ,@user NVARCHAR(1000)  

  ,@email_subject NVARCHAR(1000)  

  ,@email_recipients NVARCHAR(1000)  

  ,@event_type NVARCHAR(100)  

  ,@createdloginname NVARCHAR(1000)  

  ,@msg NVARCHAR(1000)  

  

 --SET @data = EVENTDATA();  

 SELECT @user = SUSER_SNAME()  

  ,@email_recipients = 'dba@cleanharbors.com'  

  ,@text = @data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(max)')  

  ,@event_type = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)')  

  ,@createdloginname = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(1000)')  

  

 --Only DBA should be making changes here.  

 IF (  

   @event_type IN (  

    'CREATE_USER'  

    ,'ALTER_USER'  

    ,'DROP_USER'  

    )  

   AND @user NOT IN (  

    'ksrtech.com\SQLAgent_Dev'  

    ,'ksrtech.com\SQLAgent_Dev1'  

    ,'ksrtech.com\SQLAgent_DR'  

    ,'ksrtech.com\SQLAgent'  

    )  

   AND (  

    IS_MEMBER('ksrtech.com\SQL-Administrators') <> 1  

    )  

   )  

 BEGIN  

  SET @msg = 'Only DBAs can add accounts to a database, contact the DBA group for any permission changes.' + CHAR(10);  

  

  PRINT @msg  

  

  ROLLBACK  

  

  --Send message  

  BEGIN TRANSACTION  

  

  SELECT @email_subject = 'LOGIN ' + @createdloginname + ' CREATED BY ' + @user + ' ON ' + @@SERVERNAME + ' - this has been denied!'  

  

  EXEC msdb.dbo.sp_send_dbmail @recipients = @email_recipients  

   ,@body = @text  

   ,@subject = @email_subject;  

  

  COMMIT TRANSACTION  

 END;  

  

 IF (  

   @event_type NOT IN (  

    'UPDATE_STATISTICS'  

    ,'CREATE_STATISTICS'  

    )  

   OR @event_type NOT LIKE '%STATISTICS%'  

   )  

 BEGIN  

  INSERT DBA.dbo.chi_ddl_log (  

   [PostTime]  

   ,[ServerName]  

   ,[DBName]  

   ,[DBUser]  

   ,[Event]  

   ,[TSQL]  

   )  

  VALUES (  

   GETDATE()  

   ,@data.value('(/EVENT_INSTANCE/ServerName)[1]', 'nvarchar(100)')  

   ,@data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(100)')  

   ,@data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(100)')  

   ,@data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)')  

   ,@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(4000)')  

   );  

 END  

  

 SET ARITHABORT OFF  

 SET QUOTED_IDENTIFIER OFF  

END  

GO