Solved

SSIS Import Export Difficulties

Posted on 2008-10-24
15
2,911 Views
Last Modified: 2013-11-10
I am trying to use the import export feature of sql server 2005 from my localhost installation to move tables to a server on my host environment. I am gettting errors in sequence that read:

ERROR : errorCode=1073548...  description=Esecuting the query ""failed with the following error: "Retrieving the COM class factory for component with CLSID{7816B7A...} failed due to the following error: 80040154". Possible failure reasons: Problems with the query. "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I have never used this feature, and it seems like it could be a great one if I can get it to work. I am sure it must be something on my machine since I had the same problems with tests on my localhost, but I was hoping that they would resolve if I tried to export to a different server. I hope I am not too vague here. I simply select the tables, click on next, and the first 5 lines execute successfully then line 6 (Execute the transfer....) fails on down the line.
I would appreciate any assistance.. Thank you.

P.S. I am not sure it matters, but I am using a Vista machine.
0
Comment
Question by:wdarnellg
  • 8
  • 7
15 Comments
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22800649
First reccomendation, instead of selecting the Execute Immediately option - save it to a SSIS Package and debug it in the Business Intelligence Development Studio. In BIDS you will see warnings and get better error messages.
0
 

Author Comment

by:wdarnellg
ID: 22801317
I am trying to save to a SSIS Package, but I am getting an error:


TITLE: SQL Server Import and Export Wizard
------------------------------

The operation could not be completed.

------------------------------
ADDITIONAL INFORMATION:

The ExistsOnSQLServer method has encountered OLE DB error code 0x80040E09 (The EXECUTE permission was denied on the object 'sp_dts_checkexists', database 'msdb', schema 'dbo'.). The SQL statement issued has failed.


0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22804499
That error is caused by oe of two issues. Either you do not have an SQL Server save location created on msdb or you do not have permissios to save there. Instead, save to the File System - not the SQL Server. A file is what you need to open in BIDS anyway.
0
 

Author Comment

by:wdarnellg
ID: 22804935
Maybe I just don't have this installed correctly. I can access all of my databases using Visual Studio and my dotnetnuke portals. I can write queries and such, but I can't seem to use this Import / Export Wizard.

Even with trying to save to the file system I get the same failure error. I have attached a screenshot in a word document.
SSISScreenShot.doc
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22805815
What I need you to do is save to File System only. Do not Execute Immediately. Once you save it to a file we can look at the details of this error inside the dtsx package file.
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22805838
I am trying to research the error as well. This guy says the problem was solved when he started the SQL Server Agent. I wouldn't have thought that, but something to check.
http://www.sqlnewsgroups.net/group/microsoft.public.sqlserver.server/topic24315.aspx
Still should save it to an SSIS Package file so we can see more details.
0
 

Author Comment

by:wdarnellg
ID: 22808498
I apologize. A package was saved. I just saw the errors and assumed one didn't. What kind of details should I look for? It shows the same error run in debug mode.
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 17

Expert Comment

by:HoggZilla
ID: 22808879
In the output window, can you copy the complete details and paste here. As well, there may be some warnings or errors in the error window. Paste here if exists. Thanks
0
 

Author Comment

by:wdarnellg
ID: 22809222
I have it saved. What details do I need to share with you?
0
 

Author Comment

by:wdarnellg
ID: 22809229
Sorry about the previous post. Your last comment didn't show at first. I will get right on your suggestion.
0
 

Author Comment

by:wdarnellg
ID: 22809243
Here is the copy/paste from the output window:


SSIS package "ADW16.dtsx" starting.
Error: 0xC002F210 at Preparation SQL Task, Execute SQL Task: Executing the query "" failed with the following error: "Retrieving the COM class factory for component with CLSID {7816B7A3-CD60-4539-BD38-C35AFC61F200} failed due to the following error: 80040154.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Preparation SQL Task
SSIS package "ADW16.dtsx" finished: Failure.
The program '[5228] ADW16.dtsx: DTS' has exited with code 0 (0x0).
0
 
LVL 17

Accepted Solution

by:
HoggZilla earned 500 total points
ID: 22809311
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2101747&SiteID=1
At the command line:
regsvr32  SQLTaskConnections.dll
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22809354
Here is the screenshot you should get after registering the dll.

regsvr32.bmp
0
 

Author Comment

by:wdarnellg
ID: 22809386
Take the 500 points. All systems are green!!

I will keep studying all of this, but this little problem is seems to be solved.
Thank you.
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22809614
It is troubling that the reference noted the problem kept returning. At least this is a workarround. Good luck.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now