Script to assign task name to a variable

Hi,

I have a OnPre Execute event on a task in SSIS. I want to be able to assign the parent task name to this event to a variable.

I am using a script task. Are there any examples available on how to achieve this?
I have looked at the DTSProperty, but have to admit that I am struggling with this.

Once I have assigned the taks name to the varaible, I can then use my bubble up error handler, and still be able to log the name of the task which caused the error.

Many thnaks

RudyThe name of the task (more accurately - the container) that threw the event is available in the System::SourceName variable in the eventhandler.

Is that what you want?

Here's an example of doing something very similar: http://blogs.conchango.com/jamiethomson/archive/2005/06/11/1593.aspx

-Jamiesql

Script task: Dts.Logging.Log

I am trying to understand the use-case of Dts.Logging.Log.

Following BOL, I see that I can use Dts.Log to send a custom log message to my log provider. The following example code produces an event called "User:ScriptTaskLogEntry", with the message "My custom message here".

Dim dataBytes(0) As Byte

Dts.Log("My custom message here", 0, dataBytes)

I would like to be able to produce my own custom event (lets call it "MyCustomEvent"), as well as a custom log message. I thought it would be possible via Dts.Logging.Log, which requires that I specify an event name, in addition to a log entry. However, when I place the following code in the script task, I do not get any logging whatsoever.

Dim dataBytes(0) As Byte

Dts.Logging.Log("MyCustomEvent", System.Environment.MachineName, System.Environment.UserName, "Custom Log", "", "", "My custom message here", Now, Now, 0, dataBytes)

Any thoughts?

Thanks!

The script task can't log custom message type. The problem is that custom log message needs to be declared by task in advance so that it appears in the Details tab of the Logging window.

Custom task (reusable separately packaged .NET or C++ code, that appears on toolbox just like any Microsoft task) can do it, but this functionality is not available in Script task.sql

Script task: Bindingsource

I used a binding source in my script task codes to filter the data table. when i used that, even if i declare it (dim bs as bindingsource), i'm still having an error - "type bindingsource is not defined".

cherrie

Hi, bindingsource is part of the forms name space. Does your code include

Imports System.Windows.Forms

Script task: ADODB connection and recordset

Hi,

I used adodb connection and recordset in script task. but i have an error saying adodb is not defined. how do i add it to reference? or, is adodb can run in script task or only ado.net?

cherrie

Some information in here about moving DLLs about that may help: http://blogs.conchango.com/jamiethomson/archive/2005/02/08/960.aspx

-Jamie

|||

Hi,

It's mentioned on your blog that you used a variable object and assigned that to recordset destination. I did the same. I created, first, an "ADODB" variable with object data type. then I added a data flow task in my project. Also inside it, i put an Oledb source connected to my recordset destination where the adodb variable is assigned. Then I created a script task connected to the data flow task (which handles the recordset destination). however, when i began using my adodb variable in the scriptask (dim cn as adodb), it says type 'adodb' is not defined.

Did i miss something?

Cherrie

Script Task working in Visual Studio but not when the package is run by a job?

I have a script that changes the name of a file after a data upload. The script works fine if I execute the package in Visual Studio but when I run the file package from a SQL server job it does not rename the file. The data does get uploaded it just does not run the final script.

Any help would be appreciated.

Steve

Have you enabled logging to get the error?

The problems when running under Agent are mostly related to authentication and permissions issues - as the job probably runs under different credentials compared to interactive execution. Your options are either configure job to run under your account, or fix the permissions to allow job account to perform the operation that is failing.

See this KB for troubleshooting steps:
http://support.microsoft.com/kb/918760

Script Task vs. Script Component

Under "Control Flow Items" appears the Script Task. Under "Data Flow Sources" appears the Script Component. The Script Task also supports .NET scripting so why is it intended to be used with control flow and not data flow?

TIA,

barkingdog

The Script Task executes in isolation. It has no reliance on any other task nor vice versa. Components however work in unison with other components in the same data-flow. That is why the script task works in the control-flow and script component works in the data-flow.

What requirement do you have that caused you to ask?

-Jamie

|||

Jamie,

I don't have any requirements as such. I am learning about SSIS as fast as I can any many of my questions are just about things that seem peculiar to me. The answers I get often help me understand the "bigger" picture of how things in SSIS fit together. In many ways, I'm still at the "how many letters are there in your alphabet?" phase of SSIS

Barkding

sql

Script Task Variables

script task: there should be another way to select variables than the comma seperated list

One has to type in a whole list of variables, hoping not to make any mistakes

IntelliSense for example?

But hey, I'm not complaining...

greets

There is! You can do it in code.

Writing to a variable from a script task
(http://blogs.conchango.com/jamiethomson/archive/2005/02/09/964.aspx)

Still no intellisense though!!

I highly recommend you use the code option because this minimises the risk of variable locking. I plan to blog about this soon and have written about it in an upcoming article in SQL Server Standard.

-Jamie

|||

ok, I'll do it your way

thanks

|||

Dear Jamie,

Your method works fine for 1 variable.
I assume you know this, but for other readers of this thread:

ms-help://MS.VSCC.v80/MS.VSIPCC.v80/MS.SQLSVR.v9.en/dtsref9mref/html/T_Microsoft_SqlServer_Dts_Runtime_VariableDispenser.htm

quoting:

There are two scenarios for using the variable dispenser.

You want just one variable. In this scenario, call LockOneForRead or LockOneForWrite, and a collection with one element is returned.

You want several variables. In this scenario, call LockForRead and LockForWrite several times, one for each variable. This builds up two lists, one list that contains variables for reading and a list of variables for writing. Next, call GetVariables, which gives you a collection that contains all of the locked variables. If GetVariables succeeds, the two lock lists, which are the lists of variable names, not actual locks, is cleared.

To clear the locks, call Unlock on the collection when finished to explicitly release the locks. This unlocks the variables themselves. If GetVariables fails, the lists remain unchanged, and you can call GetVariables again. If you still do not succeed, call Reset to clear the lists and bring the variable dispenser back to its initial state.

Cheers,

Tom

Script task User defined variable and Data Reader Source SQL Statement

Hello,

Please can anyone tell me if or how I have to cross reference to a user defined variable from a script task in the SQL of a Data Reader Source?

The script task creates a variable for the last Sales Ledger Session. The SQL in the data reader source that updates the DW sales invoice lines file based upon those invoice lines where the session number is greater than the value from the script task.

The SQL command in the custom properties doesn't cross reference back to the variable.

If anyone can help or needs further detail to help please post a resonse.

Thanks,

Marcus Simpson

The SqlCommand property can be overridden with a property expression. For data flow components expression are exposed on the parent Data Flow task rather than at the individual component level.

If you are not already familiar with expressions, look them up in Books Online.

To quickly set an expression, select the Data Flow task and hit F4. Then find the Expressions property in the property grid (as shown by the F4 key) and then select the property for the your DR source, SqlCommand. The name will be something like [DataReader Source].[SqlCommand]. Now add an expression that uses the variable as part of the command.

|||

Darren,

Thankyou very much for your response. It was correct.

Marcus.

|||

Marcus Simpson wrote:

Darren,

Thankyou very much for your response. It was correct.

Marcus.

Please mark Darren's post as answered.

Script Task TimeOut

Anybody knows how to change the TimeOut of a Script Task?

Thanks!

What is timing out?

What exactly are you trying to do?

-Jamie

|||

The task call one asp page that makes some updates in a remote sql server.

If I call the asp page without SSIS the proces finish ok, but if I call the asp page with Script Task, the task returns a TimeOut error.

I need that the Script Task wait until the asp work finish.

|||

The script task doesn't have a timeout setting. I would expect that you could set a timeout in your calling code can you not?

Perhaps if you posted your code and the errors people would have a better chance of helping you.

-Jamie

|||

The code of the Script Task is:

Public Sub Main()
Dim myWebClient As WebClient
Dim remoteUri As String = "http://10.3.0.5/GOT/sap/get_articoli_definitivo.asp"

Try
myWebClient = New WebClient()
' Do the actual download
myWebClient.DownloadData(remoteUri)
Dts.TaskResult = Dts.Results.Success
Catch ex As Exception
' Error message we got back.
Dts.Events.FireError(0, String.Empty, ex.Message, String.Empty, 0)
Dts.TaskResult = Dts.Results.Failure
End Try
End Sub

|||

And the error message is?

I'm not saying I'll be able to help but anyone that might be able to help will need as much ifo as possible.

-Jamie

|||

A quick Google produced the following which may help:

http://www.dotnet247.com/247reference/msgs/58/291939.aspx

http://www.dotnet247.com/247reference/msgs/19/98610.aspx

http://www.codecomments.com/archive291-2004-3-160028.html

Did you try googling this yourself?

-Jamie

Script Task scripts will not stop at breakpoints.

I can not debug any of my scripts. They execute just fine, but if I have a breakpoint set in the script code (in VSA), I get the message:

SQL server integration services script task has encountered a problem and needs to close. We are sorry for the inconvenience.

If you were in the middle of something, the information you were working on might be lost.

Debug Close

When I choose "Close" I get this:

sMicrosoft Visual Studio for Applications has lost the link to .

Your work will be exported to <<my Local Settings My Documents path>> when you quit the application.

OK

When I click "OK" my package executes normally, but no breakpoints will fire from that point on.

No need to post your message twice....

Script Tasks, not Script Components, correct?

One workaround is to use a MSGBOX().|||I was in the process of deleting my other post when you replied. I didn't want to post in a thread that had something marked as an answer. Yes, this is for script tasks. I certainly could use MsgBox() but that is hardly an answer to this problem. I could also write text out to a file, call a web service, insert a record into a table, etc. None of those things take the place of stepping through code. Is there a fix in the works for this issue? It really makes script tasks of limited use, or at least it makes them take much longer to develop.|||And you are following these steps?

http://technet.microsoft.com/en-us/library/ms140033.aspx|||With the exception of right-clicking (I was clicking in the margin to set the breakpoint), yes, that is exactly what I did.|||Do you have any SQL Server service packs applied?|||How would I tell?|||

JWardRogers wrote:

How would I tell?

In BIDS, go to Help->About and select SSIS. What is the version number reported there?|||9.00.1399.00|||That's the original.

Try grabbing the SQL Server Service Pack 2 and installing that. Many fixes are contained in SP1 and SP2 as well.|||

Phil Brammer wrote:

That's the original.

Try grabbing the SQL Server Service Pack 2 and installing that. Many fixes are contained in SP1 and SP2 as well.

But unfortunately not the fix for my problem. :-(

SP2 is installed and it is still behaving the same way.

Version is now 9.00.3042.00

|||

I'm still having the same problem...

|||XP? Vista?|||

Server 2003 version 5.2.3790 (service pack 1.0)

Hot Fix: KB931836

4GB RAM

|||

JWardRogers wrote:

Server 2003 version 5.2.3790 (service pack 1.0)

Hot Fix: KB931836

4GB RAM

Do you also develop on a local workstation? Have you tried breakpoints on that if so?

Script Task scripts will not stop at breakpoints.

I can not debug any of my scripts. They execute just fine, but if I have a breakpoint set in the script code (in VSA), I get the message:

SQL server integration services script task has encountered a problem and needs to close. We are sorry for the inconvenience.

If you were in the middle of something, the information you were working on might be lost.

Debug Close

When I choose "Close" I get this:

sMicrosoft Visual Studio for Applications has lost the link to .

Your work will be exported to <<my Local Settings My Documents path>> when you quit the application.

OK

When I click "OK" my package executes normally, but no breakpoints will fire from that point on.

No need to post your message twice....

Script Tasks, not Script Components, correct?

One workaround is to use a MSGBOX().|||I was in the process of deleting my other post when you replied. I didn't want to post in a thread that had something marked as an answer. Yes, this is for script tasks. I certainly could use MsgBox() but that is hardly an answer to this problem. I could also write text out to a file, call a web service, insert a record into a table, etc. None of those things take the place of stepping through code. Is there a fix in the works for this issue? It really makes script tasks of limited use, or at least it makes them take much longer to develop.|||And you are following these steps?

http://technet.microsoft.com/en-us/library/ms140033.aspx|||With the exception of right-clicking (I was clicking in the margin to set the breakpoint), yes, that is exactly what I did.|||Do you have any SQL Server service packs applied?|||How would I tell?|||

JWardRogers wrote:

How would I tell?

In BIDS, go to Help->About and select SSIS. What is the version number reported there?|||9.00.1399.00|||That's the original.

Try grabbing the SQL Server Service Pack 2 and installing that. Many fixes are contained in SP1 and SP2 as well.|||

Phil Brammer wrote:

That's the original.

Try grabbing the SQL Server Service Pack 2 and installing that. Many fixes are contained in SP1 and SP2 as well.

But unfortunately not the fix for my problem. :-(

SP2 is installed and it is still behaving the same way.

Version is now 9.00.3042.00

|||

I'm still having the same problem...

|||XP? Vista?|||

Server 2003 version 5.2.3790 (service pack 1.0)

Hot Fix: KB931836

4GB RAM

|||

JWardRogers wrote:

Server 2003 version 5.2.3790 (service pack 1.0)

Hot Fix: KB931836

4GB RAM

Do you also develop on a local workstation? Have you tried breakpoints on that if so?sql

Script Task scripts will not stop at breakpoints.

I can not debug any of my scripts. They execute just fine, but if I have a breakpoint set in the script code (in VSA), I get the message:

SQL server integration services script task has encountered a problem and needs to close. We are sorry for the inconvenience.

If you were in the middle of something, the information you were working on might be lost.

Debug Close

When I choose "Close" I get this:

sMicrosoft Visual Studio for Applications has lost the link to .

Your work will be exported to <<my Local Settings My Documents path>> when you quit the application.

OK

When I click "OK" my package executes normally, but no breakpoints will fire from that point on.

No need to post your message twice....

Script Tasks, not Script Components, correct?

One workaround is to use a MSGBOX().|||I was in the process of deleting my other post when you replied. I didn't want to post in a thread that had something marked as an answer. Yes, this is for script tasks. I certainly could use MsgBox() but that is hardly an answer to this problem. I could also write text out to a file, call a web service, insert a record into a table, etc. None of those things take the place of stepping through code. Is there a fix in the works for this issue? It really makes script tasks of limited use, or at least it makes them take much longer to develop.|||And you are following these steps?

http://technet.microsoft.com/en-us/library/ms140033.aspx|||With the exception of right-clicking (I was clicking in the margin to set the breakpoint), yes, that is exactly what I did.|||Do you have any SQL Server service packs applied?|||How would I tell?|||

JWardRogers wrote:

How would I tell?

In BIDS, go to Help->About and select SSIS. What is the version number reported there?|||9.00.1399.00|||That's the original.

Try grabbing the SQL Server Service Pack 2 and installing that. Many fixes are contained in SP1 and SP2 as well.|||

Phil Brammer wrote:

That's the original.

Try grabbing the SQL Server Service Pack 2 and installing that. Many fixes are contained in SP1 and SP2 as well.

But unfortunately not the fix for my problem. :-(

SP2 is installed and it is still behaving the same way.

Version is now 9.00.3042.00

|||

I'm still having the same problem...

|||XP? Vista?|||

Server 2003 version 5.2.3790 (service pack 1.0)

Hot Fix: KB931836

4GB RAM

|||

JWardRogers wrote:

Server 2003 version 5.2.3790 (service pack 1.0)

Hot Fix: KB931836

4GB RAM

Do you also develop on a local workstation? Have you tried breakpoints on that if so?

Script Task scripts will not stop at breakpoints.

I can not debug any of my scripts. They execute just fine, but if I have a breakpoint set in the script code (in VSA), I get the message:

SQL server integration services script task has encountered a problem and needs to close. We are sorry for the inconvenience.

If you were in the middle of something, the information you were working on might be lost.

Debug Close

When I choose "Close" I get this:

sMicrosoft Visual Studio for Applications has lost the link to .

Your work will be exported to <<my Local Settings My Documents path>> when you quit the application.

OK

When I click "OK" my package executes normally, but no breakpoints will fire from that point on.

No need to post your message twice....

Script Tasks, not Script Components, correct?

One workaround is to use a MSGBOX().|||I was in the process of deleting my other post when you replied. I didn't want to post in a thread that had something marked as an answer. Yes, this is for script tasks. I certainly could use MsgBox() but that is hardly an answer to this problem. I could also write text out to a file, call a web service, insert a record into a table, etc. None of those things take the place of stepping through code. Is there a fix in the works for this issue? It really makes script tasks of limited use, or at least it makes them take much longer to develop.|||And you are following these steps?

http://technet.microsoft.com/en-us/library/ms140033.aspx|||With the exception of right-clicking (I was clicking in the margin to set the breakpoint), yes, that is exactly what I did.|||Do you have any SQL Server service packs applied?|||How would I tell?|||

JWardRogers wrote:

How would I tell?

In BIDS, go to Help->About and select SSIS. What is the version number reported there?|||9.00.1399.00|||That's the original.

Try grabbing the SQL Server Service Pack 2 and installing that. Many fixes are contained in SP1 and SP2 as well.|||

Phil Brammer wrote:

That's the original.

Try grabbing the SQL Server Service Pack 2 and installing that. Many fixes are contained in SP1 and SP2 as well.

But unfortunately not the fix for my problem. :-(

SP2 is installed and it is still behaving the same way.

Version is now 9.00.3042.00

|||

I'm still having the same problem...

|||XP? Vista?|||

Server 2003 version 5.2.3790 (service pack 1.0)

Hot Fix: KB931836

4GB RAM

|||

JWardRogers wrote:

Server 2003 version 5.2.3790 (service pack 1.0)

Hot Fix: KB931836

4GB RAM

Do you also develop on a local workstation? Have you tried breakpoints on that if so?

Script Task scripts will not stop at breakpoints.

I can not debug any of my scripts. They execute just fine, but if I have a breakpoint set in the script code (in VSA), I get the message:

SQL server integration services script task has encountered a problem and needs to close. We are sorry for the inconvenience.

If you were in the middle of something, the information you were working on might be lost.

Debug Close

When I choose "Close" I get this:

sMicrosoft Visual Studio for Applications has lost the link to .

Your work will be exported to <<my Local Settings My Documents path>> when you quit the application.

OK

When I click "OK" my package executes normally, but no breakpoints will fire from that point on.

No need to post your message twice....

Script Tasks, not Script Components, correct?

One workaround is to use a MSGBOX().|||I was in the process of deleting my other post when you replied. I didn't want to post in a thread that had something marked as an answer. Yes, this is for script tasks. I certainly could use MsgBox() but that is hardly an answer to this problem. I could also write text out to a file, call a web service, insert a record into a table, etc. None of those things take the place of stepping through code. Is there a fix in the works for this issue? It really makes script tasks of limited use, or at least it makes them take much longer to develop.|||And you are following these steps?

http://technet.microsoft.com/en-us/library/ms140033.aspx|||With the exception of right-clicking (I was clicking in the margin to set the breakpoint), yes, that is exactly what I did.|||Do you have any SQL Server service packs applied?|||How would I tell?|||

JWardRogers wrote:

How would I tell?

In BIDS, go to Help->About and select SSIS. What is the version number reported there?|||9.00.1399.00|||That's the original.

Try grabbing the SQL Server Service Pack 2 and installing that. Many fixes are contained in SP1 and SP2 as well.|||

Phil Brammer wrote:

That's the original.

Try grabbing the SQL Server Service Pack 2 and installing that. Many fixes are contained in SP1 and SP2 as well.

But unfortunately not the fix for my problem. :-(

SP2 is installed and it is still behaving the same way.

Version is now 9.00.3042.00

|||

I'm still having the same problem...

|||XP? Vista?|||

Server 2003 version 5.2.3790 (service pack 1.0)

Hot Fix: KB931836

4GB RAM

|||

JWardRogers wrote:

Server 2003 version 5.2.3790 (service pack 1.0)

Hot Fix: KB931836

4GB RAM

Do you also develop on a local workstation? Have you tried breakpoints on that if so?

Script Task scripting help

Hi all,
I use oledb connection in the script just like as follows:

msConnStr = "Provider=SQLOLEDB;SERVER=" & msServer & ";DATABASE=" & msDb
msConn = New OleDbConnection(msConnStr)
msConn.Open()
ds = New DataSet
msData = New OleDbDataAdapter(msSqlStr, msConn)
rows = msData.Fill(ds, "TableName")

This command is working fine, I just need to ask if there is a method to do retry on the connection if the connection cannot be opened for reasons like server is temporary down? instead of just make the script task a failure. Thanks in advance.
Daren
There is no magic method on any of the opbjects, but you can easily use the Try Catch syntax to catch the rror, and warp tis within a loop to retry n times. Perhaps add a delay in the loop as well to pause between attempts. if successfull, just break out of the loop.

Script task raising OnError Events

Is there a way I can stop a script task of a control flow from raising OnError Events? or Is there a way I can Set Dts.Results to Fail without having the script task raise OnError event.

The reason why I need to fail the task is because I am using Precedence constraints and by setting the status to Fail I can stop the control flow. However, this action raises OnError event. I expect onError event to be raised if there are any exceptions in the script not when the Results is changed to fail.

I have tried capturing the failure into a dummy script, however the OnError event is still raised.

Try putting the code that raises the error in a TRY...CATCH block, that'll stop individual lines of code from throwing errors.
I'm pretty sure though that if you set Dts.TaskResult = Dts.Results.Failure then it'll trigger an OnError event.

-Jamie

|||Failing a task is designed for signalling an error,
and should not be used to drive regular (non
exceptional) control flow.

To alter control flow, a better solution is using
conditions on precedence contraints instead.

You can set some variable in the script task, then
add on-success or on-completed precedence constraint
with condition that checks that variable.

Hope this helps,
Michael [SSIS Technical Lead]|||There are better ways to stop the task without returning failure result from the script task. For example, you could have an expression on the precedence constraint that checks a variable value you set in the script for whether to go on or not.
Try that, it will solve both problems I think.
K|||I have tried setting expressions within precedence constraint and it works. But I find setting variable and changing variable's value little kludge and I am surprised that Script Task does not have a property that will fail precedence constraint without raising an error.

On the contrary end, I guess it makes sense to have script task raise an error if I set it to failure manually, since all the other tasks have similiar behaviour. That is if they fail they raise OnError event.

Thanks everyone.|||

Do you want to force a failure result without actually throwing an error? If so try the ForceExecutionResult property. It is a container property, so available on all tasks.

|||

Hi

I think ive got a similar problem.

We have set up an SSIS package which goes to an FTP site and downloads files.

Everything is fine... EXCEPT (lol) when there are no files to download. This then fails the task.

However, I want the package to continue to run.

Is there away of assigning the error message given to an expression and then using the expression in the precedence contraint?

thanking you in advance

David

sql

Script task raising OnError Events

Is there a way I can stop a script task of a control flow from raising OnError Events? or Is there a way I can Set Dts.Results to Fail without having the script task raise OnError event.

The reason why I need to fail the task is because I am using Precedence constraints and by setting the status to Fail I can stop the control flow. However, this action raises OnError event. I expect onError event to be raised if there are any exceptions in the script not when the Results is changed to fail.

I have tried capturing the failure into a dummy script, however the OnError event is still raised.

Try putting the code that raises the error in a TRY...CATCH block, that'll stop individual lines of code from throwing errors.
I'm pretty sure though that if you set Dts.TaskResult = Dts.Results.Failure then it'll trigger an OnError event.

-Jamie

|||Failing a task is designed for signalling an error,
and should not be used to drive regular (non
exceptional) control flow.

To alter control flow, a better solution is using
conditions on precedence contraints instead.

You can set some variable in the script task, then
add on-success or on-completed precedence constraint
with condition that checks that variable.

Hope this helps,
Michael [SSIS Technical Lead]|||There are better ways to stop the task without returning failure result from the script task. For example, you could have an expression on the precedence constraint that checks a variable value you set in the script for whether to go on or not.
Try that, it will solve both problems I think.
K|||I have tried setting expressions within precedence constraint and it works. But I find setting variable and changing variable's value little kludge and I am surprised that Script Task does not have a property that will fail precedence constraint without raising an error.

On the contrary end, I guess it makes sense to have script task raise an error if I set it to failure manually, since all the other tasks have similiar behaviour. That is if they fail they raise OnError event.

Thanks everyone.|||

Do you want to force a failure result without actually throwing an error? If so try the ForceExecutionResult property. It is a container property, so available on all tasks.

|||

Hi

I think ive got a similar problem.

We have set up an SSIS package which goes to an FTP site and downloads files.

Everything is fine... EXCEPT (lol) when there are no files to download. This then fails the task.

However, I want the package to continue to run.

Is there away of assigning the error message given to an expression and then using the expression in the precedence contraint?

thanking you in advance

David

Script Task Not Updating Package Variable

I am trying to update a package variable. The package consists only of a script task and a package user variable. I have included the variable, myVar (scope: package; type: string), in the ReadWriteVariables property of the script task.

The only code I have used, in Public Sub Main, is:

Dts.Variables("myVar").Value = "2"

The package runs successfully but the variable does not change. I thought that maybe the underlying value really does change even though the value as seen in the package variables window does not (I tested this in another package/solution but it does not seem to - not even during runtime).

I also tried running the variabledispenser method but this resulted in the package running continuously until I stop debugging.

Any suggestions greatly appreciated.

Regards,

Puzzled Again

Hi, Create a local (within script) variable that is a copy pacakge. The following is how I populate a variable in a parent package(failflag), from a variable in a child package(childfailvalue). This script in the child package.

Dim localfailflag As Variable = Dts.Variables("failflag")
Dim localchildfailvalue As Variable = Dts.Variables("childfailvalue")
localfailflag.Value = localchildfailvalue.Value

|||

Thanks Craig. I tried your code but it didn't work...probably because I didn't include other parts of code that were required.

I changed the routine to using an Execute SQL Task to update the package variable and this seems to have worked.

Regards,

Puz

Script Task Logging

I seem to be doing something wrong I have a script task as follows:

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

Public Sub Main()

Dim emptyBytes(0) As Byte

Dts.Log("The change in records between this load and the last load is " & CDbl(Dts.Variables("percent").Value) * 100 & "%.", 0, emptyBytes)

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

I have logging turned on for the package going to a text file with just errors checked. For this step I specifically checked the box for logging on this step (instead of inheriting logging) checked the logging provider, and on details checked ScriptTaskLogEntry.

The upshot is, the step runs fine but I have no entry in the log. HELP

Thanks,

LarryC

Well I guess I was wrong It is logging just fine. I kept looking in the Execution Results for the message and it wouldn't show up. Is there a way to get the message in the Execution Results?

|||

Try using FireInformation (Dts.Events.FireInformation)

|||

Smokin! For what I was doing that worked really great. I had a step I wanted to fail the package and I wanted to see the reason I was failing the package. Using the FireError method accomplished both things I wanted: To be able to see the message in progress when interactively running, and to see the message in the log.

As an unexpected bonus, I can log failure messages now without resorting to special considerations with logging. My script task can now inherit package events which are just logging errors Thanks a million.

The syntax I used for anyone else having a similar problem is:

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

Public Sub Main()

Dim emptyBytes(0) As Byte

Dim msg As String = "The change in records between this load and the last load is " + CStr(CDbl(Dts.Variables("percent").Value) * 100) + "%."

Dts.Events.FireError(0, "Fail Package", msg, "", 0)

Dts.TaskResult = Dts.Results.Failure

End Sub

End Class

LarryC

Script Task Hang - Had to Re-Compile

Greetings,

This morning one of our jobs failed, so I eventually ran the SSIS package manually and found the flow "hanging" at a script task. The fix was to open the script in design mode and hit Save, which I believe compiles the code. Then the package ran as it has normally for several months.

There was a recent Windows update run on this server. I don't know what was updated, as it was the DBA that did that. It seems possible that a .NET framework update would cause this problem, does anyone have any thoughts on this or anything else causing this?

Given the extent of some SSIS environments, and ours is pretty extensive, this could be a real pain to go in and manually recompile each and every script task.

Thanks

Read the first post at the top of this forum.|||

Thanks Phil,

Just wanted to give some details about what I understand was our situation here.

We have had SQL Server 2005 SP2 for several months. This morning there was a big push which included Windows SP2, .NET 2.0, and .NET 3.0. So it doesn't surprise me that I'd have to recompile, but according to the article referenced at the top of this forum, it seems that SQL SP2 should have taken care of this. Possibly it was our unique sequence of updates here.

|||I'm surprised .Net 2.0 wasn't already installed. It should've been, I believe. You might have to reinstall SQL Server SP2 to reap its benefits.

I'm not entirely sure on the whole deal. That sounds like a pretty major update though.|||

Yes I was thinking the same thing, that the DBA must have been mistaken when he told me that. I thought .NET 2 was installed with SQL 2005, a prerequisite. Will probably just wait to see if it happens again, and if so then reinstall as you suggested.

Thanks for for your time on this