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 first 10 max salaries of employees out of say 100 employees

hi,
i got a small pbrobs
i want to get first 10 max salaries out of 100 employees from my_tbl(ex)
i tried by writing subqueries..but i need a simple query to get itHow about posting your DDL, plus INSERT statements of sample data plus
expected results?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"joshithay via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:542E98E7F30FA@.webservertalk.com...
hi,
i got a small pbrobs
i want to get first 10 max salaries out of 100 employees from my_tbl(ex)
i tried by writing subqueries..but i need a simple query to get it

How to get the current date in C# and how do I pass this date to SQL Server

I am writing a ASP.NET C# web application. I will need to store the date in one field in one of my tables. It appears that I need to use the datetime data type for the date in SQL Server 2005.

So I have a question

1.) How do I get today's date in C# and how should this be passed to SQL server?

you can get the current date directly in SQL using getdate()|||Doing it in C# will return you the client system date. Getting the current date in SQL will ensure all datetime recorded in your application are in sync. If you are using the date for some sort of comparison determine the sequence of event, using the date from the client might pose some timing issue. Unless what you want is really the client's local time, you should use the Server time.|||

brgdotnet:

I am writing a ASP.NET C# web application. I will need to store the date in one field in one of my tables. It appears that I need to use the datetime data type for the date in SQL Server 2005.

So I have a question

1.) How do I get today's date in C# and how should this be passed to SQL server?

to get current date in c# use DateTime.Now.ToString()......... there is also some different methods under Now... use what is appropriate for u .

if u r using a procedure it is easier.... say proc. name saveCurrentDate

String s=DateTime.Now.ToString();

String queryString="saveCurrentDate '"+s+"'"; //// or String queryString="insert int myTable(mydate) values ('"+s+"')";

hope it will hellped u

|||

Actually I am processing some records from a comma delimited .txt file. Each item seperated by a comma, will map to a field in a SQL Server database table.

The date is in the format: 20070423, that is YYMMDD

So I need to take the date value and then write it into SQL server. So this brings us to another question. Do I need to change this date format to comply with that of SQL server? If so what format does it need to be in? I need some specifics so if you could even produce a code sample for C#, that would be great.

|||You can pass in the date in format YYYYMMDD as a string. SQL Server will implicitly convert it to string. As long as you are using Universal format YYYYMMDD, it is fine. Other format like MMDDYYYY or DDMMYYYY will be depending on the language setting that you used. So stick to YYYYMMDD and you will not go wrong.|||

I am confused about one thing though? I am reading from a text file where the format is in YYMMDD. Should I store this in SQL server as a data type of datetime or smalldatetime?

If so, I definitely have a date that, I can read from the text file, but I don't have a time? What do you think? If I did use datetime, would it just append the current time to the date I entered?

|||

you should always use proper data type for the data. In this case, you should use datetime or smalldatetime to store the date.

"I can read from the text file, but I don't have a time?"
You can still use datetime data type. Just set the time to 00:00:00. For your case, as your date string is in YYYYMMDD format without time, when you insert into table, the time will be stored as 00:00:00

How to get last record ?

Dear all,
I am writing a store procedure which should return the last entry whcih has
been written in a database table.
That store procedure will always return the last recorded entry from a table
How to do that ?
regards
sergeHi Serge,
What is the "last" record in your case ?
SQL Server doesn=B4t care about the order of data unless you specify an
Order in a Select query or you can determine the last entry via an
identity column or a column which store the modified date.
HTH, Jens Suessmeyer.|||You need to add datetime column and insert the value when you add a record.
Then you can select the most recent record in the table.
something like
select top 1 <column list>
from table
order by Time_inserted DESC
Thats offcourse if you mean 'last' by time of insert. If you want to handle
updates ('LastChanged') read about timestamp in the BOL.
MC
"serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
message news:EB64CB5C-40D0-42E2-A56B-31205AB24C7D@.microsoft.com...
> Dear all,
> I am writing a store procedure which should return the last entry whcih
> has
> been written in a database table.
> That store procedure will always return the last recorded entry from a
> table
> How to do that ?
> regards
> serge|||If you don't record the information about which row was the last then
SQL Server won't do it for you. The answer is something like:
SELECT ,,,
FROM your_table
WHERE created_date =
(SELECT MAX(created_date)
FROM your_table) ;
David Portas
SQL Server MVP
--|||Hi Jens,
nased on that, I have a column in my database table Identified as START_TIME
which is of type DataTime.
Based on that column, the "last" record will be the one who have the biggest
DateTIme value.
any tip ?
"Jens" wrote:

> Hi Serge,
> What is the "last" record in your case ?
> SQL Server doesn′t care about the order of data unless you specify an
> Order in a Select query or you can determine the last entry via an
> identity column or a column which store the modified date.
> HTH, Jens Suessmeyer.
>|||Thnaks it works
"MC" wrote:

> You need to add datetime column and insert the value when you add a record
.
> Then you can select the most recent record in the table.
> something like
> select top 1 <column list>
> from table
> order by Time_inserted DESC
> Thats offcourse if you mean 'last' by time of insert. If you want to handl
e
> updates ('LastChanged') read about timestamp in the BOL.
> MC
>
> "serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
> message news:EB64CB5C-40D0-42E2-A56B-31205AB24C7D@.microsoft.com...
>
>