November 26, 2007 at 1:14 pm
I am attempting to query a DB2 table that is accessible through an Oracle gateway using an
existing dblink. I have successufully set up a connection manager that let's me use an Oracle
table as input so I am pretty sure my connection manager is ok.
Basically, I need to be able to use the output from this sql query.
select emp_no,sta_cd from sqlprod.emp@sqlfcl
emp_no and sta_cd are strings.
I think (not sure) that the best way to do this is to use a source script component.
I have added two output columns to Output0 in the Script Transformation Editor.
They are called empno and stacd.
When I try to click on the DataType in the Data Type Properties, the darn thing just
closes on me, never giving me the chance to set it to a sting. This happens before I
even get to the point of designing the script.
When I do go into the script designer, the following code has a problem.
Specifically the .empno = SqlReader.GetString(0) line complains that
Option Strict On disallows implicit conversions from 'String' to 'Integer'
Same for the stacd.
Public Overrides Sub CreateNewOutputRows()
'
' Add rows by calling AddRow method on member variable called " Buffer"
' E.g., MyOutputBuffer.AddRow() if your output was named "My Output"
'
Dim SqlReader As SqlDataReader
SqlReader = sqlCmd.ExecuteReader()
Do While SqlReader.Read
With Output0Buffer
.AddRow()
.empno = SqlReader.GetString(0)
.stacd = SqlReader.GetString(1)
End With
Loop
End Sub
I need to have two output columns that are strings, not integers.
How do I set the datatype of the output columns if I cannot get into
the datatype property?
Thanks in advance for any help.
Derek
November 26, 2007 at 1:33 pm
Hi Derek,
Is there a reason that an OLEDB source with the Oracle OLEDB privder was not used in the transformation? It has the capability to have a query as the Data Access Mode.
Norman
DTS Package Search
November 26, 2007 at 2:19 pm
I was not using the OLE DB source because I could not see how to make it use the
database link that in turn connects me to the DB2 database on the mainframe.
The mainframe DB2 database does not have a listener that supports a network
connection other than the Oracle agent that is running on the mainframe in support of
the Oracle transparent gateway.
All I saw when using the OLE DB source is the ability to pick which table to access.
That all said, I just took a closer look due to your question.
Turns out I can set the OpenRowset to sqlprod.emp@sqlflc when using the advanced editor.
Thanks for prodding me along. 🙂
This will probably meet my needs.
Derek
November 26, 2007 at 2:50 pm
Derek,
Looks like you made it, but not where I was "prodding"
On the "standard" OLEDB property editor there should be four items on the Data Access Mode drop down. One of them should be SQL Command. I would hope you could have pasted your query in there dblink or no dblink.
Norman
DTS Package Search
November 26, 2007 at 3:06 pm
Yep, that's exactly where I ended up as I looked closer at that data access mode field.
derek
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply