Getting a reference to a Script task in VB.net

I am trying to get a reference to a script task so I can manipulate it's properties. However I can't seem to figure it out? I have a reference to Microsoft.SqlServer.ManagedDTS and Microsoft.SqlServer.Dts.Design and based on BOL they show

Imports System

Imports System.Collections.Generic

Imports System.Text

Imports Microsoft.SqlServer.Dts.Runtime

Imports Microsoft.SqlServer.Dts.Tasks.BulkInsertTask

Imports Microsoft.SqlServer.Dts.Tasks.FileSystemTask

Imports Microsoft.SqlServer.Dts.Runtime

But I get errors saying

Imports Microsoft.SqlServer.Dts.Tasks.BulkInsertTask

Imports Microsoft.SqlServer.Dts.Tasks.FileSystemTask

can't be found? What do I need to reference or Import to be able to see the Tasks and/or Task types so I can convet InnerObject to a ScriptTask type and manipulate?

What am doing wrong?

Dim pkgPath As String = "C:\Program Files\\ETL\ODS\Policy\"Dim pkgName As String = "LoadOdsCountryCodes"

Dim pkg1 As String = pkgPath + pkgName + ".dtsx"

Dim pkg2 As String = pkgPath + pkgName + "2.dtsx"

Dim app As Application = New Application()

Dim pkg As Package = app.LoadPackage(pkg1, Nothing)

Dim x As Executable

For Each x In pkg.Executables

Dim t As TaskHost = CType(x, TaskHost)

If t.Name = "SCT Set Global Variables" Then

Dim sct As ? = ctype(t.InnerObject,?)

End If

Next

You cannot manipulate properties of objects in the package directly from script within the package. Period.

What you CAN do is put expressions on properties which affect them at execution-time.

-Jamie

|||

I am not trying to do it from within a package. this is outside of SSIS in the vb.net IDE.

I have figured it out.

Imports Microsoft.SqlServer.Dts.Runtime

Imports Microsoft.SqlServer.Dts.Tasks.ScriptTask

Module SSISPackage

Public Sub main()

Dim pkgPath As String = "C:\Program Files\Insurity\Reporting Decisions\ETL\ODS\Policy\"

Dim pkgName As String = "LoadOdsCountryCodes"

Dim pkg1 As String = pkgPath + pkgName + ".dtsx"

Dim pkg2 As String = pkgPath + pkgName + "2.dtsx"

Dim app As Application = New Application()

Dim pkg As Package = app.LoadPackage(pkg1, Nothing)

Dim x As Executable

For Each x In pkg.Executables

Dim t As TaskHost = CType(x, TaskHost)

If t.Name = "SCT Set Global Variables" Then

Dim sct As ScriptTask = CType(t.InnerObject, ScriptTask)

Dim cp As ScriptTaskCodeProvider = sct.CodeProvider

Dim Moniker As String = "dts://Scripts/" & sct.VsaProjectName & "/" & sct.VsaProjectName & ".vsaproj"

Dim code As String = cp.GetSourceCode(Moniker)

End If

Next

But still does not seem to show the user text added by the developer? Strange.

|||

I had to add a reference to:

Microsoft.SqlServer.ScriptTask

Microsoft.SqlServer.VSAHosting

to my project and then this would work:

Imports Microsoft.SqlServer.Dts.Tasks.ScriptTask

|||

Ah OK. Sorry, my bad!

Glad you got it working.

-Jamie

sql

Getting "extremely complex query" error when using xml data type parameters

Good morning, I am trying to figure out, and understand, why I am receiving the error that I am when I use xml data type parameters. If you look at the proc below, I have a proc with 8 xml data type parameters. When i call this proc adn pass in the values for the parameters, I recieve is the following:

The query processor ran out of internal resources and could not produce a query plan.

This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions.

Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.

In debugging, I found that I DON'T get this error if i have only 3 or 4 xml data type parameters, but if I add more than 4, I get the above error.

The proc looks like the following:

CREATE PROCEDURE dbo.dosomething

@.BegDate DateTime,

@.EndDate DateTime,

@.InClass xml,

@.InDept xml,

@.InCat xml,

@.InPayPer xml,

@.InEmp xml,

@.InLoc xml,

@.InLeave xml,

@.InSalClass xml

AS

SELECT blah, blah

FROM blah

WHERE table.column IN (select [JoinValues].[ref].value('@.id', 'int') from @.InEmp.nodes('ids/values') JoinValues([ref])

AND...join other xml datatypes just like the one above

I call the stored proc by the following...and notice that i am not passing anything very large for each value...

exec dosomething

'20060101','20060101',

N'<ids>

<values id="1" />

<values id="3" />

</ids>'

,N'<ids>

<values id="1" />

<values id="3" />

</ids>',

N'<ids>

<values id="1" />

<values id="3" />

</ids>',

N'<ids>

<values id="1" />

<values id="3" />

</ids>',

N'<ids>

<values id="1" />

<values id="3" />

</ids>',

N'<ids>

<values id="1" />

<values id="3" />

</ids>',

N'<ids>

<values id="1" />

<values id="3" />

</ids>',

N'<ids>

<values id="1" />

<values id="3" />

</ids>'

Does anyone have any suggestions as to what might be causing this? Am i doign my compare in my proc incorrectly? Any insight would be greatly appreciated...

Scott

Hi Scott

This error message normally means that you should rewrite your query. Could you please post a complete repro to either my work address (mrys at the microsoft.com address) or post it here and we will take a look.

Thanks

Michael

PS: Sorry I thought I posted this reply yesterday but my travel connectivity at the moment seems to be shakey.

|||

I guess the problem is not you have lots xml data type parameters. It's caused by the WHERE clause in your query :

"WHERE table.column IN (select [JoinValues].[ref].value('@.id', 'int') from @.InEmp.nodes('ids/values') JoinValues([ref])

AND...join other xml datatypes just like the one above"

You can try to define some temp tables and shred the ID values in the xml into those tables. Jojn those temp tables in your query.

Get the top 2 sold without using temp table.

Hi
I ran into a small issue and have a mental block on how to figure it out
without using a temp table. what I am basically trying to do is retreive the
result set without the use of a temp table and use a derived table approach.
The scenario is I have a products table where in i have the list of products
that are sold. I am trying to track down those products the have the highest
sales (in $$). Mind you its the number sold. I am not interested in how many
got sold. I am interested in which had the top 5 highest sales.
Here is the DDL and the T-SQL Queries that I came up with:
Create table #Products (ProductID INT,ProductName VARCHAR(50),Price Money)
INSERT INTO #Products VALUES (145,'Tomatoes',230.00)
INSERT INTO #Products VALUES (145,'Tomatoes',130.00)
INSERT INTO #Products VALUES (145,'Tomatoes',100.00)
INSERT INTO #Products VALUES (145,'Tomatoes',200.00)
INSERT INTO #Products VALUES (150,'Tide',400.00)
INSERT INTO #Products VALUES (105,'Cheese',120.00)
INSERT INTO #Products VALUES (105,'Cheese',560.00)
INSERT INTO #Products VALUES (150,'Tide', 80.00)
--1). Query 1 Gets me the top 5 products which are the highest sold (in
number).
SELECT TOP 5 ProductID, ProductName, SUM(Price) AS Price,Count(ProductID) AS
TotalSold
FROM #Products
GROUP BY ProductID, ProductName
ORDER BY Count(ProductID) DESC
--2). Now I want those products that have the highest sales.($$$). so i used
a temp table to get the results.
--Get top 5 which are the highest sold (that means the sum of price for them
should be higher).
--Step 1.Get into a temp table
SELECT TOP 5 ProductID, ProductName, SUM(Price) AS Price,Count(ProductID) AS
TotalSold
INTO #Temp
FROM #Products
GROUP BY ProductID, ProductName
ORDER BY Count(ProductID) DESC
--Get the top sold
Select DISTINCT TOP 5 ProductID,ProductName,Price
from #temp
WHERE (SELECT count(*) from #temp E2
WHERE E2.Price > #temp.Price
) < 2
ORDER BY #temp.Price DESC
Drop table #temp
My question is there anyway I can avoid the temp table and the query 2 can
be written using a derived table?. I am thinking more like I have to use the
max and top clause together. Any rewrite would be helpful.
Thanks
Mwhat about this?
SELECT TOP 2 ProductID, ProductName, SUM(Price) AS
Price,Count(ProductID) AS
TotalSold
FROM #Products
GROUP BY ProductID, ProductName
ORDER BY SUM(Price) DESC
http://sqlservercode.blogspot.com/|||Beautiful way of rewriting. It works. so all it needs is a change is order
by. Thanks SQL.
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1139496548.028813.239790@.f14g2000cwb.googlegroups.com...
> what about this?
> SELECT TOP 2 ProductID, ProductName, SUM(Price) AS
> Price,Count(ProductID) AS
> TotalSold
> FROM #Products
> GROUP BY ProductID, ProductName
> ORDER BY SUM(Price) DESC
> http://sqlservercode.blogspot.com/
>|||Though not very intuitive, you can use TOP 5 in the derived table as well:
SELECT TOP 2 *
FROM ( SELECT TOP 5 ProductID, SUM( Price ), COUNT( * )
FROM #Products
GROUP BY ProductID
ORDER BY COUNT( * ) DESC ) D ( ProductID, Price, Total )
ORDER BY Price DESC ;
Anith|||Thanks Anith. Even this is a good solution.
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:e9cm0sYLGHA.1028@.TK2MSFTNGP11.phx.gbl...
> Though not very intuitive, you can use TOP 5 in the derived table as well:
> SELECT TOP 2 *
> FROM ( SELECT TOP 5 ProductID, SUM( Price ), COUNT( * )
> FROM #Products
> GROUP BY ProductID
> ORDER BY COUNT( * ) DESC ) D ( ProductID, Price, Total )
> ORDER BY Price DESC ;
> --
> Anith
>