How to Get the Output Column in OLE DB Command Transformation

Hi,

I am writing a Dataflow task which will take a Particular column from the source table and i am passing the column value in the SQL command property. My SQL Command will look like this,

Select SerialNumber From SerialNumbers Where OrderID = @.OrderID

If i go and check the output column in the Input and output properties tab, I am not able to see this serial number column in the output column tree,So i cant able to access this column in the next transformation component.

Please help me.

Thanks in advance.

Hi,

I am writing a Dataflow task which will take a Particular column from the source table and i am passing the column value in the SQL command property. My SQL Command will look like this,

Select SerialNumber From SerialNumbers Where OrderID = ?

If i go and check the output column in the Input and output properties tab, I am not able to see this serial number column in the output column tree,So i cant able to access this column in the next transformation component.

Please help me.

Thanks in advance.

|||

It sounds as tho you are using the wrong component. To source stuff use the OLE DB Source Adapter, not the OLE DB Command.

-Jamie

|||

Dear Jamie,

Thanks for such a quick reply.

U Mean OLEDB Source From DataFlow Sources.

Actually the My dataflow task contains one OLEDB source component which is having connection to one table, from that table i am getting the orderID column, Then i am passing this OrderID column values to the query Which will get the serialnumber in the SerialNumbers table based on this OrderID. And my problem is i cant able to get this selected serialnumber column in the output column tree view,so i that column is not accessable for futher transformations.

Please give me some solution.

Thanks in advance.

- Dhivya

|||

You need the LOOKUP transform. That s exactly what it does.

-Jamie

|||

Dear Jamie,

That also i tried,the table contains multiple values(for same OrderID multiple serial numbers) and the lookup transform will take only the first value and map the same to the others.

-Dhivya

|||

So its a many-to-many?

Then you should use the MERGE JOIN component!

-Jamie

|||

Good advice, Jamie.

Dhivya, remember that the Merge Join needs a sorted input, so you'll also need to use sort components. Alternatively, use ORDER BY in the source queries, and set the IsSorted property of the source adapter output to True.

Donald

|||

I tried merge join, the problem is order ID is not unique in my source table and in transaction table. so if i put some inner or left joins i am not getting the values what i want.

-Dhivya

|||

Merge Join worked for me. I did right outer join.

Thank u Jamie and donald.

But still my question is, we cant get the output columns in OLE DB Command Component if we use select command?

|||

No. That's not what its for!

-Jamie

|||

OK. Thanks a lot

-Dhivya

How to get the Out put from the Stored Procedure

Hi Every body,

i am trying to execute a stored procedure and want the out put to be passed to another database table.

I tried to create a OLEDB Source and gave the Exec Procedure Statement. I tried to see the preview and able to see the out put results. But when i am clicking on the columns to map to the destination i am not able to see the metadata.

Can you guys pls let me know how to do this.

thanx in advance..

Regards,

Dev

If your stored procedure is complex, you may need to insert a SELECT that returns the expected columns as the first statement in your stored proc. You can add a WHERE clause like WHERE 0 = 1 to ensure that no rows are returned.

When you are using a multiple operation stored procedures, a lot of tools (SSIS included) use the first resultset to determine the metadata for the stored proc. You can add a "dummy" resultset to ensure that it gets the right metadata.

|||

Hi,

I really appreciate your early reply. i will give a try and let you know the out come.

Thanx & Regards,

Dev

|||

hi,

can you pls let me know how to write a dummy SQL Statement for the out put, as i am not able to get the table dosent existsin the Database

Dev

|||

Hi,

I tried a sample like this

1. Created a SP

ALTERprocedure [dbo].[usp_test]

as

begin

declare @.error_number int,

@.row_count int

CREATETABLE #temp (

test1 varchar(50),

test2 varchar(50)

)

SELECT*from #temp where 0=1;

end

2. Added OLEDB SOURCE -- > added as a SQL Command EXEC usp_test.

3. now when i click on the metadata it's not showing up the coloumns

Please suggest what i am doing wrong

Regds,

Dev

|||

SELECT '' AS mystringcolumn, 1 as myintcolumn WHERE 1 = 0

The columns here need to match your expected resultset, both in name and type.

|||You need to put the select before the create table. See my post above.|||

Add this t-sql at the end of the storedproc

declare @.sql varchar (50 )

selelct @.sql='select * from #temp where 0=1'

Exec (@.sql)

give it a try

How to get the count of source records doing UPDATE with joined tables.

I have an sql like this in my stored procedure.
UPDATE T1
SET COL1 = T2.COL1
FROM T1, T2
WHERE T1.COL2=T2.COL2
So, COL1 of T1 table is modified when T1 finds matching records in T2
table with T1.COL2=T2.COL2. The problem is T1.COL1 is updated when
there are multiple matching records from T2. I want to make T1 table
be updated when there is exactly one matching record in T2.
Of course I can check the count of the matching records in T2 before
doing the above but T2 is actually from openquery interface(so dynamic
sql) to a remote server and I don't know exactly how I can get a
cursor with a dynamic sql.(I guess I should search this soon.)
If there is a way to find out the count of matching source records for
the above sql, it will help me a lot. Thanks..I think this should work
UPDATE T1
SET COL1 = T2.COL1
FROM T1, T2
WHERE T1.COL2=T2.COL2
And (Select Count(*) From T2 Where T2.COL2=T1.COL2)=1
Dmitriy
"Yi, Dong-ryon" <feeva@.hanmail.net> wrote in message
news:bf8bb96c.0503131958.770f80ea@.posting.google.com...
>I have an sql like this in my stored procedure.
> UPDATE T1
> SET COL1 = T2.COL1
> FROM T1, T2
> WHERE T1.COL2=T2.COL2
> So, COL1 of T1 table is modified when T1 finds matching records in T2
> table with T1.COL2=T2.COL2. The problem is T1.COL1 is updated when
> there are multiple matching records from T2. I want to make T1 table
> be updated when there is exactly one matching record in T2.
> Of course I can check the count of the matching records in T2 before
> doing the above but T2 is actually from openquery interface(so dynamic
> sql) to a remote server and I don't know exactly how I can get a
> cursor with a dynamic sql.(I guess I should search this soon.)
> If there is a way to find out the count of matching source records for
> the above sql, it will help me a lot. Thanks..|||On 13 Mar 2005 19:58:20 -0800, Yi, Dong-ryon wrote:

>I have an sql like this in my stored procedure.
>UPDATE T1
>SET COL1 = T2.COL1
>FROM T1, T2
>WHERE T1.COL2=T2.COL2
>So, COL1 of T1 table is modified when T1 finds matching records in T2
>table with T1.COL2=T2.COL2. The problem is T1.COL1 is updated when
>there are multiple matching records from T2. I want to make T1 table
>be updated when there is exactly one matching record in T2.
>Of course I can check the count of the matching records in T2 before
>doing the above but T2 is actually from openquery interface(so dynamic
>sql) to a remote server and I don't know exactly how I can get a
>cursor with a dynamic sql.(I guess I should search this soon.)
>If there is a way to find out the count of matching source records for
>the above sql, it will help me a lot. Thanks..
Hi Yi,
As an alternative to the suggestion made by Dmitriy, here's a version
that refers to the T2 table in only one place:
UPDATE T1
SET Col1 = T2a.Col1
FROM T1
INNER JOIN (SELECT Col2, MIN(Col1) AS Col1
FROM T2
GROUP BY Col2
HAVING COUNT(*) = 1) AS T2a
ON T2a.Col2 = T1.Col2
(untested)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

How to get source IP address of a database connection

Hi,
Is there a way to find out the source IP address of a database connection?
In trace there are only netbios names available, while I need to log the IP
addresses that connect to the database.
Thx,
Jacek Skaznik
We store the MAC address in sysprocesses while the connection is there. If
you query master..sysprocesses you'll see it, but we don't map the MAC to
an IP.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

How to get source IP address of a database connection

Hi,
Is there a way to find out the source IP address of a database connection?
In trace there are only netbios names available, while I need to log the IP
addresses that connect to the database.
Thx,
Jacek SkaznikWe store the MAC address in sysprocesses while the connection is there. If
you query master..sysprocesses you'll see it, but we don't map the MAC to
an IP.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

How to get source by execution oracle sp using ref cursor

Hi,

I need to get recordset returned by oracle sp in execute sql task to process futher in For Each Loop container and on same lines i want to use oracle sp for extraction data in Data Flow Task. Could anybody suggest if it how we could do it in SSIS?

All suggestions will be highly appreciated.

Thanks,

Lalit

You should be able to do this. Is there a specific problem you are encountering?|||There is no variable type in SSIS that maps to refcursor type in Oracle. I don't think a variable of type object can be used for this either. So I guess you cannot execute Oracle SP using Execute SQL task to get the recordset.

How to get Read only control value through SQl data Source

Hello,

I am using this sql data source control.

<asp:textboxrunat="server"id="txtFromDate" ReadOnly="true"></></asp:textbox>

<asp:textboxrunat="server"id="txtToDate" ReadOnly="true"></asp:textbox>

---------->

<asp:sqldatasourceid="dsClickInfo"runat="server"connectionstring="<%$ ConnectionStrings:activeConnectionString %>"

selectcommand="SProc_GetTransaction"selectcommandtype="StoredProcedure"><SelectParameters><asp:QueryStringParameterQueryStringField="Id"Name="MerchantID"Type="int32"/><asp:ControlParameterControlID="txtFromDate"Name="StartDate"PropertyName="Text"Type="String"DefaultValue="0"/><asp:ControlParameterControlID="txtToDate"Name="EndDate"PropertyName="Text"Type="String"DefaultValue="0"/></SelectParameters>

</asp:sqldatasource>

I abouve code txtFromDate and txtToDate are marked as readonly so sqldatasource is not able to get value from these controls.

How is it possible?

Please help me..

Hi Welcome to asp.net

I don't know the way you get value from these controls.

When I bind a textbox to field I'd like to set Enabled to false instead of the ReadOnly ,you can have a try,

<asp:TextBox ID="TextBox1" runat="server" Enabled=false Text='<%# Bind("contact_firstno") %>'></asp:TextBox>

|||

Thanks for reply..

But my problem is different..

I dont want to bind the text box. I am getting the text box value in sqldatasource control by the property

<SelectParameters>

<asp:QueryStringParameterQueryStringField="Id"Name="MerchantID"Type="int32"/>

<asp:ControlParameterControlID="txtFromDate"Name="StartDate"PropertyName="Text"

Type="String"DefaultValue="0"/>

<asp:ControlParameterControlID="txtToDate"Name="EndDate"PropertyName="Text"

Type="String"DefaultValue="0"/>

</SelectParameters>

and after that these values are passing to the DB (by the select command) for search a result according to the from date and to date.

Now if i am not making thetxtFromDate and txtToDatereadonly then getting proper result.

But if these are readonly then after click on the search button i am getting these controls values empty and dont get any changes in result according to search date.

Thanks

Rahul Panwar

|||

Sorry I misunderstood you.

Make sure Text in txtFromDate and txtToDate are only modified at ther server side (not by javascript...)

And debug your webapp to make sure the textbox value is right when you click search button .

And also make sure yourenableviewstateisnot set to false,like this:

<%@.Pagelanguage="c#"enableviewstate="false"Trace="true"%>

Hope it helps.

How to Get Error Output from and OLE DB Command Destination

I have a data flow that takes an OLE DB Source, transforms it and then uses an OLE DB Command as a destination. The OLE DB Command executes a call to a stored procedure and I have the proper wild cards indicated. The entire process runs great and does exactly what is intended to do.

However, I need to know when a SQL insert fails what record failed and I need to log this in a file somewhere. I added a Flat File Destination object and configured appropriately. I created 3 column names for the headers in the flat file and matched them with column names existing for output. When I run this package the flat file log is created ok, but no data is ever pumped into the file when a failure of the OLE DB Command occurs.

I checked the Advanced Editor for the OLE DB Command object and under the OLE DB Command Error Output node on the Input and Output Properties tab I notice that the ErrorCode and ErrorColumn output columns both have ErrorRowDisposition set to RD_NotUsed. I would guess this is the problem and why no data is written to my log file, but I cannot figure out how to get this changed (fields are greyed out so no access).

Any help would be greatly appreciated.

To get rows down the error output you change the ErrorRowDisposition property for the input to be redirect row. Have you done this? If not go the last page of the Advanced Editor, select the Input, and change the ErrorRowDisposition property.|||

I reviewed your suggestion of changing the ErrorRowDisposition value to RD_RedirectRow and that is where the issue is. I view the Advanced Editor for the OLE DB Command destination object and expand the Input Columns under OLE DB Command Input and see several input columns. However, the problem is every one of those columns has an ErrorRowDisposition=RD_NotUsed and the field is greyed out so I am unable to change the setting. Would I need to change any settings in the source or data conversion objects to allow these values to be editable?

|||Select the input and stop there, don't expand the columns. The setting is on the input which is in effect the parent for the columns.