Created Excel Spreadsheet from SQL Stored Procedure - need help on next steps

I have been trying to do this all day. I followed your directions and everything seemed to set up fine, but when i go to the Data Flow I see a red x in the task to output to excel with the error message "SSIS Error Code DTS_E_OLEDBERROR. AN OLE Error has occurred. Error code: 0X80040e37..any idea of why this doesn't work???

Hi,

Thanks. I got everything working. Then the company that we are sending the file to informed me they wanted a text file, not an .xls file.

turned out to be relatively simple to do. I just changed the dataflow task to have a Flat File Destination instead of an excel Destination. I had to jump through a few hoops but I got it done.

My problem is dates in the source. I am writing the data I need to a source table with the dates defined as [surveyend_date] nvarchar NULL.

In my SQL table they are as I want, e.g. 053010, however in my .csv destination they show up as 53010. Is there any way I can get them the right way in the .csv file?

Thanks,

Dick

Hi Dick,

This should be able to help you.

1. Open your package that creates the Excel file.

2. Click on an empty space in the control flow (important step otherwise the variable will be out of scope). Then click on SSIS in the menu bar and select Variables.

3. Click on the leftmost button in the variable window to create a new variable.

4. Name the variable, I like to call mine with meaningful names and what type of variable they are, I called mine strExcelFile. Make sure the scope is set to the package name (go back to step 2 if it is not). Change the datatype to string, and leave the value blank.

5. While the variable is selected, press F4 to get the properties window, and change the property EvaluateAsExpression value to TRUE. Click on Expression in the properties window, and then click on the ellipses to get the expression builder window.

6. In the Expression Window copy, double quotes included "D:\\testssis\\FileName_" + SUBSTRING((DT_WSTR,30)GETDATE(),1,10)+".xls". This sets the path of the file, and the file name with a timestamp. The double backslashes are required. Click on the Evaluate Expression button and check to see if the Evaluated value = D:\testssis\FileName_2010-05-06.xls.

7. Click ok to get out of the Expression builder window, and then click on your Excel connection, click on Expressions in the properties of the excel connection, and click on the ellipses again to open the Property Expression Builder.

8. Click on the Property to get the drop down menu and select ExcelFilePath. Click on the ellipses to open the expression builder.

9. This step is easy because we already created the path in step 6. Expand the variables window on the left and select the USER variable you create in step 4. Click and drag the variable into the expression window. Then click on the Evaluate Expression button to make sure the Evaluated Value = D:\testssis\FileName_2010-05-06.xls. If it is not then go back to step 5 and make sure the EvaluateAsExpression value is set to TRUE.

10. What we have done here is we created a variable with a date stamp, and we are going to use that variable when the excel file is created so we don't need to worry about changing the name. You can test the package now by running it, and there should be a file created in the path you chose for the variable. If not, check the error messages and try again.

11. Now to the FTP task, Add a FTP task item to the control flow, and connect from the Data flow task that creates the excel file.

12. Click on the blank FTPConnection space and select . Enter the Servername, Username and password and I have always had to use passive mode, but you can test which mode you need. Test the connection and click OK.

13. Click on the File Transfer property on the left on the FTP task editor. In the Remote Path section, click on the white space and click on the ellipse and select the FTP path.

14. Change the IsLocalPathVariable in the Local Parameters to True, and set the LocalVariable property to the variable created in step 4. Click OK, and fingers crossed, that should be it.

15. Press F5 to start the package, and hopefully you will see all green. Depending on how you setup the wizard, you might have to delete the excel file from the local path and the FTP server.

Let me know if you run into any problems.

Dan.