Reed Me

Development schtuff, SQL Server & other random geekness. Now with more [fill in the blank]!

Setting FastParse programmatically for SSIS Packages

Setting FastParse programmatically for SSIS Packages

  • Comments 1

I've been otherwise occupied for a bit, but there are lots of little things about programmatically generating SQL Server 2005 Integration Services (SSIS) packages that I don't want to forget... so this is a good place to remember them. Heh.

This is also a good opportunity to try out the code formatter plugin for Live Writer. Seems to work well (on Vista with IE7).

 

   1:  [... snip ...]
   2:   
   3:  using dts = Microsoft.SqlServer.Dts.Runtime; // C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.SqlServer.ManagedDTS.dll
   4:   
   5:  [... snip ...]
   6:   
   7:  namespace Microsoft.CodeGeneration.SqlServer.IntegrationServices
   8:  {
   9:      /// <summary>
  10:      /// Framework class for generating SSIS packages.
  11:      /// </summary>
  12:      public class PackageGenerator
  13:      {
  14:   
  15:  [... snip ...]
  16:   
  17:          /// <summary>
  18:          /// Append a flat file source to the DFT.
  19:          /// </summary>
  20:          /// <param name="package"></param>
  21:          /// <param name="dft"></param>
  22:          /// <param name="componentName"></param>
  23:          /// <param name="cm"></param>
  24:          private static pw.IDTSComponentMetaData90 CreateFlatFileSource(dts.Package package, 
  25:              dts.Executable dft, string componentName, dts.ConnectionManager cm)
  26:          {
  27:              // See also ms-help://MS.VSCC.v80/MS.VSIPCC.v80/MS.SQLSVR.v9.en/dtsref9/html/c06065cf-43e5-4b6b-9824-7309d7f5e35e.htm
  28:              dts.TaskHost th = (dts.TaskHost)dft;
  29:              Microsoft.SqlServer.Dts.Pipeline.Wrapper.MainPipe mp =
  30:                  (Microsoft.SqlServer.Dts.Pipeline.Wrapper.MainPipe)th.InnerObject;
  31:              Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSComponentMetaData90 ffs =
  32:                   mp.ComponentMetaDataCollection.New();
  33:              ffs.ComponentClassID = "DTSAdapter.FlatFileSource.1";
  34:   
  35:              // Get the design time instance of the component.
  36:              Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapper instance = ffs.Instantiate();
  37:   
  38:              // Initialize the component
  39:              instance.ProvideComponentProperties();
  40:   
  41:              // These have to be set *after* ProvideComponetProperties() is called -- or their values are lost. :-(
  42:              ffs.Name = componentName;
  43:              ffs.Description = componentName;
  44:   
  45:              // Specify the connection manager.
  46:              ffs.RuntimeConnectionCollection[0].ConnectionManager = dts.DtsConvert.ToConnectionManager90(cm);
  47:              ffs.RuntimeConnectionCollection[0].ConnectionManagerID = package.Connections[cm.Name].ID;
  48:   
  49:              // Setup the custom component properties.
  50:              instance.SetComponentProperty("RetainNulls", true); // Treat empty columns as null.
  51:   
  52:              // Reinitialize the metadata.
  53:              // HACK Does this require a live connection to a valid schema? Yes, unfortunately.
  54:              instance.AcquireConnections(null);
  55:              instance.ReinitializeMetaData();
  56:              instance.ReleaseConnections();
  57:   
  58:              // Set FastParse where appropriate? They can't be set until after the metadata voodoo?
  59:              foreach (pw.IDTSOutputColumn90 outcol in 
  60:                  ffs.OutputCollection["Flat File Source Output"].OutputColumnCollection)
  61:              {
  62:                  switch (outcol.DataType)
  63:                  {
  64:                      case rw.DataType.DT_BYTES:
  65:                      case rw.DataType.DT_EMPTY:
  66:                      case rw.DataType.DT_IMAGE:
  67:                      case rw.DataType.DT_NTEXT:
  68:                      case rw.DataType.DT_NULL:
  69:                      case rw.DataType.DT_STR:
  70:                      case rw.DataType.DT_TEXT:
  71:                      case rw.DataType.DT_WSTR:
  72:                          // Skip the text types because FastParse cannot be set for them.
  73:                          break;
  74:                      default:
  75:                          outcol.CustomPropertyCollection["FastParse"].Value = true;
  76:                          break;
  77:                  }
  78:              }
  79:   
  80:              return ffs;
  81:          }
  82:   
  83:  [... snip ...]
  84:   
  85:      }
  86:  }
Leave a Comment
  • Please add 2 and 7 and type the answer here:
  • Post