November 5, 2009 at 8:17 am
Hi.
I am working on a projected to extract data from an SQL database into XML files. I have 4 stored procedures that return data based on a DATETIME parameter and a Text parameter that are common to all of the procedures. Each of the procures is constructed using the SELECT FOR XML PATH, ELEMENTS options. I have not used the TYPE option and I am not returning an XML variable in the stored procedures.
Basically my SSIS package needs to work as follows:
SET the current DATETIME into a variable: This I have working
SET the Text Parameter value into a variable: This I have working
Use these variables to run 4 stored procedures to populate some tables: Not attempted yet, secondary importance
Use these variables to run 4 stored procedures that each return XML string as described above. This is the bit I am stuck on. I have set up a Script Task to populate the variables and then in the Data Flow Task I have created a Script Component to tackle the first stored procedure, the code is as follows:
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
<Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute> _
<CLSCompliant(False)> _
Public Class ScriptMain
Inherits UserComponent
Dim connMgr As IDTSConnectionManager100
Dim sqlConn As SqlConnection
Dim sqlReader As SqlDataReader
Public Overrides Sub AcquireConnections(ByVal Transaction As Object)
connMgr = Me.Connections.MyConnection
sqlConn = CType(connMgr.AcquireConnection(Nothing), SqlConnection)
End Sub
Public Overrides Sub PreExecute()
Dim cmd As New SqlCommand
cmd.CommandText = "EXEC [TFS].[p_UnitDetailFeed] '" & Me.ReadOnlyVariables("RemarketingCompany").Value & "', " + Me.ReadOnlyVariables("MyDate").Value
cmd.Connection = sqlConn
sqlReader = cmd.ExecuteReader
End Sub
Public Overrides Sub PostExecute()
sqlReader.Close()
End Sub
Public Overrides Sub CreateNewOutputRows()
Do While sqlReader.Read
With Me.MyOutputBuffer
.AddRow()
.MyXML = sqlReader.GetString(0)
End With
Loop
End Sub
Public Overrides Sub ReleaseConnections()
connMgr.ReleaseConnection(sqlConn)
End Sub
End Class
The next stage is a Flat File Destination to save the file.
When I run the package I get the following runtime error:
Script Component has encountered an exception in user code:
Project name:
Incorrect syntax near '/'
I have no idea what is causing this as none of my code contains a / character
All I can tell is that it is failing in the pre-excecute phase.
Any ideas what is wrong?
Thank you
Tim.
November 5, 2009 at 8:47 am
Can you set up the procs to dump their data into a table with an XML column, then use an Execute task to run the procs, and then the data flow task to export from the table? That seems easier to me than what you're doing now.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 5, 2009 at 8:50 am
Hi.
I have solved part of my problem. I realised that the / character was part of the DateTime. I changed the stored procedure parameter to VARCHAR(20) and converted the time by using Now().ToString("yyyy-mm-dd hh:mm:ss")
I also realised my Select statament results are being CAST as XML.
The SSIS package now fails because their is too much data for the buffer.
Can SSIS accept XML from a stored procedure? If so what do I need to do to make this work.
Thank you
Tim.
November 5, 2009 at 10:29 am
Is there a reason you used a script component for this? Why not just use an OLE DB Source that calls your SP?
Yes, you can pass XML from a SP into SSIS. I've built out a package that creates XML files. I used a SP to build out the XML into a variable of type XML. I then cast that variable as varchar(max) as I return it from the SP. The XML data ends up in SSIS as DT_TEXT data type which can be easily written out to a flat file destination.
November 6, 2009 at 9:46 am
John Rowan (11/5/2009)
Is there a reason you used a script component for this? Why not just use an OLE DB Source that calls your SP?Yes, you can pass XML from a SP into SSIS. I've built out a package that creates XML files. I used a SP to build out the XML into a variable of type XML. I then cast that variable as varchar(max) as I return it from the SP. The XML data ends up in SSIS as DT_TEXT data type which can be easily written out to a flat file destination.
I got there in the end. I realised that all I needed was a third package variable to store a dynamically built EXEC statement, bin the script component and as you say use and OLE DB source.
Simple when you know how.
Thanks
November 6, 2009 at 10:51 am
Good to hear that you're up and running Tim. I agree, it's simple when you know how, but I'm not sure I'd call using a script task for this simple when the built-in data sources can do what you had to script. It may look simple to a programmer, but simply dragging an OLE DB Source component into your data flow and setting it up to call your SP to me would be much more simple.
November 6, 2009 at 10:56 am
John Rowan (11/6/2009)
Good to hear that you're up and running Tim. I agree, it's simple when you know how, but I'm not sure I'd call using a script task for this simple when the built-in data sources can do what you had to script. It may look simple to a programmer, but simply dragging an OLE DB Source component into your data flow and setting it up to call your SP to me would be much more simple.
I think you need to reread the post John. Tim said
bin the script component and as you say use an OLE DB source
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.
November 6, 2009 at 1:12 pm
Yea, you're right Phil. Thanks for helping me save grace.
Sorry Tim, I guess I misunderstood 'bin' the script component.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply