SAS Loads 1st Time to AZURE Dedicated SQL pool, but Subsequent Re-Write Fails

[Amitkmr1979]

Dear Friend,

We have SAS 9.4M7 which is trying to connect with Azure Synapse dedicated pool (DWH), the connection is successful

It can extract Data from Another application and load it the first time. 

But while running the same DI job 2nd time its giving the following error:

  Line 383: ERROR: The ODBC table xxxxxxxx has been opened for OUTPUT. This table already exists, or there is a name conflict with an existing object. This table will not be replaced. This engine does not support the REPLACE option.

We tried few options to drop the complete table/Delete it and re-load, but still it is doesn't go away

[ballardw · Accepted solution]

Anytime that you have a question about an error or other message you should as a minimum include the entire procedure or code block including all of the messages.

That way we don't make suggestions that are already in the code, might conflict or interact improperly with other options already used.

Since the engine you are using does not support a Replace option it sounds like you may have to use another tool to remove the existing table OR use a different approach.

[Amitkmr1979]

Apologies,

As it is a DI code with Source Table, Extract Transformation and the Loader Table. 

Below is the code for Loader Transformation and libname to connect to Azure 

LIBNAME AZRSQL ODBC  INSERTBUFF=32767  READBUFF=32767  DELETE_MULT_ROWS=YES  UPDATE_SQL=YES  UPDATE_MULT_ROWS=YES  USE_ODBC_CL=YES  DATAsrc=SynapseDev  AUTHDOMAIN="SIT-SSAS-AZURE-Auth" ;

Code for Loader Transformation:

/* --------------------------------------------------------------
Load Technique Selection: Replace - EntireTable
Constraint and index action selections: 'ASIS','ASIS','ASIS','ASIS'
Additional options selections...
Set unmapped to missing on updates: false
-------------------------------------------------------------- */
%macro etls_loader;

%let etls_tableOptions = ;

/*---- Map the columns ----*/
proc datasets lib = work nolist nowarn memtype = (data view);
delete WITB146H;
quit;

%put %str(NOTE: Mapping columns ...);
proc sql;
create view work.WITB146H as
select
INSTITUTION,
ACAD_CAREER,
STRM,
UN_ROW_NUMBER,
UN_WEEK_NO,
UN_WEEK_BEGIN_DT
format = DATETIME22.3,
UN_RECESS_WK,
UN_EXAM_PERIOD,
UN_RECESS_WK_TXT,
LASTUPDOPRID,
LASTUPDDTTM
format = DATETIME22.3,
RUN_DTTM
format = DATETIME22.3
from &etls_lastTable
;
quit;

%let SYSLAST = work.WITB146H;

%let etls_lastTable = &SYSLAST;
%let etls_tableOptions = ;

/* Determine if the target table exists */
%let etls_tableExist = %eval(%sysfunc(exist(AZRSQL.TEST_EXT_STUD, DATA)) or
%sysfunc(exist(AZRSQL.TEST_EXT_STUD, VIEW)));

/*---- Drop a table ----*/
%put %str(NOTE: Dropping table ...);
proc datasets lib = AZRSQL nolist nowarn memtype = (data view);
delete TEST_EXT_STUD;
quit;

%rcSet(&syserr);

%let etls_tableExist = 0;

/*---- Create a new table ----*/
data AZRSQL.TEST_EXT_STUD
(dbnull = (
INSTITUTION = YES
ACAD_CAREER = YES
STRM = YES
UN_ROW_NUMBER = YES
UN_WEEK_NO = YES
UN_WEEK_BEGIN_DT = YES
UN_RECESS_WK = YES
UN_EXAM_PERIOD = YES
UN_RECESS_WK_TXT = YES
LASTUPDOPRID = YES
LASTUPDDTTM = YES
RUN_DTTM = YES));
attrib INSTITUTION length = $5
format = $5.
informat = $5.
label = 'INSTITUTION';
attrib ACAD_CAREER length = $4
format = $4.
informat = $4.
label = 'ACAD_CAREER';
attrib STRM length = $4
format = $4.
informat = $4.
label = 'STRM';
attrib UN_ROW_NUMBER length = 8
label = 'UN_ROW_NUMBER';
attrib UN_WEEK_NO length = 8
label = 'UN_WEEK_NO';
attrib UN_WEEK_BEGIN_DT length = 8
format = DATETIME22.3
informat = DATETIME20.
label = 'UN_WEEK_BEGIN_DT';
attrib UN_RECESS_WK length = $1
format = $1.
informat = $1.
label = 'UN_RECESS_WK';
attrib UN_EXAM_PERIOD length = $1
format = $1.
informat = $1.
label = 'UN_EXAM_PERIOD';
attrib UN_RECESS_WK_TXT length = $100
format = $100.
informat = $100.
label = 'UN_RECESS_WK_TXT';
attrib LASTUPDOPRID length = $30
format = $30.
informat = $30.
label = 'LASTUPDOPRID';
attrib LASTUPDDTTM length = 8
format = DATETIME22.3
informat = DATETIME27.6
label = 'LASTUPDDTTM';
attrib RUN_DTTM length = 8
format = DATETIME22.3
label = 'Run Date/Time';
call missing(of _all_);
stop;
run;

%rcSet(&syserr);

/*---- Append ----*/
%put %str(NOTE: Appending data ...);

proc append base = AZRSQL.TEST_EXT_STUD
data = &etls_lastTable (&etls_tableOptions) force ;
run;

%rcSet(&syserr);

proc datasets lib = work nolist nowarn memtype = (data view);
delete WITB146H;
quit;

%mend etls_loader;
%etls_loader;

%let etls_recnt = 0;
%macro etls_recordCheck;
%let etls_recCheckExist = %eval(%sysfunc(exist(AZRSQL.TEST_EXT_STUD, DATA)) or
%sysfunc(exist(AZRSQL.TEST_EXT_STUD, VIEW)));

%if (&etls_recCheckExist) %then
%do;
proc sql noprint;
select count(*) into :etls_recnt from AZRSQL.TEST_EXT_STUD;
quit;
%end;
%mend etls_recordCheck;
%etls_recordCheck;

%perfstop(metrVal6=%sysfunc(max(&etls_recnt,-1)));
%let etls_recnt=-1;



/** Step end Table Loader **/

 Error Message:

ERROR: The ODBC table TEST_EXT_STUD has been opened for OUTPUT. This table already exists, or there is a name 
       conflict with an existing object. This table will not be replaced. This engine does not support the REPLACE option.

ERROR: The ODBC table TEST_EXT_STUD_UN_TERM_SCHEDUL_3 has been opened for OUTPUT. This table already exists, or there is a name 
       conflict with an existing object. This table will not be replaced. This engine does not support the REPLACE option.

[SASKiwi]

It is going to take someone familiar with the DI Studio interface here as the code is generated by the DI interface so it's not a good idea to tinker with it. Also you have provided the code but not the SAS log so we don't know where the errors are happening. Also when providing the SAS log you will need to add - options mprint; - so we can see macro-generated code.

The most likely scenario I can think of is that the DROP TABLE step is not being applied, so the table creation step fails on the second run. You might want to open a Tech Support track as there are not a lot of DI Studio users active in SAS Communities.

[Amitkmr1979]

Looks like the way Azure dedicated pool architecture is, it loads row by row to the SQL pool from SAS. Also, it is not taking parameters on libname such as bulk load, insert buffer etc...

We are looking for other strategy to load into Azure by leveraging the Azure Data lake Gen2. Not sure if this will work at this stage though.