Creating Oracle Tables using SSIS

It’s possible to use SSIS to create a new Oracle table for you. However, there are a few ‘gotchas’ that it’s necessary to look out for.
It’s possible to use the ‘OLE DB Destination’ adapter to insert data into Oracle.  Thus, if a new Oracle table is required, it’s possible to select the destination Oracle database, and use the ‘New’ button to generate code for you automatically. This will bring up a text editor, which may have some content similar to the following snippet:

CREATE TABLE “MyTable” (
“ETLLoadID” VARCHAR2(255),
“Column1” NUMERIC(10),
“Column2” NUMERIC(10)
)

This will create a table called “MyTable” for you, which initially sounds like good news. When the SSIS package runs, it will deliver the data into the destination Oracle table.
Don’t get comfort from those green SSIS boxes, however. It’s important to be careful with those quotation marks.
Thus, when you try to select from the Oracle table, using the following statement, it won’t work:
select ETLLoadID from MyTable
This does not work, and generates the following error message:
ORA-00904: Invalid Column Name
Then, the select statement does not work; it needs quotation marks around it.  This will work better:
select “ETLLoadID” from MyTable
In order to find out exactly how the table has been implemented, it’s possible to use the DESCRIBE command. This is like SP_HELP, and provides you with data you need for the table.
Instead of using SSIS to generate tables, I now create my own tables in Oracle since it gives me the control I need, and means that I don’t have to worry about quotation marks. If you need a useful link to help you with the syntax, here it is.
I hope that helps!

Add to Technorati Favorites

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s