export using stored procedure

Question

Post reply

export using stored procedure

afgone

SSCommitted

Points: 1551
More actions
August 28, 2008 at 7:54 am

#123163

hi,
would i be able to run a stored procedure in ssis and get the result in an excel file. can you please show me how to do this?
thank you

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Chuck Rivel SSCrazy Points: 2991 More actions · Answer 1

here are the steps:

create a package, in the control flow, add a data flow task

create a connection to your sql server

in the data flow task, add a OLE DB Source using your sql connection and use a sql command as your data access mode and type the stored procedure call in the SQL Command textbox

ensure that you click on the columns tab to produce the meta data for the result set from the usp

add an excel destination to the data flow task and connect the source to the destination. you will need to create an excel connection for your output

open the excel destination to map the columns

run the package.

hth

afgone SSCommitted Points: 1551 More actions · Answer 2

thanks i did this. but now i want the filename to change according to date everytime the package is run(which is daily). i have added a script to change filename and two variable one to hold filename one to hold path.

in connection string i am calling this path and filename variable but i get error....

Chuck Rivel SSCrazy Points: 2991 More actions · Answer 3

the issue is that the Excel Destination will not create a file on the fly, it needs the file to exist. Can you create a CSV file instead of an excel file? SSIS will allow you to auto create a CSV file on the fly.

If you can live with CSV files, here is a sample approach:

Create a variable, sResultsPath, to hold a Stub Path of the name of the file, something like:

networkshare\SPResults Folder\SPResultsFile

Create a Flat File Destination object that will contain the original flat file to store the values to, I called my destination object Dynamic Flat File Dest

Then, place a script task that has the following code:

Public Sub Main()

Try

Dim sPath As String

sPath = Dts.Variables("sResultsPath").Value.ToString + Month(Now).ToString + Day(Now).ToString + Year(Now).ToString + ".csv"

Dts.Connections("Dynamic Flat File Dest").ConnectionString = sPath.ToString

Dts.TaskResult = Dts.Results.Success

Catch

'error occurred, return failure

Dts.TaskResult = Dts.Results.Failure

End Try

End Sub

After that, in your dataflow, connect the procedure to the Flat File Destination and execute.

What the script is doing above is changing the path of the Connection object to point to the stub path variable + date + .csv extension

HTH