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?
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?
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 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy