Convert Excel File Problem
yes, Im reading part of the data into a table.
July 1st, 2012 4:33am
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.
Free Windows Admin Tool Kit Click here and download it now
July 1st, 2012 8:16am
Well, it says it is failing when trying to open the file on this line
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);
So the questions are - does the file exist or do you already have it open?Chuck Pedretti | Magenic North Region | magenic.com
July 1st, 2012 8:25am
the file exists in the right place in the right format the old file that im deleting in the script was deleted ,so i know that the script is working until the line :
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);
what i don't understand is why in VS it runs OK and when i run the package as a job its not OK, ???
Free Windows Admin Tool Kit Click here and download it now
July 1st, 2012 8:56am
Later in your package are you attempting to use that file?Chuck Pedretti | Magenic North Region | magenic.com
July 1st, 2012 8:58am
Maybe its permission issue?
Free Windows Admin Tool Kit Click here and download it now
July 4th, 2012 9:42am
Hi talg80,
It is maybe caused by the permission issue, When you see a SSIS package fails running in a SQL Agent job, you need to first consider the following conditions:
1. The user account that is used to run the package under SQL Server Agent differs from the original package author.
2. The user account does not have the required permissions to make connections or to access resources outside the SSIS package.
For more information about it, please see:
http://social.technet.microsoft.com/Forums/en-US/sqlintegrationservices/thread/e13c137c-1535-4475-8c2f-c7e6e7d125fc
Thanks,
Eileen
July 4th, 2012 11:31pm
Hi talg80,
It is maybe caused by the permission issue, When you see a SSIS package fails running in a SQL Agent job, you need to first consider the following conditions:
1. The user account that is used to run the package under SQL Server Agent differs from the original package author.
2. The user account does not have the required permissions to make connections or to access resources outside the SSIS package.
For more information about it, please see:
http://social.technet.microsoft.com/Forums/en-US/sqlintegrationservices/thread/e13c137c-1535-4475-8c2f-c7e6e7d125fc
Thanks,
Eileen
Free Windows Admin Tool Kit Click here and download it now
July 4th, 2012 11:48pm