Showing posts with label DTS. Show all posts
Showing posts with label DTS. Show all posts

Friday, April 25, 2014

System.Runtime.InteropServices.COMException (0x800A03EC): Microsoft Excel cannot access the file

I develop a package in SSIS, I need to use format excel sheets and I am sure many of you will using it because of business people needing data in pretty form. Any ways, till this part when I run from SSIS it works like a champ. But as soon as I scheduled in SQL agent on server it started to fail with below error.

Description: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Runtime.InteropServices.COMException (0x800A03EC): Microsoft Excel cannot access the file 'xxxxxlsx'. There are several possible reasons:    ? The file name or path does not exist.  ? The file is being used by another program.  ? The workbook you are trying to save has the same name as a currently open workbook.     at Microsoft.Office.Interop.Excel.Workbooks


After sometime when I was about to give up because the solutions I was seeing on the web either say go play with DcomConfig or hack registry keys yeah like some one is gonna let me do that on prod server.

Finally found MS document that hints the below procedure it works and best part don't need to mess with registry keys.

Solution(1)
Create folder called "Desktop" really dont know why but it works.
Create directory "C:\Windows\SysWOW64\config\systemprofile\Desktop” (for 64 bit Windows) or "C:\Windows\System32\config\systemprofile\Desktop” (for 32 bit Windows)
In my case I had created folder on both locations and give read write permissions on it

P.S
You will still see this error even if you have created the folder but the permissions are not correct.


Thursday, July 19, 2012

Execution cannot continue as the language dependent resource file

Arrrrrrr!! Yes that the first reaction came from my junior DBA. And I think it necessary as he is learning. Ok what to do now. Solution is straight and simple.

As you receive this error you will be prompted for  location where the SQL cannot find the required file.
Download those files easily available put in the folder specified and you are good to go.

Is nt this simple and straight? So Join our mailing for small but effective techniques.

DTS Packages in SQL 2005/2008

 I was given a task to import DTS packages in SQL 2008 which were imported successfully done.  However when my team tried to edit them, they would not open.   --The following error was displayed:  'SQL Server 2000 DTS Designer components are required to edit DTS packages. Install the special Web download, "SQL Server 2000 DTS Designer Components" to use this feature.  Microsoft.SqlServer.DtsObjectExplorerUI) .


This is so frustrating DTS package is imported and not opening I searched the web and didnt find much except people just trying to give their advice's for NONE that worked for me.


But afterwards I found an awesome article that can actually help many of the developer looking to import and convert old DTS into SSIS packages. But it didnt work well so I need to fix more things but that didn't mean the page was crap no it was good and gave alot of information. My blogger is having issue not uploading the link so pasting the information.







1) Install Sql Server 2000 DTS Designer Components using the following link

2) Install Sql Server 2005 backward compatibility components available with Microsoft SQL Server 2008 Feature Pack using the following link depending (x86,x64 and IA64)
3) We need to change the Environment Variable for the Server so that c:\Program files(x86)\Microsoft Sql Server\80\Tools\Binn is ahead and all the sql 2008 paths comes at the end and we can verify it by opening the command prompt and firing the command  “path”
C:\>path
PATH=C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\PROGRA~1\CA\SHARED~1\SCANEN~1;C:\Program Files\CA\eTrust Antivirus;C:\Program Files (x86)\Windows Imaging\;C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn\;C:\Program Files\Microsoft SQL Server\100\DTS\Binn\;C:\Program Files\Microsoft SQL Server\100\Tools\binn\;C:\Program Files (x86)\Microsoft SQL Server\100\Tools\binn\;C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\;C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\;C:\Program Files (x86)\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\
OR
If you are not able to change the path Environment variable you can follow the following steps
Manually Copy the following files
%Program Files%\Microsoft SQL Server\80\Tools\Binn\semsfc.dll
%Program Files%\Microsoft SQL Server\80\Tools\Binn\sqlgui.dll
%Program Files%\Microsoft SQL Server\80\Tools\Binn\sqlsvc.dll
%Program Files%\Microsoft SQL Server\80\Tools\Binn\Resources\%lang_id%\semsfc.rll
%Program Files%\Microsoft SQL Server\80\Tools\Binn\Resources\%lang_id%\sqlgui.rll
%Program Files%\Microsoft SQL Server\80\Tools\Binn\Resources\%lang_id%\sqlsvc.rll
To the following destination respectively
%Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\semsfc.dll
%Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\sqlgui.dll
%Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\sqlsvc.dll
%Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\%lang_id%\semsfc.dll
%Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\%lang_id%\sqlgui.dll
%Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\%lang_id%\sqlsvc.dll
%Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Resources\%lang_id%\semsfc.rll
%Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Resources\%lang_id%\sqlgui.rll
%Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Resources\%lang_id%\sqlsvc.rll
To make the DTS designer work under BIDS for SQL Server 2008, user will need to manually copy the following files
%Program Files%\Microsoft SQL Server\80\Tools\Binn\semsfc.dll
%Program Files%\Microsoft SQL Server\80\Tools\Binn\sqlgui.dll
%Program Files%\Microsoft SQL Server\80\Tools\Binn\sqlsvc.dll
%Program Files%\Microsoft SQL Server\80\Tools\Binn\Resources\%lang_id%\semsfc.rll
%Program Files%\Microsoft SQL Server\80\Tools\Binn\Resources\%lang_id%\sqlgui.rll
%Program Files%\Microsoft SQL Server\80\Tools\Binn\Resources\%lang_id%\sqlsvc.rll
To the following destination respectively
%Program Files%\Microsoft Visual Studio 9.0\Common7\IDE\semsfc.dll
%Program Files%\Microsoft Visual Studio 9.0\Common7\IDE\sqlgui.dll
%Program Files%\Microsoft Visual Studio 9.0\Common7\IDE\sqlsvc.dll
%Program Files%\Microsoft Visual Studio 9.0\Common7\IDE\Resources\%lang_id%\semsfc.rll
%Program Files%\Microsoft Visual Studio 9.0\Common7\IDE\Resources\%lang_id%\sqlgui.rll
%Program Files%\Microsoft Visual Studio 9.0\Common7\IDE\Resources\%lang_id%\sqlsvc.rll
4) Run the Repair for the sql server 2005 backward compatibility from Control Panel, Add/Remove Programs, Change
And now you should be able to open the DTS packages under SSMS.



For Trouble shooting if you have further problems Click this post 
 

Popular Posts