asked on

SSIS Import Export Difficulties

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.

Steve Hogg

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.

wdarnellg

ASKER

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.

Steve Hogg

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.

wdarnellg

ASKER

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

Steve Hogg

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.

Steve Hogg

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.

wdarnellg

ASKER

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.

Steve Hogg

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

wdarnellg

ASKER

I have it saved. What details do I need to share with you?

wdarnellg

ASKER

Sorry about the previous post. Your last comment didn't show at first. I will get right on your suggestion.

wdarnellg

ASKER

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

ASKER CERTIFIED SOLUTION

Steve Hogg

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Steve Hogg

Here is the screenshot you should get after registering the dll.

regsvr32.bmp

wdarnellg

ASKER

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.

Steve Hogg

It is troubling that the reference noted the problem kept returning. At least this is a workarround. Good luck.