SSIS Deployment from SYBASE to SQL SERVER 2005
-
I am trying to get data from Sybase to Sql Server 2005. I am using an OLEDB conecction for Sybase and in debug it works fine. But when i generate a configuration file to register the package in database to run it from a job it doesn't work. I obtain that error:
Error: 0xC0202009 at Package, Connection manager "SourceConnectionOLEDB": An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "ASE OLE DB Provider" Hresult: 0x80004005 Description: "Login Failed. Check for valid user ID, server name and password.".
An OLE DB record is available. Source: "ASE OLE DB Provider" Hresult: 0x80004005 Description: "[Native Error code: 4002]
[DataDirect ADO Sybase Provider] Login failed.
".
Error: 0xC020801C at Data Flow Task, Source - Query [1]: The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" failed with error code 0xC0202009.
Error: 0xC0047017 at Data Flow Task, DTS.Pipeline: component "Source - Query" (1) failed validation and returned error code 0xC020801C.
Even when the the user, password and server configurations is fine. Does anyone have an idea about it ?
-
Is Sybase on the same host with SQL and your package? Are you using ASA or ASE? and what version? If not on the same host, are they on the same subnet? This was a problem for us - without the commlinks=tcpip parameter in the connection string it could not connect across the subnet. Silly question; but you did find and load the appropriate provider, right? Can't think of anything else at the moment; but I will watch for your response, as I have done quite a bit of this - although not so far as to register the package and run from a job.
Very interesting
-
thanks for you answer, Sql Server and the pakage to be registered is installed in a windows 2003 server with SQL2005 CTP JUNE, Sybase is in another server and both is in the same subnet. The drivers used is OLEDB FOR ASE SYBASE... i really don't know why is the problems because in the package i used the OLEDB driver and when i run it in Debug mode it works very well but when i generate the configuration file to register the package it doesn't.
-
Immediately I would have to say to ditch the June CTP. There was so much wrong with it that we put a lot of stuff on hold until we could get a newer product. The September CTP wasn't much better and if you were around you might have heard me turn into a sailor. The RTM/GA product is so much better that a whole lot of management here is finally convinced we will produce what is expected. If at all possible to install the new product on a clean OS you will experience no to few problems. If not possible, I understand that there is a new cleanup tool (not the one that shipped with the June CTP, or any CTP for that matter) that will clean all of the beta versions successfully. I have not tried it - very quickly becoming savvy with Ghost and reimaging
I would put a 90% chance of the problem disappearing with the newer software.
I will look forward to your posts since with have some things in common.
-
did this ever get resolved? I'm getting the same error and need to know how to resolve this.
-
I am using Sybase 12.5, XP, VS .NET 2005, using DAAB or normal DATA.* namespace. Connection Strings are below.
Inside your app.config or web.config; connectionStrings section:
*ASE ADO*
"Data Source=IpAddress;Port=PortNumber;UID=UserId;password=Password;Database=DatabaseName;"
*OLE DB*
"Provider=Sybase.ASEOLEDBProvider.2;Data Source=FileName.IDS;password=Password;database=DatabaseName;uid=UserId;"
*ODBC*
"Driver={SYBASE ASE ODBC Driver};NA=IpAddress,PortNumber;uid=UserId;password=Password;db=DatabaseName;"
example:
<add name="SybaseODBC" connectionString= [copy string from *ODBC* and replace the values]
providerName="System.Data.Odbc" />
For ASE ADO you have to create your connectionString as above and either use it directly in your APP (hardcode) or add it in your config file section.
-
I have been having a similar issue described earlier so tried the Sybase ASE OLE DB Provider and it kicked back an error that the provider(Sybase.ASEOLEDBProvider.2) is not registered on the local machine.
Let me know if I should post this in a seperate chain...
Thanks!
-
You might try setting the Pooling - OLE DB Services to EnableAll instead of Default.
It's under the Connection Manager - ALL (detailed connection properties).
Good luck!
-
I havent extracted from Sybase yet, but have to ask the question (and shoot me if I am wayyy off track), what is your package encryption set as, and are you using package configurations?
~PD
-
I think you're a little off of this topic as we were discussing Sybase connection properties.
Anyway, I'm using EncryptSensitiveWithUserKey, but I'm not actually using it because I don't have anything sensitive to encrypt.
As to your second question, I'm not using multiple configurations. I'm developing database packages for consolidating data to a data warehouse. Actually, I'm upgrading my DTS packages (from 2000) to SSIS packages and doing it from scratch instead of using the upgrade wizard. I save the packages to a file then import them to the SQL server.
-
An OLE DB record is available. Source: "ASE OLE DB Provider" Hresult: 0x80004005 Description: "Login Failed. Check for valid user ID, server name and password.".
Is this occurring when running on the local machine, or when you deploy to the server or both?
Are you using special authentication, or simply a userid and password?
The thing is, package encryption plays a role, as does package configurations.
When you create the package on the server, it may just be that the root cause is that it is "losing" part of your connectionstring (userid and password).
-
In the detailed connection properties (ALL), fill in the server name and server port information. Also fill in the user id and password. You might need the initial catalog (database you want to connect to).
You don't really need anything on the (CONNECTION) screen.
The connection can be renamed after it's created.
-
I see what you were talking about now.
I was trying to get the Sybase connection to work in BI, but once I did that, I still couldn't get the SSIS packages to execute in SQL Server.
A lot of articles I read said to run the SSIS packages from the command line, and that usually works because it circumvents a lot of security.
When I imported them into the MSDB in SQL Server, I couldn't schedule them as a job.
I had to log unto the actual server where I was going to deploy the SSIS packages as the user that I use for the SQL Server services. I had to build the SSIS packages (I used BI) on the server as that user. I then had to import them into the MSDB in SQL Server logged into SQL Server as the user that runs the SQL Server services. Basically, I connected to the integration servers while logged into the actual server running SQL Server and used windows authentication.
The "encryptsensitivewithuserkey" creates a "key" that depends on the PC and the user logged in. If you use this security setting then move the package to a different machine or deploy it with a different user, the "key" no longer works.
Also, since the SQL Server jobs run under the context of the user that's set to start the services, I think you have to import the packages to SQL Server logged in as that same user or the job can't connect all the dots.
As long as I used the same user security context all the way thru the entire process, I was able to run SQL server jobs using the Integration Services type step and not using file references and the CmdExec step type.
I did not get different configurations to work because I suppose the "key" issue. Multiple configurations aren't that advantageous to me on this job so I didn't work at it too much.
Sorry I dismissed your questions last week...it was all related. I just hadn't gotten that far in the process yet.
M
-
No prob,
I am actually offline from SSC for a while to finalise my project but thought I would just put in 5 cents today on this post....
Crever has made a video for package configurations, have a look at it.
Basically, you will need these to assist with your security. You need something to tell SSIS what the connectionstrings etc. are.
Good luck
~PD
-
Make sure you open SSIS package and save it using the same login that you are using to run the SQLAgent service under. The 'sensative data; that is being encrypted is the login to the Sybase server and that needs to be stored in the registry under the SQLAgent Service login.
Works for me.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply