June 5, 2007 at 5:37 am
I am new to SSIS but I have created a number of DTS packages in the past. Here is a problem I have been struggling with for a few days. I am trying to migrate data from a set of SQL Server 2000 to a SQL Server 2005 implementation. The tables are not in 'true' relational form so I have to do an insert to get the base data and then do a series of updates to fill in the missing columns from serveral additional tables.
I created an SSIS package, using an OLE DB source (2000), a lookup data transformation and an OLE DB destination (2005), focusing on two columns of interest: stock ticker and date. These are the columns I am using to find the data in the source table that I want to write into the dimension table on the data warehouse server in SQL 2005. I am using a SQL command (SELECT with a WHERE clause) to find the matching rows. I am then using a SQL command (UPDATE) to write the data into the 2005 warehouse. When I execute the package, I get the following errors.
Error 1
Validation error. Import Moving Averages and Open Interest Data: OLE DB Command [3411]: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Statement(s) could not be prepared.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Could not find server 'SIR-Research' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.". Prices_MA_OI.dtsx 0 0
The 2000 server is actually set up on the 2005 as a linked server. Any ideas?
June 6, 2007 at 11:46 pm
Hi,
I found a few links to problems faced by others which are similar to yours. Hope you could find a solutions here:
https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1417123&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=925127&SiteID=1
http://www.eggheadcafe.com/forumarchives/sqlserverintegrationsvcs/feb2006/post25445179.asp
Regards,
Gogula
June 7, 2007 at 6:10 am
Gogula, thanks for the references. I don't think they solve my problem however. I found out that the 2003 server upon which my SQL Server 2005 implementation is sitting did not have its capability to perform remote transactions turned on. That may be the issue with the linked server. Although, it is still very confusing because the Object Explorer clearly shows that I have a linked server set up which is a SQL Server 2000 implementation. Also, I have been unable to look at the system table referenced by the error message - sysservers.
I haven't had a chance to test it just yet. I'm still running some massive data loads that take hours to complete. I will report on the outcome of my adjustments as soon as I can test it. Thanks again.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply