SSIS goodness… kind of.

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

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s