question

Dick Rosenberg avatar image
Dick Rosenberg asked

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

Hi,

I am writing an SSIS package. I am fairly new to SSIS (2005 if that makes a difference). I have taken a course but they have never addressed this particular situation.

I have to:

1 - Create an Excel spreadsheet from a stored procedure

2 - Rename it to a date stamped file name

3 - Copy it to an FTP server

I have been able to accomplish #1 with the help of the Import/Export wizard, so now I have an Excel sheet with a fixed name. I can't get # 2 to work. I think I want to use a File System task to rename the file to the date stamped version and capture the name of the file in a variable so that I can use it in the FTP Task, but I can't figure out how to do this (or even if my approach is correct).

Any Help?

Thanks,

Dick Rosenberg

ssisexcel
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
Would love to help, but SSIS is not my strong suit, so I don't really know - but +1 for a well stated question.
0 Likes 0 ·
Daniel Ross avatar image
Daniel Ross answered

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.

2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image Fatherjack ♦♦ commented ·
daniel, back slash is the escape character in the wysiwyg editor - there isnt a double back slash showing in your answer. Nice comprehensive answer though. nice one. +1
0 Likes 0 ·
Daniel Ross avatar image Daniel Ross commented ·
Oh, I didn't check the output from the editor. Nice pickup, thanks Jack
0 Likes 0 ·
Dick Rosenberg avatar image
Dick Rosenberg answered

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

2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TimothyAWiseman avatar image TimothyAWiseman commented ·
You have a couple of options, but probably the best is to use convert to convert to string and set the display format the way you want it to appear.
0 Likes 0 ·
Dick Rosenberg avatar image Dick Rosenberg commented ·
I don't understand. I'm creating the flat file on the fly as a Flat File Destination in the Data Flow Task. Where/how would I convert it to string and set the display format. It is already string on the SQL Server Source side and looks exactly the way I want. It's just not being propogated to the Detination Flat file. Thanks, Dick
0 Likes 0 ·
brookstom1 avatar image
brookstom1 answered
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???
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.