Insert records into oracle table

[bnath]

Howdy folks,

New to SSIS. Here is the situation. Seems to be a very simple task but I need some guidance here.

Both the source and destination are oracle tables. I have an oracle table. I need to read data from source oracle table (based on a "where" condition) and insert into another oracle table.

How should I go about this? Do I need a Execute SQL task? If there is no "Where" condition I could probably connect to the source oracle table using "OLEDB data source " task.

Thanks
nath

 

[RiverGuy]

You usually use a Data Flow Task. You could use an Execute SQL Task if both tables are on the same server or if you have both servers linked up somehow. But by and large, Data Flow Task is what is used to transfer data from one place to another.

As far as your "Where" clause, it depends. If the Where clause is always the same, you can just code it in the Source query in your Data Flow Task. If it varies (say by date criteria for example), you can use a parameterized query depending on the data provider used, or your can set your source query to an SSIS variable.

 

[bnath]

Thanks for the response.

If I go down the route of using "Data flow task", what should it contain?

1. OLEDB Data source
2. Data Conversion
3. OLEDB Data destination

Correct? Where should I specify the Date parameter? I don't see any place for specifying in the OLEB data source.

 

[RiverGuy]

You will need 1 and 3. You don't always need #2.

Your source query syntax would look something like the following:

SELECT * FROM SomeTable WHERE SomeDateColumn >= ?

Then, click the "parameters" button on the connection manager tab of your OLE DB Source Editor.

 

[bnath]

I am trying right now.
nath

 

[bnath]

I don't see any "parameters" tab. Please see the attachement.

 

[RiverGuy]

The attachment must not have gone through. What data provider are you using for the source/destination?

 

[bnath]

I tried MSDA0ra.1 and Oracle driver

This is the issue:

In the OLEDB source editor screen, I chose the access mode as "sql command" and I am trying to get the data for a specific as_of_date

SELECT AS_OF_DATE, DEAL_NUMBER, SCID, FHLB_ID
FROM ODS.V_ADVANCES_EOM
where as_of_date = '31-may-2009'

I don't get any data though I have data for that specific period in the table.
nath

 

[RiverGuy]

I don't have much experience with Oracle, however in my few Oracle/SSIS packages, my date criteria is specified as follows:

WHERE LAST_UPDATE_DATE >= to_date('2008/12/03:12:00:00AM', 'yyyy/mm/dd:hh:mi:ssam')

 

[bnath]

Thanks RiverGuy. The harded coded date works.

Instead of hard coding the date, I want to pass the date as a parameter.

I kept the data access mode as "sql command" and clicked on "parameters" button.

I got the following message:

TITLE: Microsoft Visual Studio
------------------------------

Parameters cannot be extracted from the SQL command. The provider might not help to parse parameter information from the command. In that case, use the "SQL command from variable" access mode, in which the entire SQL command is stored in a variable.

------------------------------
ADDITIONAL INFORMATION:

Provider cannot derive parameter information and SetParameterInfo has not been called. (OraOLEDB)

------------------------------
BUTTONS:

OK
------------------------------

Any suggestions how to pass parameter.
I tried

 

[bnath]

I am still trying to figure out this.

I will post the update as soon as I am done.

thanks
nath

 

[MDXer]

The OLEDB for ORACLE drivers do not allow parameterized queries on datasource objects or fast load options on destination objects. So for simply going from oracle to oracle SSIS is not the greatest option. That being said there are workaround which require significant work. For a parameterized query you do the following.

2 Variables for each query.

var_Sql
Exec_var_SQL

As examples the var_sql you will paste in your parameterized query example:

SELECT * FROM MYTABLE WHERE ID = @ID

The Exec_Var_SQL you will set the EvaluateAs Expression Property to true and then build your expression your expression will look like this.

REPLACE(var_SQL,"@ID",@user::IdVar)

May not be 100% on the syntax as I don't have a way to test.

That just gets the data out getting the data IN efficiently is even more difficult. You could use an OLEDB destination and point it at the Oracle server, but as I said before there is no fast load option meaning that each record is inserted as a logged transaction rather than a batch. If you are loading a lot of records this quickly becomes an issue. but you have a couple options here.

1) Output to a flat file that you can then use SQLLoader to bulk load the records.
2) SSIS 2008 comes with a 3rd party oracle connector which has fast load options. It is the atunity (probably spelled wrong) and requires a seperate installation and restart of the SSIS service.

 

[bnath]

MDX

I almost got it. I am stuck with the synatax.

I want some thing like:

SELECT * FROM ODS.V_ADVANCES_EOM
where as_of_DATE = to_date('2009/05/31', 'yyyy/mm/dd')

So far I got till here:
"select * from ods.v_advances_eom where as_of_date = "to_date("+ @[User:aram_AsofDate]+", "+"'"+yyyy/mm/dd+"'"+")"

 

[MDXer]

"select * from ods.v_advances_eom where as_of_date =  to_date(\"" + (DT_STR, 22,1252) @[User::AsofDate] + "\", \"yyyy/mm/dd\")"

I built this in bids so it should work for you. The issue was the special characters needed to be escaped which is why you see \.