Drop User from SSISDB

USE [SSISDB]

GO

DELETE FROM [SSISDB].[internal].[operation_permissions] WHERE sid in (SELECT [sid] FROM [sys].[database_principals] WHERE name = N’LoginName’);

GO

DELETE FROM [SSISDB].[internal].[project_permissions] WHERE sid in (SELECT [sid] FROM [sys].[database_principals] WHERE name = N’LoginName’); –Safe Side not Required

GO

DELETE FROM [SSISDB].[internal].[folder_permissions] WHERE sid in (SELECT [sid] FROM [sys].[database_principals] WHERE name = N’LoginName’);–Safe Side not Required

GO

DELETE FROM [SSISDB].[internal].[environment_permissions] WHERE sid in (SELECT [sid] FROM [sys].[database_principals] WHERE name = N’LoginName’);–Safe Side not Required

GO

IF EXISTS (SELECT name FROM sys.database_principals WHERE name = N’LoginName’)

BEGIN

DROP USER N’LoginName’

END

CREATE USER N’LoginName’ FOR LOGIN N’LoginName’

GO

USE [SSISDB]

GO

EXEC sp_addrolemember N’ssis_admin’, N’LoginName’

GO

Get SSIS Job Details

create table #enum_job (

Job_ID uniqueidentifier,

Last_Run_Date int,

Last_Run_Time int,

Next_Run_Date int,

Next_Run_Time int,

Next_Run_Schedule_ID int,

Requested_To_Run int,

Request_Source int,

Request_Source_ID varchar(100),

Running int,

Current_Step int,

Current_Retry_Attempt int,

State int

)

insert into #enum_job exec master.dbo.xp_sqlagent_enum_jobs 1,garbage

SELECT ja.job_id,

Convert(varchar(20),

SERVERPROPERTY(‘ServerName’)) AS ServerName,

j.name AS name,

j.enabled,

sj.State current_execution_status,

SJS.last_run_outcome,

jh.run_date as last_run_date,

jh.run_time as last_run_time

FROM(msdb.dbo.sysjobactivity ja

LEFT JOIN msdb.dbo.sysjobhistory jh

ON ja.job_history_id = jh.instance_id)

Join #enum_job sj on sj.job_id=ja.job_id

join msdb.dbo.sysjobs_view j on ja.job_id = j.job_id

LEFT JOIN (SELECT DISTINCT(SJS.job_id), sjs.last_run_outcome, sjh.run_status FROM msdb.dbo.sysjobs AS SJ INNER JOIN msdb.dbo.sysjobhistory AS SJH ON SJ.job_id = SJH.job_id INNER JOIN msdb.dbo.sysjobsteps AS SJS ON sj.job_id = SJS.job_id WHERE SJS.last_run_outcome = 0 AND SJH.run_status = 0) SJS ON Ja.job_id = SJS.job_id WHERE ja.session_id=(SELECT MAX(session_id) from msdb.dbo.sysjobactivity) ORDER BY Name Drop table #enum_job

BCP QueryOut/In SSMS

EXEC sp_configure ‘show advanced options’, 1
GO
— To update the currently configured value for advanced options.
RECONFIGURE
GO
— To enable the feature.
EXEC sp_configure ‘xp_cmdshell’, 1
GO
— To update the currently configured value for this feature.
RECONFIGURE
GO

What ever be the service account the SQL server service is running on should have read/write permission on folder where we BCP OUT/IN [MY Case 567Tech\SQLServer].

exec xp_cmdshell ‘BCP “SELECT * FROM \\ServerPath\FolderPath\FileName.dat WITH(NOLOCK)  WHERE  Condition”  queryout “\\ServerPath\FolderPath\FileName.dat” -T -c -t -r’

exec xp_cmdshell ‘bcp DBName.SchemaName.TableName in “\\ServerPath\FolderPath\FileName.dat” -T -c -t -r’