Man, it’s been a WHILE since I’ve worked with SSIS packages. Why, I remember when it was part of something called Business Intelligence Development Studio- and before that something else… now its part of SQL Server Data Tools, whatever that means. Still looks the same though as good ol’ SSIS (and DTS before that.) And there’s a part of me that enjoys it… most of me though just wants to wash my hands of the “plumbing” and get back to REAL programming. Something about writing VB code (Yes, I KNOW C# is available, you’re still working in a dated IDE!) just makes me feel icky.
Here’s the process I hacked out. yes, I’m sure there’s a more elegant way to do it – but I was done in a few hours, and it seems elegant. I’m receiving an XML file like this:
This is an XML fragment, not a true XML document –it’s missing a root node. (I.e. should be <Keys><Key>, not just <Key>.) So, if you try to do a data transform directly against this, you’ll end up with no source columns.
Create a SSIS package with the following variables:
So, I added a Script Task with the following variables passed in as R/W:
And the following script:
public
void Main()
{
String ErrInfo = “”;
String FilePath = Dts.Variables[“User::FilePath”].Value.ToString()+ Dts.Variables[“User::FileName”].Value.ToString();
//MessageBox.Show(“Filename: ” + FilePath);
try
{
String FileContent; //Variable to store File Contents
FileContent = ReadFile(FilePath, ErrInfo);
if (ErrInfo.Length > 0)
{
Dts.Log(“Error while reading File “ + FilePath, 0, null);
Dts.Log(ErrInfo, 0, null);
Dts.TaskResult = (int)ScriptResults.Failure;
return;
}
//FileContent Before Replace;
//MessageBox.Show(FileContent);
//Find and Replace –> Modify WHERE clause
FileContent = FileContent.Replace(
“<Key>”,
“<Keys><Key>”
);
FileContent = FileContent.Replace(
“</Key>”,
“</Key></Keys>”
);
//FileContent After Replace;
//MessageBox.Show(FileContent);
Dts.Variables[“User::FileContent”].Value = FileContent;
String ArchivePath = Dts.Variables[“User::ArchivePath”].Value.ToString() + Dts.Variables[“User::FileName”].Value.ToString();// +@”\Processed\” + Dts.Variables[“User::FileName”].Value.ToString();
String ProcessPath = Dts.Variables[“User::ProcessPath”].Value.ToString() + Dts.Variables[“User::FileName”].Value.ToString();
//MessageBox.Show(ArchivePath);
//Write the contents back to File
if (File.Exists(ProcessPath))
{
File.Delete(ProcessPath);
}
WriteToFile(ProcessPath, FileContent, ErrInfo);
if (ErrInfo.Length > 0)
{
Dts.Log(“Error while writing File “ + ProcessPath, 0, null);
Dts.Log(ErrInfo, 0, null);
Dts.TaskResult = (int)ScriptResults.Failure;
return;
}
//and move the orig files
if (File.Exists(ArchivePath))
{
File.Delete(ArchivePath);
}
File.Move(FilePath, ArchivePath);
}
catch (Exception e)
{
Dts.Log(e.Message, 0, null);
Dts.TaskResult = (int)ScriptResults.Failure;
}
}
public
String ReadFile(String FilePath, String ErrInfo)
{
String strContents;
StreamReader sReader;
try
{
sReader = File.OpenText(FilePath);
strContents = sReader.ReadToEnd();
sReader.Close();
return strContents;
}
catch (Exception e)
{
MessageBox.Show(ErrInfo);
ErrInfo = e.Message;
return
“”;
}
}
public
void WriteToFile(String FilePath, String strContents, String ErrInfo)
{
StreamWriter sWriter;
try
{
sWriter = new
StreamWriter(FilePath);
sWriter.Write(strContents);
sWriter.Close();
}
catch (Exception e)
{
MessageBox.Show(ErrInfo);
ErrInfo = e.Message;
}
}
At this point you’ve kicked out a modified version of the XML fragments – only they’re readable now – to a subfolder called Processed. Add a new for-each loop on this folder, and set up your XML source on this:
Above I generated the XSD file from a generated XML file, and copied the file out to a new folder called Schemas. (Yes, it’s great to run this first using hardcoded values for the XML file versus a for-each loop to reduce complexity.) Clicking on Columns then gives us a view of the sequence columns, just like if this was in a familiar SQL-to-SQL operation.
Then you run a File System task to delete the contents of the Processed folder – you’re done with them now – and fire off a sproc to add any new records from your Raw table to a more formatted, validated regular table in SQL. Voila!!!
More Links
- A simple XML to SQL link. Note the <colors><color> sample – they started with valid XML, much easier! http://blog.hoegaerden.be/2011/04/07/loading-xml-using-ssis/… and another great article on complex XML here.
- A purchase order walkthrough is here.
- A very elegant find/replace script here.
- An example serving up XML out of SQL is here.
- This was “semi-functional” but at least got me thinking along the right lines.
- Some good MSDN links on using XSLT are here and here.
- A hackneyed console app solution is here. I’m including it for laughs mostly.
- Foreach loop walkthrough. Very limited here.
- Just for grins – I really enjoyed this article on analyzing graphical execution plans in SQL Server – http://www.sqlservercentral.com/articles/Execution+Plans/105810/ – this really broke things down in an easy-to-understand manner.]
/* driftboatdave */ 