May 9, 2016 at 6:46 am
Hello, i'm trying to connect to a oracle DB through a script component.
My script looks like:
#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using Oracle.ManagedDataAccess.Client;
#endregion
/// <summary>
/// This is the class to which to add your code. Do not change the name, attributes, or parent
/// of this class.
/// </summary>
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
private IDTSConnectionManager100 conMgr;
private OracleConnection oraCon;
private OracleCommand oraCmd;
private string cmd;
public override void AcquireConnections(object Transaction)
{
base.AcquireConnections(Transaction);
conMgr = Connections.Oracle;
oraCon = (OracleConnection)conMgr.AcquireConnection(Transaction);
}
public override void PreExecute()
{
base.PreExecute();
cmd = "SELECT SEQUENCE.NEXTVAL FROM DUAL";
}
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
oraCmd = new OracleCommand(cmd, oraCon);
Row.NEXT_VAL = Convert.ToDecimal(oraCmd.ExecuteScalar().ToString());
}
}
The Connection I use is a ADO.NET Connection to the Oracle DB.
When I start the whole SSIS Package I get the following Error:
System.InvalidCastException: Unable to cast obect of type 'System.Data.OracleClient.OracleConnection' to type "Oracle.ManagedDataAccess.Client.OracleConnection'.....
Anyone with an idea?
May 9, 2016 at 7:52 am
Is there any reason why you would not use a simple data source to do this?
And why use a script component rather than a data transformation?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 9, 2016 at 8:03 am
I must use it to get a "nextval" of the Oracle Sequence for my rows in the Data Flow Task, so I cant do it with a simple Source.
May 9, 2016 at 8:28 am
I'm not sure about your cast error, but here's an example of a script source that I wrote to pull database users from an Oracle instance. Hope this helps...
#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using Oracle.DataAccess.Client;
#endregion
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
string oraError = "";
public override void PreExecute()
{
base.PreExecute();
}
public override void PostExecute()
{
base.PostExecute();
Variables.UsersError = oraError;
}
public override void CreateNewOutputRows()
{
try
{
// connect to Oracle, run the query, and load results into Users table
string oraDB = "Data Source=" + Variables.Platform + ";User ID=" + Variables.OracleUser + ";Password=" + Variables.UserKey + ";";
string sql = Variables.GetUsersQuery;
using (OracleConnection conn = new OracleConnection(oraDB))
using (OracleCommand cmd = new OracleCommand(sql, conn))
using (OracleDataAdapter adp = new OracleDataAdapter(cmd))
{
conn.Open();
DataTable users = new DataTable();
adp.Fill(users);
if (users != null && users.Rows.Count > 0)
{
// iterate through users table and load into output buffer
foreach (DataRow row in users.Rows)
{
Output0Buffer.AddRow();
Output0Buffer.username = row["username"].ToString();
Output0Buffer.accountstatus = row["account_status"].ToString();
DateTime expiry_date, created, LastLogon;
bool success = DateTime.TryParse(row["expiry_date"].ToString(), out expiry_date);
if (success) Output0Buffer.expirydate = expiry_date;
bool success2 = DateTime.TryParse(row["created"].ToString(), out created);
if (success2) Output0Buffer.created = created;
bool success3 = DateTime.TryParse(row["LastLogon"].ToString(), out LastLogon);
if (success3) Output0Buffer.LastLogon = LastLogon;
}
}
conn.Close();
}
}
catch (Exception ex)
{
oraError = ex.Message.ToString();
}
}
}
May 9, 2016 at 9:42 am
totto (5/9/2016)
I must use it to get a "nextval" of the Oracle Sequence for my rows in the Data Flow Task, so I cant do it with a simple Source.
OK. I've not worked much with Oracle, so excuse my ignorance, but might the OLEDB Command work for this?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 9, 2016 at 3:22 pm
issue is using Oracle.ManagedDataAccess.Client; -- don't use the managed class.
Example from John should work but it will be slow depending on the volumes.
if you can it might be better to load onto a staging table and then just do a straight execute sql statement to insert from the staging table onto the final table - and here you can use the normal nextval syntax.
May 10, 2016 at 1:02 am
frederico_fonseca (5/9/2016)
issue is using Oracle.ManagedDataAccess.Client; -- don't use the managed class.
Should I use only the "Oracle.DataAccess.Client" like John? If yes, where can I get it?
frederico_fonseca (5/9/2016)
if you can it might be better to load onto a staging table and then just do a straight execute sql statement to insert from the staging table onto the final table - and here you can use the normal nextval syntax.
I already had the same idea. The Problem is, that I have to do it without the staging table. The Performance is not very important, I'm writing my final masterthesis about ETL-Software and have to solve these Problems only with the ETL Software and not workaround with the staging table.
I found a solution that works:
public override void Eingabe0_ProcessInputRow(Eingabe0Buffer Row)
{
OracleConnection conn = new OracleConnection("Data Source=OracleDB;User ID=ETLTEST;Password=TEST");
conn.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "SELECT ETLTEST.nextval FROM DUAL";
cmd.CommandType = CommandType.Text;
OracleDataReader dr = cmd.ExecuteReader();
dr.Read();
Row.NEXTSEQ = dr.GetInt64(0);
conn.Dispose();
}
I think the performance is not good in this solution but it works for me now.
But anyway I have one question when we look at my first solution and this solution:
In the first solution I want to use the ConnectionManager to use the connection to the Oracle DB.
In this solution here I 'manually' open the Connection by "conn.open()" ...
Is between this two ways of getting the connection a big difference? Or is the use of the ConnectionManager only an easier way?
I hope you understand what I mean.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply