problem with Excel Componnent - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

problem with Excel Componnent

Author

Topic

talg80
Starting Member

5 Posts

Posted - 2012-07-01 : 07:50:45

hi all,

i have a package that preform lode of excel file 97 from the web and save the excel file as 2007 workbook.

load File scriptTask:

public void Main()
{
try
{
// Logging start of download

bool fireAgain = true;
Dts.Events.FireInformation(0, "Download File", "Start downloading " + Dts.Variables["DownloadURL"].Value.ToString(), string.Empty, 0, ref fireAgain);

// Create a webclient to download a file
WebClient mySSISWebClient = new WebClient();

string url = "http://www.cbs.gov.il/reader/?MIval=%2Fprices_db%2FPriceInd_M_OneSeries_OneBase_H.html&Separated=11120010&MyCode=11120010&BasePeriods=28%2F01%2F2003&DataType=Ind&Years_1=2005&Years_2=2012&Months_1=1&Months_2=5&Subjects=38&MyPeriod=m&Radio1=1_3&FileType=2";
//string url = Dts.Variables["DownloadURL"].Value.ToString();

// Download file and use the Flat File Connectionstring (D:\SourceFiles\Products.csv)
// to save the file (and replace the existing file)
//mySSISWebClient.Proxy.ToString.GetProxy(http://aaa;)

mySSISWebClient.Proxy = new WebProxy("212.143.74.205",8080);

mySSISWebClient.Proxy.Credentials = CredentialCache.DefaultCredentials; ; // new NetworkCredential("amirv", "Bracha5%", "SNSMART");

ICredentials cred = CredentialCache.DefaultCredentials; ; // new NetworkCredential("user", "pass%", "dom");
mySSISWebClient.Credentials = cred;//new NetworkCredential("user", "pass%", "dom");

//mySSISWebClient.Proxy.GetProxy(WebProxy.GetDefaultProxy);

//mySSISWebClient.Credentials = CredentialCache.DefaultCredentials;

//mySSISWebClient.Proxy = WebProxy.GetDefaultProxy;
//wrq.Proxy.Credentials = CredentialCache.DefaultCredentials

//((Dts.Variables["DownloadURL"].Value.ToString(), Dts.Connections["Central Bureau of Statistics EXCEL File"].ConnectionString);

//mySSISWebClient.DownloadFile(Dts.Variables["DownloadURL"].Value.ToString(), Dts.Connections["Central Bureau of Statistics EXCEL File"].ConnectionString);
mySSISWebClient.DownloadFile(url, "D:\\MSSQL\\XML_File\\Central.xls");
//mySSISWebClient.DownloadFile(url, "Central.xls");

// Logging end of download
Dts.Events.FireInformation(0, "Download File", "Finished downloading " + Dts.Connections["Central Bureau of Statistics EXCEL File"].ConnectionString, string.Empty, 0, ref fireAgain);

// Quit Script Task succesful
Dts.TaskResult = (int)ScriptResults.Success;
}
catch (Exception ex)
{
// Logging why download failed
Dts.Events.FireError(0, "Download File", "Download failed: " + ex.Message, string.Empty, 0);

// Quit Script Task unsuccesful
Dts.TaskResult = (int)ScriptResults.Failure;

Convert to Excel 2007 file ScriptTask:

public void Main()
{
File.Delete("D:\\MSSQL\\XML_File\\Central.xlsx"); //Delte old file
Excel.Application excel = new Excel.Application();
Excel.Workbook wb = excel.Workbooks.Open("D:\\MSSQL\\XML_File\\Central.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
wb.SaveAs("D:\\MSSQL\\XML_File\\Central.xlsx", Excel.XlFileFormat.xlOpenXMLWorkbook, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
wb.Close(false, "D:\\MSSQL\\XML_File\\Central.xlsx", false);
}
}

when i run the package in VS its complete successfully, but when i treed to run it as a job i get the Error Below

Message
Executed as user: SNSMART\administrator. Microsoft (R) SQL Server Execute Package Utility Version 10.50.2500.0 for 32-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 5:26:11 PM Error: 2012-06-28 17:26:15.32 Code: 0x00000001 Source: Convert to 2007 Excel File Description: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Runtime.InteropServices.COMException (0x800A03EC): Microsoft Office Excel cannot access the file 'D:\MSSQL\XML_File\Central.xls'. 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.Open(String Filename, Object UpdateLinks, Object ReadOnly, Object Format, Object Password, Object WriteResPassword, Object IgnoreReadOnlyRecommended, Object Origin, Object Delimiter, Object Editable, Object Notify, Object Converter, Object AddToMru, Object Local, Object CorruptLoad) at ST_d539a0573c284cf99150d7804668e0d6.csproj.ScriptMain.Main() --- End of inner exception stack trace --- at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) at System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams) at System.Type.InvokeMember(String name, BindingFlags invokeAttr, Binder binder, Object target, Object[] args, CultureInfo culture) at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript() End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 5:26:11 PM Finished: 5:26:16 PM Elapsed: 4.274 seconds. The package execution failed. The step failed.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-01 : 11:49:08

are you sure service account the job uses have access to path D:\MSSQL\XML_File\Central.xls? also is it server path or path in remote computer?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

talg80
Starting Member

5 Posts

Posted - 2012-07-04 : 09:20:47

hi,
how can i check the service account have access?, its server path.

quote:
Originally posted by visakh16

are you sure service account the job uses have access to path D:\MSSQL\XML_File\Central.xls? also is it server path or path in remote computer?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-04 : 19:00:15

go to services.msc and check login that Integration services uses. then see if the login is included among group that has access to your file folder

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

talg80
Starting Member

5 Posts

Posted - 2012-07-05 : 08:51:44

hi,
i change the log on of ssis service to user with administrator rights,
and cheeked the folder and file security to see that they have reed wright access , i run the job and get the same error :

Code: 0x00000001 Source: Convert to 2007 Excel File Description: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Runtime.InteropServices.COMException (0x800A03EC): Microsoft Office Excel cannot access the file 'D:\MSSQL\XML_File\Central.xls'. 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.

quote:
Originally posted by visakh16

go to services.msc and check login that Integration services uses. then see if the login is included among group that has access to your file folder

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources