I continue to try and find "easy" solutions to what should be a straightforward problem of outputting the results from a stored procedure to a file.
I tried using both XML task and file system task with the thought that one of those would actually be able to output a file from a variable, but both of those tasks threw fits when I tried using different variable types (file system required a string, but the XML result set never seemed to throw anything but an object) so I decided to just try a script task and do everything "manually".
So my latest gyrations have been thus:
1) Set execute sql task to output XML and push to a script task to write a file
2) Set execute sql task to output a full result set and push to a script task to write a file
Number 1 was the only one I could get working, because I kept getting this error with Number 2 that said the variable wasn't a recordset (maybe it was null?)
I can actually create files now via the script task, but it seems like the variable that should get the results from the stored procedure isn't getting anything. I tried using a MsgBox to see what was actually being passed to the script task, and all I got was the number 0 which I'm assuming is the default for the object type.
What's the best way to debug this? The package runs without errors, and I'm not familiar with debugging in SSIS. How can I tell if the stored procedure is returning results into the result set variable?
Mike,
Have you tried using the data-flow to do this? You can consume a stored procedure in a OLE DB Source adapter (although there are some caveats to doing it).
-Jamie
|||Here is code to shred a recordset in a script task and write it to a tab-delimited file. Notice there are different code sections depending on if you execute your procedure with OLE DB or ADO.NET connection.
Code Snippet
Public Sub Main()
Dim dt As Data.DataTable
Dim FirstColumn As Boolean = True
' for OLEDB
Dim ad As New Data.OleDb.OleDbDataAdapter
dt = New System.Data.DataTable
ad.Fill(dt, Dts.Variables("Recordset").Value)
'' for ADO.NET
'Dim ds As Data.DataSet = CType(Dts.Variables("Recordset").Value, DataSet)
'dt = ds.Tables(0)
Dim fs As StreamWriter = File.CreateText("c:\myfile.txt")
' write a header
FirstColumn = True
For Each column As Data.DataColumn In dt.Columns
If Not FirstColumn Then
fs.Write(vbTab)
End If
fs.Write(column.ColumnName)
FirstColumn = False
Next
fs.WriteLine()
For Each row As Data.DataRow In dt.Rows
FirstColumn = True
For Each column As Data.DataColumn In dt.Columns
If Not FirstColumn Then
fs.Write(vbTab)
End If
fs.Write(row(column.Ordinal).ToString())
FirstColumn = False
Next
fs.WriteLine()
Next
fs.Close()
Dts.TaskResult = Dts.Results.Success
End Sub
|||Jay, do you even use SSIS or have you bypassed most components and gone with scripts instead?
|||
JayH wrote:
Here is code to shred a recordset in a script task and write it to a tab-delimited file. Notice there are different code sections depending on if you execute your procedure with OLE DB or ADO.NET connection.
Oh, and you'll have to set a reference (Project menu | Add References...) to System.Xml.dll.
|||
Jay, do you even use SSIS or have you bypassed most components and gone with scripts instead?
I do use a lot of script, but I think it's justified in most cases. There is some background on this one. We tried to use the out-of-the box components yesterday on another thread.
|||
I do use a lot of script, but I think it's justified in most cases. There is some background on this one. We tried to use the out-of-the box components yesterday on another thread.
HAHAHA! I hear ya...|||
I wasn't sure how to best incorporate a data flow into the package. Let me step all the way out and give a full overview...
My goal is to write a custom report generator in a package that I can schedule to run daily via SQL agent. I wanted it to be as generic as possible, so I created several tables to hold the parameters of the reports - output columns, where clause constraints, and data objects, along with some other info. I wrote a stored procedure that takes a report ID as a parameter and outputs the results for that report; this can create varying results depending on which output columns and object it uses.
In the package I have an execute sql task which selects all report IDs, and a for each loop container which passes each of those IDs to another execute sql task which runs the stored procedure. I also placed a script task within the loop connected to the execute sql task to write the results to a file. I originally wanted to write to excel files, but I realized that XML will work just as well for the project, so I thought I'd be able to just output either a straight result set in XML from the execute sql or pass the rows to the script and call WriteXML from a datatable or something like that.
I've been having issues with the latter method, but the XML result set approach lets me complete the package execution - except the files don't contain any data, and I'm not sure why.
Is a data flow required the way that I'm doing it?
|||
Is a data flow required the way that I'm doing it?
If the sproc returns different columns depending on the parameter you pass it then no, you cannot use a data-flow. Unless you write a data-flow for each expected resultset.
-Jamie
|||I went and recreated the package from scratch, and I think doing that may have gotten rid of weird orphan variable references and other stuff that may have been floating around. In doing so, I now get a file created that contains the following line (and only the following line):
Microsoft.SqlServer.MSXML6.DOMDocument60Class
So I guess that's an improvement over "0", but still no data. And then package execution halts with the error:
Error: 0xC001F009 at Package: The type of the value being assigned to variable "User::intExcelReportID" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
I'm not sure what's happening here now - the for each loop goes through once with the first ID, then seems to quit on subsequent tries saying that the variable type has changed? That variable is an int16 which I parameter map in the execute sql task to a data type SHORT. Is that wrong?
|||
I went and recreated the package from scratch, and I think doing that may have gotten rid of weird orphan variable references and other stuff that may have been floating around. In doing so, I now get a file created that contains the following line (and only the following line):
Microsoft.SqlServer.MSXML6.DOMDocument60Class
So I guess that's an improvement over "0", but still no data. And then package execution halts with the error:
Error: 0xC001F009 at Package: The type of the value being assigned to variable "User::intExcelReportID" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
I'm not sure what's happening here now - the for each loop goes through once with the first ID, then seems to quit on subsequent tries saying that the variable type has changed? That variable is an int16 which I parameter map in the execute sql task to a data type SHORT. Is that wrong?
If you're getting the results into the variable, this is all the code you need to write it out as XML.
Code Snippet
Public Sub Main()
Dim dt As Data.DataTable
Dim FirstColumn As Boolean = True
' for OLEDB
Dim ad As New Data.OleDb.OleDbDataAdapter
dt = New System.Data.DataTable
ad.Fill(dt, Dts.Variables("Recordset").Value)
dt.TableName = "MyTable"
dt.WriteXml("c:\myfile.xml")
Dts.TaskResult = Dts.Results.Success
End Sub
You're assigning an output parameter to intExcelID? I'd use a 32-bit integer datatype for the parameter and the variable. I've observed SSIS has problems with smallint and bigint.
|||
I think I'm getting results into the variable, but I'm not sure because it's not actually putting any data into the file. How do I check to see if I'm getting results into the variable?
intExcelReportID is an int from a result set that I use in the for each loop, and the other execute sql task uses it as a parameter for each call of the stored procedure. I guess I can up it to 32 and use LONG?
|||Changing to int32 and LONG worked for the looping problem. Weird behavior!!
The code you posted is pretty similar to what I used for the full result set approach I was using previously - I'm assuming that's the result set output you're using here, and not the XML result set?
|||
Changing to int32 and LONG worked for the looping problem. Weird behavior!!
The code you posted is pretty similar to what I used for the full result set approach I was using previously - I'm assuming that's the result set output you're using here, and not the XML result set?
Yes, if have your Execute SQL task set to Full Result Set and then on the Result Set page map the Result Name "0" to a variable of type Object, then the script will turn the resultset into XML. I've never used an XML resultset from the Execute SQL.
|||
Not using the XML resultset was the key thing. I've finally gotten it to produce an XML file!
There's just one problem remaining:
Error: 0xC002F210 at Execute webExcelReportGenerate Stored Procedure, Execute SQL Task: Executing the query "EXEC webExcelReportGenerate ?" failed with the following error: "No disconnected record set is available for the specified SQL statement.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
That's the only thing left keeping the package from running cleanly. What does it mean?
No comments:
Post a Comment