May 30, 2008 at 8:11 am
I have problem when transferring the data from lotus notes db to SQL server 2005 db. The SSIS package transferring only 2000 records out of 10000 records and there is no error message is showing.
The same package in SQL server 2000 is working fine and transferring total of total rows.
There is no filter or where clause in the package. The package design is like this:
connection managers:
.Net provider\ODBC data provider -> Lotus notes DB
Native OLE DB\SQL Native Client -> SQL Server 2005 DB
1) Data Flow task:
source -> Data reader source connected to Lotus notes DB. Here query is select * from Admin_package
Destination -> Raw file Destination saving it in local disk.
2) Execute SQL task: I have given a query here to empty the Destination table as TRUNCATE TABLE Admin_Package.
3) Data Flow task:
Source -> Raw file source taking the saved file from local disk
Destination -> OLE DB Destination connected the SQL db table (Admin_package)
There is no query here.
In the first step it self , the total records are not coming to Raw file. Only 2500 records coming from the total of 10000 records.
__________________________________________________________________
In SQL server 2000, the design is different. But it worked correctly. The design is:
source connection as OTHERS -> lotus notes db.
Destination connection as Microsoft OLE db for SQL server
Transform data task: set up as
Source: lotus notes view/table (Admin_Package)
Destination: table of SQL db (Admin_Package)
Thank You
June 1, 2008 at 11:00 pm
hey Venki,
Not that I have ever queried Lotus Notes DB (actually, first time that I heard that something like that exists....), but are you sure that you are querying the same table?
In a specific Datastage scenario, the developer swore high and low that they were querying the right resultset, but then we discovered that he wasnt querying the dbo... table, hence the results were different.
In all likelihood not related, but worthwhile just checking this first
~PD
June 2, 2008 at 6:24 am
Thanks for your response. Yes I am quering the right table.
When I link that table to MS Access db and checking then it is showing the 10000 records. Linking to Access db was done using ODBC connection.
Thank You
June 2, 2008 at 6:27 am
What driver are you using in SSIS to query lotus notes db with?
June 2, 2008 at 6:51 am
I configured the connection manager as
.Net provider/ODBC data provider
If I try to configure with ODBC connection the it is showing error. The error is attached.
I found in one of our forums that we have to use .Net provider only to connect to Lotus notes db.
Thank You
June 2, 2008 at 7:07 am
Unfortunately this is beyond what I can help you with.
I would suggest you read up on your .NET provider documentation
June 3, 2008 at 2:39 am
Please, any body help me out?
Thank You
June 3, 2008 at 2:44 am
hey,
Apologies for the short reply yesterday (my bad, had a deadline and one very irate client to deal with...)
Here is a posting which looks similar to the problems you are experiencing
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=3368930&SiteID=17
Please advise if this will help solve your problem
~PD
June 3, 2008 at 2:54 am
🙂 Thanks a lot for your response. But I only possed that questions there. Venkat Thota is me only. There also no body responding. No body aware with this type of problem.
Thank You
June 3, 2008 at 1:03 pm
Try using OpenRowSet with a DSN-less connection string instead of going through ODBC to get to the Notes db.
Don't know if this will work, but it might avoid the managed connection manager that's giving you fits.
Steve G.
June 4, 2008 at 12:57 am
Also, try and create a linked server to the Lotus Notes DB.
Then you can use openqueries. This will allow you to use a normal OLEDB source
Sorry, not much help on the root of the problem, but sometimes we have to use workarounds if all else fails
~PD
June 4, 2008 at 3:33 am
Open Record set means, I didn't understand. I think as per my knowledge, we can not connect to Lotus notes DB with out DSN.
If you let me know the procedure what you are talking about then I will give a try and check.
Thank You
June 4, 2008 at 7:52 am
Sorry, I am new to this database it self.
I am not clear about link server. Did you mean you want me to register the lotus notes Domino Server into my SQL Server Management Studio? Please explain so that I will learn it and it will be good to recap for you.
Thanks a lot for your support.
Thank You
June 4, 2008 at 11:42 am
Hey,
The more I read up on your specific problem, the more the hair on my neck stands on end...
I have honestly never heard of Lotus Notes DB before. And then I read that its not even relational (so what is it? hierarchical? dimensional? Anyone from IBM reading this may want to chip in with a few comments)
I am not even sure this will resolve your problem, but its worthwhile trying....
Here is an article that you also may want to look at, as it explains all the ODBC related stuff for Lotues Notes database.
You will need to have some kind of ODBC driver installed in order to create a linked server.
In SSMS, expand the Server Objects, and right click on the linked servers.
Select the New Linked Server, and change this to "Other data source".
When you name it, give it a nice descriptive name. For arguments sake, lets say "venki_notes"
As indicated before, I am not really sure what the correct settings are for Lotus DB, but the site above may assist.
On the security, change this to "Be made with this security context", and supply the credentials that you use to log into the Lotus DB.
If you have come this far and SQL hasnt complained yet, there may be light at the end of the tunnel.
Now, once you have a linked server set up, the query becomes very easy
Select * from openquery(venki_notes, 'Select * from venkistablethatismissingrows') (if my syntax is wrong, please dont shoot me - read up on it)That is your first check. That you can see the data.
- Be careful regarding dates. From my linking to DB2 server days, I can recall that MS and DB2 sometimes have issues talking to one another when it comes to dates...
Your second check Select count(*) from openquery(venki_notes, 'Select * from venkistablethatismissingrows')
That is your second check. That SQL reads the correct amount of rows from your table (the crux of your problem - your rowcount was x previously and it is now y).
If you have come this far, you definitely have a winner. I recently helped a client who didnt want to fork out the cash for a JDBC .NET client provider, and we set up a linked server, which allowed him still to use the rest of the wonderful features of SSIS. Do the following:
- Create an OLEDB source. Change it to a SQL query: Select * from openquery(venki_notes, 'Select * from venkistablethatismissingrows')
TADA, you are reading from a linked server pointing to a Lotus DB in SSIS
Really man, good luck on this one, wish I could help you more, but yours is indeed a very unique scenario.
~PD
Anyone else have any other suggestions?
June 9, 2008 at 8:35 am
pduplessis (6/4/2008)
Hey,The more I read up on your specific problem, the more the hair on my neck stands on end...
I have honestly never heard of Lotus Notes DB before. And then I read that its not even relational (so what is it? hierarchical? dimensional? Anyone from IBM reading this may want to chip in with a few comments)
I am not even sure this will resolve your problem, but its worthwhile trying....
Here is an article that you also may want to look at, as it explains all the ODBC related stuff for Lotues Notes database.
You will need to have some kind of ODBC driver installed in order to create a linked server.
In SSMS, expand the Server Objects, and right click on the linked servers.
Select the New Linked Server, and change this to "Other data source".
When you name it, give it a nice descriptive name. For arguments sake, lets say "venki_notes"
As indicated before, I am not really sure what the correct settings are for Lotus DB, but the site above may assist.
On the security, change this to "Be made with this security context", and supply the credentials that you use to log into the Lotus DB.
If you have come this far and SQL hasnt complained yet, there may be light at the end of the tunnel.
Now, once you have a linked server set up, the query becomes very easy
Select * from openquery(venki_notes, 'Select * from venkistablethatismissingrows') (if my syntax is wrong, please dont shoot me - read up on it)That is your first check. That you can see the data.
- Be careful regarding dates. From my linking to DB2 server days, I can recall that MS and DB2 sometimes have issues talking to one another when it comes to dates...
Your second check Select count(*) from openquery(venki_notes, 'Select * from venkistablethatismissingrows')
That is your second check. That SQL reads the correct amount of rows from your table (the crux of your problem - your rowcount was x previously and it is now y).
If you have come this far, you definitely have a winner. I recently helped a client who didnt want to fork out the cash for a JDBC .NET client provider, and we set up a linked server, which allowed him still to use the rest of the wonderful features of SSIS. Do the following:
- Create an OLEDB source. Change it to a SQL query: Select * from openquery(venki_notes, 'Select * from venkistablethatismissingrows')
TADA, you are reading from a linked server pointing to a Lotus DB in SSIS
Really man, good luck on this one, wish I could help you more, but yours is indeed a very unique scenario.
~PD
Anyone else have any other suggestions?
Thanks a lot for yor suggestions. I will try this.
Thank You
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply