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 "Object reference not set to an instance of an object" in Execute SQL Task

When I try and parse a simple execute of a stored procedure in the Execute SQL Task Editor, I get the error:

"Object reference not set to an instance of an object"

Now, I ONLY get this error on my laptop, so I'm assuming it might be an installation error. I've tried to do the exact same thing in other environments, and received no error. Here's what I'm doing:

1. I create a simple stored procedure on a SQL 2005 database. Here's what it does:

createprocusp_testsp

as

begin

select'whatever';

end

2. I create a new SSIS package in BIS.

3. I create an ADO.NET connection to the above SQL 2005 database

4. I pull over an Execute SQL Task item from the toolbox to the Control Flow tab.

5. I choose the ADO.NET connectiontype, the connection I created in #2, SQLSourceType of Direct input, SQLStatement is: exec usp_testsp, IsQueryStoredProcedure set to True. And I try ResultSet as both Single row and None

When I try to Parse the Query, I get the above error. If I still try to run the task in the debugger, here's what I get with the ResultSet set to None:

Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "exec usp_testsp" failed with the following error: "Could not find stored procedure 'exec usp_testsp'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

And just so you know, I can execute the sp with no problems. And just to check, I granted execute to public on the sp.

And here's what I get with ResultSet set to Single row:

Error: 0xC00291E2 at Execute SQL Task, Execute SQL Task: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_SingleRow".

I only get this on my laptop. I have SQL Server 2005 SP2 Developer Edition on Windows XP Professional, SP2.

Thanks,

Michael

Hi Michael,

I just saw the same error with "Parse Query" on this task, so I've logged an internal defect for the issue.

As far as actually being able to execute this stored procedure through the Execute SQL Task, try setting IsQueryStoredProcedure to True, your ResultSet to None, and set your SQLStatement to simply "usp_testsp", not "exec usp_testsp". I think this might resolve your problem with execution/debugging.

If you needed the results back from this stored procedure, you'd change ResultSet to SingleRow, and also add a result set binding in the "Result Set" page of the Execute SQL Task editor.

-David

|||

David,

Thanks for the followup.

I tried all of the above and still get the same error. If I switch to OLE DB it parses fine. Since it only happens on this machine, I think it might be a problem with the installation...though I only started having this problem in the last 3 days.

-Michael

|||

Additionally, I'm having trouble (but only yesterday) with doing estimated explain plans in managment studio. I don't have the problem today, though. And I still have the parsing problem with BIS. Here's the other problem:

When I try and do a Display Estimated Query Plan in SQL Server Management Studio on a SQL 2005 database, I get the following error:

An error occurred while executing batch. Error message is: Error processing execution plan results. The error message is:

The type initializer for 'Microsoft.SqlServer.Management.SqlMgmt.ShowPlan.XmlPlanNodeBuilder' threw an exception.

Cannot execute a program. The command being executed was "C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\csc.exe" /noconfig /fullpaths @."C:\Documents and Settings\xxxxxxx\Local Settings\Temp\zcxrpqb0.cmdline".

Not sure if they're related.

Thanks,

Michael

Getting "Object reference not set to an instance of an object" in Execute SQL Task

When I try and parse a simple execute of a stored procedure in the Execute SQL Task Editor, I get the error:

"Object reference not set to an instance of an object"

Now, I ONLY get this error on my laptop, so I'm assuming it might be an installation error. I've tried to do the exact same thing in other environments, and received no error. Here's what I'm doing:

1. I create a simple stored procedure on a SQL 2005 database. Here's what it does:

createprocusp_testsp

as

begin

select'whatever';

end

2. I create a new SSIS package in BIS.

3. I create an ADO.NET connection to the above SQL 2005 database

4. I pull over an Execute SQL Task item from the toolbox to the Control Flow tab.

5. I choose the ADO.NET connectiontype, the connection I created in #2, SQLSourceType of Direct input, SQLStatement is: exec usp_testsp, IsQueryStoredProcedure set to True. And I try ResultSet as both Single row and None

When I try to Parse the Query, I get the above error. If I still try to run the task in the debugger, here's what I get with the ResultSet set to None:

Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "exec usp_testsp" failed with the following error: "Could not find stored procedure 'exec usp_testsp'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

And just so you know, I can execute the sp with no problems. And just to check, I granted execute to public on the sp.

And here's what I get with ResultSet set to Single row:

Error: 0xC00291E2 at Execute SQL Task, Execute SQL Task: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_SingleRow".

I only get this on my laptop. I have SQL Server 2005 SP2 Developer Edition on Windows XP Professional, SP2.

Thanks,

Michael

Hi Michael,

I just saw the same error with "Parse Query" on this task, so I've logged an internal defect for the issue.

As far as actually being able to execute this stored procedure through the Execute SQL Task, try setting IsQueryStoredProcedure to True, your ResultSet to None, and set your SQLStatement to simply "usp_testsp", not "exec usp_testsp". I think this might resolve your problem with execution/debugging.

If you needed the results back from this stored procedure, you'd change ResultSet to SingleRow, and also add a result set binding in the "Result Set" page of the Execute SQL Task editor.

-David

|||

David,

Thanks for the followup.

I tried all of the above and still get the same error. If I switch to OLE DB it parses fine. Since it only happens on this machine, I think it might be a problem with the installation...though I only started having this problem in the last 3 days.

-Michael

|||

Additionally, I'm having trouble (but only yesterday) with doing estimated explain plans in managment studio. I don't have the problem today, though. And I still have the parsing problem with BIS. Here's the other problem:

When I try and do a Display Estimated Query Plan in SQL Server Management Studio on a SQL 2005 database, I get the following error:

An error occurred while executing batch. Error message is: Error processing execution plan results. The error message is:

The type initializer for 'Microsoft.SqlServer.Management.SqlMgmt.ShowPlan.XmlPlanNodeBuilder' threw an exception.

Cannot execute a program. The command being executed was "C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\csc.exe" /noconfig /fullpaths @."C:\Documents and Settings\xxxxxxx\Local Settings\Temp\zcxrpqb0.cmdline".

Not sure if they're related.

Thanks,

Michael

sql

GET MAX Date of previous year

Dear Friends,

I need to create a new column in a VIEW with a reference date to call from SQL Analysis Services 2005.

FactTable ID Name DateID Date 1 blabla… 8225 16-03-2005 2 blabla… 12-08-2006 … 9999 … … TIME Dimension DateID Day Year MonthKey Month QuarterKey Quarter 8224 15-03-2005 0:00 2005 20053 March 20051 Q1 8225 16-03-2005 0:00 2005 20053 March 20051 Q1 8226 17-03-2005 0:00 2005 20053 March 20051 Q1 8227 18-03-2005 0:00 2005 20053 March 20051 Q1 .. … … … … … … 9321 16-03-2008 0:00 2008 20083 March 20081 Q1 9322 17-03-2008 0:00 2008 20083 March 20081 Q1 9323 18-03-2008 0:00 2008 20083 March 20081 Q1 9324 19-03-2008 0:00 2008 20083 March 20081 Q1 9325 20-03-2008 0:00 2008 20083 March 20081 Q1 9326 21-03-2008 0:00 2008 20083 March 20081 Q1 9327 22-03-2008 0:00 2008 20083 March 20081 Q1 9328 23-03-2008 0:00 2008 20083 March 20081 Q1 9329 24-03-2008 0:00 2008 20083 March 20081 Q1 9330 25-03-2008 0:00 2008 20083 March 20081 Q1 9331 26-03-2008 0:00 2008 20083 March 20081 Q1 9332 27-03-2008 0:00 2008 20083 March 20081 Q1 9333 28-03-2008 0:00 2008 20083 March 20081 Q1 Result ID Name DateID Date RefDate 1 blabla… 8225 16-03-2005 25-12-2005 2 blabla… 12-08-2006 29-12-2006 … 9999 … … …

The refDate is the last VALID date from previous year for each date in each row of FactTable. My TimeTable only has valid dates (does not has holidays, saturday, sunday and forcedHolidays). so I need a query to get for each date in each row of FactTable the last date for previous year. Probably using the parameter "year" of the date in each row in facttable (using Datepart)

Someone help me?

Help me please!|||

Pedro,

To clarify: RefDate is the highest date from the Time Dimension table for a given year (dateid should be ignored)?

Also, you say "the last date for previous year" but your sample result shows the last date of the same year. Should DateID=8225 have 25-12-2004?

|||

Dalej you are write!!

I made a mistake, is

CORRECT Result ID Name DateID Date RefDate 1 blabla… 8225 16-03-2005 25-12-2004 2 blabla… 12-08-2006 29-12-2005 … 9999 … … …

The RefDate is the highest date from Time Table for a given year (for each date in each row)

Could help me?!

THANKS

|||

Code Snippet

select ft.*, td.RefDate

from FactTabl ft

innerjoin

(

select [Year],max([Day])as RefDate

from [Time Dimension]

groupby [Year]

)as td

on(ft.datepart(yy, Date)-1)= td.[Year]

|||

Dear alej,

I customize your statment to my database and there is an error...

Code Snippet

select ft.*, td.RefDate

from FactCashFlows ft

innerjoin

(

select [Ano],max([Dia])as RefDate

from [DimTime]

groupby [Ano]

)as td

on(ft.datepart(yy, T_Dia)-1)= td.[Ano]

The error is:

Code Snippet

Msg 4121, Level 16, State 1, Line 1

Cannot find either column "ft" or the user-defined function or aggregate "ft.datepart", or the name is ambiguous.

When I was trying to customize your code I saw that I dont have the DATE in my FactTable, only the integer foreign key for time dimension. So, I created the FactCashFlow as a view to do a inner join to time table to get the date value.

If I can do it only in one view would be perfect!!

Could help me?
Thanks!!!

|||

OK!

I changed and this statment finally works:

Code Snippet

select ft.*, td.RefDate

from FactCashFlows ft

innerjoin

(

select [Ano],max([Dia])as RefDate

from [DimTime]

groupby [Ano]

)as td

on(datepart(yy, ft.T_Dia)-1)= td.[Ano]

But I have 2 views, and would be better using only one...

I need to change the the first select to get the date value from time dimension...

|||

In order to have only one view to call from SSAS, I need the query something like this:

Code Snippet

select ft.*, td.RefDate, MyTable.Dia

from(SELECT Dia FROM dbo.Time INNERJOIN dbo.CashFlows ON CF_RKData_ID=time.ID) MyTable INNERJOIN

CashFlows ft

innerjoin

(

select [Ano],max([Dia])as RefDate

from [DimTime]

groupby [Ano]

)as td

on(datepart(yy, ft.T_Dia)-1)= td.[Ano]

Code Snippet

Msg 102, Level 15, State 1, Line 11

Incorrect syntax near 'Ano'.

And this view will be the FactCashFlows in SSAS!

But this is wrong, could someone help me?

Thanks!

|||

OK I Found the solution...

Code Snippet

select ft.*, td.RefDate

from(dbo.time INNERJOIN CashFlows ft ON CF_RKData_ID=time.ID)

innerjoin

(

select [Ano],max([Dia])as RefDate

from [DimTime]

groupby [Ano]

)as td

on(datepart(yy, dbo.time.Dia)-1)= td.[Ano]

THANKS ALL!!!