Sql Server Agent – Step – “SQL Server Integration Services Package” – Error – “Errors were detected in the command line arguments…”

Background

Trying to edit the parameters on a SQL Server Agent Job, but ran into a hard wall.

 

Error Message

Error

Image

Textual

TITLE: Microsoft SQL Server Management Studio

Errors were detected in the command line arguments, please make sure all arguments are set correctly. (SqlManagerUI)

 

Advanced Information

Upon clicking the “Advanced Information” we will click the message pasted below.

Image

Textual

===================================

Errors were detected in the command line arguments, please make sure all arguments are set correctly. (SqlManagerUI)

——————————
Program Location:

at Microsoft.SqlServer.Management.SqlManagerUI.DTSJobSubSystemDefinition.Microsoft.SqlServer.Management.SqlManagerUI.IJobStepPropertiesControl.Save(JobStepData data, Boolean isSwitching)
at Microsoft.SqlServer.Management.SqlManagerUI.JobStepProperties.OnGatherUiInformation(RunType runType)
at Microsoft.SqlServer.Management.SqlMgmt.ViewSwitcherControlsManager.OnGatherUiInformation(RunType runType)

Scenario

This problem is reproducible this way.

  1. Start Sql Server Management Studio ( SSMS )
  2. Connect to SQL Server Instance
  3. Access SQL Server Agent \ Jobs \ [Job Name]
  4. Edit a Job Step whose type is “SQL Server Integration Services Package
  5. There are two tabs, Package and Configuration
  6. Access the “Configuration” Tab
  7. Within the “Configuration” Tab there are three Tabs.  The Tabs are “Parameters”, “Connection Managers”, and Advanced
  8. Select the “ExcelConnectionManager_ConnectionString” parameter
    • Click on “ellipse” button
    • The “Edit Literal Value for Execution” window appears
    • Access the “value” textbox and make cosmetic changes
    • Click the OK button
  9. The error, “Errors were detected in the command line arguments, please make sure all arguments are set correctly. (SqlManagerUI)“, aforementioned appears

 

Quick Points

  1. Other Parameters can be modified
    • This error only occurs when we tried amending the Excel File Connection String
    • We were able to modify other parameters; such as SQL Server and SMTP Server
  2. Connection Manager
    • All Connections ( Excel, SQL Server, and SMTP) can be successfully modified via the Connection Manager Tab

 

Honorable Mention

  1. Cody Konior
    • Agent job errors when running an SSIS package
      Published On :- 2015-August-2nd
      Link

 

SQL Server Versions

Here are the versions that exhibit this issue.

SQL Server Engine

Tabulated

Version Version Literal Version#
SQL Server v2014
Microsoft SQL Server 2014 (SP2-CU4) (KB4010394) – 12.0.5540.0 (X64) 12.0.5540.0

 

SQL Server Client

SQL Server Management Studio ( SSMS )

Tabulated

Version Version Literal Version#
SQL Server v2017
v 17.2 14.0.17177.0
SQL Server v2014
SQL Server 2014 SP2 CU10 12.0.5571.0

 

Image

SQL Server v2017

SQL Server v2014

Sql Server Data Tools – Integration Services Deployment Wizard–Error Message–“The variables were not found in the variable collection”

 

Background

Ran into problems deploying and running SSIS Packages.

Here is a brief write-up.

 

Scenario

Importing the package into SQL Server Data Tools.

And, also preparing to deploy it into .ispac file.

 

Error

Import Package into SSDT

Error :- Warning – The variables “$Project – Source [database collection]” were not found

Image

integrationServicesDeploymentWizard_Tab_SelectSource_FailedToDecrypt_20180206_0433PM

 

Textual

Warning: Failed to decrypt an encrypted XML node.
Verify that the Project was created by the same user.
Project load will attempt to continue without the encrypted information.

Package Deployment

Error :- Warning – The variables “$Project – Source [database collection]” were not found

 Image

deployPackage_subjectFailed_20180207_0226PM

 

Textual

 

Warning: The variables “Project-Source_Database_Connection” were not found in the variable collection.

The variables might not exist in the correct scope.

Error :

The expression for variable “Subject” failed evaluation.  There was an error in the expression.

 

TroubleShooting

Expression Builder

Accessed Expression builder for the variable, Subject, noted:

Image

expressionBuilder_20180207_0259PM

 

Text


"DataRefresh From "  +  SUBSTRING( @[$Project::Source_Database_Connection] , 13, 15) +" to " + SUBSTRING( @[$Project::Target_Database_Connection] , 13,15 )

 

Explanation

Noted that the expression makes reference to the Database Connection which can potentially contain sensitive data; such as server name and password.

 

Remediation

Outline

Correct package by:

  1. Removing potential sensitive data
  2. Change package properties
    • Security – “Protection Level”
      • Change from “EncryptSensitiveWithUserKey to “DontSaveSensitive”
  3. Place sensitive data in environment specific configuration files

 

Images

Package

Package – Property

Current
Image

package_properties_20180207_0554PM

 

Revised

Image

package_properties_ProtectionLevel_20180207_0601PM

Integration Services – Error – “SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E37”

 

Error

Error Image

Error Text

Started:  6:15:02 AM
Error: 2018-01-18 06:15:07.61
Code: 0xC0202009
Source: Read Excel Data Excel Source File [49]
Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E37.
End Error
Error: 2018-01-18 06:15:07.61
Code: 0xC02020E8
Source: Read Excel Data Excel Source File [49]
Description: Opening a rowset for "Sheet1$" failed. Check that the object exists in the database.
End Error
Error: 2018-01-18 06:15:07.65
Code: 0xC004706B
Source: Read Excel Data SSIS.Pipeline
Description: "Excel Source File" failed validation and returned validation status "VS_ISBROKEN".
End Error
Error: 2018-01-18 06:15:07.65
Code: 0xC004700C
Source: Read Excel Data SSIS.Pipeline
Description: One or more component failed validation.
End Error
Error: 2018-01-18 06:15:07.65
Code: 0xC0024107
Source: Read Excel Data
Description: There were errors during task validation.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started:  6:15:02 AM
Finished: 6:15:08 AM
Elapsed:  6.422 seconds

 

TroubleShooting

SQL Server Agent

Job Step

Image

Tabulate

Item Value Breakdown
Connection String Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\Import\Sample.xlsx;Extended Properties=”EXCEL 12.0 XML;HDR=YES”;
Provider = Microsoft.ACE.OLEDB.12.0
Extended Properties=”EXCEL 12.0 XML;HDR=YES”
Extended Properties=”EXCEL 12.0 XML;HDR=YES”

 

Remediation

Microsoft Access Database Engine 2010 Redistributable

Guidance

Link

Step

Download and apply “Microsoft Access Database Engine 2010 Redistributable“.  It is available here.

We are on a 64-bit machine and so we downloaded the 64-bit version.

Restart the machine or just the related services ( SQL Server Engine, SQL Server Agent and Integration Services ).

SSIS – “Could not load package because of error 0xC00160AE ” – “Access is Denied”

Error

Error Message

Could not load package “\MSDB\ImportData” because of error 0xC00160AE.
Description: Connecting to the Integration Services service on the computer “LAB-DB” failed with the following error: “Access is denied.”

Error Image

 

Remediation

Component Services

Global

Objective

Add SQL Server Agent Proxy Account to the local “Distributed COM Users” group.

This allows the proxy account to launch and activate all COM Objects on the local machine.

Image

Specific Objects

Steps

  1. Launch “Component Services” and navigate to each “Microsoft SQL Server Integration Services” component.
  2. Right click on each such component and grant permission to each Integration Service Component.
    • Access the Services Tab
      • Group box :- “Launch and Activation” permissions
        • Access the “Launch and Activation” permissions group box
        • Ensure the “Customize” option is chosen
        • Click the “Edit” button
        • Grant your proxy account “Local Launch” and “Local Activation” access
      • Group box :- Access permissions
        • Access the “Access” permissions group box
        • Ensure the “Customize” option is chosen
        • Click the “Edit” button
        • Grant your proxy account “Local Access
    • Restart relevant Sql Server Integration Services

Images

Microsoft SQL Server Integration Services
Security
Security – Launch and Activation Permission

 

Security – Access Permission

 

Services Applet

SQL Server

msdb

Objective

Please make sure that proxy account has access to the msdb database and that it has been granted membership in the db_ssisoperation role.

Syntax


USE [msdb]
GO
if user_id('account') is null
begin

    CREATE USER [proxyAccount] FOR LOGIN [proxyAccount]

end

ALTER ROLE [db_ssisoperator] 
   ADD MEMBER [proxyAccount]
GO

References

  1. Microsoft
    • Docs
      • Docs / SQL / SQL Server / Integration Services / Service
        • Integration Services Service (SSIS Service)
          Link
    • Technet
      • Integration Services > Integration Services Features and Tasks > Security
        • Integration Services Roles (SSIS Service)
          Link
  2. StackExchange
    • How do I grant the privilege of connecting to SSIS?
      Link

Sql Server – Integration Services Catalogs – Permission Management Using SSMS

Background

Let us quickly review how we can use SQL Server Management Studio (SSMS) to manage the Integration Services Catalogs.

 

Lineage

Here is our journey so far …

  1. Sql Server – Integration Services – SSIS Catalog ( SSISDB ) – Metadata – List Parameters
    Link
  2. Sql Server – Integration Services – SSIS Catalog ( SSISDB ) – Metadata – List Permissions
    Link

 

 

SQL Server Management Studio ( SSMS )

Outline

Here are the steps:

  1. Launch SQL Server Management Studio ( SSMS)
  2. Connect to the SQL Server Instance
  3. Under the SQL Server Instance, if an Integration Service Catalog has previously been created, look for the Integration Services Catalog Node
  4. Under the Integration Services Catalog Node, the database will appear
  5. Access the database, review the list of folders listed
    • Under each folder, Projects and Environments are listed
  6. We will be granting permissions at the Folder level
  7. Return to the Folder, right click on the Folder and from the dropdown menu select “Folder Properties”
  8. The Folder Properties window appears
    • In the Folder Properties window there are two pages, the General and Permissions
  9. In the Folder Properties window access the Permissions page
  10. The permissions available at the Folder Level appears
    • The permissions available includes Read, Modify, Manage Permissions, Create Objects, Modify Objects, Execute Objects, Read Objects, and Manage Object Permissions
    • Delegation of Tasks
      • For developers, the most common permissions to grant are the read permissions ( Read & Read Objects )
      • For Operation DBAs, all permissions outside of execute permissions can be granted
      • Ffr the Proxy Service account that scheduled jobs will run under, please grant the Read and Execute permissions

 

Image

Object Explorer

Folder Properties

Folder Properties – Permissions

 

Sql Server – Integration Services – SSIS Catalog ( SSISDB ) – Metadata – List Permissions

Introduction

Quick follow-up to our last post, here is how to retrieve permission set for our SSIS Catalog ( SSISDB).

BTW, here is a link to our last post.

 

SQL



use [SSISDB]
go

; with ctePermissionType
(
	  [permissionTypeID]
	, [permissionName]
)
as
(

	select 1, 'Read'

	union
	select 2, 'Modify'

	union
	select 3, 'Execute'

	union
	select 4, 'ManagePermissions'

	union

	select 100, 'CreateObjects'

	union

	select 101, 'ReadObjects'

	union
	select 102, 'ModifyObjects'

	union
	select 103, 'ExecuteObjects'

	union
	select 104, 'ManageObjectPermissions'

)
, ctePermission
as
(

	SELECT DISTINCT 
	      [catalog]
			= cat.[name]

		, [principal]
			= pris.[name]

		, [isDeny]
			= pers.[is_deny]

		, [permisionType]
			= pris.[type] 

		, ctePT.[permissionTypeID]

		, ctePT.permissionName

		, [granted]
			= case
				when pers.is_deny = 1 then 'No'
				else 'Yes'
			 end

	FROM [catalog].[folders] cat

	INNER JOIN [SSISDB].[catalog].[explicit_object_permissions] pers  

		on cat.folder_id = pers.[object_id]

	INNER JOIN [SSISDB].[internal].[get_database_principals]() pris  
		ON pers.principal_id = pris.principal_id  

	INNER JOIN ctePermissionType ctePT
		ON pers.permission_type = ctePT.permissionTypeID

)


select 
		  ctePerm.[catalog]
		, ctePerm.[principal]

		, [read]
			= isNull
				(
						ctePerm.[Read]
					, 'No'
				)

		, [modify]
			= isNull
				(
						ctePerm.[Modify]
					, 'No'
				)

		, [execute]
			= isNull
				(
					ctePerm.[Execute]
					, 'No'
				)

		, [managePermissions]
			= isNull
				(
					ctePerm.[ManagePermissions]
					, 'No'
				)

		, [createObjects]
			= isNull
				(
					ctePerm.[CreateObjects]
					, 'No'
				)

		, [readObjects]
			= isNull
				(
					ctePerm.[ReadObjects]
					, 'No'
				)

		, [modifyObjects]
			= isNull
				(
					ctePerm.[ModifyObjects]
					, 'No'
				)

		, [executeObjects]
			= isNull
				(
					  ctePerm.[ExecuteObjects]
					, 'No'
				)

		, [manageObjectPermissions]
			= isNull
				(
					  ctePerm.[ManageObjectPermissions]
					, 'No'
				)

from 
	(

		select
			  ctePerm.[catalog]
			, ctePerm.[principal]
			, ctePerm.[granted]
			, ctePerm.[permissionName]

		from   ctePermission ctePerm

	)
	ctePerm

pivot (

		 max 
		 (
			[granted]
		 )       

		for ctePerm.[permissionName] in 
		(
			  [Read]
			, [Modify]
			, [Execute]
			, [ManagePermissions]

			, [CreateObjects]
			, [ReadObjects]
			, [ModifyObjects]
			, [ExecuteObjects]
			, [ManageObjectPermissions]
		)
	
	)       

     as [ctePerm]

order by
		  ctePerm.[catalog]
		, ctePerm.[principal]








Output

 

Sql Server – Integration Services – SSIS Catalog ( SSISDB ) – Metadata – List Parameters

Introduction

Microsoft continues to build out Sql Server Integration Services (SSIS).

There is support for various package repositories; inclusive are Flat files, MSDB, and SSIS DB.

SSIS Catalog / SSISDB

If you go with SSISDB, it is easy to review the package configuration data.

 

SQL


use [SSISDB]
go

SELECT 
		  [folder] 
			= folders.folder_id

		, [folderName]
			 = folders.[name]

		, [projectName] 
			= projects.[name]

		, [packageName]
			= [packages].[name]

		, [objectType]
			 = case

				when ([paras].[object_type] = 20) then 'Project'
				when ([paras].[object_type] = 30) then 'Package'
				else cast
						(
							[paras].[object_type] 
								as varchar(30)
						)

			   end	

		, [parameterName]
			= [paras].parameter_name

		, [parameterDataType]
			= [paras].data_type

		, [designeDefaultValue]
			= [paras].[design_default_value]

		, [defaultValue]
			= [paras].[default_value]

		, [valueSet]
			= case
				when ([paras].[value_set] =1) then 'Yes'
				else 'No'
		      end

		, [projectDeployTime]
			= convert(varchar(30), [projects].[last_deployed_time], 100)

FROM   [catalog].[folders] folders

INNER JOIN [catalog].[projects] projects 

	ON projects.[folder_id]=folders.[folder_id]

INNER JOIN [catalog].[packages] packages 
	ON packages.[project_id]=projects.[project_id]

INNER JOIN [catalog].[object_parameters] paras 
	ON  ( paras.[object_name]=packages.[name] )
	AND ( paras.[project_id]=packages.[project_id])

 

 

Output

Acknowledgment

Nothing here original.

Trained SQL Server Profiler and catch the SQL it is using.