Skip to content

Posts from the ‘SSIS’ Category

Apr 10

1 Comment

Not able to delete SQL Server Maintenance Plan and/or SQL Server Agent jobs

You may get below error while trying to delete MP after server name change: -

TITLE: Microsoft SQL Server Management Studio

Exception has been thrown by the target of an invocation. (mscorlib)

ADDITIONAL INFORMATION:

An error was encountered when trying to remove the package “Maintenance Plans\UserDB_Tlog” from SQL Server. 

and/or

You may get below error while trying to delete SQL Server agent job: -

TITLE: Microsoft SQL Server Management Studio

Drop failed for Job ‘UserDB_Tlog.Subplan_3′.  (Microsoft.SqlServer.Smo)

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The DELETE statement conflicted with the REFERENCE constraint “FK_subplan_job_id”. The conflict occurred in database “msdb”, table “dbo.sysmaintplan_subplans”, column ‘job_id’.

The DELETE statement conflicted with the REFERENCE constraint “FK_subplan_schedule_id”. The conflict occurred in database “msdb”, table “dbo.sysmaintplan_subplans”, column ‘schedule_id’.

The statement has been terminated.

The statement has been terminated. (Microsoft SQL Server, Error: 547)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.4060&EvtSrc=MSSQLServer&EvtID=547&LinkId=20476

Possible cause of this issue: -

Mainly you will face issue if your SQL Server host name is changed.

Solution: -

In order to delete the maintenance plan and/or SQL Server agents jobs, we have to manually delete the entries from system tables in msdb database. Below are the queries you need to fire in msdb database in order to get this done.

select * from sysjobs -- to find the correct jobid

select * from sysjobschedules where job_id ='B0C44798-7ABC-456B-B819-C369CE80150C' -- update job_id with your job id number

delete from [dbo].[sysjobschedules] where job_id ='B0C44798-7ABC-456B-B819-C369CE80150C' -- update job_id with your job id number

select plan_id from [dbo].[sysmaintplan_subplans] where job_id ='B0C44798-7ABC-456B-B819-C369CE80150C' -- update job_id with your job id number

select * from dbo.sysmaintplan_log where plan_id = '755708E5-5B24-4B31-8AC3-38944E6A99A3' -- planid is the value from above sql

delete from [dbo].[sysmaintplan_log] where plan_id = '755708E5-5B24-4B31-8AC3-38944E6A99A3' -- planid is the value frm above sql

select * from [dbo].[sysmaintplan_subplans] where job_id ='B0C44798-7ABC-456B-B819-C369CE80150C' -- update job_id with your job id number

delete from [dbo].[sysmaintplan_subplans] where job_id ='B0C44798-7ABC-456B-B819-C369CE80150C' -- update job_id with your job id number

delete from sysjobs where job_id ='B0C44798-7ABC-456B-B819-C369CE80150C' -- update job_id with your job id number

Now connect to integration services on this server and manually delete the package under msdb

Sep 26

Configuring firewall settings for SQL Server Integration Services (SSIS) on Windows 2008 R2

Here we will show step-by-step configuration of firewall settings for SQL Server Integration Services (SSIS) on Windows 2008 R2 server.

Firewall configuration for SSIS is little different from database engine or SSAS or SSRS. Here we have to open port as well as to allow connection to execute MsDtsSrvr.exe

Following are the steps to be followed for this activity: -

1) Go to Administrative tools and open Windows Firewall with Advanced Security utility.

2) Navigate to inbound rules and go to New Rule option, in order to create new rule.

3) In this new inbound rule window, select the rule type as Program and move to next step.

4) Here select the option “this program” and then click on Browse. Then navigate to the path where MsDtsSrvr.exe is stored. If you have installed SQL Server 2008 on C drive then path will be like “C:\Program Files\Microsoft SQL Server\100\DTS\Binn\MsDtsSrvr.exe”

Once you have selected the program, move to next step.

5) Under action tab, select the option “allow the connection” and move next.

6) Here Select Domain to allow connections from same domain only. Or select appropriate option as per your requirement and move forward.

7) Name this rule as SQL Server Integration Services and click on Finish.

8) Now you can see, this new rule is visible under inbound rules tab and is enabled by default. Move to next step.

9) Again, navigate to Inbound rule and right click –> New rule

10) Now, select the rule type as port.

11) Protocol type will be TCP and port number at which SSIS will listen is 135. Move to next step.

12) Select the action as “Allow the connections” and move next.

13) Here select the appropriate option. Normally it should be similar to what was selected in step 6

14) Name this rule as RPC(TCP/135) and click on finish.

15) Verify this newly created rule is created and enabled by looking at the inbound rules.