When we are importing data using DIXF framework – during get staging data process – following error could be thrown:
SQL error description: [Microsoft][SQL Server Native Client 10.0][SQL Server]The specified schema name “<schema name>” either does not exist or you do not have permission to use it.
We can see in the SQL statement:
SQL statement: CREATE FUNCTION [<schema name>].FN_FMT_NUMBERSEQUENCE
Error is thrown when function FN_FMT_NUMBERSEQUENCE is created and used schema name looks to be invalid.
Schema name is obtained in function DMFGenerateSSISPackage::getSchemaName (see below):
private static str getSchemaName()
{
ResultSet resultSet;
str sql_Current_user = 'SELECT current_user';
str schemaName;
Connection con;
SqlStatementExecutePermission sqlStatementExecutePermission;
...
}
The original code: ‘SELECT current_user’ instead of expected “dbo” can return AOS service account. To solve this issue we need just to change it to SELECT SCHEMA_NAME()
private static str getSchemaName()
{
ResultSet resultSet;
//-->
// str sql_Current_user = 'SELECT current_user';
str sql_Current_user = 'SELECT SCHEMA_NAME()';
//<--
str schemaName;
Connection con;
SqlStatementExecutePermission sqlStatementExecutePermission
...
}
