How do I access Excel data source from an SSIS package deployed on a 6. This step- by- step example is for others who might stumble upon this question. This example uses SSIS 2. SQL Server 2. 00.
I have an SSIS package that exports data to a couple of Excel files for transfer to a third party. To get this to run as a scheduled job on a 64-bit server I.
The answer here concentrates only on fixing the error message mentioned in the question. The example will demonstrate the steps to recreate the issue and also the cause of the issue followed by how to fix it. NOTE: I would recommend using the option of storing the package configuration values in database or using indirect XML configuration with the help of Environment Variables.
- Integration Services, a component of SQL Server, deals mainly with ETL – Extract, Transform and Load – operations. A package is like a macro-batch program.
- We often need to create similar SSIS packages which contain some common components such as connection managers, data flow components, log providers, event handlers.
- It is true that DBAs do everything in their capacity to protect the data in a database, but what about data that is loaded into Excel using an SSIS package and sent.
Learn how to implement Microsoft SQL Server Integration Services (SSIS) package logging and data auditing in a production environment. Use Business Intelligence. I recently had a chat with some BI developers about the design patterns they’re using in SSIS when building an ETL system. We all agreed in creating multiple.
Also, the steps to create Excel file would be done using a template which would then archived by moving to a different folder. These steps are not discussed in this post. As mentioned earlier, the purpose of this post is to address the error. Let. I have also blogged about this answer, which can be found in this link. It is the same answer. Create an SSIS package (Steps to create an SSIS package).
This example uses BIDS 2. I have named the package in the format YYYYMMDD. I am not saying that you should name your package like this. This is for me to easily refer this back later. Note that I also have a Data Sources named Adventure Works.
I will be using Adventure Works data source, which points to Adventure. Works database downloaded from this link. The example uses SQL Server 2.
R2 database. Refer screenshot #1. In the Adventure. Works database, create a stored procedure named dbo. Get. Currency using the below given script.
CREATE PROCEDURE . On the Select Data Source dialog, select Adventure Works and click OK. You should now see the Adventure Works data source under the Connection Managers section. On the package. This is to create the Excel connection. On the Add SSIS Connection Manager, select EXCEL. On the Excel Connection Manager, enter the path C: \Temp\Template.
When we deploy it to the server, we will change this path. I have selected Excel version Microsoft Excel 9. First row has column names checked so that the create the Excel file is created column headers. Rename the Excel connection to Excel, just to keep it simple.
Refer screenshots #2 - #7. On the package, create the following variable. Refer screenshot #8.
SQLGet. Data: This variable is of type String. This will contain the Stored Procedure execution statement. This example uses the value EXEC dbo. Get. Currency. Screenshot #9 shows the output of the stored procedure execution statement EXEC dbo. Get. Currency. On the package. Refer screenshot #1. Double- click on the Data Flow Task to switch to the Data Flow tab.
On the Data Flow tab, place an OLE DB Source to connect to the SQL Server data to fetch the data from the stored procedure and name it as SQL. Double- click on the OLE DB Source to bring up the OLE DB Source Editor. On the Connection Manager section, select Adventure Works from the OLE DB connection manager, select SQL command from variable from Data access mode and select the variable User: :SQLGet.
Data from the Variable name drop down. On the Columns section, make sure the column names are mapped correctly. Click OK to close the OLE DB Source Editor. Refer screenshots #1. On the Data Flow tab, place an Excel Destination to insert the data into the Excel file and name it as Excel. Double- click on the Excel Destination to open the Excel Destination Editor. On the Connection Manager section, select Excel from the OLE DB connection manager and select Table or view from Data access mode.
At this point, we don. On the Create Table window, BIDS automatically provide a create sheet based on the incoming data source.
You can change the values according to your preferences. I will simply click OK by retaining the default value. The name of the sheet will be populated in the drop down Name of the Excel sheet. The name of the sheet is taken from the task name, here in this case the Excel Destination, which we have named it as Excel. On the Mappings section, make sure the column names are mapped correctly. Click OK to close the Excel Destination Editor. Refer screenshots #1.
Once the data flow task is configured, it should look like as shown in screenshot #1. Execute the package by pressing F5. Screenshots #1. 8 - #2. Control Flow and Data Flow Task. Also, the file is generated in the path C: \Temp\Template. Excel connection and the data shown in the stored procedure execution output matches with the data written to the file. The package developed on my local machine in the folder path C: \Learn\Learn.
VS2. 00. 5\Learn. SSIS. Now, we need to deploy the files on to the Server that hosts the 6. SQL Server to schedule a job.
So, the folder on the server would be D: \SSIS\Practice. Copy the package file (. Also, in order for the package to run correctly, we need to have the Excel spreadsheet present on the server. Otherwise, the validation will fail. Usually, I create a Template folder that will contain the empty Excel spreadsheet file that matches the output. Later, during run time I will change the Excel output path to a different location using package configuration. For this example, I am going to keep it simple.
I want the SQL job to generate the file in the name Currencies. So, rename the file Template. Currencies. xls. Refer screenshot #2. To show that I am indeed going to run the job on the server in a 6. SQL Server, I executed the command SELECT @@version on the SQL Server and screenshot #2. We will use Execute Package Utility (dtexec. Log into the server which will run the SSIS package in an SQL job.
Double- click on the package file, this will bring the Execute Package Utility. On the General section, select File system from Package source. Click on the Ellipsis and browse to the package path. On the Connection Managers section, select Excel and change the path inside the Excel file from C: \Temp\Template.
D: \SSIS\Practice\Currencies. The changes made in the Utility will generate a command line accordingly on the Command Line section. On the Command Line section, copy the Command line that contains all the necessary parameters. We are not going to execute the package from here. Refer screenshots #2. Next, we need to set up a job to run the SSIS package. We cannot choose SQL Server Integration Services Package type because that will run under 6.
So, we have to run it as Operating System (Cmd. Exec) job type. Go to SQL Server Management Studio and connect to the Database Engine. Expand SQL Server Agent and right- click on Jobs node. On the General section of the Job Properties window, provide the job name as 0. I have a Category named SSIS, so I will select that but the default category is . On the Steps section, click New.
This will bring Job Step properties. On the General section of the Job Step properties, provide Step name as Export to Excel, Select type Operating system (Cmd. Exec), leave the default Run as account as SQL Server Agent Service Account and provide the following Command. On the New Job window, Click OK. Refer screenshots #2.
C: \Program Files (x. Microsoft SQL Server\9. DTS\Binn\DTExec. exe /FILE. Right- click on the newly created job 0. The job will fail as expected because that is the context of this issue. Click Close to close the Start Jobs dialog.
Refer screenshots #3. Let. Go to SQL Server Agent and Jobs node. Right- click on the job 0. This will bring the Log File Viewer window. You can notice that the job failed. Expand the node near the red cross and click on the line that Step ID value of 1.
Click Close to close the Log File Viewer window. Refer screenshots #3. Now, right- click on the job and select Properties to open the Job Properties. You can also double- click on the job to bring the Job Properties window.
Click on the Steps on the left section. Replace the command with the following command and click OK. Click OK on the Job Properties to close the window. Right- click on the job 0. The job will fail execute successfully. Click Close to close the Start Jobs dialog. Right- click on the job 0.
This will bring the Log File Viewer window. You can notice that the job succeeded during the second run. Expand the node near the green tick cross and click on the line that Step ID value of 1. Click Close to close the Log File Viewer window.
The file D: \SSIS\Practice\Currencies. If you execute the job successfully multiple times, the data will get appended to the file and you will find more data. As I mentioned earlier, this is not the right- way to generate the files. This example was created to demonstrate a fix for this issue.
Refer screenshots #3. Screenshot #3. 9 shows the differences between the working and the non- working command line arguments. The one on the right is the working command line and the left one is incorrect.
It required another double quotes with backslash escape sequence to fix the error. There could be other ways to fix this well but this option seems to work. Thus, the example demonstrated a way to fix the command line argument issue while accessing Excel data source from an SSIS package that is deployed on a 6. Hope that helps someone. Screenshots: #1: Solution.