I have a view in the database essentially doing all my transformations and formatting, as a requirement is to write Date fields in MM/DD/YYYY format.

In my view I used a CONVERT( varchar(10), MY_DATE, 101)  to get the format - however - SSIS kept turning this into a Date field as YYYY-MM-DD.

So I hook up a Data Viewer on the pipeline to the Flat File destination, and run the package.  Looks good.  The date field is a varchar as MM/DD/YYYY.

I open the flat file up and still the value is being written as YYYY-MM-DD.  Its almost as SSIS ignores my Advanced Setting in the the FF connection manager to say its a DT_STR type.

I delete the FF connection manager and recreate it, to the same result.  No other errors reported.  I mess with the OLE DB source query, to no avail whatever I do, the Flat file is written as YYYY-MM-DD.  

Now knowing SSIS's end result is a .dtsx file and XML related - I know I need to recreate some of these objects here and there - its the nature of the beast.

So although SSIS was not complaining about anything - I knew it had to be metadata related at some point.  

By opening SSMS and doing an alter on the view to recompile it - it threw SSIS to detect the metadata change.  So I must have had some old metadata somewhere in the .dtsx that was not being refreshed.  Once SSIS prompted me that it found the change ( after my view ALTER - to recompile ), I started to see my correct formatting of MM/DD/YYYY for the column.

First problem, TNS connect issues on local Sqlplus.  Could not simply set the SID and connect / as sydba.

The problem was that I have the 32 bit 11g client installed as well.  I just had to fix my path to put the client bin path after the 64 bit server path.  

Bingo that was fixed.

Next, couldn't connect to the default localhost:8080/apex site.  This required a bit more detective work, and i'm surprised this doesn't work out of the box.

I followed these instructions I wrote last year:

Configuring the Embedded Apex Gateway on Windows/Linux

1.       Go to the $ORACLE_HOME/apex Directory

2.       Use SQL*Plus to connect as SYS to 11g database where APEX is installed

3.       SQL> @apxconf

a.       Enter values for the XDB listener port and the password

4.       Unlock the ANONYMOUS / APEX_PUBLIC_USER account

a.       SQL> alter user anonymous account unlock ;

b.      SQL> alter user apex_public_user account unlock ;

5.       Enable the Oracle XML DB HTTP server

a.       SQL> exec  DBMS_XDB.SETHTTPPORT(8080) ;

b.      SQL> commit;

6.       You should now be able to hit:  http://localhost:8080/[apex] | [apex_admin]

This didn't fix the problem.

The issue is that my XDB service was not appearing in the Listener status;  This is how I set it.

This fix was to set the LOCAL_LISTENER parameter in my spfile:  

    SQL> alter system set LOCAL_LISTENER="(address(protocol=tcp)(host=localhost)(port=1521))" scope=both ;

I then bounced both the DB and listener for good measure.  Then I did a :

    SQL> alter system register;

And voila it worked ...

But then when I scheduled it with the Sql Agent to run every week - it ran successfully - but I didn't see any result.  The result was supposed to be some updated files out on the network - that are sent to some people as email attachements.

My first suspicion was Agent permissions.  I created a credential and a proxy, then ran it again under the proxy.  Same result.  Success in the job and the package, but no updated files or emails.

A little searching gave me the answer - and I should have known.  I had mapped drive dependencies in my package, although they were being created via property expressions.  Once I took out the drive letter mapping and put the full UNC path - it worked find under the agent.  For example, instead of using the P: drive,  use \\server\folder\folder\....  etc etc.  

Another good practice in SSIS design.  Always use UNC paths.

1. The standalone HTTP Server

2. The embedded XML HTTP Gateway

In production environments, you most likely will want to run headless standalone HTTP configurations ( no web interface ) for security reasons.

In sandbox or smaller dev or VM environments - the XML Gateway works well.  In order to configure the gateway, you must perform some basic steps: