locked
Multiple runs of same stored procedure RRS feed

  • Question

  • I am an SSIS newbie so please excuse how little I know.

    We have a manual job that I would like to automate in SSIS.

    We run the same stored procedure six times with different parameters. Each run creates a csv file that we load into excel and then mail the attachments to the user. Can SSIS run the SP, and in a send mail task, send the six outputs?

    Monday, September 22, 2014 1:12 PM

Answers

  • Changing the stored procedure to cast everything to nvarchar got rid of the error messages.
    Tuesday, September 30, 2014 12:32 PM
  • Put it after your insert.

    Usually if you're getting an issue like this, it means your excel file wasn't already empty.

    Thursday, October 16, 2014 4:06 PM
  • Patrick:

    Now it works. Cutting the template file to 27 lines or whatever the Excel default is fixed it.

    Thanks for your patient unending efforts. Does my solution make sense to an expert like you?

    Thursday, October 16, 2014 4:29 PM

All replies

  • Yes SSIS can do all the jobs you described.
    Monday, September 22, 2014 1:14 PM
  • You can even do them in parallel 6 data flow tasks running the stored procedure that write to CSV file outputs.

    And then a Mail Task to send the e-mails.

    Don't forget since the attachment won't be around until you create the csv files to mark "Delay Validation" (or have dummy files and overwrite them).

    Monday, September 22, 2014 1:18 PM

  • Regards, RSingh


    Monday, September 22, 2014 2:12 PM
  • You seem to imply that your stored proc is also creating the CSV with data. Usually I'd just let the stored proc populate a staging table with the data and let SSIS tasks to handle the export to CSV and the email part.

    You can use an SSIS Execute SQL Task to run the stored proc with parameters. Then use an SSIS Data Flow Task (DFT) to export data to a CSV and use Send Mail Task (SMT) to send the CSV as an attachment. The path to the CSV file needs to be in an SSIS variable. This variable can be populated via a config file, parameters or expression. The destination File Connection and the SMT will use this variable to set their File Path and Attachment properties respectively.


    - Aalamjeet Rangi | (Blog)

    Monday, September 22, 2014 2:32 PM
  • As far as I understand what you need is a ForEachLoop with ADO enumerator. You need to load a ADO object variable with values to be passed to sp.

    Inside loop get individual values onto variables and pass it to execute sql task using command like

    EXEC SPName @Param1 = ?@Param2=?,..

    and map variables to corresponding parameters

    Then add a sent mail task to send generated file as an attachment. For this add expression for attachment property to map to generated file's path


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Monday, September 22, 2014 4:57 PM
  • Does this flow make sense? Each execute SQL task runs the stored procedure with different parameter values and the result set will be named a different variable. The send mail will need to have the six variables as attachments.

    Monday, September 22, 2014 6:18 PM
  • It looks as it would work after fixing the send mail task
    Monday, September 22, 2014 6:49 PM
  • Does this flow make sense? Each execute SQL task runs the stored procedure with different parameter values and the result set will be named a different variable. The send mail will need to have the six variables as attachments.


    yes it makes sense. It means package will only send the email with attachments when all the 6 processes complete successfully. As cnk_gr pointed, you have some error in the send mail task configuration. Once it is fixed, test it.

    Thanks, hsbal

    Monday, September 22, 2014 6:56 PM
  • I Suggest you to use 6 config files for 6 set of parameters. 
    Monday, September 22, 2014 8:11 PM
  • I get this error when I try to run my package. Looking at the many articles on this topic, i added a data conversion routine.

    I changed all the strings complained about to unicode. So what else do I need to do?

    Wednesday, September 24, 2014 5:14 PM
  • You should convert towards the destination type.

    If excel worksheet (destination) columns are indeed unicode you go as you are. Don't forget to change the mapping to the new column names !

    If they were non-unicode you'd have to convert DEPT, COURSE_NAME, NTES, L_NAME and F_NAME columns to DT_STR using a codepage (depending on your country and data).


    "If there's nothing wrong with me, maybe there's something wrong with the universe!"


    • Edited by cnk_gr Wednesday, September 24, 2014 5:35 PM
    • Proposed as answer by Katherine Xiong Monday, September 29, 2014 2:33 AM
    • Marked as answer by Katherine Xiong Tuesday, September 30, 2014 12:25 PM
    • Unmarked as answer by Katherine Xiong Wednesday, October 1, 2014 2:17 AM
    Wednesday, September 24, 2014 5:34 PM
  • Changing the stored procedure to cast everything to nvarchar got rid of the error messages.
    Tuesday, September 30, 2014 12:32 PM
  • This is my data flow. It basically works except my Excel file continues to append the results of the latest run. How do I prevent this from happening? I don't know where I would put a delete file in so it regenerates. 


    Thursday, October 2, 2014 9:38 PM
  • Add an execute SQL command in there, that performs a DELETE FROM [myexceltablesheet$] in the control flow.

    You won't need to rebuild the files each time, and it will still clean them out. Make sure you point the SQL command at the excel connection though!

    Thursday, October 2, 2014 9:45 PM
  • Here is the control flow which does the three data flow tasks. I don't quite understand where to put the Execute SQL.

    Thursday, October 2, 2014 9:52 PM
  • There's nothing to stop you issuing the delete(s) or truncates (not sure if excel parses truncates or not)

    as your first step prior to the DFTs. If you wrapped the whole caboodle with a container you could issue just one command to delete all the data, and then move on to the loads.

    Thursday, October 2, 2014 10:05 PM
  • Pat:

    I tried putting a file system task here to delete one of the six files I want to overwrite. It deleted but the Excel task failed because the file wasn't there. Do I need to re-create it also?

    Monday, October 6, 2014 3:35 PM
  • Don't delete the files, use SQL to delete the data:

    DELETE FROM [mySheet$]

    Monday, October 6, 2014 9:27 PM
  • I created two Excel template files with just the headings and one of my procedures works perfectly. the other one gets the data out to the spreadsheet but not before putting out a bunch of nulls in the first 100 records. 

    Here is the preview query from the oledb

    Here is what my Excel preview looks like

    How can I fix this?

    Friday, October 10, 2014 3:34 PM
  • WHERE [what ever column 1 is called] IS NOT NULL

    in your source query.

    Friday, October 10, 2014 3:47 PM
  • F1 (Column 1)  is the STA field which is set to 'CAN'. 

    I don't know where the NULLS come from

    Friday, October 10, 2014 6:58 PM
  • If you really can't find where they are coming from, just run a delete from table where column = 'NULL' afterwards :)

    Friday, October 10, 2014 7:03 PM
  • So in my data flow, I would add an Ole db where I would delete from sheet1$ where a cell is null?

    Wednesday, October 15, 2014 8:48 PM
  • My Excel procedure always starts to display data in row 193 if that rings a bell with anyone.
    Thursday, October 16, 2014 4:04 PM
  • Put it after your insert.

    Usually if you're getting an issue like this, it means your excel file wasn't already empty.

    Thursday, October 16, 2014 4:06 PM
  • It isn't empty.  I use the file system task to add two different template files that have headings in row 1 and nothing else, but I do notice a difference in my two template files. The one that works only scrolls down to line 27 in Excel while the one that shows nulls until line 193 shows 193 lines. 

    I am going to try and recreate the non working template file so it doesn't show many lines.

    Thursday, October 16, 2014 4:24 PM
  • Patrick:

    Now it works. Cutting the template file to 27 lines or whatever the Excel default is fixed it.

    Thanks for your patient unending efforts. Does my solution make sense to an expert like you?

    Thursday, October 16, 2014 4:29 PM
  • I am trying to use dynamically created files 

    I try to copy the data to the file below but it tells me it doesn't exist. I have checked some of the pointers but still don't understand what i need to do.

    Monday, October 20, 2014 4:39 PM