Solved

SSIS Import Export Difficulties

Posted on 2008-10-24
15
2,916 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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS SQL - Rotating Values in SQL 9 52
TSQL previous 5 24
Need a starter for ETL protocol? 4 42
SQL Server Update Query Streamline 4 10
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

910 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

19 Experts available now in Live!

Get 1:1 Help Now