Hi, Following is my requirement for the SSIS package: 1) I need to loop through a SQL Server table "QXML" which has got a single column called "QCol". 2) I'm supposed to generate an XML file for every value of QCol in the table. I have designed my pkg like this: 1) I'm using a Data Flow Task which gets all the records of the table "QXML" in a dataset. I'm using OLE DB Source and Record Set Destination for achieving this. 2) I'm using For each Loop Container which is connected below the Data Flow Task metioned above. 3) The properties of the container in Collection Pane is as shown below: Enumerator - Foreach ADO Enumerator ADO Object Source Variable - User::HoneyWell I have selected Rows in the first Table 4) In the Variable Mappings section of Container i have entered the following variables: Variable Index User::QCol 0 5) Inside the container i have i have an Execute SQL task and Script Task. The execute sql task is being used to call a stored procedure which generates the XML for every value of QCol and stores it in a variable called XMLOutput. Since my XMLs can be huge i'm using ADO DB connection here. The properties are set like this: Result Set - XML Connetion Type - ADO.Net Connection - ADODB SQLSource Type - Direct Input Sql Statement - HoneyWell_QCol (This is my sp name) IsQueryStoredProcedure - True 6) Under parameter mapping i have written the following: Variable Name Direction DataType Parameter Name User::QCol Input String QCol 7) Result Set looks like this: ResultName VariableName 0 User::XMLOutput 8) In the script task i'm using the following script to write into an XML file: ' Microsoft SQL Server Integration Services Script Task ' Write scripts using Microsoft Visual Basic ' The ScriptMain class is the entry point of the Script Task. Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Runtime Public Class ScriptMain Public Sub Main() ' ' Add your code here ' Dim XMLString As String = " " XMLString = Dts.Variables("XMLOutput").Value.ToString.Replace("<ROOT>", "").Replace("</ROOT>", "") 'XMLString = "<?xml version=""1.0"" ?>" + XMLString GenerateXmlFile("D:\Projects\HONEYWELL_QCol.xml", XMLString) End Sub Public Sub GenerateXmlFile(ByVal filePath As String, ByVal fileContents As String) Dim objStreamWriter As IO.StreamWriter Try objStreamWriter = New IO.StreamWriter(filePath) objStreamWriter.Write(fileContents) objStreamWriter.Close() Catch Excep As Exception MsgBox(Excep.Message) End Try Dts.TaskResult = Dts.Results.Success End Sub End Class The problems i'm facing are: 1) The Execute SQL task is FAILING WITH FOLLOWING ERROR: Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly Am i going wrong anywhr in configuring Execute SQL task? 2) I need to generate a new XMl file for every QCol in the table. The Script right now keeps overwriting the files. Could you please modify it for me to so that it generates new file for every new QCol. This is very urgent for me. I have reached till this point after researching on my own. Would appreciate if you can help in completing it. Thanks, Deepti

1) What's the ResultSet? It should be "Full result set" 2) you should use a variable instead of a hardcoded path for example: - create an integer variable (scope package) - in the script task you first add 1 to that value (here are some examples: http://microsoft-ssis.blogspot.com/2010/12/how-to-use-variables-in-script-task.html) - use the hardcoded path in combination with that number: GenerateXmlFile("D:\Projects\HONEYWELL_QCol" + Dts.Variables("User::Counter").Value.ToString() + ".xml", XMLString) or you can use an other variable that you already used in the foreachloop to create a unique filename.Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com

Thanks a lot for replying SSISJoost. :) For the execute SQL Task i had set the ResultSet to XML as i'm getting the XML in an variable called @XMLOutput(declared as datatype XML in the sp) in the sp. In the execute sql task i have defined one more variable called User::XMLOutput which the value of the @XMLOutput into it. I tried setting the result set to Full and i get following error: Error: 0xC001F009 at RAYDEBXMLFF_NEW: The type of the value being assigned to variable "User::XMLOutput" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. Error: 0xC002F210 at HONEYWELL, Execute SQL Task: Executing the query "HONEYWELL_QCol" failed with the following error: "The type of the value being assigned to variable "User::XMLOutput" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. ". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. Task failed: HONEYWELL Any idea why this is coming up? I think thats because of data type mismatch between @XMLOutput and User::XmlOutput. Deepti

What kind of ssis variable are you using to store the XML result in? Object? String?Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com

I'm using User::XmlOutput which is of string datatype to store the XML result.Deepti

I'm using User::XmlOutput which is of string datatype to store the XML result. Deepti I'm not that familiar with the XML result set type.... but apparently the XML datatype in TSQL doesn't fit in a String variable of SSIS. Try to fit it in an Object variable or change the output type of your TSQL statement to string (may be with a CAST or Convert). Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com

I tried doing everything as you suggested.I changed the datatype of variable being used in sp to nvarchar(max) and also the SSIS variable to Object. Package works but the file doesn't have an XML but its default value System.Dataset.Object. I get the following error: Error: 0xC001F009 at RAYDEBXMLFF_NEW: The type of the value being assigned to variable "User::XMLOutput" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. Error: 0xC002F210 at HONEYWELL, Execute SQL Task: Executing the query "HONEYWELL_QCol" failed with the following error: "The type of the value being assigned to variable "User::XMLOutput" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. ". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. Task failed: HONEYWELL Deepti

Hi SSISJoost, Thanks for helping me out. I could get my package working. I was using datatype NVARCHAR for my input parameter in sp. I changed it to VARCHAR and it started working. :) Thanks Again!! :) Deepti

Great!Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com