Connectivity Portal (WIKI)

Hi All,

Wouldn’t it be nice to have a single portal to check the list of data sources SSIS can connect to? Like, if and how SSIS can connect to SAP/Oracle/DB2/SQL Server/etc? along with what to expect from that connection, such as 64-bit support, fast data load option, scalability, etc?

The know-how around connectivity in SSIS has particularly been challenging to structure in terms of gathering and reaching all sorts of connectors and data sources out there.

The challenge is due to two main reasons:

Microsoft does not have central command/control on connector developers, and the connectivity story for SSIS has mainly been the support around ADO.NET, Ole DB and ODBC libraries. Since we don’t have a certification process, it’s up to the customer to evaluate a specific third party connector before making a decision on using it.

The extensibility story offered by SSIS inherently promotes development of new/specialized connectors to new/existing data sources. Since any application can talk to any other application given the right connector, the pool of supported connectors keep growing over time.

We are working to make this story better, but in the mean time, because of this ever growing nature of connectors and their features, I thought a WIKI portal can help this community in terms of formalizing/structuring/sharing information.

And thus, I am launching a new wiki site on connectivity. Well…Blogs are kind of not as cool these days anyways J MSDN has a recent wiki initiative, but it is not quite involving the community as I’d like to have in this one.

We have a great talent pool and a huge base of know-how around connectivity in this forum. Let’s channel that into sorting out connectivity!

I created the initial site framework and provided simple samples here : http://ssis.wik.is/Home

I’ll add more stuff to it in the coming days as well, but I really want this forum to be actively editing/providing content in there.

Please take a look at it, and start growing/editing it. -> http://ssis.wik.is/Home

Ideas/questions/feedback are all welcome, you can also e-mail me directly at denizerkan@.yahoo.com

Thanks,

Deniz Erkan

PS: this is not in conflict or competition with the forum. Forum interface does not have a way to structure data to build a know-how, and the scope of this initiative is SSIS connectivity-only.

Hi Deniz,

This is commendable but are you aware of: http://wiki.sqlis.com/default.aspx/SQLISWiki/HomePage.html

-Jamie

|||

Yes, and I think it's full of very useful components, ideas and tips&tricks to get the best out of SSIS.

Connectivity is a slightly different and challenging area within SSIS that involves growing third party connectors, partner participation and new data sources, along with connectivity libraries (ADO.NET, OLE DB, ODBC) SSIS interfaces with, which makes it mostly an organic aspect of SSIS.

The connectivity wiki has by no means the goal of replacing other information portals, but to support and complement them. I'll see how the connectivity wiki initiative goes, and perhaps later we can consolidate SSIS wikis.

|||

Deniz Erkan - msft wrote:

Yes, and I think it's full of very useful components, ideas and tips&tricks to get the best out of SSIS.

Connectivity is a slightly different and challenging area within SSIS that involves growing third party connectors, partner participation and new data sources, along with connectivity libraries (ADO.NET, OLE DB, ODBC) SSIS interfaces with, which makes it mostly an organic aspect of SSIS.

The connectivity wiki has by no means the goal of replacing other information portals, but to support and complement them. I'll see how the connectivity wiki initiative goes, and perhaps later we can consolidate SSIS wikis.

OK Deniz, sounds good.

The first thing you should get up there is a webcast (or a link to one) that has exactly the same content that Jeff Bernhardt presented at PASS yesterday. It was a great session and would help to answer lots of the questions that people ask on this forum.

-Jamie

Connection to Red Brick

Hi all,

Is it possible to connect to an IBM Redbrick Database via Data Sources in AS 2005? I can't get to ODBC's and don't see an OLE driver.

Thanks in advance,

Mark

You must have a driver AND a cartridge that will work with Redbrick. The driver is pretty straightforward. The cartridge is an XSLT file that translates an internally generated XML-based query into a valid SQL statement for submission to the source system. On my system, the cartidges are available at:

C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\bin\Cartridges

If a cartridge is not available for your specific system (which is the case for Redbrick), SSAS attempts to use the sql2000.xsl cartridge. This might work for you, but I'm not certain.

Another optoin for you is to create a set of views in SQL Server that use the OPENROWSET function to establish the connection to Redbrick for you. You might be able to then expose those views to SSAS and since SSAS has cartridges for SQL Server this might work out.

Good luck,

Bryan

|||

Thanks for the suggestions/guidance Bryan.

I appreciate the help.

Connection to Oracle

I have created reports in SQl Server 2005 Reporting Services using an Oracle Data Sources. When I Preview the report in VS 2005, It runs fine but when I deploy it, it fails to make a coonection to the oracle data source.

Reports off the SQL Server Data Sources arerunning fine.

Can any one out there help me out on this. I will really appreaciate.

Regards,

Sam.

Assuming Oracle's client software is installed on the report server machine, it is most likely a directory permissions issue with Oracle's client software installation - see http://support.microsoft.com/?kbid=870668

-- Robert

|||

Hi Robert,

Thanks for the guideline but I have done what is required as stated in the link and I still get the same error.

|||Do you deploy the reports on the same PC? Maybe your Server has a different Oracle installation or tnsnames.ora..
For TESTING!! you could give "everyone" read/execute -access to your oracle-directory to eliminate permission-problems.
|||

It seems Permissions were my problem.

I assigned read/execute access to each of the Oracle Home Directory Folders and Subfolder to Network Service and it sorted out the problem

Thanks for all the Advise.

|||

Hi there,

I have the same issue as described earlier in this thread. I have applied the security (Read and Execute) to all known Oracle instance directories to the Network Service account (which is being used for starting the Reporting Service) and I am still receiving the following error message:

An error has occurred during report processing.

Cannot create a connection to data source 'XXX'.

System.Data.OracleClient requires Oracle client software version 8.1.7 or greater.|||

I'm trying to use the Oracle instant client on my report server. it is setup correctly and I can connect via SQL+. I've modified reporting services to use Network Service as account for both windows and web services. And I've set permission on the OIC directory appropriately according to MS guidelines.

But same error, will not connect to the Oracle data source.

Connection to Oracle

I have created reports in SQl Server 2005 Reporting Services using an Oracle Data Sources. When I Preview the report in VS 2005, It runs fine but when I deploy it, it fails to make a coonection to the oracle data source.

Reports off the SQL Server Data Sources arerunning fine.

Can any one out there help me out on this. I will really appreaciate.

Regards,

Sam.

Assuming Oracle's client software is installed on the report server machine, it is most likely a directory permissions issue with Oracle's client software installation - see http://support.microsoft.com/?kbid=870668

-- Robert

|||

Hi Robert,

Thanks for the guideline but I have done what is required as stated in the link and I still get the same error.

|||Do you deploy the reports on the same PC? Maybe your Server has a different Oracle installation or tnsnames.ora..
For TESTING!! you could give "everyone" read/execute -access to your oracle-directory to eliminate permission-problems.
|||

It seems Permissions were my problem.

I assigned read/execute access to each of the Oracle Home Directory Folders and Subfolder to Network Service and it sorted out the problem

Thanks for all the Advise.

|||

Hi there,

I have the same issue as described earlier in this thread. I have applied the security (Read and Execute) to all known Oracle instance directories to the Network Service account (which is being used for starting the Reporting Service) and I am still receiving the following error message:

An error has occurred during report processing.

Cannot create a connection to data source 'XXX'.

System.Data.OracleClient requires Oracle client software version 8.1.7 or greater.|||

I'm trying to use the Oracle instant client on my report server. it is setup correctly and I can connect via SQL+. I've modified reporting services to use Network Service as account for both windows and web services. And I've set permission on the OIC directory appropriately according to MS guidelines.

But same error, will not connect to the Oracle data source.

Connection to Oracle

I have created reports in SQl Server 2005 Reporting Services using an Oracle Data Sources. When I Preview the report in VS 2005, It runs fine but when I deploy it, it fails to make a coonection to the oracle data source.

Reports off the SQL Server Data Sources arerunning fine.

Can any one out there help me out on this. I will really appreaciate.

Regards,

Sam.

Assuming Oracle's client software is installed on the report server machine, it is most likely a directory permissions issue with Oracle's client software installation - see http://support.microsoft.com/?kbid=870668

-- Robert

|||

Hi Robert,

Thanks for the guideline but I have done what is required as stated in the link and I still get the same error.

|||Do you deploy the reports on the same PC? Maybe your Server has a different Oracle installation or tnsnames.ora..
For TESTING!! you could give "everyone" read/execute -access to your oracle-directory to eliminate permission-problems.
|||

It seems Permissions were my problem.

I assigned read/execute access to each of the Oracle Home Directory Folders and Subfolder to Network Service and it sorted out the problem

Thanks for all the Advise.

|||

Hi there,

I have the same issue as described earlier in this thread. I have applied the security (Read and Execute) to all known Oracle instance directories to the Network Service account (which is being used for starting the Reporting Service) and I am still receiving the following error message:

An error has occurred during report processing.

Cannot create a connection to data source 'XXX'.

System.Data.OracleClient requires Oracle client software version 8.1.7 or greater.|||

I'm trying to use the Oracle instant client on my report server. it is setup correctly and I can connect via SQL+. I've modified reporting services to use Network Service as account for both windows and web services. And I've set permission on the OIC directory appropriately according to MS guidelines.

But same error, will not connect to the Oracle data source.

Connection to Oracle

I have created reports in SQl Server 2005 Reporting Services using an Oracle Data Sources. When I Preview the report in VS 2005, It runs fine but when I deploy it, it fails to make a coonection to the oracle data source.

Reports off the SQL Server Data Sources arerunning fine.

Can any one out there help me out on this. I will really appreaciate.

Regards,

Sam.

Assuming Oracle's client software is installed on the report server machine, it is most likely a directory permissions issue with Oracle's client software installation - see http://support.microsoft.com/?kbid=870668

-- Robert

|||

Hi Robert,

Thanks for the guideline but I have done what is required as stated in the link and I still get the same error.

|||Do you deploy the reports on the same PC? Maybe your Server has a different Oracle installation or tnsnames.ora..
For TESTING!! you could give "everyone" read/execute -access to your oracle-directory to eliminate permission-problems.
|||

It seems Permissions were my problem.

I assigned read/execute access to each of the Oracle Home Directory Folders and Subfolder to Network Service and it sorted out the problem

Thanks for all the Advise.

|||

Hi there,

I have the same issue as described earlier in this thread. I have applied the security (Read and Execute) to all known Oracle instance directories to the Network Service account (which is being used for starting the Reporting Service) and I am still receiving the following error message:

An error has occurred during report processing.

Cannot create a connection to data source 'XXX'.

System.Data.OracleClient requires Oracle client software version 8.1.7 or greater.|||

I'm trying to use the Oracle instant client on my report server. it is setup correctly and I can connect via SQL+. I've modified reporting services to use Network Service as account for both windows and web services. And I've set permission on the OIC directory appropriately according to MS guidelines.

But same error, will not connect to the Oracle data source.

Connection Strings/Data Sources

Hey all,

I'm coming from ASP and I used to have a global connection string accessible to all of my ASP pages that I'd use for all of my data access. This was really convenient because I could easily switch to a backup or local data source for testing/debug by changing the connection string in one place.

Now, I'm using ASP.NET with Web Matrix and I love the drag and drop functionality but its dropping my connection string all over the place. How can I do this and keep my connection string in one spot so I can have the same convenience of switching data sources.

Thanks in advance!
LarryIf you are dropping connection objects, drag and drop functionality wasn't meant for that completely. You could try in your code to switch it to the connection in the web.config in one of the page events before it is used.

In the next version of Visual Studio, and most likely Visual Web Developer, you can use web.config entries in the connection objects.

Brian|||Hmmm...

So what can I do now? Should I define some kind of global string or an application variable with my database connection string and then search and replace all references to the connection string with that?

My application needs to be able to easily switch to a backup data source if the main source goes down.

Thanks in advance!
Larry|||I like the idea better of an n-tier architecture, where you have a data access layer that defines the connection information. However, if that's not for you, then I don't know what to tell you other than define the connection object in your web page. But then that throws off your datadapter or command objects dropped into the form.

I personally always created the database objects in the code because of that. Or look at changing the connection string in one of the page events. I think I got that concept to work in a windows form app, I think it may also work in a page event.

Brian