Solved

SSIS Import Export Difficulties

Posted on 2008-10-24
15
2,953 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
Turn Insights Into Action

You’ve already invested in ITSM tools, chat applications, automation utilities, and more. Fortify these solutions with intelligent communications so you can drive business processes forward.

With xMatters, you'll never miss a beat.

 

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

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

717 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