x

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

more ▼

asked May 05, 2010 at 03:16 PM in Default

Dick Rosenberg gravatar image

Dick Rosenberg
103 6 6 7

Would love to help, but SSIS is not my strong suit, so I don't really know - but +1 for a well stated question.
May 05, 2010 at 04:44 PM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left

3 answers: sort newest
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???
more ▼

answered Jun 01, 2012 at 08:45 PM

brookstom1 gravatar image

brookstom1
0

(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered May 14, 2010 at 01:01 PM

Dick Rosenberg gravatar image

Dick Rosenberg
103 6 6 7

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.
May 14, 2010 at 01:19 PM TimothyAWiseman

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
May 14, 2010 at 02:29 PM Dick Rosenberg
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered May 05, 2010 at 10:11 PM

Daniel Ross gravatar image

Daniel Ross
2.9k 10 12 14

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
May 06, 2010 at 04:36 AM Fatherjack ♦♦
Oh, I didn't check the output from the editor. Nice pickup, thanks Jack
May 06, 2010 at 08:28 PM Daniel Ross
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x928
x115

asked: May 05, 2010 at 03:16 PM

Seen: 3730 times

Last Updated: Jun 01, 2012 at 08:49 PM