Connection problems: ssis

Showing posts with label ssis. Show all posts

Sunday, March 25, 2012

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

Thursday, March 8, 2012

Connection to oracle / sqlserver / db2 databases

Hi All,

Is there a way in SSIS to change the database connection to sqlserver or oracle or db2 without choosing the connection manager in the source.

For e.g. I ran a package which is reading data from sqlserver and later i want to connect to oracle to read the data but without changing the connection manager in the source.

Basically, i want to find out from you gurus is about a way to switch between databases without changing the connection managers. OR the only way is to make different sets of packages for oracle / db2 and sqlserver sources ?

Thanks,

Vipul

Vipul123 wrote:

Hi All,
Is there a way in SSIS to change the database connection to sqlserver or oracle or db2 without choosing the connection manager in the source.
For e.g. I ran a package which is reading data from sqlserver and later i want to connect to oracle to read the data but without changing the connection manager in the source.
Basically, i want to find out from you gurus is about a way to switch between databases without changing the connection managers. OR the only way is to make different sets of packages for oracle / db2 and sqlserver sources ?
Thanks,
Vipul

You could use a configuration and change it in there. Judging by another post of yours you have already done this hence, could you lock this thread? If you don't have permission to lock the thread, let me know. I will be able to do it.

-Jamie

|||

Jamie,

Please lock the thread as i dont have the permisssion to do it.

Thanks,

Vipul

Connection to MySql in SSIS

I have a process that copies data from a MySql database to Sql Server. This process has worked fine on another server but is failing on a new server. I created an ODBC connection to the MySql database which works fine. In the job, it has a Connection Manager object to the MySql database which Tests successfully. The job has a Datareader source with a very simple SQL command in it. When I run the job, I get the following error:

Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.

Error: 0xC0047062 at Data Flow Task, DataReader Source [1]: System.Data.Odbc.OdbcException: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager90.AcquireConnection(Object pTransaction)

at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object transaction)

at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper90 wrapper, Object transaction)

Error: 0xC0047017 at Data Flow Task, DTS.Pipeline: component "DataReader Source" (1) failed validation and returned error code 0x80131937.

Error: 0xC004700C at Data Flow Task, DTS.Pipeline: One or more component failed validation.

Error: 0xC0024107 at Data Flow Task: There were errors during task validation.

It says that the Data Source Name Not Found. If this was so, how is the connection manager test succeeding?

I am running the MySql driver version 3.51.12.

Any ideas what might be causing this to fail will be greatly appreciated!

Are you using expressions or configurations on the connect string?

Saturday, February 25, 2012

Connection Strings

I have created a SSIS package, and got it to perform as I want.

I am having a problem though with connecting to my data source (OLE DB Source) using SQL Server Authentication.

If I use Integrated security, I have no problems. If I use SQL Server Authentication, I get errors.

Here is the connection string that works:

Provider=SQLNCLI.1;Data Source=.\SqlExpress;Integrated Security=SSPI;Initial Catalog=MyDataBase

Here is the connection string that fails:

Provider=SQLNCLI.1;Data Source=.\SqlExpress;Persist Security Info=True;Password=**********;User ID=myUserid;Initial Catalog=MyDataBase

The strange thing is that when I use the Connection Manager dialog screen and click on "Test Connection", I get the response that the connection succeeded. It is only when I try to run the task that I get an error.

Here is the error I am getting:

[OLE DB Source [1]] Error: The AcquireConnection method call to the connection manager "MyDataBase" failed with error code 0xC0202009.

[DTS.Pipeline] Error: component "MyDataBase" (1) failed validation and returned error code 0xC020801C.

I have eliminated the obvious errors (incorrect Userid or password). Also, I am unable to use Integrated Security as this package needs to be distributed to various systems that may or may not be on our company's primary domain.

It seems I am missing something really basic here, but am not seeing it.

Any thoughts?

Has this ever worked for you? Are you able to create a brand new package and it still fails? Are you using package configurations?|||

Has this ever worked for you? Yes, using integrated security

Are you able to create a brand new package and it still fails? No, actually I tried a new simple package, and it works.

Are you using package configurations? No

I think the key here is your second point. I created a new package, and the connection now works. So, the question is how do I fix my original package? I'm not really thrilled about creating a brand new package being that this one is quite complicated.

Suggestions?

|||Well, thanks to R.K.S. response, I created a brand new package, this time setting the connections to use SQL Server authentication from the beginning, and it fixed my issue. But what a PIA!!! I spent way too much time researching and trying to figure out the problem, and never did find what is was, nor how to fix it. There must be a better way.|||

WRBehning,

Welcome to the new world of SSIS! I have had similar problems, especially when you copy and paste items. Basically I think the internal Ids in SSIS get messed up somehow. Alot of times if you delete and re-add an item it fixes the problem. Doesn't give me great confidence when an "enterprise solution" has so many strange problems. Good Luck! (to us all)

|||

WRBehning wrote:
Well, thanks to R.K.S. response, I created a brand new package, this time setting the connections to use SQL Server authentication from the beginning, and it fixed my issue. But what a PIA!!! I spent way too much time researching and trying to figure out the problem, and never did find what is was, nor how to fix it. There must be a better way.

If you have a RFE (Request for Enhancement) then file it at http://connect.microsoft.com with a detailed write-up.

Only thru doing this will SSIS get closer to being the product that people want it to be.

-Jamie

|||

WRBehning wrote:
Well, thanks to R.K.S. response, I created a brand new package, this time setting the connections to use SQL Server authentication from the beginning, and it fixed my issue. But what a PIA!!! I spent way too much time researching and trying to figure out the problem, and never did find what is was, nor how to fix it. There must be a better way.

Can you explain exactly what the problem was in the original. Its not clear from this?

If its something that people need to know then I'd like to add it here: http://blogs.conchango.com/jamiethomson/archive/2006/10/18/SSIS_3A00_-Random-information-for-beginners.aspx

Thanks

-Jamie

|||The issue I had was that I created the SSIS package using Windows Authentication for my OLEDB connections. After I had completed the package, I tried to switch to SQL Server Authentication and even though the "Test Connection" responded with "Test Connection Succeeded", the package would not run. Finally, like I stated earlier, I created a new package, using Sql Server Authentication from the beginning, and the issue was resloved.|||

WRBehning wrote:
The issue I had was that I created the SSIS package using Windows Authentication for my OLEDB connections. After I had completed the package, I tried to switch to SQL Server Authentication and even though the "Test Connection" responded with "Test Connection Succeeded", the package would not run. Finally, like I stated earlier, I created a new package, using Sql Server Authentication from the beginning, and the issue was resloved.

That's worrying if it didn't work. However I have previously had no problems with changing connection strings (for that is what we're talking about here).

One thing that can cause problems is if (for example) the ServerName property doesn't marry up with the server name that you are setting in the ConenctioNString property although in your case the only difference is in authentication mode so I shouldn't expect it to be a problem.

I've also tried your repro steps as above and didn't experience any problems.

What version/service pack are you on?

-Jmie

|||

Here you go...

A couple of things I should mention.

I tried deleting and re-creating the connections and this did not solve the problem.

The connections are connecting to SQL Express, not standard SQL, though I do not see where this would be a consideration.

SQLExpress (SQL Server 9.0.2047)

Microsoft Visual Studio 2005
Version 8.0.50727.42 (RTM.050727-4200)
Microsoft .NET Framework
Version 2.0.50727

Installed Edition: Professional

Microsoft Visual Basic 2005 77626-009-0000007-41520
Microsoft Visual Basic 2005

Microsoft Visual C# 2005 77626-009-0000007-41520
Microsoft Visual C# 2005

Microsoft Visual C++ 2005 77626-009-0000007-41520
Microsoft Visual C++ 2005

Microsoft Visual J# 2005 77626-009-0000007-41520
Microsoft Visual J# 2005

Microsoft Visual Web Developer 2005 77626-009-0000007-41520
Microsoft Visual Web Developer 2005

Crystal Reports AAC60-G0CSA4B-V7000AY
Crystal Reports for Visual Studio 2005

SQL Server Analysis Services
Microsoft SQL Server Analysis Services Designer
Version 9.00.2047.00

SQL Server Integration Services
Microsoft SQL Server Integration Services Designer
Version 9.00.2047.00

SQL Server Reporting Services
Microsoft SQL Server Reporting Services Designers
Version 9.00.2047.00

|||

WRBehning wrote:

Here you go...
A couple of things I should mention.
I tried deleting and re-creating the connections and this did not solve the problem.
The connections are connecting to SQL Express, not standard SQL, though I do not see where this would be a consideration.
SQLExpress (SQL Server 9.0.2047)
Microsoft Visual Studio 2005
Version 8.0.50727.42 (RTM.050727-4200)
Microsoft .NET Framework
Version 2.0.50727
Installed Edition: Professional
Microsoft Visual Basic 2005 77626-009-0000007-41520
Microsoft Visual Basic 2005
Microsoft Visual C# 2005 77626-009-0000007-41520
Microsoft Visual C# 2005
Microsoft Visual C++ 2005 77626-009-0000007-41520
Microsoft Visual C++ 2005
Microsoft Visual J# 2005 77626-009-0000007-41520
Microsoft Visual J# 2005
Microsoft Visual Web Developer 2005 77626-009-0000007-41520
Microsoft Visual Web Developer 2005
Crystal Reports AAC60-G0CSA4B-V7000AY
Crystal Reports for Visual Studio 2005

SQL Server Analysis Services
Microsoft SQL Server Analysis Services Designer
Version 9.00.2047.00
SQL Server Integration Services
Microsoft SQL Server Integration Services Designer
Version 9.00.2047.00
SQL Server Reporting Services
Microsoft SQL Server Reporting Services Designers
Version 9.00.2047.00

Seems we're on the same. Strange.

Oh well. Let's chalk it up to experience. If it happens again, let us know.

Thanks

Jamie

|||

I am experiencing the same behaviour.

The package was first created using EncryptWithUserKey setting, and the connection credentials was for a trusted connection to a SQL Server.

We changed the package security to use EncryptWithPassword, and the connection credentials stayed the same and continued to work properly.

When we changed the connection credentials to use a Standard SQL login, I would set the login and password and check the "Remember password" option. The Test Connection button was successful. Then after closing the connection manager properties, I would immediately reopen the properties and the password was gone, but the checkbox was still there, and the Test Connection would fail.

Mike