How to create an SSIS Execute SQL Task Programatically?

Question

I am creating an SSIS package programatically in vb.net version 4.0 for SQL server 2008 R2 and I am having difficulty creating an Execute SQL Task. The code for the task is as follows:

  Public Sub CreateExecuteSQLTask()
    Dim TaskHost =  DirectCast(Package.Executables.Add("Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask, Microsoft.SqlServer.SQLTask, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"), Microsoft.SqlServer.Dts.Runtime.TaskHost) 
    TaskHost.Name = "Task"
    TaskHost.Description = "Task Description"
    Dim ExecuteSQLTask As Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask
    ExecuteSQLTask = DirectCast(TaskHost.InnerObject, ExecuteSQLTask)
    ExecuteSQLTask.Connection = DestinationDBConnectionManager.ID
    ExecuteSQLTask.SqlStatementSource = "Select * from Employees"
  End Sub

When the above code runs I get the following exception when casting the TaskHost to an Execute SQL command.

Unable to cast COM object of type 'System.__ComObject' to class type 'Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask'. Instances of types that represent COM components cannot be cast to types that do not represent COM components; however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface.

I have used the following article as a reference:

Creating Packages in code

Please advise how I can get past this issue.

Which version of Visual Studio are you using?

Edmund Schweppe
– Edmund Schweppe

2013-01-21 17:58:18 +00:00
Commented Jan 21, 2013 at 17:58 — Edmund Schweppe
– Edmund Schweppe, Commented Jan 21, 2013 at 17:58

Brett · Accepted Answer · 2013-01-24 09:08:47Z

1

I managed to work out the problem.

Dim ExecuteSQLTask As Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask

needs to be changed to

Dim ExecuteSQLTask As Dts.Tasks.ExecuteSQLTask.IExecuteSQLTask

As the exception suggested, the COM component can be cast to an interfaces. In the above case, i was casting the COM component to the ExecuteSQLTask class rather than the interface.

I found that casting the COM component is a lot easier than setting the SQL task's properties through XML.

answered Jan 24, 2013 at 9:08

Brett

331 silver badge9 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kyle Hale · Accepted Answer · 2013-01-23 22:14:21Z

0

Your problem is .Net 4.0. Or, more specifically, the ExecuteSQLTask assembly code has something in it that makes it incompatible with .Net 4.0. So just add the following lines to your app.config file:

<startup useLegacyV2RuntimeActivationPolicy="true">
  <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0"/>
</startup>
<runtime>
  <NetFx40_LegacySecurityPolicy enabled="true"/>
</runtime>

And it'll work. No, I have no idea why.

answered Jan 23, 2013 at 22:14

Kyle Hale

8,1301 gold badge40 silver badges59 bronze badges

1 Comment

Brett Over a year ago

Thank you for this suggestion. I had read an article that suggested this would resolve my problem, unfortunately it did not. I managed to solve this problem and have answered the question in this post.

Collectives™ on Stack Overflow

How to create an SSIS Execute SQL Task Programatically?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related