How to read sql tasks and so on from a package programmatically??

How to read sql tasks and so on from a package programmatically??

• Whoisit

  I don't get the point. Could you please be more specific

  Thanks again,

  
  
  

• Wayne Sepega

  Thanks a lot for that.

  
  
  

• ranasrule

  Below is a simple example which loads a package from the file system, finds the Execute Sql Task, changes one if its properties, and then gives options to persist the changes or execute the changed packaged.

  More information about the SSIS object model and can be found at: http://msdn2.microsoft.com/en-us/library/ms137709.aspx

  // using statements
  using Microsoft.SqlServer.Dts.Runtime;
  using Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask;

  // create in instance of Microsoft.SqlServer.Dts.Runtime.Application
  Application app = new Application();

  // load an existing package
  Package pkg = app.LoadPackage(@"C:\TestPackage.dtsx", null);

  // find Execute Sql Task
  ExecuteSQLTask task = null;

  foreach (Executable exe in pkg.Executables)
  {
  // the package in this example only has one Execute Sql Task so this
  // is sufficient to find the task, otherwise base your find on the unique name
  // given to the task
  TaskHost tmpTaskHost = (TaskHost)exe;
  if (tmpTaskHost.InnerObject is ExecuteSQLTask)
  {
  task = tmpTaskHost.InnerObject as ExecuteSQLTask;
  break;
  }
  }

  // update the task's properties
  if (task != null)
  {
  task.SqlStatementSource = "select * from tbl";
  }

  // if you want to save the changes
  // app.SaveToXml("newPackage.dtsx", pkg, null);

  // if you want to execute the package
  // DTSExecResult result = pkg.Execute();

  Hope this helps,
  Patrik

  
  
  

• Cslom

  Do you receive an error message
  
  Have you read http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.package.executables.aspx
  
  
  

• fabianus

  hi guys,

  Any idea

  
  
  

• Bart371

  No, I wanna make an application of mine.

  It cannot so difficult as all of this.

  
  
  

• Walter Fuchs

  No, not all.

  I think that I wrote my post a little bit confused. I meant, how to see/read the properties for a Sql Task, as SqlStatement, Sqlsourcetype...

  I receive this:

  {Microsoft.SqlServer.Dts.Runtime.Sequence}

  Microsoft.SqlServer.Dts.Runtime.Sequence: {Microsoft.SqlServer.Dts.Runtime.Sequence}

  Thanks again,

  
  
  

• HenryM679

  Does anyone have any idea

  Thanks again,

  
  
  

• DavidP5

  humm. Not very useful for my plan..

  If I've got a SSIS which owns 30 different tasks I'll have 30 assemblies..

  Microsoft.SqlServer.SQLTask.dll.. for Sql Task

  Microsoft.SqlServer.SendMailTask.dll for Send Mail Task

  ..

  ..

  
  
  

• Lexite

  Our goal is just to obtain the metadata for each SSIS. Now, we've got only ten SSIS but in a future there will be a lot of them running.

  Thanks.

  
  
  

• Evan Mulawski

  Enric,

  That's true.

  Something that we haven't gotten to in this thread is what you are trying to accomplish. Can you fill us in on what you're hoping to do. Maybe there's another direction we could help you with.

  Cheers,
  Patrik

  
  
  

• goh6613

  SSIS package traversal Examples (in C#) are available in Microsoft's BI Metadata Samples download.
  
  Also, try out Windows PowerShell. It's essentially a .NET object shell with the ability to perform real-time Intellisense on objects (such a SSIS package objects).
  
  The following lists the package's top level Executables' properties. Its very minimalistic, but create a package with an Execute SQL tasks at the package level; then execute the below against the package.
  
  [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ManagedDTS") > $null;
  $app = new-object Microsoft.SqlServer.Dts.Runtime.Application;
  $p = $app.LoadPackage("c:\sample.dtsx",$null)
  $p.Executables
  
  
  
  
  

• dewclaw

  Enric,

  You'll want to cast the executable from the package's executable collection to Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask, then you'll have access to the properties you are trying to set.

  ~Patrik

  
  
  
• How to read sql tasks and so on from a package programmatically??