Showing posts with label released. Show all posts
Showing posts with label released. Show all posts

Friday, March 23, 2012

New SQL Server tool

Quest Software has released a SQL Server version of its highly popular
Oracle tool - TOAD. It is freeware! You can see more about it at
http://www.toadsoft.com/toadss.html and download it at
http://www.toadsoft.com/toad_ss.zip.Thats Cool, I have been using toad for a while on Oracle, and I am glad you
have a sql version now, I will download and evaluate it straight away

--
Dandy Weyn, Belgium
MCSE, MCSA, MCDBA, MCT

http://www.dandyman.net

Check my SQL Server resource pages (currently under construction)
http://www.dandyman.net/sql

"Bert Scalzo" <bert.scalzo@.comcast.net> wrote in message
news:7e3dac68.0402140948.30189336@.posting.google.c om...
> Quest Software has released a SQL Server version of its highly popular
> Oracle tool - TOAD. It is freeware! You can see more about it at
> http://www.toadsoft.com/toadss.html and download it at
> http://www.toadsoft.com/toad_ss.zip.|||Looked at it and it does absolutely nothing that QA does not do. You cannot
test procedures/run scripts. Free and does nothing. Sorry Bert, you know
in your heart that it cannot come close to SQL-Programmer.

Lloyd Sheen

"Dandy WEYN" <no_spam_info@.dandyman.net> wrote in message
news:402e7117$0$13251$ba620e4c@.news.skynet.be...
> Thats Cool, I have been using toad for a while on Oracle, and I am glad
you
> have a sql version now, I will download and evaluate it straight away
>
> --
> Dandy Weyn, Belgium
> MCSE, MCSA, MCDBA, MCT
> http://www.dandyman.net
> Check my SQL Server resource pages (currently under construction)
> http://www.dandyman.net/sql
>
> "Bert Scalzo" <bert.scalzo@.comcast.net> wrote in message
> news:7e3dac68.0402140948.30189336@.posting.google.c om...
> > Quest Software has released a SQL Server version of its highly popular
> > Oracle tool - TOAD. It is freeware! You can see more about it at
> > http://www.toadsoft.com/toadss.html and download it at
> > http://www.toadsoft.com/toad_ss.zip.

New SQL Server tool

Quest Software has released a SQL Server version of its highly popular
Oracle tool - TOAD. It is freeware! You can see more about it at
http://www.toadsoft.com/toadss.html and download it at
http://www.toadsoft.com/toad_ss.zip.Thats Cool, I have been using toad for a while on Oracle, and I am glad you
have a sql version now, I will download and evaluate it straight away
Dandy Weyn, Belgium
MCSE, MCSA, MCDBA, MCT
http://www.dandyman.net
Check my SQL Server resource pages (currently under construction)
http://www.dandyman.net/sql
"Bert Scalzo" <bert.scalzo@.comcast.net> wrote in message
news:7e3dac68.0402140948.30189336@.posting.google.com...
> Quest Software has released a SQL Server version of its highly popular
> Oracle tool - TOAD. It is freeware! You can see more about it at
> http://www.toadsoft.com/toadss.html and download it at
> http://www.toadsoft.com/toad_ss.zip.|||Looked at it and it does absolutely nothing that QA does not do. You cannot
test procedures/run scripts. Free and does nothing. Sorry Bert, you know
in your heart that it cannot come close to SQL-Programmer.
Lloyd Sheen
"Dandy WEYN" <no_spam_info@.dandyman.net> wrote in message
news:402e7117$0$13251$ba620e4c@.news.skynet.be...
> Thats Cool, I have been using toad for a while on Oracle, and I am glad
you
> have a sql version now, I will download and evaluate it straight away
>
> --
> Dandy Weyn, Belgium
> MCSE, MCSA, MCDBA, MCT
> http://www.dandyman.net
> Check my SQL Server resource pages (currently under construction)
> http://www.dandyman.net/sql
>
> "Bert Scalzo" <bert.scalzo@.comcast.net> wrote in message
> news:7e3dac68.0402140948.30189336@.posting.google.com...
>sql

New SQL Server tool

Quest Software has released a SQL Server version of its highly popular Oracle tool - TOAD. It is f
reeware! You can see more about it at http://www.toadsoft.com/toadss.html and donwload
it at http://www.toadsoft.com/toad_ss.zip.This is not a Quest Software tool from what I can tell.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"bscalzo" <anonymous@.discussions.microsoft.com> wrote in message
news:1C861ACD-D17B-4A71-86D3-D0384C5C117A@.microsoft.com...
> Quest Software has released a SQL Server version of its highly popular
Oracle tool - TOAD. It is freeware! You can see more about it at
http://www.toadsoft.com/toadss.html and donwload it at
http://www.toadsoft.com/toad_ss.zip.|||Quest Software owns TOAD and TOADSOFT web site. The developers just find it
easier to post things using this site rather than the official Quest web sit
e. But it is owned and managed by Quest - and it is Quest employees who buil
t this product. I know, bec
ause I'm on the team ...

New SQL Server tool

Quest Software has released a SQL Server version of its highly popular Oracle tool - TOAD. It is freeware! You can see more about it at http://www.toadsoft.com/toadss.html and donwload it at http://www.toadsoft.com/toad_ss.zip.This is not a Quest Software tool from what I can tell.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"bscalzo" <anonymous@.discussions.microsoft.com> wrote in message
news:1C861ACD-D17B-4A71-86D3-D0384C5C117A@.microsoft.com...
> Quest Software has released a SQL Server version of its highly popular
Oracle tool - TOAD. It is freeware! You can see more about it at
http://www.toadsoft.com/toadss.html and donwload it at
http://www.toadsoft.com/toad_ss.zip.|||Quest Software owns TOAD and TOADSOFT web site. The developers just find it easier to post things using this site rather than the official Quest web site. But it is owned and managed by Quest - and it is Quest employees who built this product. I know, because I'm on the team ...

New SQL Server tool

Quest Software has released a SQL Server version of its highly popular
Oracle tool - TOAD. It is freeware! You can see more about it at
http://www.toadsoft.com/toadss.html and download it at
http://www.toadsoft.com/toad_ss.zip.Thats Cool, I have been using toad for a while on Oracle, and I am glad you
have a sql version now, I will download and evaluate it straight away
Dandy Weyn, Belgium
MCSE, MCSA, MCDBA, MCT
http://www.dandyman.net
Check my SQL Server resource pages (currently under construction)
http://www.dandyman.net/sql
"Bert Scalzo" <bert.scalzo@.comcast.net> wrote in message
news:7e3dac68.0402140948.30189336@.posting.google.com...
> Quest Software has released a SQL Server version of its highly popular
> Oracle tool - TOAD. It is freeware! You can see more about it at
> http://www.toadsoft.com/toadss.html and download it at
> http://www.toadsoft.com/toad_ss.zip.|||Looked at it and it does absolutely nothing that QA does not do. You cannot
test procedures/run scripts. Free and does nothing. Sorry Bert, you know
in your heart that it cannot come close to SQL-Programmer.
Lloyd Sheen
"Dandy WEYN" <no_spam_info@.dandyman.net> wrote in message
news:402e7117$0$13251$ba620e4c@.news.skynet.be...
> Thats Cool, I have been using toad for a while on Oracle, and I am glad
you
> have a sql version now, I will download and evaluate it straight away
>
> --
> Dandy Weyn, Belgium
> MCSE, MCSA, MCDBA, MCT
> http://www.dandyman.net
> Check my SQL Server resource pages (currently under construction)
> http://www.dandyman.net/sql
>
> "Bert Scalzo" <bert.scalzo@.comcast.net> wrote in message
> news:7e3dac68.0402140948.30189336@.posting.google.com...
> > Quest Software has released a SQL Server version of its highly popular
> > Oracle tool - TOAD. It is freeware! You can see more about it at
> > http://www.toadsoft.com/toadss.html and download it at
> > http://www.toadsoft.com/toad_ss.zip.
>

Monday, March 19, 2012

New RDL Spec differences

Is there somewhere that explains the major differences between the 2003 RDL spec and the new one released in November? I will be going through the two specs in detail in January looking at the differences but I was just wondering if there's a quick synopsis out there somewhere.

Are there any major differences?

Darren

I guess I'll answer my own post in case anyone else out there is interested.

First the end of the new spec has a very brief and incomplete lists of the changes.

The important changes I found were the following:

1) InteractiveWidth and InteractiveHeight subelements added to Report element.

2) Multivaluie and Hidden subelements added to ReportParameters element.

3) ConnectionProperties now support OLEDB-MD, DB2, XML, and ConnectString changed from string to Expression.

4) Custom element removed and replcaed by CustomProperties and CustomProperty elements.

5) Textbox new subelement of UserSort (a new element).

6) FixedHeader subelement added to ColumnGrouping, RowGrouping, TableHeader and Header elements.

7) SeriesGrouping now added Style as a subelement.

8) Removed HeightRatio, DepthRatio, GapDepth removed from ThreeDProperties

9) CustomReportItem completely overhauled.

10) A bunch of new elements supporting CustomReportItem including: AltReportItem, CustomData, DataColumnGroupings, DataRowGroupings, DataGroupings, DataGrouping, DataRows, DataRow, DataCell and DataValue (note that there are now TWO DataValue definitions in the spec, I find it bizarre that they would have two separately defined DataValue elements, but oh well.

11) Axis element has 5 subelements changed from string to expression type.

12) The Compliance Levels concept was removed.

13) Expressions and Aggregate Functions were fleshed out a little more.

In addition to the above mentioned changes there were some more smaller specific constraints changes to various elements.

Thats about everything I found.

Darren

|||

Darren's list is a very good description of the changes.

A few additional comments:

#4 CustomProperties are way more powerful than the old Custom element (which btw, gets automatically upgraded into a CustomProperty element). You can use RDL expressions with aggregations in CustomProperties - so you are no longer limited to "passing through" static contents to the RenderingObjectModel / custom rendering extensions.

#10 DataValue element: if you check the underlying XSD, you will see that there is only one DataValue element defined. It is reused for both, CustomReportItem and Chart. However, in the chart case we only allow the Value portion at this point.

#13 There are various minor improvements (allow the usage of Guid fields directly in textboxes, groupings, RunningValues can now also be used in charts, etc.)

-- Robert

|||

We also added so-called "extended field properties" to the RDL expression language - mainly targeted at AS/MDX queries. E.g. =Fields!Dimension.UniqueName

-- Robert

|||

#10 DataValue element: if you check the underlying XSD, you will see that there is only one DataValue element defined. It is reused for both, CustomReportItem and Chart. However, in the chart case we only allow the Value portion at this point.

Regarding your statement above. Ok, that makes more sense. But why in the spec is it defined twice? Why not define it one place in the spec and put a constraint on it like was done with other elements. Because honsetly it is very confusing and not at all clear that it is the intention that these two elements are one and the same.

Take this from the point of view of someone developing an RDL parser from the spec (much the same as someone might do for the PDF spec), there is absolutely no way to get that intent.

#13 There are various minor improvements (allow the usage of Guid fields directly in textboxes, groupings, RunningValues can now also be used in charts, etc.)

Regarding this statement above can you point me to which pages in the spec explain this part. I saw a note to this effect in the back of the spec but found nowhere actually IN the spec that identified these changes. It doesn't show up under type definitions or under textbox, so I'm a little confused here.

Thanks for all your help.

|||

#10: You are right - we should have only one DataValue element in the spec and explain the contraints in that place. Right now, you will find a comment on page 80 that says "Note: Since this is the same as the DataValue element in Chart ..." - but it is maybe confusing. We will update the document; it may take a few weeks till the changes are available on the web site.

#13: The Guid changes are mentioned on page 89 and on page 113. Note: just adding Guid to the list of supported datatypes and saying that it is treated as string implies that Guid fields can now be used everywhere throughout the RDL (textboxes, groupings, filters, even parameters - because they behave like string parameters).

-- Robert

|||

Robert,

Once again I really thank you for your quick and informative responses. I do have a couple more questions (of course ;-) ).

With regards to GUID changes if I understand you correctly, anywhere their is a string type, a GUID can be used. If this is true it would be nice if that was noted on page 9 under Element Definition conventions where string is defined.

Another point on that page is that it states:

"String - A subelement or attribute with a string text value"

Does this imply that anything of type string needs to be handled as a string or attribute? ie. QueryParameter | Name is defined as a String Type but Reporting Services treats it as an attribute only, while most String Types are treated as subelements only. My question is this: by the letter of the spec I would need to handle all string types on consumption as either attributes or elements yet I don't think you will ever see DataSet | Collation for instance, as anything but a subelement. Can this ever be an attribute? Can QueryParameter | Name ever be an element? If not can this be cleared up in the spec as well?

Hope all my nit-picking isn't bothering you too much. I think the spec is very well done, I just need some clarification on some of these issues.

Darren

|||

If there is a (mandatory) attribute that uniquely identifies an item (such as the Name attribute of a query parameter), it will always be represented as Xml attribute in the RDL representation.

Hence, QueryParameter.Name is always an attribute and can never be an element. Dataset.Collation can never be an Xml attribute because it is optional in the RDL spec.

If there are other items where it is not clear if they are represented as attributes or elements in the RDL file, you can also check directly in the XSD:

http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition/ReportDefinition.xsd

-- Robert

New RDL Spec differences

Is there somewhere that explains the major differences between the 2003 RDL spec and the new one released in November? I will be going through the two specs in detail in January looking at the differences but I was just wondering if there's a quick synopsis out there somewhere.

Are there any major differences?

Darren

I guess I'll answer my own post in case anyone else out there is interested.

First the end of the new spec has a very brief and incomplete lists of the changes.

The important changes I found were the following:

1) InteractiveWidth and InteractiveHeight subelements added to Report element.

2) Multivaluie and Hidden subelements added to ReportParameters element.

3) ConnectionProperties now support OLEDB-MD, DB2, XML, and ConnectString changed from string to Expression.

4) Custom element removed and replcaed by CustomProperties and CustomProperty elements.

5) Textbox new subelement of UserSort (a new element).

6) FixedHeader subelement added to ColumnGrouping, RowGrouping, TableHeader and Header elements.

7) SeriesGrouping now added Style as a subelement.

8) Removed HeightRatio, DepthRatio, GapDepth removed from ThreeDProperties

9) CustomReportItem completely overhauled.

10) A bunch of new elements supporting CustomReportItem including: AltReportItem, CustomData, DataColumnGroupings, DataRowGroupings, DataGroupings, DataGrouping, DataRows, DataRow, DataCell and DataValue (note that there are now TWO DataValue definitions in the spec, I find it bizarre that they would have two separately defined DataValue elements, but oh well.

11) Axis element has 5 subelements changed from string to expression type.

12) The Compliance Levels concept was removed.

13) Expressions and Aggregate Functions were fleshed out a little more.

In addition to the above mentioned changes there were some more smaller specific constraints changes to various elements.

Thats about everything I found.

Darren

|||

Darren's list is a very good description of the changes.

A few additional comments:

#4 CustomProperties are way more powerful than the old Custom element (which btw, gets automatically upgraded into a CustomProperty element). You can use RDL expressions with aggregations in CustomProperties - so you are no longer limited to "passing through" static contents to the RenderingObjectModel / custom rendering extensions.

#10 DataValue element: if you check the underlying XSD, you will see that there is only one DataValue element defined. It is reused for both, CustomReportItem and Chart. However, in the chart case we only allow the Value portion at this point.

#13 There are various minor improvements (allow the usage of Guid fields directly in textboxes, groupings, RunningValues can now also be used in charts, etc.)

-- Robert

|||

We also added so-called "extended field properties" to the RDL expression language - mainly targeted at AS/MDX queries. E.g. =Fields!Dimension.UniqueName

-- Robert

|||

#10 DataValue element: if you check the underlying XSD, you will see that there is only one DataValue element defined. It is reused for both, CustomReportItem and Chart. However, in the chart case we only allow the Value portion at this point.

Regarding your statement above. Ok, that makes more sense. But why in the spec is it defined twice? Why not define it one place in the spec and put a constraint on it like was done with other elements. Because honsetly it is very confusing and not at all clear that it is the intention that these two elements are one and the same.

Take this from the point of view of someone developing an RDL parser from the spec (much the same as someone might do for the PDF spec), there is absolutely no way to get that intent.

#13 There are various minor improvements (allow the usage of Guid fields directly in textboxes, groupings, RunningValues can now also be used in charts, etc.)

Regarding this statement above can you point me to which pages in the spec explain this part. I saw a note to this effect in the back of the spec but found nowhere actually IN the spec that identified these changes. It doesn't show up under type definitions or under textbox, so I'm a little confused here.

Thanks for all your help.

|||

#10: You are right - we should have only one DataValue element in the spec and explain the contraints in that place. Right now, you will find a comment on page 80 that says "Note: Since this is the same as the DataValue element in Chart ..." - but it is maybe confusing. We will update the document; it may take a few weeks till the changes are available on the web site.

#13: The Guid changes are mentioned on page 89 and on page 113. Note: just adding Guid to the list of supported datatypes and saying that it is treated as string implies that Guid fields can now be used everywhere throughout the RDL (textboxes, groupings, filters, even parameters - because they behave like string parameters).

-- Robert

|||

Robert,

Once again I really thank you for your quick and informative responses. I do have a couple more questions (of course ;-) ).

With regards to GUID changes if I understand you correctly, anywhere their is a string type, a GUID can be used. If this is true it would be nice if that was noted on page 9 under Element Definition conventions where string is defined.

Another point on that page is that it states:

"String - A subelement or attribute with a string text value"

Does this imply that anything of type string needs to be handled as a string or attribute? ie. QueryParameter | Name is defined as a String Type but Reporting Services treats it as an attribute only, while most String Types are treated as subelements only. My question is this: by the letter of the spec I would need to handle all string types on consumption as either attributes or elements yet I don't think you will ever see DataSet | Collation for instance, as anything but a subelement. Can this ever be an attribute? Can QueryParameter | Name ever be an element? If not can this be cleared up in the spec as well?

Hope all my nit-picking isn't bothering you too much. I think the spec is very well done, I just need some clarification on some of these issues.

Darren

|||

If there is a (mandatory) attribute that uniquely identifies an item (such as the Name attribute of a query parameter), it will always be represented as Xml attribute in the RDL representation.

Hence, QueryParameter.Name is always an attribute and can never be an element. Dataset.Collation can never be an Xml attribute because it is optional in the RDL spec.

If there are other items where it is not clear if they are represented as attributes or elements in the RDL file, you can also check directly in the XSD:

http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition/ReportDefinition.xsd

-- Robert

Monday, March 12, 2012

New Oracle Data Provider for .NET (ODP .NET) 10.2.0.2.20 failure.

Oracle recently released Oracle Data Provider for .NET (ODP .NET) 10.2.0.2.20. It happened on August 1, 2006. Oracle claims that its software is compliant with Microsoft ADO.NET 2.0 and “…more flexible, faster, and more stable….”. On top of it Oracle introduced many new features “… not available from other .NET drivers…”.

http://www.oracle.com/technology/tech/windows/odpnet/index.html

I decided to give it a shot and try in SSIS.

I installed ODP.NET on my machine and it works just fine in PL/SQL Developer

Next, I opened Data Source Wizard in BIDS for SSIS project.

I see Oracle Data Provider for .NET option is enlisted under .NET Providers node on a tree. But when I select it, I am getting an error dialog:

Failed to find or load the registered .Net Framework Data Provider.

So what could be done in such scenario?

Regards,

Yitzhak

The message seems to say the provider just cannot be found. These seems a bit extreme, but you could find out what has been registered, and try and see if the assembly is then available.

Managed providers are registered in C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\CONFIG\machine.config. There will be a section that looks a bit like this -

<system.data>
<DbProviderFactories>
<add name="Odbc Data Provider" invariant="System.Data.Odbc" description=".Net Framework Data Provider for Odbc" type="System.Data.Odbc.OdbcFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
<add name="OleDb Data Provider" invariant="System.Data.OleDb" description=".Net Framework Data Provider for OleDb" type="System.Data.OleDb.OleDbFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
<add name="OracleClient Data Provider" invariant="System.Data.OracleClient" description=".Net Framework Data Provider for Oracle" type="System.Data.OracleClient.OracleClientFactory, System.Data.OracleClient, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
<add name="SqlClient Data Provider" invariant="System.Data.SqlClient" description=".Net Framework Data Provider for SqlServer" type="System.Data.SqlClient.SqlClientFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
<add name="SQL Server CE Data Provider" invariant="Microsoft.SqlServerCe.Client" description=".NET Framework Data Provider for Microsoft SQL Server 2005 Mobile Edition" type="Microsoft.SqlServerCe.Client.SqlCeClientFactory, Microsoft.SqlServerCe.Client, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"/>
</DbProviderFactories>
</system.data>

This shows that I have 5 managed providers, and it also gives the strong name for each. They should then be available in the GAC. A simple application that loads an assembly by it's string name could be use dto check that it really is available.

This seems somewhat excessive, and there should be simpler explanation, but the documentation didn't make too much sense to me.

|||

I checked machine.config file on my machine.
Here is what it has:

<system.data>
<DbProviderFactories>
<add name="Odbc Data Provider" invariant="System.Data.Odbc" description=".Net Framework Data Provider for Odbc"
type="System.Data.Odbc.OdbcFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
<add name="OleDb Data Provider" invariant="System.Data.OleDb"
description=".Net Framework Data Provider for OleDb" type="System.Data.OleDb.OleDbFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
<add name="OracleClient Data Provider" invariant="System.Data.OracleClient"
description=".Net Framework Data Provider for Oracle" type="System.Data.OracleClient.OracleClientFactory, System.Data.OracleClient, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
<add name="SqlClient Data Provider" invariant="System.Data.SqlClient"
description=".Net Framework Data Provider for SqlServer" type="System.Data.SqlClient.SqlClientFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
<add name="Oracle Data Provider for .NET" invariant="Oracle.DataAccess.Client"
description="Oracle Data Provider for .NET" type="Oracle.DataAccess.Client.OracleClientFactory, Oracle.DataAccess, Version=2.102.2.20, Culture=neutral, PublicKeyToken=89b483f429c47342" />
</DbProviderFactories>
</system.data>

I also checked c:\winnt\assembly directory (GAC)
Oracle.DataAccess version 2.102.2.20 is there.

Regards,
Yitzhak

|||

There is some new development on the subject.

Oracle Data Provider for .NET (ODP .NET) 10.2.0.2.20 comes with the sample projects for Visual Studio 2005. I decided to try one of them - DSPopulate. It issues a single SQL statement and populates a data grid for Win Form app. Long story short, the sample application complies and works. References node in VS2005 shows a reference to Oracle Data provider.

I reopened my SSIS project and went through the same steps to create a new Data Source. I picked Oracle Data Provider for .NET. And here is some new behavior. Connection tab shows the following message:

Connection page is not available. Please switch to use ‘All’ properties tab page to set the properties of connection string.

So I switched to All tab and was able to set needed parameters and successfully test the connection.

I tested Oracle ODP.NET based connection with the Data Reader Source task. It works.

Regards,

Yitzhak

|||

Hi,

We also installed ODP.Net in my system however after the installation our usual oracle providers didnt worked. Started showing error when we used other oracle providers(Oracle Provider, Microsoft oracle provider).

Whether we can use ODP.NET as destination? If so, how can we use it?

Please help me in this regarding

Thanks

New Oracle Data Provider for .NET (ODP .NET) 10.2.0.2.20 failure.

Oracle recently released Oracle Data Provider for .NET (ODP .NET) 10.2.0.2.20. It happened on August 1, 2006. Oracle claims that its software is compliant with Microsoft ADO.NET 2.0 and “…more flexible, faster, and more stable….”. On top of it Oracle introduced many new features “… not available from other .NET drivers…”.

http://www.oracle.com/technology/tech/windows/odpnet/index.html

I decided to give it a shot and try in SSIS.

I installed ODP.NET on my machine and it works just fine in PL/SQL Developer

Next, I opened Data Source Wizard in BIDS for SSIS project.

I see Oracle Data Provider for .NET option is enlisted under .NET Providers node on a tree. But when I select it, I am getting an error dialog:

Failed to find or load the registered .Net Framework Data Provider.

So what could be done in such scenario?

Regards,

Yitzhak

The message seems to say the provider just cannot be found. These seems a bit extreme, but you could find out what has been registered, and try and see if the assembly is then available.

Managed providers are registered in C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\CONFIG\machine.config. There will be a section that looks a bit like this -

<system.data>
<DbProviderFactories>
<add name="Odbc Data Provider" invariant="System.Data.Odbc" description=".Net Framework Data Provider for Odbc" type="System.Data.Odbc.OdbcFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
<add name="OleDb Data Provider" invariant="System.Data.OleDb" description=".Net Framework Data Provider for OleDb" type="System.Data.OleDb.OleDbFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
<add name="OracleClient Data Provider" invariant="System.Data.OracleClient" description=".Net Framework Data Provider for Oracle" type="System.Data.OracleClient.OracleClientFactory, System.Data.OracleClient, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
<add name="SqlClient Data Provider" invariant="System.Data.SqlClient" description=".Net Framework Data Provider for SqlServer" type="System.Data.SqlClient.SqlClientFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
<add name="SQL Server CE Data Provider" invariant="Microsoft.SqlServerCe.Client" description=".NET Framework Data Provider for Microsoft SQL Server 2005 Mobile Edition" type="Microsoft.SqlServerCe.Client.SqlCeClientFactory, Microsoft.SqlServerCe.Client, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"/>
</DbProviderFactories>
</system.data>

This shows that I have 5 managed providers, and it also gives the strong name for each. They should then be available in the GAC. A simple application that loads an assembly by it's string name could be use dto check that it really is available.

This seems somewhat excessive, and there should be simpler explanation, but the documentation didn't make too much sense to me.

|||

I checked machine.config file on my machine.
Here is what it has:

<system.data>
<DbProviderFactories>
<add name="Odbc Data Provider" invariant="System.Data.Odbc" description=".Net Framework Data Provider for Odbc"
type="System.Data.Odbc.OdbcFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
<add name="OleDb Data Provider" invariant="System.Data.OleDb"
description=".Net Framework Data Provider for OleDb" type="System.Data.OleDb.OleDbFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
<add name="OracleClient Data Provider" invariant="System.Data.OracleClient"
description=".Net Framework Data Provider for Oracle" type="System.Data.OracleClient.OracleClientFactory, System.Data.OracleClient, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
<add name="SqlClient Data Provider" invariant="System.Data.SqlClient"
description=".Net Framework Data Provider for SqlServer" type="System.Data.SqlClient.SqlClientFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
<add name="Oracle Data Provider for .NET" invariant="Oracle.DataAccess.Client"
description="Oracle Data Provider for .NET" type="Oracle.DataAccess.Client.OracleClientFactory, Oracle.DataAccess, Version=2.102.2.20, Culture=neutral, PublicKeyToken=89b483f429c47342" />
</DbProviderFactories>
</system.data>

I also checked c:\winnt\assembly directory (GAC)
Oracle.DataAccess version 2.102.2.20 is there.

Regards,
Yitzhak

|||

There is some new development on the subject.

Oracle Data Provider for .NET (ODP .NET) 10.2.0.2.20 comes with the sample projects for Visual Studio 2005. I decided to try one of them - DSPopulate. It issues a single SQL statement and populates a data grid for Win Form app. Long story short, the sample application complies and works. References node in VS2005 shows a reference to Oracle Data provider.

I reopened my SSIS project and went through the same steps to create a new Data Source. I picked Oracle Data Provider for .NET. And here is some new behavior. Connection tab shows the following message:

Connection page is not available. Please switch to use ‘All’ properties tab page to set the properties of connection string.

So I switched to All tab and was able to set needed parameters and successfully test the connection.

I tested Oracle ODP.NET based connection with the Data Reader Source task. It works.

Regards,

Yitzhak