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

Hi everyone,

Once I've accesed to package by means of LoadFromSqlServer method how to read its Sql Tasks, for example?

I'm trying with the Executables property but unsuccessfully results:

pkg.Executables.Item(0)

Thanks in advance,

Do you receive an error message?

Have you read http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.package.executables.aspx|||

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,

|||

hi guys,

Any idea?

|||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

|||

No, I wanna make an application of mine.

It cannot so difficult as all of this.

|||

Does anyone have any idea?

Thanks again,

|||

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

|||

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

Thanks again,

|||

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

|||

Thanks a lot for that.

|||

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

..

..

|||

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

|||

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.