Hello- I am constructing an XML import package intended to consume fairly typical (if a bit complex) XML. The import must: Loop through a configured location and serially load each XML file found. Parse certain values into columnar format and insert them to a "Load" table Insert the contents of the entire file in an XML-type field in the same table. Currently, I can load the XML files via a Foreach file enumerator (works fine), and in a Data Flow inside the Foreach use an XML Source to load the XML into the flow as fields. Easy peasy, I've got my columnar data. However, this doesn't do much for me in terms of loading the entire file into a single field. I have investigated means of doing this with the Import Column transformation, but those seem to be dead ends. I can define an additional Flat File source, and pull in a column containing the XML. But I can't see how to connect this new field into the rest of my dataset. I have no Join columns to connect to in the "XML string" dataset. So I have arrived at the conclusion that I will need to add a Script Component to the data flow that accepts the full Path/Name of the file from an SSIS variable (already defined) and returns just the text value of the XML as a new Column. So I would be doing the XML-to-string manuever in that script. Does anybody have any guidance here? Am I close with the Import Column tactic, or am I better off creating a Script? Thanks, Andy T.

Hello, To load whole xml file in one column, use this code, You need to create ADO.NET Connection to your database where your table exists. AS i have created MYADOConn and used in script task. File name and path comes from VarFilePath variable. /* Microsoft SQL Server Integration Services Script Task Write scripts using Microsoft Visual C# 2008. The ScriptMain is the entry point class of the script. */ using System; using System.Data; using Microsoft.SqlServer.Dts.Runtime; using System.Windows.Forms; using System.Xml; using System.IO; using System.Data.OleDb; using System.Data.SqlClient; using System.Text.RegularExpressions; namespace ST_ec5075ad59b94e3c8c58d4e3fa56755d.csproj { [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")] public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase { #region VSTA generated code enum ScriptResults { Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success, Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure }; #endregion /* The execution engine calls this method when the task executes. To access the object model, use the Dts property. Connections, variables, events, and logging features are available as members of the Dts property as shown in the following examples. To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value; To post a log entry, call Dts.Log("This is my log text", 999, null); To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, true); To use the connections collection use something like the following: ConnectionManager cm = Dts.Connections.Add("OLEDB"); cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;"; Before returning from this method, set the value of Dts.TaskResult to indicate success or failure. To open Help, press F1. */ public void Main() { string filepath; filepath=Dts.Variables["User::VarFilePath"].Value.ToString(); SqlConnection myADONETConnection = new SqlConnection(); myADONETConnection = (SqlConnection)(Dts.Connections["MyADOConn"].AcquireConnection(Dts.Transaction) as SqlConnection); StreamReader reader = File.OpenText(filepath); string input = null; string abc = null; string Col; myADONETConnection.Close(); while ((input = reader.ReadLine()) != null) { abc = abc + input; } Col = abc.ToString(); myADONETConnection.Open(); string queryString = "insert into sandbox.dbo.PackageXML(Col) Values(@field1)"; SqlCommand command = new SqlCommand(queryString, myADONETConnection); command.Parameters.AddWithValue("@field1",Col.ToString()); command.ExecuteReader(); myADONETConnection.Close(); } } } Thankshttp://sqlage.blogspot.com/

Hello, To load whole xml file in one column, use this code, You need to create ADO.NET Connection to your database where your table exists. AS i have created MYADOConn and used in script task. File name and path comes from VarFilePath variable. /* Microsoft SQL Server Integration Services Script Task Write scripts using Microsoft Visual C# 2008. The ScriptMain is the entry point class of the script. */ using System; using System.Data; using Microsoft.SqlServer.Dts.Runtime; using System.Windows.Forms; using System.Xml; using System.IO; using System.Data.OleDb; using System.Data.SqlClient; using System.Text.RegularExpressions; namespace ST_ec5075ad59b94e3c8c58d4e3fa56755d.csproj { [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")] public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase { #region VSTA generated code enum ScriptResults { Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success, Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure }; #endregion /* The execution engine calls this method when the task executes. To access the object model, use the Dts property. Connections, variables, events, and logging features are available as members of the Dts property as shown in the following examples. To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value; To post a log entry, call Dts.Log("This is my log text", 999, null); To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, true); To use the connections collection use something like the following: ConnectionManager cm = Dts.Connections.Add("OLEDB"); cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;"; Before returning from this method, set the value of Dts.TaskResult to indicate success or failure. To open Help, press F1. */ public void Main() { string filepath; filepath=Dts.Variables["User::VarFilePath"].Value.ToString(); SqlConnection myADONETConnection = new SqlConnection(); myADONETConnection = (SqlConnection)(Dts.Connections["MyADOConn"].AcquireConnection(Dts.Transaction) as SqlConnection); StreamReader reader = File.OpenText(filepath); string input = null; string abc = null; string Col; myADONETConnection.Close(); while ((input = reader.ReadLine()) != null) { abc = abc + input; } Col = abc.ToString(); myADONETConnection.Open(); string queryString = "insert into sandbox.dbo.PackageXML(Col) Values(@field1)"; SqlCommand command = new SqlCommand(queryString, myADONETConnection); command.Parameters.AddWithValue("@field1",Col.ToString()); command.ExecuteReader(); myADONETConnection.Close(); } } } Thankshttp://sqlage.blogspot.com/

can you try to use T-SQL command of OPENROWSET in this case? Then you could save the value directly into your table without any convert work. declare @xmlDoc xml SELECT @xmldoc = CONVERT(xml, BulkColumn, 2) FROM OPENROWSET(Bulk 'file path', SINGLE_BLOB) [rowsetresults]

Hi Andy, What kind of problems do you have with the standard Import Column transformation? Do you get an error and what is the exact error message ?SSIS Tasks Components Scripts Services | http://www.cozyroc.com/