July 12, 2015 at 5:14 pm
I am developing a table driven ETL system, where I store large PL/SQL queries in a varchar(max) field. I read the field into an object variable at the package level using a SQL task. Next I have a C# script task that creates a connection to our Oracle database using the Oracle provider. I am trying to set the Command.Text property to the PL/SQL statement I've stored in the package level object variable, but I'm having difficulty getting the Pl/SQL text back out of the object. I've tried Object.value.tostring, but it just returns a generic "object__text". I have been researching this for awhile now, but I haven't found a good solution. Does anyone have any ideas on how I can proceed?:unsure:
July 13, 2015 at 8:03 am
Brandon Forest (7/12/2015)
I am developing a table driven ETL system, where I store large PL/SQL queries in a varchar(max) field. I read the field into an object variable at the package level using a SQL task. Next I have a C# script task that creates a connection to our Oracle database using the Oracle provider. I am trying to set the Command.Text property to the PL/SQL statement I've stored in the package level object variable, but I'm having difficulty getting the Pl/SQL text back out of the object. I've tried Object.value.tostring, but it just returns a generic "object__text". I have been researching this for awhile now, but I haven't found a good solution. Does anyone have any ideas on how I can proceed?:unsure:
Why use the package variable as an intermediary, if you're going down the road of using a C# script task? Why not just bring it into a recordset within the script task and then .tostring it from the value in the recordset?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 13, 2015 at 8:06 am
Brandon Forest (7/12/2015)
I am developing a table driven ETL system, where I store large PL/SQL queries in a varchar(max) field. I read the field into an object variable at the package level using a SQL task. Next I have a C# script task that creates a connection to our Oracle database using the Oracle provider. I am trying to set the Command.Text property to the PL/SQL statement I've stored in the package level object variable, but I'm having difficulty getting the Pl/SQL text back out of the object. I've tried Object.value.tostring, but it just returns a generic "object__text". I have been researching this for awhile now, but I haven't found a good solution. Does anyone have any ideas on how I can proceed?:unsure:
Alternatively, how about using dynamic SQL to use the value that comes back as part of T-SQL that uses either OPENQUERY or OPENROWSET with your Oracle provider (whichever one of those isn't using an existing linked server - I never remember which is which without looking at Books Online)?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 13, 2015 at 11:08 am
Steve, The PL/SQL I need to use to open the recordset is stored in varchar(max). That is the object text I'm having trouble recovering. Whatever method I use, I need to be able to pull the PL/SQL query from the varchar(max) field, which is a blob field that can only be accessed using an object variable.
July 13, 2015 at 7:11 pm
Brandon Forest (7/13/2015)
Steve, The PL/SQL I need to use to open the recordset is stored in varchar(max). That is the object text I'm having trouble recovering. Whatever method I use, I need to be able to pull the PL/SQL query from the varchar(max) field, which is a blob field that can only be accessed using an object variable.
Are you using a FOR EACH LOOP container, with an ADO Enumerator?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 13, 2015 at 10:25 pm
It looks like this method isn't going to work for me. I was having such a difficult time recovering the PL/SQL from the object variable that I switched methods and loaded it directly from a file into the Command.Text, but all the white spaces were jumbled and the /r/n were lost, so the unstructured SQL wouldn't parse correctly.
I'm running out of time, so I'm abandoning this concept for a table driven query processor for the less elegant ADO.net recordset that is specific to each query. This uses the Oracle Provider and I paste in the formatted PL/SQL directly to the command.text. I found a CodePlex C# project that will serialize and write out the ADO.net recordset as an XML file. That works in my POC.
My intent is to write the XML to a generic staging table as a VarChar(max) or XML data type, with enough metadata to identify the XML for future ETL processing that is specific to each ETL type. I'm working now to intercept the XML stream in the C# code before it writes it to disk, and redirect it to an object variable that will write it to my staging table instead. This means that I will need an ETL flow that is specific to each query, and there are over 200 queries, but it will work.
Thanks for your help. 😉
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply