I might be barking up a wrong tree, but hope someone can advise on the forum.
I have an established stored procedure, which returns a list of records, e.g. EXEC my_stored_procedure returns idColumn (INT) and descColumn (VARCHAR(50)). Now I have a new task to write SSIS to export same result set into an Excel file and as I deal extremely rarely with SSIS, Dr Google is my mentor.
I have seen various examples on how to create an Execute SQL Task and apply EXEC my_stored_procedure syntax in SQL Statement field and add variables (which I understand are columns) in Result Set area.
I have also seen examples on how to have Data Flow Task and use Excel as a destination, yet I cannot find any example on how to export from SQL via Stored Procedure to Excel. My attempts to link between them also failed, as my Excel component doesn't recognise the SQL's Result Set. Hence I have several questions, please:
Much appreciated!
October 5, 2020 at 1:12 pm
You don't want an Execute T-SQL Task for this, you want a Data Flow Task. i don't doubt if you look into those, you'll likely find the solution very quickly. You'll likely be able to work out what you actually need to do once you place said Task as well; just place the source (OLEDB), define it, and then place the destination (Excel) and link them together, and then finally plot your columns.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 5, 2020 at 1:22 pm
First thing is, are you looking to avoid SSIS altogether? I ask because setting up SSIS to export data from a stored procedure into Excel is relatively easy to do. The steps would be:
0.) Prepare an empty Excel spreadsheet with the column names you will get from your stored procedure. You can do this easily enough by copying and pasting just one row of data from SSMS from where you manually executed your stored procedure, and then delete only the row of data, leaving the headers in place. Be sure not to have multiple columns with the same name in the results of your stored procedure.
1.) Add a Connection Manager to point to the SQL Server and specific database, and also add one to point to the empty Excel spreadsheet, The former can be a SQL Native Client connection, and the latter is a Flat File connection.
2.) Add a Data Flow Task to the Control Flow.
3.) Create some SSIS variables to hold the values for any parameters for your stored procedure.
4.) Within the Data Flow Task, add an OLE DB Source and configure it to use your stored procedure. The EXEC statement to execute your stored procedure is your query, and you use the ? character to represent each parameter that is needed for your stored procedure. In the Parameters tab, you identify the actual parameter names in your procedure and identify them in the order they appear in your stored procedure, providing a zero-based numbering scheme to the Parameter Name column on the right-hand side, and specify the variable that holds the value for each parameter.
5.) Add an Excel Destination that points to the Flat File Connection Manager you created in step zero.
6.) Drag the output line of the OLE DB Source to the Excel Destination, and then edit the destination and do the Mapping tab to map the input columns coming out of your stored procedure (and thus the OLE DB Source), to the columns in the spreadsheet.
This will get the process to work once. Repeatable execution is going to require a fresh template spreadsheet with just the column names every time. That can be done using a File System Task within your Control Flow, prior to the Data Flow Task, by copying that empty spreadsheet file (template) over the existing file from the last execution. If, however, you want to avoid SSIS altogether, you'll need to configure SQL Server to allow something that many folks don't allow their servers to do because of the potential security risks, and I'm embarrassed to admit that I've forgotten exactly what feature that is. That would give you the ability to specify a filename for the output directly within T-SQL. I believe that would be the BULK OUTPUT statement, and you'd probably change your stored procedure to SELECT the data into a temp table and then add a filename parameter and then use dynamic SQL to execute the BULK statement that would place the data into the file. You would also be wise to validate the filename parameter and have strict naming conventions that you validate, such as not allowing spaces.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 5, 2020 at 1:27 pm
Thank you for the replies. I have tried OLE DB Source, but it failed on preview. I do see the result if I run a simple SELECT myClumn FROM myTable, but in case of the stored procedure it shows an error around entry into local temp table. The stored procedure has insert into a local temporary table, processing data and then eventually query it. The error message states the metadata cannot be determined because my statement uses temp table.
Does it mean I shall re-write stored procedure or perhaps there is some other way, please?
Thank you for the replies. I have tried OLE DB Source, but it failed on preview. I do see the result if I run a simple SELECT myClumn FROM myTable, but in case of the stored procedure it shows an error around entry into local temp table. The stored procedure has insert into a local temporary table, processing data and then eventually query it. The error message states the metadata cannot be determined because my statement uses temp table.
Does it mean I shall re-write stored procedure or perhaps there is some other way, please?
You need to use
EXECUTE proc WITH RESULT SETS (( insert result set definition here ))
in the case of selection from a temporary table.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 5, 2020 at 1:54 pm
BOR15K wrote:Thank you for the replies. I have tried OLE DB Source, but it failed on preview. I do see the result if I run a simple SELECT myClumn FROM myTable, but in case of the stored procedure it shows an error around entry into local temp table. The stored procedure has insert into a local temporary table, processing data and then eventually query it. The error message states the metadata cannot be determined because my statement uses temp table.
Does it mean I shall re-write stored procedure or perhaps there is some other way, please?
You need to use
EXECUTE proc WITH RESULT SETS (( insert result set definition here ))
in the case of selection from a temporary table.
Tried that too - comes back with Object Reference Not set to an instance of an object, although Parse Query is successful.
October 5, 2020 at 1:55 pm
Tried that too - comes back with Object Reference Not set to an instance of an object, although Parse Query is successful.
What was the full statement you used? When did you get that specific error?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 5, 2020 at 1:58 pm
If you post screenshots of your DB connection manager settings and of the OLEDB source config, we should be able to work out what is going wrong.
Unfortunately, the error message you received is very common and therefore does not help much in narrowing down where the problem lies.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 5, 2020 at 2:00 pm
If you post screenshots of your DB connection manager settings and of the OLEDB source config, we should be able to work out what is going wrong.
Unfortunately, the error message you received is very common and therefore does not help much in narrowing down where the problem lies.
Looks like my mistake: I didn't select all the columns. Cut the output to two columns to test and it works. I will try now to add all the columns, using your suggested syntax.
Thank you.
October 8, 2020 at 4:36 pm
Be careful with the output of your stored proc too, especially if you're outputting character data. I've had problems with VARCHAR columns being exported to Excel, ended up translating them to NVARCHAR in the output of the proc. Not sure if you'll have that issue, but if you get weird errors about any string/text columns (VARCHAR, columns, specifically), you might try converting them to NVARCHAR. Note I'm not saying convert your database/tables to NVARCHAR, I'm saying CAST your final output in your stored proc to NVARCHAR (if you have trouble...if it just works then great, you lucked out!).
Also, if you have NULL or mixed numeric (or numeric-looking) and string values in your output columns...that might cause you issues as well as the Excel driver tries to "guess" the column format/data type to output to, and if you have mixed values or NULLs such that the driver can't "guess" correctly you might get weird errors. It's not the fault of SSIS, trying to export to Excel has always had this issue, it's the stupid Excel (Jet?) driver.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply