SSISDB Folder creation Error
-
We recently installed SSIS on SQL Server 2022 machine. Trying to create a folder under SSISDB integration Services catalog is giving this below error.
Operation 'Create' on object 'CatalogFolder[@Name='Testing1']' failed during execution. (Microsoft.SqlServer.Management.Sdk.Sfc)
Cannot find either column "internal" or the user-defined function or aggregate "internal.is_valid_name", or the name is ambiguous.
Cannot find the folder 'Testing1' because it does not exist or you do not have sufficient permissions. (Framework Microsoft SqlClient Data Provider)
I do have sysadmin and ssis_admin access on that box.
Error trace:
Operation 'Create' on object 'CatalogFolder[@Name='Testing1']' failed during execution. (Microsoft.SqlServer.Management.Sdk.Sfc)
------------------------------
For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=17.100.40.0&LinkId=20476
------------------------------
Program Location:
at Microsoft.SqlServer.Management.Sdk.Sfc.SfcInstance.CRUDImplWorker(String operationName, SfcObjectState requiredState, SfcDependencyAction depAction, SfcObjectState finalState, Object extraParam)
at Microsoft.SqlServer.Management.IntegrationServices.CatalogFolder.Create()
at Microsoft.SqlServer.IntegrationServices.UITasks.CreateObjectController.CreateObject()
at Microsoft.SqlServer.IntegrationServices.UITasks.CreateObjectController.Perform(ITaskExecutionContext taskExecutionContext)
at Microsoft.SqlServer.Management.TaskForms.TaskExecutionManager.ExecuteTaskSequence(ISfcScriptCollector collector)
===================================
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Program Location:
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType, Boolean retry)
at Microsoft.SqlServer.Management.Sdk.Sfc.SfcTSqlExecutor.Execute(ServerConnection connection, String script, ExecutionMode mode, ExecutionTypes type)
at Microsoft.SqlServer.Management.Sdk.Sfc.SfcTSqlExecutionEngine.Microsoft.SqlServer.Management.Sdk.Sfc.ISfcExecutionEngine.Execute(ISfcScript script)
at Microsoft.SqlServer.Management.Sdk.Sfc.SfcInstance.CRUDImplWorker(String operationName, SfcObjectState requiredState, SfcDependencyAction depAction, SfcObjectState finalState, Object extraParam)
===================================
Cannot find either column "internal" or the user-defined function or aggregate "internal.is_valid_name", or the name is ambiguous.
Cannot find the folder 'Testing1' because it does not exist or you do not have sufficient permissions. (Framework Microsoft SqlClient Data Provider)
------------------------------
For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-4121-database-engine-error
------------------------------
Server Name: SQLServerName
Error Number: 4121
Severity: 16
State: 1
Procedure: SSISDB.catalog.create_folder
Line Number: 67
------------------------------
Program Location:
at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at Microsoft.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
at Microsoft.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at Microsoft.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType, Boolean retry)
-
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
-
good task
-
Did the folder actually get created, is it only the error message that is wrong.
NTFS it's own database. It has an object store and a cache of Objects in the store. You only get to see what is in the cache. Now that just about everywhere is using nvme storage, it is standard that object store updates complete and control returned to the user, before the cache updates complete. This type of problem was first hit on mainframes in the 1970s and got called a 'timing issue'.
In your own processes it is wise to add a small delay between any create/delete/rename of a ntfs object and attempting to use it, to give time for the cache to be updated. When you are using anything written by someone else, including MS, you have no control over if they allow time for cache updates before attempting to use the object.
So, I have no firm idea what caused your problem or how to fix it, just an idea of what it might be.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
-
Were you logged in as a SQL Server user or a Windows user when you tried this?
Are the 'could not find' objects still present? Eg, Is_Valid_Name should be here:
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply