?
Solved

SSIS Import Export Difficulties

Posted on 2008-10-24
15
Medium Priority
?
2,967 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

 

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
 
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 2000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

764 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