Description for Configuration Values

Hi,
From where can I get the description for the Config Values for both SQL
2000 and SQL 2005
Similar to the one that SQL-DMO returns when queried for the description
of a particular configuration values, is it stored in some system tables.
The comment field gives very little information about the configuration
value as compared to the description given by SQL-DMO.
Also, the description field value in SQL 2005 is not the same when
queried through SQL-DMO.
e.g. In the table sys.configurations the config value "affinity mask"
has description "affinity mask"
but when queried thru SQL-DMO it gives the following
"Indicates which processors SQL Server may use (default is 0, or any).
A non-zero value is interpreted as a bit mask; e.g, processors 1, 2, and 5
are specified with a hexadecimal value of 0x13 or the decimal equivalent
of 19."
TIA
PraHi, Pra
It seems that these descriptions are stored in the SQLDMO.RLL file, so
I see no easy way of retrieiving them from T-SQL (since these
descriptions are not stored in any system table).
Razvan|||Thanks Razvan
Can u tell me one more thing how can I relate the config value with the
message.
Thanks
Pra
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1139988679.719312.27840@.g47g2000cwa.googlegroups.com...
> Hi, Pra
> It seems that these descriptions are stored in the SQLDMO.RLL file, so
> I see no easy way of retrieiving them from T-SQL (since these
> descriptions are not stored in any system table).
> Razvan
>|||Hi, Pra
You can use SQL-DMO to iterate through the each of the values in the
ConfigValues collection to get the Name and Description properties and
store them in your own table.
You may also want to take a look at:
http://msdn2.microsoft.com/en-us/library/ms131974.aspx
[url]http://msdn.microsoft.com/library/en-us/sqldmo/dmoref_cnst02_2w9x.asp?frame=true[/
url]
to check if you have found all the documented configuration values in
the ConfigValues collection.
Razvan|||Thanks
But I cant do that. Any other way.
Pra
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1140002871.149041.272720@.z14g2000cwz.googlegroups.com...
> Hi, Pra
> You can use SQL-DMO to iterate through the each of the values in the
> ConfigValues collection to get the Name and Description properties and
> store them in your own table.
> You may also want to take a look at:
> http://msdn2.microsoft.com/en-us/library/ms131974.aspx
> http://msdn.microsoft.com/library/e...me=true

> to check if you have found all the documented configuration values in
> the ConfigValues collection.
> Razvan
>|||Hi, Pra
Please tell me the reason why you can't do that, so I can think of
another way that works (not another way that you can't do, either). You
can also state the overall scenario of what you are trying to do with
the description and why you need this description instead of the
description from the sys.configurations table.
In the mean time, I would say that you can invoke the SQL-DMO code from
T-SQL using the sp_OACreate / sp_SetProperty / sp_OAGetProperty
procedures. This is the "non-easy" way that I thought when I wrote the
first message.
Razvan|||Thanks Razvan
I never knew about OLE prog. using T-SQL.
See the scenario is previously we used the SQL-DMO to report on SQL
Servers.
Now with Yukon coming into picture and SQL-DMO not fully compatible with
Yukon. I have to change the implementation to ADO. I can't use SMO bcoz
there is some code which is already written in ADO so I want reuse that, so
I was thinking of getting these values from the tables/system tables/views
anything but DMO and SMO. So, I'm hunting for description other
values I got throught the sysconfigures, syscurconfigs, spt_values, only
description I couldn't get.
I'm stuck only bcoz of this field.
Thanks
Pra
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1140007841.077219.207640@.g44g2000cwa.googlegroups.com...
> Hi, Pra
> Please tell me the reason why you can't do that, so I can think of
> another way that works (not another way that you can't do, either). You
> can also state the overall scenario of what you are trying to do with
> the description and why you need this description instead of the
> description from the sys.configurations table.
> In the mean time, I would say that you can invoke the SQL-DMO code from
> T-SQL using the sp_OACreate / sp_SetProperty / sp_OAGetProperty
> procedures. This is the "non-easy" way that I thought when I wrote the
> first message.
> Razvan
>|||Hi, Pra
What do you mean by "SQL-DMO not fully compatible with Yukon" ? As far
as I know, everything that you can do using SQL-DMO against a SQL
Server 2000 server, you can also do using SQL-DMO against a SQL Server
2005 server, as long as the updated SQL-DMO components are installed
(by default they are not).
If you need to use the new features of SQL Server 2005, you should use
SMO instead of SQL-DMO (especially if you are writing a .Net
application). SMO supports SQL Server 2000, too.
If you really don't want to use SQL-DMO or SMO, you can store the
descriptions in your application: retrieve them using SQL-DMO (only
once, using a separate little app) then store them as constants (or
something else) in your code.
Razvan|||Thanks Razvan
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1140015110.381400.268180@.g43g2000cwa.googlegroups.com...
> Hi, Pra
> What do you mean by "SQL-DMO not fully compatible with Yukon" ? As far
> as I know, everything that you can do using SQL-DMO against a SQL
> Server 2000 server, you can also do using SQL-DMO against a SQL Server
> 2005 server, as long as the updated SQL-DMO components are installed
> (by default they are not).
> If you need to use the new features of SQL Server 2005, you should use
> SMO instead of SQL-DMO (especially if you are writing a .Net
> application). SMO supports SQL Server 2000, too.
> If you really don't want to use SQL-DMO or SMO, you can store the
> descriptions in your application: retrieve them using SQL-DMO (only
> once, using a separate little app) then store them as constants (or
> something else) in your code.
> Razvan
>

Description for Configuration Values

Hi,
From where can I get the description for the Config Values for both SQL
2000 and SQL 2005
Similar to the one that SQL-DMO returns when queried for the description
of a particular configuration values, is it stored in some system tables.
The comment field gives very little information about the configuration
value as compared to the description given by SQL-DMO.
Also, the description field value in SQL 2005 is not the same when
queried through SQL-DMO.
e.g. In the table sys.configurations the config value "affinity mask"
has description "affinity mask"
but when queried thru SQL-DMO it gives the following
"Indicates which processors SQL Server may use (default is 0, or any).
A non-zero value is interpreted as a bit mask; e.g, processors 1, 2, and 5
are specified with a hexadecimal value of 0x13 or the decimal equivalent
of 19."
TIA
PrasadHi, Prasad
It seems that these descriptions are stored in the SQLDMO.RLL file, so
I see no easy way of retrieiving them from T-SQL (since these
descriptions are not stored in any system table).
Razvan|||Thanks Razvan
Can u tell me one more thing how can I relate the config value with the
message.
Thanks
Prasad
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1139988679.719312.27840@.g47g2000cwa.googlegroups.com...
> Hi, Prasad
> It seems that these descriptions are stored in the SQLDMO.RLL file, so
> I see no easy way of retrieiving them from T-SQL (since these
> descriptions are not stored in any system table).
> Razvan
>|||Hi, Prasad
You can use SQL-DMO to iterate through the each of the values in the
ConfigValues collection to get the Name and Description properties and
store them in your own table.
You may also want to take a look at:
http://msdn2.microsoft.com/en-us/library/ms131974.aspx
[url]http://msdn.microsoft.com/library/en-us/sqldmo/dmoref_cnst02_2w9x.asp?frame=true[/
url]
to check if you have found all the documented configuration values in
the ConfigValues collection.
Razvan|||Thanks
But I cant do that. Any other way.
Prasad
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1140002871.149041.272720@.z14g2000cwz.googlegroups.com...
> Hi, Prasad
> You can use SQL-DMO to iterate through the each of the values in the
> ConfigValues collection to get the Name and Description properties and
> store them in your own table.
> You may also want to take a look at:
> http://msdn2.microsoft.com/en-us/library/ms131974.aspx
> http://msdn.microsoft.com/library/e...me=true

> to check if you have found all the documented configuration values in
> the ConfigValues collection.
> Razvan
>|||Hi, Prasad
Please tell me the reason why you can't do that, so I can think of
another way that works (not another way that you can't do, either). You
can also state the overall scenario of what you are trying to do with
the description and why you need this description instead of the
description from the sys.configurations table.
In the mean time, I would say that you can invoke the SQL-DMO code from
T-SQL using the sp_OACreate / sp_SetProperty / sp_OAGetProperty
procedures. This is the "non-easy" way that I thought when I wrote the
first message.
Razvan|||Thanks Razvan
I never knew about OLE prog. using T-SQL.
See the scenario is previously we used the SQL-DMO to report on SQL
Servers.
Now with Yukon coming into picture and SQL-DMO not fully compatible with
Yukon. I have to change the implementation to ADO. I can't use SMO bcoz
there is some code which is already written in ADO so I want reuse that, so
I was thinking of getting these values from the tables/system tables/views
anything but DMO and SMO. So, I'm hunting for description other
values I got throught the sysconfigures, syscurconfigs, spt_values, only
description I couldn't get.
I'm stuck only bcoz of this field.
Thanks
Prasad
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1140007841.077219.207640@.g44g2000cwa.googlegroups.com...
> Hi, Prasad
> Please tell me the reason why you can't do that, so I can think of
> another way that works (not another way that you can't do, either). You
> can also state the overall scenario of what you are trying to do with
> the description and why you need this description instead of the
> description from the sys.configurations table.
> In the mean time, I would say that you can invoke the SQL-DMO code from
> T-SQL using the sp_OACreate / sp_SetProperty / sp_OAGetProperty
> procedures. This is the "non-easy" way that I thought when I wrote the
> first message.
> Razvan
>|||Hi, Prasad
What do you mean by "SQL-DMO not fully compatible with Yukon" ? As far
as I know, everything that you can do using SQL-DMO against a SQL
Server 2000 server, you can also do using SQL-DMO against a SQL Server
2005 server, as long as the updated SQL-DMO components are installed
(by default they are not).
If you need to use the new features of SQL Server 2005, you should use
SMO instead of SQL-DMO (especially if you are writing a .Net
application). SMO supports SQL Server 2000, too.
If you really don't want to use SQL-DMO or SMO, you can store the
descriptions in your application: retrieve them using SQL-DMO (only
once, using a separate little app) then store them as constants (or
something else) in your code.
Razvan|||Thanks Razvan
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1140015110.381400.268180@.g43g2000cwa.googlegroups.com...
> Hi, Prasad
> What do you mean by "SQL-DMO not fully compatible with Yukon" ? As far
> as I know, everything that you can do using SQL-DMO against a SQL
> Server 2000 server, you can also do using SQL-DMO against a SQL Server
> 2005 server, as long as the updated SQL-DMO components are installed
> (by default they are not).
> If you need to use the new features of SQL Server 2005, you should use
> SMO instead of SQL-DMO (especially if you are writing a .Net
> application). SMO supports SQL Server 2000, too.
> If you really don't want to use SQL-DMO or SMO, you can store the
> descriptions in your application: retrieve them using SQL-DMO (only
> once, using a separate little app) then store them as constants (or
> something else) in your code.
> Razvan
>

Description for Configuration Values

Hi,
From where can I get the description for the Config Values for both SQL
2000 and SQL 2005
Similar to the one that SQL-DMO returns when queried for the description
of a particular configuration values, is it stored in some system tables.
The comment field gives very little information about the configuration
value as compared to the description given by SQL-DMO.
Also, the description field value in SQL 2005 is not the same when
queried through SQL-DMO.
e.g. In the table sys.configurations the config value "affinity mask"
has description "affinity mask"
but when queried thru SQL-DMO it gives the following
"Indicates which processors SQL Server may use (default is 0, or any).
A non-zero value is interpreted as a bit mask; e.g, processors 1, 2, and 5
are specified with a hexadecimal value of 0x13 or the decimal equivalent
of 19."
TIA
Prasad
Hi, Prasad
It seems that these descriptions are stored in the SQLDMO.RLL file, so
I see no easy way of retrieiving them from T-SQL (since these
descriptions are not stored in any system table).
Razvan
|||Thanks Razvan
Can u tell me one more thing how can I relate the config value with the
message.
Thanks
Prasad
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1139988679.719312.27840@.g47g2000cwa.googlegro ups.com...
> Hi, Prasad
> It seems that these descriptions are stored in the SQLDMO.RLL file, so
> I see no easy way of retrieiving them from T-SQL (since these
> descriptions are not stored in any system table).
> Razvan
>
|||Hi, Prasad
You can use SQL-DMO to iterate through the each of the values in the
ConfigValues collection to get the Name and Description properties and
store them in your own table.
You may also want to take a look at:
http://msdn2.microsoft.com/en-us/library/ms131974.aspx
http://msdn.microsoft.com/library/en...asp?frame=true
to check if you have found all the documented configuration values in
the ConfigValues collection.
Razvan
|||Thanks
But I cant do that. Any other way.
Prasad
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1140002871.149041.272720@.z14g2000cwz.googlegr oups.com...
> Hi, Prasad
> You can use SQL-DMO to iterate through the each of the values in the
> ConfigValues collection to get the Name and Description properties and
> store them in your own table.
> You may also want to take a look at:
> http://msdn2.microsoft.com/en-us/library/ms131974.aspx
> http://msdn.microsoft.com/library/en...asp?frame=true
> to check if you have found all the documented configuration values in
> the ConfigValues collection.
> Razvan
>
|||Hi, Prasad
Please tell me the reason why you can't do that, so I can think of
another way that works (not another way that you can't do, either). You
can also state the overall scenario of what you are trying to do with
the description and why you need this description instead of the
description from the sys.configurations table.
In the mean time, I would say that you can invoke the SQL-DMO code from
T-SQL using the sp_OACreate / sp_SetProperty / sp_OAGetProperty
procedures. This is the "non-easy" way that I thought when I wrote the
first message.
Razvan
|||Thanks Razvan
I never knew about OLE prog. using T-SQL.
See the scenario is previously we used the SQL-DMO to report on SQL
Servers.
Now with Yukon coming into picture and SQL-DMO not fully compatible with
Yukon. I have to change the implementation to ADO. I can't use SMO bcoz
there is some code which is already written in ADO so I want reuse that, so
I was thinking of getting these values from the tables/system tables/views
anything but DMO and SMO. So, I'm hunting for description other
values I got throught the sysconfigures, syscurconfigs, spt_values, only
description I couldn't get.
I'm stuck only bcoz of this field.
Thanks
Prasad
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1140007841.077219.207640@.g44g2000cwa.googlegr oups.com...
> Hi, Prasad
> Please tell me the reason why you can't do that, so I can think of
> another way that works (not another way that you can't do, either). You
> can also state the overall scenario of what you are trying to do with
> the description and why you need this description instead of the
> description from the sys.configurations table.
> In the mean time, I would say that you can invoke the SQL-DMO code from
> T-SQL using the sp_OACreate / sp_SetProperty / sp_OAGetProperty
> procedures. This is the "non-easy" way that I thought when I wrote the
> first message.
> Razvan
>
|||Hi, Prasad
What do you mean by "SQL-DMO not fully compatible with Yukon" ? As far
as I know, everything that you can do using SQL-DMO against a SQL
Server 2000 server, you can also do using SQL-DMO against a SQL Server
2005 server, as long as the updated SQL-DMO components are installed
(by default they are not).
If you need to use the new features of SQL Server 2005, you should use
SMO instead of SQL-DMO (especially if you are writing a .Net
application). SMO supports SQL Server 2000, too.
If you really don't want to use SQL-DMO or SMO, you can store the
descriptions in your application: retrieve them using SQL-DMO (only
once, using a separate little app) then store them as constants (or
something else) in your code.
Razvan
|||Thanks Razvan
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1140015110.381400.268180@.g43g2000cwa.googlegr oups.com...
> Hi, Prasad
> What do you mean by "SQL-DMO not fully compatible with Yukon" ? As far
> as I know, everything that you can do using SQL-DMO against a SQL
> Server 2000 server, you can also do using SQL-DMO against a SQL Server
> 2005 server, as long as the updated SQL-DMO components are installed
> (by default they are not).
> If you need to use the new features of SQL Server 2005, you should use
> SMO instead of SQL-DMO (especially if you are writing a .Net
> application). SMO supports SQL Server 2000, too.
> If you really don't want to use SQL-DMO or SMO, you can store the
> descriptions in your application: retrieve them using SQL-DMO (only
> once, using a separate little app) then store them as constants (or
> something else) in your code.
> Razvan
>
sql

Description for Configuration Values

Hi,
From where can I get the description for the Config Values for both SQL
2000 and SQL 2005
Similar to the one that SQL-DMO returns when queried for the description
of a particular configuration values, is it stored in some system tables.
The comment field gives very little information about the configuration
value as compared to the description given by SQL-DMO.
Also, the description field value in SQL 2005 is not the same when
queried through SQL-DMO.
e.g. In the table sys.configurations the config value "affinity mask"
has description "affinity mask"
but when queried thru SQL-DMO it gives the following
"Indicates which processors SQL Server may use (default is 0, or any).
A non-zero value is interpreted as a bit mask; e.g, processors 1, 2, and 5
are specified with a hexadecimal value of 0x13 or the decimal equivalent
of 19."
TIA
PrasadHi, Prasad
It seems that these descriptions are stored in the SQLDMO.RLL file, so
I see no easy way of retrieiving them from T-SQL (since these
descriptions are not stored in any system table).
Razvan|||Thanks Razvan
Can u tell me one more thing how can I relate the config value with the
message.
Thanks
Prasad
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1139988679.719312.27840@.g47g2000cwa.googlegroups.com...
> Hi, Prasad
> It seems that these descriptions are stored in the SQLDMO.RLL file, so
> I see no easy way of retrieiving them from T-SQL (since these
> descriptions are not stored in any system table).
> Razvan
>|||Hi, Prasad
You can use SQL-DMO to iterate through the each of the values in the
ConfigValues collection to get the Name and Description properties and
store them in your own table.
You may also want to take a look at:
http://msdn2.microsoft.com/en-us/library/ms131974.aspx
http://msdn.microsoft.com/library/en-us/sqldmo/dmoref_cnst02_2w9x.asp?frame=true
to check if you have found all the documented configuration values in
the ConfigValues collection.
Razvan|||Thanks
But I cant do that. Any other way.
Prasad
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1140002871.149041.272720@.z14g2000cwz.googlegroups.com...
> Hi, Prasad
> You can use SQL-DMO to iterate through the each of the values in the
> ConfigValues collection to get the Name and Description properties and
> store them in your own table.
> You may also want to take a look at:
> http://msdn2.microsoft.com/en-us/library/ms131974.aspx
> http://msdn.microsoft.com/library/en-us/sqldmo/dmoref_cnst02_2w9x.asp?frame=true
> to check if you have found all the documented configuration values in
> the ConfigValues collection.
> Razvan
>|||Hi, Prasad
Please tell me the reason why you can't do that, so I can think of
another way that works (not another way that you can't do, either). You
can also state the overall scenario of what you are trying to do with
the description and why you need this description instead of the
description from the sys.configurations table.
In the mean time, I would say that you can invoke the SQL-DMO code from
T-SQL using the sp_OACreate / sp_SetProperty / sp_OAGetProperty
procedures. This is the "non-easy" way that I thought when I wrote the
first message.
Razvan|||Thanks Razvan
I never knew about OLE prog. using T-SQL.
See the scenario is previously we used the SQL-DMO to report on SQL
Servers.
Now with Yukon coming into picture and SQL-DMO not fully compatible with
Yukon. I have to change the implementation to ADO. I can't use SMO bcoz
there is some code which is already written in ADO so I want reuse that, so
I was thinking of getting these values from the tables/system tables/views
anything but DMO and SMO. So, I'm hunting for description other
values I got throught the sysconfigures, syscurconfigs, spt_values, only
description I couldn't get.
I'm stuck only bcoz of this field.
Thanks
Prasad
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1140007841.077219.207640@.g44g2000cwa.googlegroups.com...
> Hi, Prasad
> Please tell me the reason why you can't do that, so I can think of
> another way that works (not another way that you can't do, either). You
> can also state the overall scenario of what you are trying to do with
> the description and why you need this description instead of the
> description from the sys.configurations table.
> In the mean time, I would say that you can invoke the SQL-DMO code from
> T-SQL using the sp_OACreate / sp_SetProperty / sp_OAGetProperty
> procedures. This is the "non-easy" way that I thought when I wrote the
> first message.
> Razvan
>|||Hi, Prasad
What do you mean by "SQL-DMO not fully compatible with Yukon" ? As far
as I know, everything that you can do using SQL-DMO against a SQL
Server 2000 server, you can also do using SQL-DMO against a SQL Server
2005 server, as long as the updated SQL-DMO components are installed
(by default they are not).
If you need to use the new features of SQL Server 2005, you should use
SMO instead of SQL-DMO (especially if you are writing a .Net
application). SMO supports SQL Server 2000, too.
If you really don't want to use SQL-DMO or SMO, you can store the
descriptions in your application: retrieve them using SQL-DMO (only
once, using a separate little app) then store them as constants (or
something else) in your code.
Razvan|||Thanks Razvan
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1140015110.381400.268180@.g43g2000cwa.googlegroups.com...
> Hi, Prasad
> What do you mean by "SQL-DMO not fully compatible with Yukon" ? As far
> as I know, everything that you can do using SQL-DMO against a SQL
> Server 2000 server, you can also do using SQL-DMO against a SQL Server
> 2005 server, as long as the updated SQL-DMO components are installed
> (by default they are not).
> If you need to use the new features of SQL Server 2005, you should use
> SMO instead of SQL-DMO (especially if you are writing a .Net
> application). SMO supports SQL Server 2000, too.
> If you really don't want to use SQL-DMO or SMO, you can store the
> descriptions in your application: retrieve them using SQL-DMO (only
> once, using a separate little app) then store them as constants (or
> something else) in your code.
> Razvan
>

Desapering rows

Hi.

I have an mdx query which return some rows and for some values there are null values for 1 column (it's ok). But when the report is rendered have disapeared all these rows. I need to show these rows because they have value for other columns.

Thanks in advance.I refresh the datasource by editing the query and refresh and that's all.

I don't know what happend.

derived column transform (flat file blanks to 0)

Hi,

Is it possible using derived column transform to change all blank values in a flat file to say a "0"

Basically convert "" to "0"

Thanks for any help,

Slash.

Hi,

I think you can generate your derived column like this :

([source_column] == "") ? "0" : [source_column]

Arno.

|||

Top man,

Worked a treat,

Thanks again,

Slash.

Derived Column Calculation

Example: (47 / 204709) * 12000 = 2.754

Both values (47 & 204709) are of data type Int.

SQL Sever - Derived Column Calculation returns 2.74

The destination data type is Float

I have converted the data types to Float, Decimal & Numeric and still got the same answer.

Any suggestions

Have you casted the result of the calculation to a float?

-Jamie

|||

Yes, I have what I have noticed with the Data Convertion component is that the input and output data types stay the same. Should it not change to the new data type.

|||

Can you post your expression up here?

-Jamie

|||

Michael Exall wrote:

Yes, I have what I have noticed with the Data Convertion component is that the input and output data types stay the same. Should it not change to the new data type.

No. If you were changing the data-type of a column then inherently you are changing the "shape" of the memory buffer and therefore this would need to be an asynchronous component.

A design choice was obviously made to keep data conversion component synchronous for perf reasons.

If you're puzzled as to the difference between synch and asynch...google it http://www.google.co.uk/search?hl=en&q=ssis+synchronous+asynchronous&meta=

-Jamie

Derived column based on result of Oracle query

Hi,

I need to create a derived column for each row in a SQL dataset.

This derived column needs to be created by passing across two values from the SQL dataset and querying an Oracle table based on those parameters. If the Oracle query returns a record(s) then the derived column should be set to 1 otherwise leave it as default (0).

One of these parameters needs to check a date range so I can't use a Lookup Transformation...any ideas how I can accomplish this ?

Thanks

Here is one way to do this...

-Have one source for the sql source and another source for the oracle source.

-Send them both to a merge join, choose a left outer join with the sql source as the left input, choose one column from the oracle source.

-After the merge join use a derived column transformation. Use an expression to say that if the field from the oracle source is null then 0 else 1.

edit: After re-reading your post, the date range makes things much more difficult. It may be best to send both sources to staging tables in sql server, and then write run a query to join them together perhaps a (correlated subquery with a case statement in the select clause).

|||

Hi,

I am not sure if this will solve your problem completely:

In a Data Flow Task pull the two values from the SQL dataset into the Recordset Destination.

Now, in the Control Flow Task, use a For Each Loop Container to store the Recordset into a variable (you will have to create variables for each field that you want to write to the destination as well as one for the recordset).

Loop through the recordset and for each pair of values, use a Excecute SQL Task to execute a SQL query or a Stored Procedure which will return matching record(s). Now based on whether a record was returned or not, write all the fields, plus the derived column value into the destination.

Regards,

B@.ns

|||

Thanks guys - I actually used a combination of your techniques to get the result.

|||I just wanted to point out that you can use a Lookup to do this. You just need to go the the advanced tab, enable memory restriction, and you can alter the query used for the lookup to include ranges.sql

derive listing of values NOT in table

I have 3 tables..
- unique listing of all employees...
- unique listing of all dates where transactions took place
- detail listing of all transactions
The detail listing of all transactions has a reference to the employee to
whom the transaction belongs as well as the date of the transaction.
I need to come up with a list of employees who did NOT have a transaction
for each date.
My initial thought was to create joins where I would get a listing of each
date along with all employees... then those employees who did not have a
transaction on a given date would showup as a null... for example:
Employees:
pete
david
joe
Dates:
10/5
10/6
10/7
Transactions
10/5 pete shoes
10/6 pete belt
10/6 david shoes
10/7 pete hat
10/7 david pants
I want to join those 3 tables so that I get this outcome:
Date Employee Transaction
10/5 pete shoes
10/5 david <null>
10/5 joe <null>
10/6 pete belt
10/6 david shoes
10/6 joe <null>
10/7 pete hat
10/7 david pants
10/7 joe <null>
I've done this type of thing before... but for some reason it's not working
for me. The outcome is not displaying the <null> rows.. only those rows
which contain a transaction.
Thanks.First Cross join the Employee table and the Dates table to get the full rang
e
of possible values. Then outer join to the Transactions table to get
yourtransactions.
SELECT d.Dates, e.EmpName, t.Trans
FROM Dates d
CROSS JOIN Emp e
LEFT JOIN Trans t ON d.Dates = t.Dates AND e.EmpName = t.EmpName
(Obviously I made up column names and table names :P )
HTH,
John Scragg
"David Sampson" wrote:

> I have 3 tables..
> - unique listing of all employees...
> - unique listing of all dates where transactions took place
> - detail listing of all transactions
> The detail listing of all transactions has a reference to the employee to
> whom the transaction belongs as well as the date of the transaction.
>
> I need to come up with a list of employees who did NOT have a transaction
> for each date.
>
> My initial thought was to create joins where I would get a listing of each
> date along with all employees... then those employees who did not have a
> transaction on a given date would showup as a null... for example:
>
> Employees:
> pete
> david
> joe
> Dates:
> 10/5
> 10/6
> 10/7
> Transactions
> 10/5 pete shoes
> 10/6 pete belt
> 10/6 david shoes
> 10/7 pete hat
> 10/7 david pants
>
> I want to join those 3 tables so that I get this outcome:
> Date Employee Transaction
> 10/5 pete shoes
> 10/5 david <null>
> 10/5 joe <null>
> 10/6 pete belt
> 10/6 david shoes
> 10/6 joe <null>
> 10/7 pete hat
> 10/7 david pants
> 10/7 joe <null>
> I've done this type of thing before... but for some reason it's not workin
g
> for me. The outcome is not displaying the <null> rows.. only those rows
> which contain a transaction.
> Thanks.
>
>|||Please post the offending query. I bet there's something in it tha diminishe
s
the effects of outer joins - that is if you use them at all. Can't tell unti
l
we see the query. :)
ML|||I've never heard of a CROSS join... I'll use that and see what it does for
me.
thanks
"John Scragg" <JohnScragg@.discussions.microsoft.com> wrote in message
news:AFDD7347-E6D1-4C85-99FA-470F0772B468@.microsoft.com...
> First Cross join the Employee table and the Dates table to get the full
> range
> of possible values. Then outer join to the Transactions table to get
> yourtransactions.
> SELECT d.Dates, e.EmpName, t.Trans
> FROM Dates d
> CROSS JOIN Emp e
> LEFT JOIN Trans t ON d.Dates = t.Dates AND e.EmpName = t.EmpName
> (Obviously I made up column names and table names :P )
> HTH,
> John Scragg
> "David Sampson" wrote:
>|||That is EXACTLY what I needed!!!!!
Thanks alot!!!!
David
"John Scragg" <JohnScragg@.discussions.microsoft.com> wrote in message
news:AFDD7347-E6D1-4C85-99FA-470F0772B468@.microsoft.com...
> First Cross join the Employee table and the Dates table to get the full
> range
> of possible values. Then outer join to the Transactions table to get
> yourtransactions.
> SELECT d.Dates, e.EmpName, t.Trans
> FROM Dates d
> CROSS JOIN Emp e
> LEFT JOIN Trans t ON d.Dates = t.Dates AND e.EmpName = t.EmpName
> (Obviously I made up column names and table names :P )
> HTH,
> John Scragg
> "David Sampson" wrote:
>|||I'm not making it up :P
A CROSS join is the cartesian product of two tables. So if you have 3 rows
in each table. A CROSS join will give you 9 rows. Every possible
combination.
It is the same as saying
SELECT d.Dates, e.EmpName
FROM Dates d, Employees e
Notice that there is no where clause. Same as there is no "ON" clause in my
example posted earlier. I just prefer the clarity of describing my join
types over the method show above.
Best of luck,
John
"David Sampson" wrote:

> I've never heard of a CROSS join... I'll use that and see what it does for
> me.
> thanks
>
> "John Scragg" <JohnScragg@.discussions.microsoft.com> wrote in message
> news:AFDD7347-E6D1-4C85-99FA-470F0772B468@.microsoft.com...
>
>sql

Deployment + XML Config file problem

Hello

I am using XML config file to store the values e.g. Server name.... it runs fine with BIDS (Development enviroment)... but when I deploy and run it to test that XML config files values are being read by e.g. giving the wrong server name, it runs fine (may be retrieving some old values). then I come bak and test it again in the development enviroment to test, it fails ( as it supposed to be caz server name is wrong in config file)

can ne one tell me wr is the problem?

ur help and support is appreciated

regards,

Anas

Sorry to bother you but that was the problem with my config file...

it is so pathetic that package running does not show ne error or warning that's y wasted a lot of time.... fortunately i was able to solve the problem b4 I go on my holidays... i big sigh of reliefs...

regards,

Anas