Re: SAS ETL- is Password Protection for files a problem?

Ankit,

You have bumped into one of the areas where ETL Studio is inconsistent. I replicated what you are doing in my environment (ETL Studio 3.2 and SAS server 9.1.3 Service Pack 3). The Loader transformation is not using the table options in the delete step like it should. It does use them in the append step.

A couple of suggestions:

1. Generally, you will want to use Drop Table in the Load Technique option of Refresh instead of Truncate. This is because SAS does not give up the physical space with Truncate.

2. To get around this particular problem, you will have to turn off the loader (Select "Do not generate code for this process" on the Loader properties Process tab) and write your own code in a user written code transform. You can copy the code from the existing Loader, paste it into the UWC transform, and then modify it as necessary. I find it good practice to change the name of the non-executing Loader transform to "Loader (not used)" or something similar.

3. If you are going to have a lot of these then you may want to consider creating a SAS Code Transformation to ensure consistency and reduce the work load. It will be more work up front but could well be worth the effort.

4. You should open a ticket with SAS Tech Support and register your complaint about this. It is possible (even probable) that they know about the issue already but having a real user complain gives more leverage to getting the problem fixed. It is possible that the problem is resolved in ETL Studio 3.3 but I haven't checked.

HTH...

Clint

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU]On Behalf Of
Ankit
Sent: Saturday, January 14, 2006 11:23 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: SAS ETL- is Password Protection for files a problem?


Hi All,

I am currently working on ETL studio and am facing problem creating
jobs when I am using password protection facility for the SAS datasets.
The ETL job is using a "Splitter" transformation to read data from a
SAS dataset and then create 3 datasets out of the input dataset. The 3
datasets to be created are password protected and have both alter and
write protection.

The diagram flow appears somewhat like this in the ETL process editor:

sas dataset --> Spliter --> loader --> final dataset

This is the flow for one dataset. The splitter creates the same flow
for the remianing 2 dataset also. Now the intermediate dataset created
by the splitter is in work and this intermediate dataset is being read
by the loader. In the loader the mapping rules are being applied and
the output is finally loaded to the target dataset.

In the loader the table load option specfied is "truncate" . i.e delete
all rows before loading the data.

However, this job does not work on submission and the log displays the
message related to the "alter" password not specified. When I take a
look at the generated code I see that the macro created by the ETL
Loader has a intermediate step related to the "truncate" and here it is
deleting the rows but is nowhere using the password. I have specified
the password options in the options page on both the loader and the
target table tranaformation.

Any idea how I can overcome this problem. Or is it a problem with ETL
studio that it cannot tackle the password protected SAS datasets
properly.

Thanks & Regards,
Ankit

Hi Richard,

Thanks for the info...I was also of the opinion that the problem seems
to be at SAS end..but wanted to confirm with others as well. ..I
think I will go ahead and log this as a complaint with the technical
support of SAS.

Regards,
Ankit