Thursday, December 22, 2022
Using Variables To Update and Increment the Value by 1
Load Dim_date ( i have loaded 2023)
Wednesday, December 21, 2022
USE [AuditDB]
GO
/****** Object: Table [dbo].[NK_BlockingReport] Script Date: 3/8/2022 12:41:56 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[BlockingReport](
[ID] [INT] IDENTITY(1,1) NOT NULL,
[ObjectName] [sysname] NULL,
[BlockingObjectName] [sysname] NULL,
[StatementText] [VARCHAR](MAX) NULL,
[BlockingStatementText] [VARCHAR](MAX) NULL,
[SessionID] [INT] NULL,
[BlockingSessionID] [INT] NULL,
[Status] [VARCHAR](50) NULL,
[BlockingStatus] [VARCHAR](50) NULL,
[DateCreated] [DATETIME] NULL,
[DatabaseName] [VARCHAR](100) NULL,
[CPU] [BIGINT] NULL,
[RunningTime] [INT] NULL,
[RuuningFrom] [VARCHAR](800) NULL,
[RuuningBy] [VARCHAR](800) NULL,
[ProgramName] [VARCHAR](100) NULL,
[LoginName] [VARCHAR](100) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
USE [AuditDB]
GO
/****** Object: Table [dbo].[Deadlock] Script Date: 3/8/2022 12:42:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DeadlockReport](
[RecordId] [INT] IDENTITY(1,1) NOT NULL,
[AlertTime] [DATETIME] NOT NULL,
[DeadlockGraph] [XML] NULL,
[Notified] [INT] NOT NULL,
PRIMARY KEY CLUSTERED
(
[RecordId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Deadlock] ADD CONSTRAINT [DF_deadlock_flag] DEFAULT ((0)) FOR [Notified]
GO
Alerts that should have in sql prod environments
Alert Index fragmentation report
' + N'Below are the Indexes in Workbench2Prod Database with Average Fragmentation above 30 Percent' + N'
'; SET @DocumentTail = ''; SET @TableHead = N'| TableName | ' + N'IndexName | ' + N'AverageFragmentationPercent | ' + N'PageCount | ' + N'
|---|
Capture blocking
SendJobFailureReport
' + N'The following Jobs failed in FPSQL1Listener SQL Server' + N'
'; SET @DocumentTail = ''; SET @TableHead = N'| Server Name | ' + N'Job Name | ' + N'Job Failed DateTime | ' + N'Status | ' + N'Error Message | ' + N'
|---|
Alert when a login is created or dropped
Tuesday, December 20, 2022
How to stop and start DBmail
Monday, December 19, 2022
Index rebuild online
DECLARE @page_count_minimum smallint
DECLARE @fragmentation_minimum float
SET @page_count_minimum =10
SET @fragmentation_minimum = 10
SELECT
'ALTER INDEX ' + ' [' +sys.indexes.name + '] ' + 'On'+ ' ' + sys.objects.name + ' REBUILD WITH (ONLINE = ON)' as b,sys.objects.name,
avg_fragmentation_in_percent AS frag,
page_count AS page_count,
sys.dm_db_index_physical_stats.object_id AS objectid,
partition_number AS partitionnum
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
inner join sys.objects
on sys.objects.object_id = sys.dm_db_index_physical_stats.object_id
inner join sys.indexes
on sys.indexes.index_id = sys.dm_db_index_physical_stats.index_id
and sys.indexes.object_id = sys.dm_db_index_physical_stats.object_id
WHERE avg_fragmentation_in_percent > @fragmentation_minimum
AND sys.dm_db_index_physical_stats.index_id > 0
AND page_count > @page_count_minimum
ORDER BY 2
go
-- can filter top 100 or select only table names by joining sysojbects and can also get schema name by joining with schemas
select s.name as schema_name, s.schema_id, u.name as schema_owner from sys.schemas s inner join sys.sysusers u on u.uid = s.principal_id order by s.name
Rebuild index on very Large tables 1 TB table with 2K tables
- changed db to bulk recovery model from full
- rebuild indexes
- changed db to FULL recovery model
UPDATE STATS WITH FULL SCAN is a must run when u migrate or upgrade SQL SERVER
We had some performance issues after migrating SQL Server from windows server 2012 to windows server 2019 and after analysis, I found that I should do
sp_msforeachtable 'UPDATE STATISTICS? WITH FULLSCAN'
or do individual tables
This helped....
If you use FULLSCAN, it will read the entire table.
Question: When do you recommend using updated stats with FULLSCAN rather than default sampled?
Answer: When you know there is skew in the column.
Updating statistics ensures that queries compile with up-to-date statistics. Updating statistics via any process may cause query plans to recompile automatically.
Sunday, March 13, 2022
adding secondary file to an exiting database
this is a online opertion and can be added in business hours
say for example:
disk E with MDF is almost full (only 10GB left), cannot shrink mdf and also disk cannot be extended , add a secondary files on F . this should help
Tuesday, March 1, 2022
Backup report daily via sp and job everyday 9am
EXEC..Backup_Report
@MailProfile = 'ABC' ,
@MailID = 'abc@abc.com',
@Server = 'ABC'
--keep sp and run job 9am everyday
USE [master]
GO
/****** Object: StoredProcedure [dbo].[Backup_Report] Script Date: 3/1/2022 4:18:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Backup_Report] (
@MailProfile NVARCHAR(200),
@MailID NVARCHAR(2000),
@Server VARCHAR(100) = NULL)
AS
BEGIN
SET NOCOUNT ON;
SET ARITHABORT ON;
DECLARE @ServerName VARCHAR(100);
SET @ServerName = ISNULL(@Server,@@SERVERNAME);
CREATE TABLE #Backup_Report(
ServerName VARCHAR(300),
Database_name varchar(300),
BackupType varchar(50),
NotBackedUpSince varchar(100),
LastBackupDate VARCHAR(50),
Backuppath nvarchar(1000),
NotBackedUpSince_hours varchar(50));
INSERT INTO #Backup_Report
SELECT @@servername as ServerName,B.name as Database_Name ,
case when A.type = 'D' then 'Full'
when A.type = 'L' then 'Log'
When A.type ='I' then 'Diff'
end as BackupType ,
case
when A.type = 'D' then
ISNULL(STR(ABS(DATEDIFF(day, GetDate(),MAX(Backup_finish_date)))), 'NEVER') + '-- Days'
when A.type = 'I' then
ISNULL(STR(ABS(DATEDIFF(day, GetDate(),MAX(Backup_finish_date)))), 'NEVER') + '-- Days'
when A.type = 'L' then
ISNULL(STR(ABS(DATEDIFF(minute, GetDate(),MAX(Backup_finish_date)))), 'NEVER') + '-- Minutes'
end as NotBackedUpSince
,
ISNULL(Convert(char(20), MAX(backup_finish_date), 120), 'NEVER') as LastBackupDate
,
max(convert(char(100),physical_device_name)) as BackupPath ,
-- case when A.type = 'D' then 'Full'
--when A.type = 'L' then 'Log'
--When A.type ='I' then 'Diff'
--end as BackupType ,
case
when A.type = 'D' then
ISNULL(STR(ABS(DATEDIFF(hour, GetDate(),MAX(Backup_finish_date)))), 'NEVER')
when A.type = 'I' then
ISNULL(STR(ABS(DATEDIFF(Hour, GetDate(),MAX(Backup_finish_date)))), 'NEVER')
when A.type = 'L' then
ISNULL(STR(ABS(DATEDIFF(minute, GetDate(),MAX(Backup_finish_date)))), 'NEVER')
end as NotBackedUpSince_hours
FROM master.dbo.sysdatabases B LEFT OUTER JOIN msdb.dbo.backupset A
ON A.database_name = B.name
left outer join msdb..backupmediafamily C
on C.media_set_id = A.media_set_id
where B.name not in ('tempdb')
--and A.type ='D'
GROUP BY B.Name,A.type order by 1
/*************************************************************/
/****************** HTML Preparation *************************/
/*************************************************************/
DECLARE @TableHTML VARCHAR(MAX),
@StrSubject VARCHAR(100),
@Oriserver VARCHAR(100),
@Version VARCHAR(250),
@Edition VARCHAR(100),
@ISClustered VARCHAR(100),
@SP VARCHAR(100),
@ServerCollation VARCHAR(100),
@SingleUser VARCHAR(5),
@LicenseType VARCHAR(100),
@Cnt int,
@URL varchar(1000),
@Str varchar(1000),
@NoofCriErrors varchar(3)
-- Variable Assignment
SELECT @Version = @@version
SELECT @Edition = CONVERT(VARCHAR(100), serverproperty('Edition'))
SET @Cnt = 0
IF serverproperty('IsClustered') = 0
BEGIN
SELECT @ISClustered = 'No'
END
ELSE
BEGIN
SELECT @ISClustered = 'YES'
END
SELECT @OriServer = CONVERT(VARCHAR(50), SERVERPROPERTY('servername'))
SELECT @strSubject = 'Backup Report ('+ CONVERT(VARCHAR(100), @SERVERNAME) + ')'
SET @TableHTML =
'<font face="Verdana" size="4">Backup Report</font>
<table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="47%" id="AutoNumber1" height="50">
<tr>
<td width="39%" height="22" bgcolor="#000080"><b>
<font face="Verdana" size="2" color="#FFFFFF">Server Name</font></b></td>
</tr>
<tr>
<td width="39%" height="27"><font face="Verdana" size="2">' + @ServerName +'</font></td>
</tr>
</table>
<table id="AutoNumber1" style="BORDER-COLLAPSE: collapse" borderColor="#111111" height="40" cellSpacing="0" cellPadding="0" width="933" border="1">
<tr>
</table>
<table style="BORDER-COLLAPSE: collapse" borderColor="#111111" cellPadding="0" width="933" bgColor="#ffffff" borderColorLight="#000000" border="1">
<tr>
</tr>'
SELECT
@TableHTML = @TableHTML +
'</table>
<p style="margin-top: 1; margin-bottom: 0"> </p>
<font face="Verdana" size="4">Backup Report</font>
<table id="AutoNumber1" style="BORDER-COLLAPSE: collapse" borderColor="#111111" height="40" cellSpacing="0" cellPadding="0" width="933" border="1">
<tr>
<th align="left" width="300" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">ServerName</font></th>
<th align="left" width="200" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">Database_Name</font></th>
<th align="left" width="136" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">BackupType</font></th>
<th align="left" width="136" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">NotBackedupsince</font></th>
<th align="left" width="136" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">lastBackupDate</font></th>
<th align="left" width="136" bgColor="#000080">
<font face="Verdana" size="1" color="#FFFFFF">backuppath</font></th>
</tr>'
SELECT
@TableHTML = @TableHTML +
'<tr>
<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), ServerName), '') +'</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), Database_Name), '') +'</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), BackupType), '') +'</font></td>' +
CASE WHEN NotBackedUpSince_hours > 170 and backuptype = 'Full' THEN
'<td><font face="Verdana" size="1" color="#FF0000"><b>' + ISNULL(CONVERT(VARCHAR(100), NotBackedupsince), '') +'</font></td>'
WHEN NotBackedUpSince_hours >30 and BackupType = 'Diff' THEN
'<td><font face="Verdana" size="1" color="#FF0000"><b>' + ISNULL(CONVERT(VARCHAR(100), NotBackedupsince), '') +'</font></td>'
WHEN NotBackedUpSince_hours >30 and BackupType = 'Log' THEN
'<td><font face="Verdana" size="1" color="#FF0000"><b>' + ISNULL(CONVERT(VARCHAR(100), NotBackedupsince), '') +'</font></td>'
ELSE
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), NotBackedupsince), '') +'</font></td>'
END +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(1000), lastBackupDate), '') +'</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(1000), backuppath), '') +'</font></td>' +
'</tr>'
FROM
#Backup_Report
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @MailProfile,--'MMStuckup',
@recipients=@MailID, --'uday.arumilli@ge.com',
@subject = @strSubject,
@body = @TableHTML,
@body_format = 'HTML' ;
DROP TABLE #Backup_Report;
SET NOCOUNT OFF;
SET ARITHABORT OFF;
END
send job failure report
DECLARE @ServerName NVARCHAR(256) = @@SERVERNAME
DECLARE @RoleDesc NVARCHAR(60)
SELECT @RoleDesc = a.role_desc
FROM sys.dm_hadr_availability_replica_states AS a
JOIN sys.availability_replicas AS b
ON b.replica_id = a.replica_id
WHERE b.replica_server_name = @ServerName
IF @RoleDesc = 'PRIMARY'
BEGIN
DECLARE @Document NVARCHAR(MAX)
, @DocumentHead NVARCHAR(MAX)
, @DocumentTail NVARCHAR(MAX)
, @TableHead NVARCHAR(MAX)
, @TableBody NVARCHAR(MAX)
, @TableTail NVARCHAR(MAX)
, @Now DATETIME
SET @DocumentHead = N'<html><head>'
+ N'<style>'
+ N'th, td'
+ N'{ '
+ N' border: solid black 1px;'
+ N' padding: 1px 5px 1px 5px;'
+ N' font-size: 11pt;'
+ N'}'
+ N'</style>'
+ N'</head>'
+ N'<body>'
+ N'<h3>'
+ N'The following Jobs failed in FPSQL1Listener SQL Server'
+ N'</h3>';
SET @DocumentTail = '</body></html>';
SET @TableHead = N'<table cellpadding=0 cellspacing=0 border=0>'
+ N'<tr bgcolor=#C0C0C0>'
+ N' <th>Server Name</th>'
+ N' <th>Job Name</th>'
+ N' <th>Job Failed DateTime</th>'
+ N' <th>Status</th>'
+ N' <th>Error Message</th>'
+ N'</tr>';
SET @TableTail = '</table>';
SELECT @TableBody = (SELECT ROW_NUMBER() Over(Order By run_date) % 2 As [TRRow],
T1.server AS [TD],
SUBSTRING(T2.name,1,140) AS [TD],
msdb.dbo.agent_datetime(run_date, run_time) as [TD],
CASE T1.run_status
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancelled'
WHEN 4 THEN 'In Progress'
END AS [TD],
T1.message AS [TD]
FROM msdb..sysjobhistory T1 INNER JOIN msdb..sysjobs T2 ON T1.job_id = T2.job_id
WHERE T1.run_status NOT IN (1, 4) AND T1.step_id != 0 AND run_date >= CONVERT(CHAR(8), (SELECT DATEADD (DAY,(-1), GETDATE())), 112)
FOR XML RAW('tr'), ELEMENTS);
--SELECT @TableBody
IF @TableBody IS NULL
BEGIN
PRINT 'No Jobs failed';
RETURN;
END
ELSE
BEGIN
-- Replace the entity codes and row numbers
SET @TableBody = Replace(@TableBody, '_x0020_', space(1))
SET @TableBody = Replace(@TableBody, '_x003D_', '=')
SET @TableBody = Replace(@TableBody, '<tr><TRRow>1</TRRow>', '<tr bgcolor=#C6CFFF>')
SET @TableBody = Replace(@TableBody, '<TRRow>0</TRRow>', '');
SELECT @Document = @DocumentHead + @TableHead + @TableBody + @TableTail + @DocumentTail
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Default'
, @recipients= 'SQL1Operator@frontpointsecurity.com'
, @subject = 'Failed SQL Server Jobs on SQL1'
, @body = @Document
, @body_format = 'HTML';
END
END;
ELSE
BEGIN
RETURN
END
Alert index fragmentatondetails
USE [WorkBench2Prod]
GO
/****** Object: StoredProcedure [dbo].[AlertSendIndexFragmentationdetails_Job_Step1] Script Date: 3/1/2022 4:13:33 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Proc [dbo].[AlertSendIndexFragmentationdetails_Job_Step1]
AS
BEGIN
/*
Author:Naresh DBA
Date: 04/01/2016
Purpose:Send Index fragmentation details every 1 hour
Paremeters: page_count_minimum = 1000 and fragmentation_minimum = 30
*/
DECLARE
@Document NVARCHAR(MAX) ,
@DocumentHead NVARCHAR(MAX) ,
@DocumentTail NVARCHAR(MAX) ,
@TableHead NVARCHAR(MAX) ,
@TableBody NVARCHAR(MAX) ,
@TableTail NVARCHAR(MAX) ,
@page_count_minimum SMALLINT ,
@fragmentation_minimum INT;
SET @page_count_minimum =1000;
SET @fragmentation_minimum = 30;
SET @DocumentHead = N'<html><head>' + N'<style>' + N'th, td' + N'{ '
+ N' border: solid black 1px;' + N' padding: 1px 5px 1px 5px;'
+ N' font-size: 11pt;' + N'}' + N'</style>' + N'</head>' + N'<body>'
+ N'<h3>'
+ N'Below are the Indexes in Workbench2Prod Database with Average Fragmentation above 30 Percent'
+ N'</h3>';
SET @DocumentTail = '</body></html>';
SET @TableHead = N'<table cellpadding=0 cellspacing=0 border=0>'
+ N'<tr bgcolor=#C0C0C0>' + N' <th>TableName</th>'
+ N' <th>IndexName</th>' + N' <th>AverageFragmentationPercent</th>'
+ N' <th>PageCount</th>' + N'</tr>';
SET @TableTail = '</table>';
SELECT @TableBody = ( SELECT ROW_NUMBER() OVER ( ORDER BY avg_fragmentation_in_percent DESC )
% 2 AS [TRRow] ,
sys.objects.name AS [TD] ,
sys.indexes.name AS [TD] ,
CONVERT(VARCHAR(10), avg_fragmentation_in_percent, 121) AS [TD] ,
page_count AS [TD]
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL,
NULL, NULL,
'LIMITED')
INNER JOIN sys.objects ON sys.objects.object_id = sys.dm_db_index_physical_stats.object_id
INNER JOIN sys.indexes ON sys.indexes.index_id = sys.dm_db_index_physical_stats.index_id
AND sys.indexes.object_id = sys.dm_db_index_physical_stats.object_id
WHERE avg_fragmentation_in_percent > @fragmentation_minimum
AND objects.is_ms_shipped = 0
AND sys.dm_db_index_physical_stats.index_id > 0 and sys.indexes.name<>'NIX_AccountTicketNote_AccountTicketID_TicketStateID'--Excluded This particular index Condition on may5th 2016
AND page_count > @page_count_minimum
ORDER BY avg_fragmentation_in_percent DESC
FOR
XML RAW('tr') ,
ELEMENTS
);
IF @TableBody IS NULL
BEGIN
RETURN;
END;
ELSE
BEGIN
SET @TableBody = REPLACE(@TableBody, '_x0020_', SPACE(1));
SET @TableBody = REPLACE(@TableBody, '_x003D_', '=');
SET @TableBody = REPLACE(@TableBody, '<tr><TRRow>1</TRRow>','<tr bgcolor=#C6CFFF>');
SET @TableBody = REPLACE(@TableBody, '<TRRow>0</TRRow>', '');
SELECT @Document = @DocumentHead + @TableHead + @TableBody+ @TableTail + @DocumentTail;
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Default',
@recipients ='AlertIndexFragmentationDetails@frontpoint.com',
-- 'naresh.koudagani@frontpointsecurity.com;mark.hildreth@frontpointsecurity.com',
@subject = 'Average Index Fragmentation with above 30 Percent',
@body = @Document,
@body_format = 'HTML';
END
END
Wednesday, January 19, 2022
The connection type "AzureStorage" specified for connection manager "SSIS Connection Manager for Azure Storage" is not recognized as a valid connection manager type
I was trying to upload CSV files to Azure using SSIS blob upload task
received many errors
1. The connection type "AzureStorage" specified for connection manager "SSIS Connection Manager for Azure Storage" is not recognized as a valid connection manager type
2.Executed as user: FPSSI\sa_SQLSvcAdmin. Microsoft (R) SQL Server Execute Package Utility Version 13.0.5026.0 for 64-bit Copyright (C) 2016 Microsoft. All rights reserved. Started: 1:34:04 PM Error: 2022-01-19 13:34:05.29 Code: 0xC0016016 Source: Copy2Azure Description: Failed to decrypt protected XML node "AccountKey" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2022-01-19 13:34:05.37 Code: 0x00000000 Source: Copy2Azure Connection manager "SSIS Connection Manager for Azure Storage" (SSIS Connection Manager for Azure Storage) Description: Property "AccountKey" is not specified. End Error Error: 2022-01-19 13:34:05.37 Code: 0xC001401D Source: Copy2Azure Description: Connection "SSIS Connection Manager for Azure Storage" failed validation. End Error Error: 2022-01-19 13:34:05.39 Code: 0x00000000 Source: Copy2Azure Connection manager "SSIS Connection Manager for Azure Storage" (SSIS Connection Manager for Azure Storage) Description: Property "AccountKey" is not specified. End Error Error: 2022-01-19 13:34:05.39 Code: 0x00000000 Source: Copy2Azure Connection manager "SSIS Connection Manager for Azure Storage" (SSIS Connection Manager for Azure Storage) Description: Property "AccountKey" is not specified. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 1:34:04 PM Finished: 1:34:05 PM Elapsed: 0.5 seconds. The package execution failed. The step failed.
issue was:
- SQL SERVER 2016
- VS is 2017
- Installed Azure SSIS Feature pack 2017
solution was:
- SQL SERVER 2016
- VS is 2017 but saved package as SSDT 2016 and modified the package with password
- Azure SSIS Feature pack 2016 we have both 64 and 32 bit on dev server (uninstall 2017)
script out and see if its there or not, GUI not visible
Option "OFF" is not valid. The command line parameters are invalid.The step failed SQL Agent job error while running package with password
Option "Off" is not valid. The command-line parameters are invalid.The step failed
SQL Agent job error while running package with password
make sure you did not remove any space while adding /DECRYPT Password
at edit command line manually.
I removed some space and got the error, but got fixed after re doing it with out any space
Monday, January 3, 2022
failed to start project exception deserializing the package "The process cannot access the file because it is being used by another process."
Exception deserializing the package
"The process cannot access the file because it is being used by another process."
TITLE: Microsoft Visual Studio
------------------------------
Failed to start project
------------------------------
While running SSIS package i got the error “The process cannot access the file ‘*.ispac’ because it is being used by another process”.
The solution?/Fix:
1. Go to Task Manager
2. Details Tab.
3. Locate the process “DtsDebugHost.exe“.
4. Kill this process. There might be multiple instances of this process. Kill all of them.
5. Reexecute SSIS package