BizTalk & SSIS

Moving one block over...

SteveB — Mon, 22 Oct 2007 14:57:00 GMT

This blog is moving to http://aspadvice.com/blogs/stevenbarden.

SSIS - Mapping Flat File Import Source And Sql 2005 Destination Column Translation Types

SteveB — Sun, 24 Dec 2006 13:46:00 GMT

This is my spin of an obscure Microsoft document that answers the need to translate SSIS import types (.NET mainly) with their Sql 2005 types on a 32 bit OS. The original Microsoft post is as http://msdn2.microsoft.com/en-us/library/ms141036.aspx. The following table is my reduction to the most commonly used data types. This implies that I will not use certain import types if they do not easily match common Sql 2005 types. This does not account for the fact that overflows can occur at the import level or the SQL level if data is larger than what is required.

Data Type

SQL Server (SQLOLEDB; SQLNCLI)

DT_I8

bigint

DT_BYTES

binary, varbinary, timestamp

DT_BOOL

bit

DT_STR

char, varchar

DT_DBTIMESTAMP

datetime, smalldatetime

DT_NUMERIC

decimal, numeric

DT_R8

float

DT_IMAGE

image

DT_I4

int

DT_WSTR

nchar, nvarchar, sql_variant, xml

DT_NTEXT

ntext

DT_R4

real

DT_I2

smallint

DT_CY

smallmoney, money

DT_TEXT

text

DT_UI1

tinyint

DT_GUID

uniqueidentifier

I hope this helps and plase feel free to post back and I will update this page.

SSIS - Setting Variables To Non-Variable Objects (Flat File Path For Example)

SteveB — Fri, 08 Dec 2006 14:42:00 GMT

SSIS is a very powerful tool indeed but there is one aspect that confused me for a short time until I did some poking around as the need arose. I hope this helps you are much as it has helped me already.

The example is reading a flat file to be imported in to a database. When you view (Edit) the properties for the Flat File Connection Manager you will see that, unlike the FTP connection Manager, the file path is fixed, rather it needs to be specified at design time. With the FTP Connection Manager you can assign source and destinations via selectable variables. *sigh, grumble grumble*.

The way to get around this is to use the Expressions editor of the Flat File Connection Object. Start by r-mousing the Flat File Connection you started. Quick note, when doing you mappings at design time it is best to make the path bar point to an actual file soul so can line up your mappings. But when Done, make the path something like "C:\". The you know that what you are about to do with a Variable and the Expression editor really works.

Start by declaring a package level string variable, called Path. Put in it the path you intend to use. Realize that this is the variable you can change in code during run time now. In my case I set it to C:\ to start and change it at run time, but for testing all we want to prove is that the expression / Variable over rides the fixed value set in the Flat File Connection Editor. Now r-mouse the Flat File Connection Editor and view the properties on the right. Select Expressions and click the button with the ellipsis that opens the Expression Editor for the Flat File Connection. You will see two columns. Select the Variable Connection String in the left column. On the right click the bottom with the ellipsis to open the Expression Builder, expand the variables on the left, and drag the variable you want (probably "@[User::Path]") to the expression window. Click Eval Expression and you will see what you added as the value of the Variable in the Variables window.

You are now done. The Expression Builders use of variables will over-ride that of the fixed Gui elements. And of course you can set these variables in code as needed.

Another good example of this can be found at "Dynamic Flat File Connections in SQL Server Integration Services" http://www.mssqltips.com/tip.asp?tip=1084.

Using Dts.Variables Vs Dts.VariableDispenser.LockForRead

SteveB — Thu, 07 Dec 2006 18:41:00 GMT

... and then we are humbled by the mistake we made... it's just me? Well, anyway, I could not get Dts.Variables to work early on so I took the long route with Dts.VariableDispenser. In time this grew very tiring so after getting a few packages done I decided to go back and find out what I was doing wrong the first time around. And yes I was able to turn

Dts.VariableDispenser.LockForRead("User::ZipFiles")
Dts.VariableDispenser.GetVariables(vars)
Dim ZipFiles As String = CType(vars("User::ZipFiles").Value, String)

Into...

Dim ZipFiles As String = CType(Dts.Variables("User::ZipFiles").Value, String)

... and as others have stated, to do this you enter the Script Task Editor, highlight Script in the left window, and view the ReadOnlyVariables and ReadWriteVariables. Here you enter the variable in question that you want the Script Task to essentially auto-lock for you. It should now work, right? Wrong, not for me, and much frustration was realized. The end result is that I was doing it right but in my testing from the three line version to the one line version I had left the first line in place. The first line, Dts.VariableDispenser.LockForRead("User::ZipFiles"), was still locking the variable, thus I was doing some odd double locking of the variable, resulting in a few bad words and not accomplishing what I wanted. After I commented out the code lock, joy was once again realized.

SSIS: Error using FTP Task - 0xc002f313 failed with error 0xc02090f3

SteveB — Tue, 05 Dec 2006 22:32:00 GMT

I was plugging variables in to an Ftp task and testing but I could not get my file to pull down. It looked like it should be good. It turns out of course that the ftp site was unix. I knew the variable as /subdir/file.txt.gz but I could not get that file to download, much less the others I wanted to iterate. Next I tested locally using FileZilla FTP Server on a local windows box and it worked perfectly, watched the logs all work file. It was not until I decided to NOT use my generated file location variable and to use a design time fixed Ftp location that it became clear that a Unix path was needed. What looked to me in IE as /subdir/file was really /export/home/subdir/subdir/file. Only after I specifically picked the file I wanted with the FTP Task did the real path show up. After this I plugged the new path in to my package level variable and it went smoothly.

Ftp And Script Tasks To Enumerating A Generic List Of Ftp Files

SteveB — Fri, 01 Dec 2006 19:52:00 GMT

Ok, so it's not BizTalk, and I have not written more about BizTalk lately due to work... my current project has no BizTalk. But along comes SQL 2005 SSIS... and if you have read between the lines, it is part BizTalk. And joy of joys, today (figuratively) I get to use it!

My current task has me rewriting a DTS package to download a collection of files via FTP. It seems simple enough after the getting started learning curve (which seems to be every day), so I start with a script task, add some code to concatenate multiple strings that represent file paths on an ftp location and pop them in to a Generic List. Did I mention that I tossed out half of my brain and one hand by having to do this in VB.NET, the SSIS scripting language of choice? I do so love VB.NET (dripping sarcasm). Now I make a package level variable of type object called Files, notice the plural. Next drop in a For Each Loop task and add a scoped variable to it of type string. Finally drop an Ftp task in to place inside the loop object that uses a remote path variable of type File. Now here comes the fun part... you'll notice a mild disconnect in that the Ftp object cannot see the Files object, because it is a generic list, but as a variable it is of type Object, not string. Thus the File string and the enumerate. Set the ForEach to enum by variable. Select the User::Files variable as the variable of choice. Now in the For-Each go to variable mappings and set your variable to the Object. This way the Loop iterates and auto casts the Generic List Object to strings called by the same name. Next drop in a script task and take the value of Files and put it in the File (String) object. Now you should be able to use the File (String) as the Ftp location variable...

I have included the outside script task code that builds the generic list / Object variable and the Loop-Contained Script Task that contains the break out code. All referenced variables are Package Level:

Public Sub Main()
Dim FtpZipFileList As List(Of String)
Dim DateStamp As String
Dim vars As Variables
FtpZipFileList = New System.Collections.Generic.List(Of String)
DateStamp = Date.Now.AddDays(-1).ToString("yyyyMMdd")
'build the files list
FtpZipFileList.Add("feed01_abc." + DateStamp + ".txt.gz")
FtpZipFileList.Add("feed02_abc." + DateStamp + ".txt.gz")
'FtpZipFileList.Add("feed03_abc." + DateStamp + ".txt.gz")
'FtpZipFileList.Add("feed04_abc." + DateStamp + ".txt.gz")
'FtpZipFileList.Add("feed05_abc." + DateStamp + ".txt.gz")
'FtpZipFileList.Add("feed06_abc." + DateStamp + ".txt.gz")
'FtpZipFileList.Add("feed07_abc." + DateStamp + ".txt.gz")
'FtpZipFileList.Add("feed08_abc." + DateStamp + ".txt.gz")
'setup and populate variables
Dts.VariableDispenser.LockForRead("User::ZipFiles")
Dts.VariableDispenser.GetVariables(vars)
vars("User::ZipFiles").Value = FtpZipFileList
vars.Unlock()
Dts.TaskResult = Dts.Results.Success
End Sub

Public Sub Main()
'the purpose here is to take the iterated file name and make it a string that the ftp object can see
Dim vars As Variables
Dim fileName As String
Dts.VariableDispenser.LockForRead("User::ZipFiles")
Dts.VariableDispenser.LockForWrite("User::ZipFile")
Dts.VariableDispenser.LockForRead("User::FtpDir")
Dts.VariableDispenser.GetVariables(vars)
fileName = vars("User::FtpDir").Value.ToString() + vars("User::ZipFiles").Value.ToString()
vars("User::ZipFile").Value = fileName
'MsgBox(vars("User::ZipFile").Value)
'vars.Unlock()
Dts.TaskResult = Dts.Results.Success
End Sub

Command Line Date Time Stamped Automated Undeploy

SteveB — Tue, 20 Jun 2006 17:02:00 GMT

There is really nothing overly special about this un-deploy script versus other examples you can find out there. But I have included one small feature that I like to use. When un-deploying a BizTalk process I want to export the Bindings, and I want to datetime stamp a copy of it. Included here is the shortest way I have found to datetime stamp my files without the use of perl, vbs or other external scripts. You will also notice it makes use of the StopOrch.vbs script as found in the BizTalk directory. I truly prefer to use a watered down version of Scott Colestock's BizTalk Deployment Framework, but not all cases allow for such.

:: -- start undeply script --
SET btsserver=<BtSqlServer>
SET btsdb=BizTalkMgmtDb

set sep =-
for /f "tokens=2,3,4 delims=/ " %a in ('date /t') do set zdate=%c-%b-%a
for /f "tokens=1,2 delims=: " %a in ('time /t') do set ztime1=%a-%b
for /f "tokens=1,2 delims= " %a in ('time /t') do set zampm=%b
set now=%zdate%-%ztime1%-%zampm%
:: echo %now%

btsdeploy export server=%btsserver% database=%btsdb% name="BizTalk.Orchestrations" version="1.0.0.0" culture="neutral" PublicKeyToken="5e1dc6d2ce0ef2c3" binding="BizTalk.Orchestrations.%now%.xml"

btsdeploy export server=%btsserver% database=%btsdb% name="BizTalk.Orchestrations" version="1.0.0.0" culture="neutral" PublicKeyToken="5e1dc6d2ce0ef2c3" binding="BizTalk.Orchestrations.xml"

@CScript /NoLogo StopOrch.vbs BizTalk.Orchestrations.Process BizTalk.Orchestrations Unenlist

btsdeploy remove server=%btsserver% database=%btsdb% name="BizTalk.Orchestrations" version="1.0.0.0" culture="neutral" PublicKeyToken="5e1dc6d2ce0ef2c3" uninstall=true

btsdeploy remove server=%btsserver% database=%btsdb% name="BizTalk.Transforms" version="1.0.0.0" culture="neutral" PublicKeyToken="5e1dc6d2ce0ef2c3" uninstall=true
btsdeploy remove server=%btsserver% database=%btsdb% name="BizTalk.Schemas" version="1.0.0.0" culture="neutral" PublicKeyToken="5e1dc6d2ce0ef2c3" uninstall=true

net stop "BizTalk Service BizTalk Group : <Host>"
net start "BizTalk Service BizTalk Group : <Host>"

pause

Another Way To Share A BizTalk / Non-BizTalk Schema - Embedded Resoources

SteveB — Tue, 13 Jun 2006 00:43:00 GMT

In a previous post I pointed out one way that worked for me in the goal of sharing a schema between a BizTalk and C# project(s). This basically entailed running xsd.exe against the a Schema in a Schema assembly and linking to that .cs class file from the other project.

Scott's work is far more elegant, and complicated in its goals and functionality. If you build full libraries in to your BizTalkProject.Componenets project, this solution may be for you (and me in upcoming projects).

http://www.traceofthought.net/PermaLink,guid,c1164c59-72e2-49e2-be7a-47e4e8dc46d4.aspx

Binary Conversion (FileStream to MemoryStream) For Tar / Zip to Custom Pipeline Message

SteveB — Fri, 09 Jun 2006 11:41:00 GMT

The requirement is to input an Xml trigger file (see previous posts on this) and output a Zip or Tar file. Later requirements could entail sending pictures (binary). The inbound trigger options are used to determine the outbound port used, which may send the output to file, ftp, or sftp. My employer uses /nsoftware adapters.

Although this project started by using the ICSharpCode.SharpZipLib to produce Tar / Zip streams, there were just various issues. We switched to using /nsoftware’s .Net Zip Library, but then Tar streams required intermediate temp files (Note: if tarring a Stream using these components can be done, it simply did not produce itself as an easy answer in the compressed time I had). So when I tar’d files, read the FileStream I needed to modify it some so the pipeline message body worked right. Although this version reads and sends Tar and Zip files, I may want to send a picture of other binary format. This method has allowed me to read any file I want and replace the pipeline message body quite easily.

FileStream fs = new FileStream(
      tarFileName,
      FileMode.Open,
      FileAccess.Read);
int length = Convert.ToInt32(fs.Length);
MemoryStream ms = new MemoryStream(length);
BinaryReader br = new BinaryReader(fs);
byte[] byteBuffer = br.ReadBytes(length);
ms.Write(byteBuffer,0,length);
ms.Position = 0;
inmsg.BodyPart.Data = ms ;

Sharing A Trigger Schema Between BizTalk And A Trigger.exe

SteveB — Wed, 07 Jun 2006 16:52:00 GMT

Ok, I really like this trick. Oh the wonders of simple inheritance. What I wanted in the outset was to have my trigger project share the BizTalk schemas by way of the Assembly project. You know, share the code. I am not going to say that this cannot be done, but I have it on impeccable authority that it is not Microsoft supported.

So I dropped to a command line and running xsd.exe on my Trigger Schema in the BizTalk Trigger Assembly project to produce Trigger.cs (xsd /c TriggerSchema.xsd). Now I have a class that I serialize to string and file. But here is the hitch... I don’t want to EVER manually modify the Trigger.cs file that is produced by the xsd.exe. Yet I do want to add functionality, such as Trigger.ToString(). The Trigger.cs file is easily wrapped and inherited, then you can use the TriggerWrapper.cs. Now I can use the serializable class representation of my BizTalk Schema, add my own functionality, and only need to run the xsd.exe from time to time to update the base class.

I have included the wrapper below, including the link to a Microsoft KB you may find you need if you run in to certain errors.

http://msdn.microsoft.com/library/en-us/dnxmlnet/html/trblshtxsd.asp?frame=true

using System;
using System.IO;
using System.Xml;
using System.Diagnostics;
using System.Xml.Serialization;
using System.Text;
namespace Transport.Trigger
{
   public class TriggerWrapper: TriggerSchema
   {
      public TriggerWrapper(){}
      public string TriggerToString()
      {
            MemoryStream memoryStream = new MemoryStream();
            XmlSerializer xs = new XmlSerializer(
                  typeof(TriggerWrapper)
                  );
            XmlTextWriter xmlTextWriter = new XmlTextWriter(memoryStream,Encoding.Default);
            xs.Serialize(xmlTextWriter, this);
            memoryStream = (MemoryStream)xmlTextWriter.BaseStream;
            UTF8Encoding encoding = new UTF8Encoding();
            return encoding.GetString(memoryStream.ToArray());
      }
   }
}

%SourceFileName% Clarification – The Best Macro! (well, now anyway)

SteveB — Thu, 01 Jun 2006 11:41:00 GMT

It is documented by many others that the use of %SourceFileName% can allow you to change the name of the output file in your process. Or output files, depending on your process. I found a few points to add to this based on my experiences. This is based on what I have found and I am not claiming that this is the sole answer or only solution, but I hope it helps.

%SourceFileName% is based on FILE.ReceivedFileName. So you can use a Message Assignment to perform the following: ‘MsgNewTrigger(FILE.ReceivedFileName) = MsgTrigger(FILE.ReceivedFileName);’. This is of enormous benefit because you can programmatically control the outbound file name.

Here are the catches and misconceptions I have found:

1) When using a solution that uses and orchestration (versus and pipeline only solution) I could not use %SourceFileName% if I did not set this information in a message assignment. If I did not use a message assignment, %SourceFileName% is exactly the name of the file at the other end of the process. Since I was making a new trigger file based on the original trigger file, I first thought it should get the name used by the inbound file, but this is not the case. It seems very clear to me now why this happened.

2) Some people have posted to others’ blogs that the use of %SourceFileName% will not work when the inbound or outbound file is not XML. After I determined what was required in step one above, I found this was not the case. My trigger file is XML and my outbound file is a zip file… actually, my outbound file starts as a new trigger file, copies the elements of the original trigger file, then has the body switched out with a zip file stream as an IBaseMessage in a custom pipeline component.

Zip Compression In A Custom Pipeline Component

SteveB — Tue, 30 May 2006 01:47:00 GMT

The Requirement... trigger a process that that zips files and ftp's them to a location. The Tools… BizTalk, .Net the ICSharpCode SharpZipLib. The Process… Use a C# console application to trigger dropping an xml file that is picked up by BizTalk. This file contains the information to be used by a .net component in an orchestration to locate and compress files to the desired location while performing other housecleaning and logging. A new (modified) trigger file is created in-orchestration that contains information needed in the next step, and passed out of the orchestration. It routes into a pipeline and through a custom component. In the encoding stage the body of the message is pulled out as xml. This data contains the location of the previously compressed files as xml, which are then streamed from file and are used to replace the body of the IBaseMessage inmsg. This little (or large) stream is then passed on to the ftp adapter via the magical qualities of BizTalk.

Templating BizTalk Solutions

SteveB — Thu, 18 May 2006 00:17:00 GMT

It may not be very often that you need to make several nearly identical BizTalk solutions, but it is nice to know it is easy (enough) to do. My requirement was to replace several processes on a legacy system that are very nearly identical. It was required that all of the processes be separate, have different receive locations, etc.

My solution was to use C# to create an Xml file that acts as a trigger, and pass variables to an Orchestration, that uses an Sql adapter to pull data, transform it, and drop a flat file. After reading the Java files that the legacy system uses to do the heavy lifting, I was able to create the flat file header, body and trailer from the data.

Source code is source code, all text, and BizTalk is no different. The solution I selected was to use a nifty little text editor to search and replace the project and object names. In this case almost all of the projects use a similar name for the project, objects, etc, such as P12, P12SqlData, P12SqlRequest, P12SqlResponse, etc. So why not just replace P12 with P13 and so on. The only two tricky parts were recognizing that after removing source control from the copied source, it is required that you run “attrib *.* /s –r –h –s –a” so that all files can be searched and replaced. Renaming all of the directories and files can be a little fun, but the act of renaming the directories, then the files did the trick. Hint, “dir /s /d /b *P12*” and “move <dir names> <dir names>” with a text editor, then rename the files.

After that it was a matter of creating new Sql Adapter XSD’s (or you can edit existing ones), and realigning the maps and joy was discovered… ok, there was a small amount of editing after that, but this process did provide a nice jumpstart on templating multiple BizTalk solutions. What was really cool was the fact that all of the btsdeploy exported xml binding files were nicely receptive to this process as well.

Multi-Input Map Problem Using An Sql Adapter – File Blank

SteveB — Tue, 09 May 2006 01:32:00 GMT

Multi-Input Map Problem With Sql Adapter

Today I ran into a problem that caused me to pause… I added a new Transform object, selected my desired input schemas and output schema. I select the check box to open the map upon completion, but when I click OK, nothing happens. I perform the same action and the same result. Now I realize something out of the ordinary is taking place. I refresh the project and see that for every time I create a new map, mapX.btm (substitute map name and increment number) is created and they are not included in the project. I include the map in the project and try to open it and it turns out that the file is empty and BizTalk tosses an error stating that the object is formatted wrong. Lost time, less hair and I am beginning to hope that something odd has not taken place in my Visual Studio installation. When I use any schemas other than the new Sql Adapters, the map creation process works… now I have something to go on.

As it turns out, I had created a new Sql Port, and in my hurry I used the name space of the main project. http://project (names have been changed for their own protection). Being a lazy programmer and former sysadmin who commonly avoids the use of the mouse, I had copied this text with ctl-c. This is the same namespace that I used when creating the Sql Adapter based on the new Sql Port with a neat ctl-v. It turns out the answer was to adjust the namespace in the Sql Port and the Sql Adapter XSD file. I right mouse the XSD and use my favorite text editor to adjust the namespace in Sql Adapter XSD file to http://project/sqladapter. Then I adjust the namespace used in the Sql Port and test the map creatin again… bingo! Multi-map happiness using the SqlResponse message part is again in the air!

Don’t hurry… precious minutes can get hurt this way.

Changing Stored Procedures In BizTalk 2004 Sql Adapters For Speed And Development

SteveB — Fri, 05 May 2006 01:57:00 GMT

This post expects that you are creating an SQL Adapter in an orchestration using a Static One Way Send Port in the BizTalk Server Manager. Go ahead and adjust as you see fit.

One project I am working on takes about eight minutes to run each SP needed for the BizTalk process. Actually this is the case for many of the SP’s. The joins are amazing, and I have no control over it’s optimization (so please do not suggest optimization… that is out of my hands for today). This causes the Sql Adapter creation process to time out, in addition to killing development time. My answer was to cache the output into another table on my local PC. Then I make a temporary SP (locally) with the name of the SP I want to use in the long term. This local SP outputs the locally cached table data and appends FOR XML AUTO. It is then easy to add and remove the XMLDATA as needed for creation of the SQL Adapter.

But I had a new problem. For reasons I cannot quite explain (I’m sure others can answer it), when I switched back to using the real SP, the column names came out as the join aliases, rather than what the cached table column names were. Actually there were worse problems, but this was bad enough. I did not want to change my BizTalk project to account for these column names.

Luckily the answer was not all that different from using the caching table… use a temp table in the SP. The simple addition of:

INTO #tempTable

In the appropriate location started the answer. Then, at the bottom of the SP I added:

SELECT * FROM #tempTable as <name used in SQL Adapter> FOR XML AUTO

DROP TABLE # tempTable

The alias was very important, because the SQL Adapter creation had assigned the name used as the returned dataset and I did not want to break this (note, you can adjust the SQL Adapter XSD to account for this, but not in this posting).