This step-by-step example is for others who may stumble upon this question. This example uses SSIS 2005 and uses the 64-bit version of SQL Server 2005 to complete the job.
The answer here focuses only on fixing the error message mentioned in the question. In this example, steps will be taken to recreate the problem, as well as the cause of the problem, which should be fixed.
NOTE: I would recommend using the option to save package configuration values in a database or using an indirect XML configuration using environment variables. In addition, the steps to create an Excel file will be performed using a template, which will then be archived by going to another folder. These steps are not discussed in this post. As mentioned earlier, the purpose of this message is to resolve the error.
Let's move on to the example. I also wrote about this answer, which can be found in this link . This is the same answer.
Create the SSIS package ( Steps to Create an SSIS Package ). This example uses BIDS 2005. I named the package in the format YYYYMMDD_hhmm at the beginning, followed by SO, for Stack Overflow, followed by the identifier SO, and finally a description. I am not saying that you should call your package like this. It is for me to easily attribute it later. Please note that I also have data sources called Adventure Works. I will use the Adventure Works data source, which points to the AdventureWorks database downloaded from this link . This example uses the SQL Server 2008 R2 database. See Screenshot # 1 .
In the AdventureWorks database, create a stored procedure called dbo.GetCurrency using the script below.
CREATE PROCEDURE [dbo].[GetCurrency] AS BEGIN SET NOCOUNT ON; SELECT TOP 10 CurrencyCode , Name , ModifiedDate FROM Sales.Currency ORDER BY CurrencyCode END GO
In the "Package Connection Manager" section, right-click and select "New connection from data source." In the Select Data Source dialog box, select Adventure and click OK. You should now see the Adventure Works data source in the Connection Managers section.
In the "Package Connection Managers" section, right-click again, and this time select "New Connection ...". This is done to create an Excel connection. In the Add SSIS Connection Manager, select EXCEL. In Excel Connection Manager, enter the path C: \ Temp \ Template.xls. When we deploy it to the server, we will change this path. I chose the Excel version of Microsoft Excel 97-2005 and decided to leave a check mark. The first line contains the column names, so column headers are created to create the Excel file. Click OK. Rename the Excel to Excel connection just to make it simple. See Screenshots # 2 - # 7 .
In the package, create the following variable. See Screenshot # 8 .
- SQLGetData: This variable is of type String. This will contain the statement to execute the stored procedure. This example uses the EXEC value dbo.GetCurrency
Screenshot # 9 shows the result of executing an EXEC execution instruction dbo.GetCurrency
On the Package Management Flow tab, place the Data Flow task and name it Export to Excel. See Screenshot # 10 .
Double-click on the Data Stream Task to go to the Data Stream tab.
On the Data Flow tab, place OLE DB Source to connect to SQL Server data to retrieve data from the stored procedure and name it SQL. Double-click the OLE DB source to open the OLE DB source editor. In the "Connection Manager" section, select "Adventures" from the OLE DB connection manager, select the SQL command from the variable from the data access mode, and select the User :: SQLGetData variable from the "Variable Name" drop-down list. In the Columns section, verify that the column names are mapped correctly. Click OK to close the OLE DB Source Editor. See Screenshots # 11 and # 12 .
On the Data Flow tab, place Excel Destination to insert data into an Excel file and name it as Excel. Double-click on the Excel destination to open the Excel destination editor. In the "Connection Manager" section, select "Excel" from the OLE DB Connection Manager and select "Table" or "View from Data Access Mode." At the moment, we do not have Excel, because when creating the Excel Connection Manager, we simply specified the path, but never created the file. Therefore, there will be no values in the drop-down name of the Excel sheet. So, click the "Create ..." button (second new) to create a new Excel sheet. In the Create Table window, BIDS automatically creates a creation sheet based on an incoming data source. You can change the values according to your preference. I just click OK, keeping the default value. The sheet name will be populated in the "Excel Sheet Name" drop-down list. The sheet name is taken from the task name, here in this case the Excel destination is used, which we named it Excel. In the Mapping section, verify that the column names are mapped correctly. Click OK to close the Excel Destination Editor. See Screenshots # 13 - # 16 .
Once the data flow task is configured, it should look like the one shown in screenshot # 17 .
Run the package by pressing F5. Screenshots # 18 - # 21 show the successful execution of the package both in the control stream and in the data stream. In addition, the file is created in the path C: \ Temp \ Template.xls provided in the Excel connection, and the data displayed in the output of the stored procedure matches the data written to the file.
A package developed on my local machine in the folder C: \ Learn \ Learn.VS2005 \ Learn.SSIS. Now we need to deploy the files to the Server that hosts the 64-bit version of SQL Server to schedule the job. So, the folder on the server will be D: \ SSIS \ Practice. Copy the package file ( .dtsx ) from the local computer and paste it into the server folder. In addition, for the package to work correctly, we need to have an Excel spreadsheet present on the server. Otherwise, the check will fail. Usually I create a Template folder that will contain an empty Excel spreadsheet file corresponding to the output. Later, at runtime, I will change the Excel output path to another location using the package configuration. In this example, I'm going to keep it simple. Thus, copy the Excel file generated on the local computer to the path C: \ Temp \ Template.xls to the server location D: \ SSIS \ Practice. I want the SQL job to generate a file called Currencies.xls. So, rename the Template.xls file to Currencies.xls. See Screenshot # 22 .
To show that I actually ran the job on the server in the 64-bit version of SQL Server, I ran the SELECT @@ version command on SQL Server and screenshot # 23 shows the results.
We will use the Execute Package utility (dtexec.exe) to generate command line parameters. Log in to the server that will run the SSIS package in the SQL job. Double-click the package file, this will result in the Execute Package Utility. In the "General" section, select "File System" from the package source. Click on Ellipsis and go to the package path. In the "Connection Managers" section, select "Excel" and change the path in the Excel file from C: \ Temp \ Template.xls to D: \ SSIS \ Practice \ Currencies.xls. Changes made to the Utility will generate a command line, respectively, in the "Command Line" section. In the "Command line" section, copy the command line containing all the necessary parameters. We are not going to execute the package from here. Click "Close." See Screenshots # 24 - # 26 .
Then we need to configure the job to run the SSIS package. We cannot choose the type of Integration Services SQL Server package because it will work under the 64-bit version and will not be able to find the provider for connecting to Excel. Therefore, we must run it as an Operating System (CmdExec) job type. Go to SQL Server Management Studio and connect to the database engine. Expand SQL Server Agent and right-click on Jobs node. Select New Task .... In the General section of the Task Properties window, specify the name of the task as 01_SSIS_Export_To_Excel. The owner will be the user creating the task. I have a category called SSIS, so I will choose this, but the default category is [Uncategorized (Local)] and will provide a brief description. In the "Steps" section, click the "Create ..." button. This will result in Job Step properties. In the "General settings for job properties" section, specify the name of the step as "Export to Excel", select "Type" Operating System (CmdExec) , leave the Run Operating System (CmdExec) account for the SQL Server service account, and provide the following command. Click OK. In the "New Task" window, click "OK." See Screenshots # 27 - # 31 .
C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe /FILE "D:\SSIS\Practice\20110723_1015_SO_21448_Excel_64_bit_Error.dtsx" /CONNECTION Excel;"\"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\SSIS\Practice\Currencies.xls;Extended Properties=""EXCEL 8.0;HDR=YES"";\"" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EWCDI
A new job should appear under the SQL Server Agent -> Jobs node. Right-click on the newly created task 01_SSIS_Export_To_Excel and select "Start work at the stage ...", this will start the task. The work will not work as expected, because this is the context of this problem. Click Close to close the Start Jobs dialog box. See Screenshots # 32 and # 33 .
Let's see what happened. Go to the SQL Server Agent and node jobs. Right-click on job 01_SSIS_Export_To_Excel and select View History. This will bring up the log file viewing window. You may notice that the work failed. Expand the node next to the red cross and click on the line in which the identifier value of step 1. At the bottom you will see the error message Option "8.0;HDR=YES';" is not valid. Option "8.0;HDR=YES';" is not valid. . Click Close to close the View Log Files window. window. See Screenshots # 34 and # 35 .
Now right-click on the task and select "Properties" to open the properties of the task. You can also double-click on a task to open the "Task Properties" window. Click "Steps" on the left. and click "Edit." Replace the command with the following command and click OK. Click “OK” in “Job Properties” to close the window. Right-click on the task 01_SSIS_Export_To_Excel and select "Start work at the stage ...", this will start the task. Work will fail. Click Close to close the Start Jobs dialog box. Let's take a look at the story. Right-click on job 01_SSIS_Export_To_Excel and select View History. This will bring up the log file viewing window. You may notice that the task was completed during the second run. Expand the node next to the green cross and click on the line in which the identifier value of step 1 is. At the bottom you will see the Option message. The step was successful. Click Close to close the View Log File window. The file D: \ SSIS \ Practice \ Currencies.xls will be successfully filled with data. If you successfully completed the task several times, the data will be added to the file and you will find more data. As I mentioned earlier, this is not the right way to generate files. This example was created to demonstrate a fix for this problem. See Screenshots # 36 - # 38 .
Screenshot # 39 shows the differences between working and non-working command line arguments. The rule is the working working line, and the left is incorrect. To correct the error, two more double quotes were required with the backslash escape sequence. There may be other ways to fix this, but this option seems to work.
Thus, the example showed how to fix the problem with the command line argument when accessing the Excel data source from the SSIS package deployed on a 64-bit server.
Hope this helps someone.
Screenshots:
# 1: Solution_Explorer
# 2: New_Connection_Data_Source
# 3: Select_Data_Source
# 4: New_Connection
# 5: Add_SSIS_Connection_Manager
# 6: Excel_Connection_Manager
# 7: Connection_Managers
# 8: Variables
# 9: Stored_Procedure_Output
# 10: Control_Flow
# 11: OLE_DB_Source_Connections_Manager
# 12: OLE_DB_Source_Columns
<T411>
# 13: Excel_Destination_Editor_New
# 14: Excel_Destination_Create_Table
# 15: Excel_Destination_Edito
# 16: Excel_Destination_Mappings
# 17: Data_Flow
# 18: Successful_Package_Execution_Control
# 19: Successful_Package_Execution_Data_Flow
# 20: C_Temp_File_Created
# 21: Data_Populated
# 22: File_On_Server
# 23: SQL_Server_Version
# 24: Execute_Package_Utility_General
# 25: Execute_Package_Utility_Connection_Manager
# 26: Execute_Package_Utility_Command_Line
# 27: Job_New_Job
# 28: New_Job_General
# 29: New_Job_Step
# 30: New_Job_Step_General
# 31: New_Job_Steps_Added
# 32: Job_Start_Job_at_Step
# 33: SQL_Job_Execution_Failure
# 34: View_History
# 35: SQL_Job_Error_Message
# 36: SQL_Job_Execution_Success
# 37: SQL_Job_Success_Message
# 38: Excel_File_Generated
# 39: Command_Comparison