I am able to execute my SSIS via a batch file but I am unable to execute it in Visual Studio 2010. We are using this for our scheduled jobs so it is fine.
I am extracting records from SQL Server and generate to an Excel document. I am using a DTS Configuration File when I execute the batch file thus there are no errors.
However, I would like to run this in Visual Studio as well.
This is because the Excel Connection String in Visual Studio is as follows:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=xxx.xls;Extended Properties=HDR=YES;EXCEL 8.0;HDR=YES;
May I know why does HDR=YES appears twice and how do I to solve the issue above?
Connecting to Excel
The Microsoft Jet provider is used to connect to an Excel workbook. In the following connection string, the Extended Properties keyword sets properties that are specific to Excel. "HDR=Yes;" indicates that the first row contains column names, not data, and "IMEX=1;" tells the driver to always read "intermixed" data columns as text.
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\MyExcel.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1""Note that the double quotation character required for the Extended Properties must also be enclosed in double quotation marks.