Reading a variable in a script taks used as source

Hi,

I'm using a script task as source and I need to read a global variable.

I tried stuff I found on the internet, but they all use Dts.VariableDispenser.LockForRead(varName)

This gives an errero on 'Dts.' saying it can not resolve it.

If you'd like, you can predict what variable you'd like to read or write to, you can use the ReadOnlyVariable or ReadWriteVariable properties to pass in the variable to your script. At that point, you won't need the variable dispenser. Then, you could read the variable by using code as easy as this:

LocalVariable = DTS.Variables("VariableName").Value

This video may help you as well: http://www.jumpstarttv.com/Media.aspx?vid=6

-- Brian Knight

|||Hi,

I've typed in the variables in the ReadOnlyVariables, but how can I access them in the script?

Also, 'LocalVariable = DTS.Variables("VariableName").Value' doesn't work (because it's a source script?). I get an error that VB is unable to resolve 'dts'.

I'm using folowing import:
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports Microsoft.SqlServer.Dts
Imports Microsoft.SqlServer.Dts.Runtime

Thank you

Reading a directory

I do a lot of file processing and I usually run a little script I copy
and paste to read directory information to see if a new file it there
and then process the file if it is. So, I decided to wise up and make
a stored procedure to automate a lot of that.

The pivotal step in this is that i run a command that looks like:

CREATE TABLE #DIR (FileName varchar(100))

DECLARE @.Cmd varchar(1050)
SET@.Cmd = 'DIR "' + @.Path + CASE WHEN RIGHT(@.Path, 1) = '\' THEN ''
ELSE '\' END + @.WildCard + '"'

INSERT INTO #DIR
EXEC master..xp_CmdShell @.Cmd

When I run the stored procedure I get back the files and folders in
there that match the wildcard and all is good!!!!

...Until I try to put that information into a table while calling that
stored procedure:

CREATE TABLE #Files (
Path varchar(100),
FileName varchar(100),
PathAndFileName varchar(150),
FileDateTime SmallDateTime,
FileLength int,
FileType Varchar(10))

INSERT INTO #Files
EXEC sp_GetFileNames @.Path = '\\isoft2\ftp\Legacy\Billing\', @.Wildcard
= '*.txt'

When I run this I get:

Server: Msg 8164, Level 16, State 1, Procedure sp_GetFileNames, Line
53
An INSERT EXEC statement cannot be nested.

Because I use an INSERT EXEC to with the results from the @.Cmd.

Anybody have any ideas how I can get that information into a table?

I did try to just copy the data to c:\temp\dir.txt and then bulk
import it in. But when it runs the @.Cmd to create the file it comes
back with a NULL value and my stored procedure returns two sets of
values... which I can't do.

So, I would appreciate anybody who can help.

Thanks!

-utahWhat I have done in the past is create a global temp table (##Files) and
then in the called procedure(sp_GetFileNames) insert into the global temp
table directly.

<Utahduck@.hotmail.comwrote in message
news:1172623203.275737.283660@.v33g2000cwv.googlegr oups.com...

Quote:

Originally Posted by

>I do a lot of file processing and I usually run a little script I copy
and paste to read directory information to see if a new file it there
and then process the file if it is. So, I decided to wise up and make
a stored procedure to automate a lot of that.
>
The pivotal step in this is that i run a command that looks like:
>
CREATE TABLE #DIR (FileName varchar(100))
>
DECLARE @.Cmd varchar(1050)
SET @.Cmd = 'DIR "' + @.Path + CASE WHEN RIGHT(@.Path, 1) = '\' THEN ''
ELSE '\' END + @.WildCard + '"'
>
INSERT INTO #DIR
EXEC master..xp_CmdShell @.Cmd
>
When I run the stored procedure I get back the files and folders in
there that match the wildcard and all is good!!!!
>
...Until I try to put that information into a table while calling that
stored procedure:
>
CREATE TABLE #Files (
Path varchar(100),
FileName varchar(100),
PathAndFileName varchar(150),
FileDateTime SmallDateTime,
FileLength int,
FileType Varchar(10))
>
INSERT INTO #Files
EXEC sp_GetFileNames @.Path = '\\isoft2\ftp\Legacy\Billing\', @.Wildcard
= '*.txt'
>
When I run this I get:
>
Server: Msg 8164, Level 16, State 1, Procedure sp_GetFileNames, Line
53
An INSERT EXEC statement cannot be nested.
>
Because I use an INSERT EXEC to with the results from the @.Cmd.
>
Anybody have any ideas how I can get that information into a table?
>
I did try to just copy the data to c:\temp\dir.txt and then bulk
import it in. But when it runs the @.Cmd to create the file it comes
back with a NULL value and my stored procedure returns two sets of
values... which I can't do.
>
So, I would appreciate anybody who can help.
>
Thanks!
>
-utah
>

|||Maybe I'm bot understanding your problem correctly ,, but if you did
CREATE TABLE #DIR (FileName varchar(100))

Quote:

Originally Posted by

>
DECLARE @.Cmd varchar(1050)
SET @.Cmd = 'DIR "' + @.Path + CASE WHEN RIGHT(@.Path, 1) = '\' THEN ''
ELSE '\' END + @.WildCard + '"'
>
INSERT INTO #DIR
EXEC master..xp_CmdShell @.Cmd

INSERT INTO myTABLE
SELECT filename FROM #DIR

would that not do the job?

--

Jack Vamvas
___________________________________
The latest IT jobs - www.ITjobfeed.com
<a href="http://links.10026.com/?link=http://www.itjobfeed.com">UK IT Jobs</a>

<Utahduck@.hotmail.comwrote in message
news:1172623203.275737.283660@.v33g2000cwv.googlegr oups.com...

Quote:

Originally Posted by

>I do a lot of file processing and I usually run a little script I copy
and paste to read directory information to see if a new file it there
and then process the file if it is. So, I decided to wise up and make
a stored procedure to automate a lot of that.
>
The pivotal step in this is that i run a command that looks like:
>
CREATE TABLE #DIR (FileName varchar(100))
>
DECLARE @.Cmd varchar(1050)
SET @.Cmd = 'DIR "' + @.Path + CASE WHEN RIGHT(@.Path, 1) = '\' THEN ''
ELSE '\' END + @.WildCard + '"'
>
INSERT INTO #DIR
EXEC master..xp_CmdShell @.Cmd
>
When I run the stored procedure I get back the files and folders in
there that match the wildcard and all is good!!!!
>
...Until I try to put that information into a table while calling that
stored procedure:
>
CREATE TABLE #Files (
Path varchar(100),
FileName varchar(100),
PathAndFileName varchar(150),
FileDateTime SmallDateTime,
FileLength int,
FileType Varchar(10))
>
INSERT INTO #Files
EXEC sp_GetFileNames @.Path = '\\isoft2\ftp\Legacy\Billing\', @.Wildcard
= '*.txt'
>
When I run this I get:
>
Server: Msg 8164, Level 16, State 1, Procedure sp_GetFileNames, Line
53
An INSERT EXEC statement cannot be nested.
>
Because I use an INSERT EXEC to with the results from the @.Cmd.
>
Anybody have any ideas how I can get that information into a table?
>
I did try to just copy the data to c:\temp\dir.txt and then bulk
import it in. But when it runs the @.Cmd to create the file it comes
back with a NULL value and my stored procedure returns two sets of
values... which I can't do.
>
So, I would appreciate anybody who can help.
>
Thanks!
>
-utah
>

|||utah,

You should paste your stored procedure. One thing, how are you getting from
a one column table (#Dir) to a multiple column table (#files) based upon
your insert? You are going to have to do some parsing to get all this info
into multiple columns.

-- Bill

<Utahduck@.hotmail.comwrote in message
news:1172623203.275737.283660@.v33g2000cwv.googlegr oups.com...

Quote:

Originally Posted by

>I do a lot of file processing and I usually run a little script I copy
and paste to read directory information to see if a new file it there
and then process the file if it is. So, I decided to wise up and make
a stored procedure to automate a lot of that.
>
The pivotal step in this is that i run a command that looks like:
>
CREATE TABLE #DIR (FileName varchar(100))
>
DECLARE @.Cmd varchar(1050)
SET @.Cmd = 'DIR "' + @.Path + CASE WHEN RIGHT(@.Path, 1) = '\' THEN ''
ELSE '\' END + @.WildCard + '"'
>
INSERT INTO #DIR
EXEC master..xp_CmdShell @.Cmd
>
When I run the stored procedure I get back the files and folders in
there that match the wildcard and all is good!!!!
>
...Until I try to put that information into a table while calling that
stored procedure:
>
CREATE TABLE #Files (
Path varchar(100),
FileName varchar(100),
PathAndFileName varchar(150),
FileDateTime SmallDateTime,
FileLength int,
FileType Varchar(10))
>
INSERT INTO #Files
EXEC sp_GetFileNames @.Path = '\\isoft2\ftp\Legacy\Billing\', @.Wildcard
= '*.txt'
>
When I run this I get:
>
Server: Msg 8164, Level 16, State 1, Procedure sp_GetFileNames, Line
53
An INSERT EXEC statement cannot be nested.
>
Because I use an INSERT EXEC to with the results from the @.Cmd.
>
Anybody have any ideas how I can get that information into a table?
>
I did try to just copy the data to c:\temp\dir.txt and then bulk
import it in. But when it runs the @.Cmd to create the file it comes
back with a NULL value and my stored procedure returns two sets of
values... which I can't do.
>
So, I would appreciate anybody who can help.
>
Thanks!
>
-utah
>

|||(Utahduck@.hotmail.com) writes:

Quote:

Originally Posted by

INSERT INTO #Files
EXEC sp_GetFileNames @.Path = '\\isoft2\ftp\Legacy\Billing\', @.Wildcard
>= '*.txt'

Note that the sp_ prefix is reserved for system procedures, and SQL Server
will first look for these in the master database. Do not use it for your
own code.

Quote:

Originally Posted by

When I run this I get:
>
Server: Msg 8164, Level 16, State 1, Procedure sp_GetFileNames, Line
53
An INSERT EXEC statement cannot be nested.
>
Because I use an INSERT EXEC to with the results from the @.Cmd.
>
Anybody have any ideas how I can get that information into a table?

I have an article on my web site that discusses a couple of alternatives:
http://www.sommarskog.se/share_data.html.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Read/Write for Variables

Hi,

I am using a Script Component and I have a Read/Write Variable varStatusCase (as assigned in the Custom Properties of my Script Component). I used this inside my script to get a specific value. However, when I ran it I get this error:

The collection of variables locked for read and write access is not available outside of PostExecute.

How do I repair this?

Thanks,

cherriesh

If you want to write on a variable you have to use the post execute method something like this

Public Overrides Sub PostExecute()

Me.Variables.varName = Something

End Sub

I dont know if there's another way to do it but this is the only one i've found. And it works.

|||

But just know that this doesn't execute for every row.

|||

Check if this post help you!

http://pedrocgd.blogspot.com/2007/05/ssis-populating-dimension_28.html

Regards!

|||

hi,

I tried doing what is in your link:

Private NextKey As Int32 = 0

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim MaxKey As Integer = Me.Variables.varStatus

NextKey = MaxKey + 1

Row.oStatusCode = NextKey

MyBase.PreExecute()

End Sub

Public Overrides Sub PreExecute()

Me.VariableDispenser.LockForRead("User::varStatus")

MyBase.PreExecute()

End Sub

Public Overrides Sub PostExecute()

Me.ReadWriteVariables("varStatus").Value = NextKey

MyBase.PostExecute()

End Sub

My variable "varStatus" is assigned in Script Component ReadWrite Variable.

Am I missing anything? I'm still having the same error.

cherriesh

|||

dear friend,

copy my statment in my post... check the link... the solution is there.

check the pre-execute method

helped?

Read package variable in script component

For an SCD surrogate generator, I want to read a package variable that I've populated with the MAX () surrogate to begin my surrogate seed at. Does anyone have a piece of code sample to do this?

In your script component assign the variable in the properties of the component either to the readonly list or readWrite List. You can then access the variable in the script component with

Variables.myVariable

Where myVariable is the name of your variable.

read images from SQL server

Hi Guru's,
I want to show images in my webforms, but whitout any succes...... , hope some can tell me why
as base I've used exactly the same script as defined in the article http://www.dotnetbips.com/displayarticle.aspx?id=101
Maybe its because some of the HTML is incorrect ?
<itemtemplate>
<asp:image id="img" runat= server></asp:image>
</itemtemplate>
Please , a need some assitance here

regards
STry the link below for working code and there is a Microsoft Channel 9 show doing the same thing. Hope this helps.
http://www.codeproject.com/cs/database/ImageSaveInDataBase.asp

Read datasource value, send to textbox in VB script

I want to read a value from a datasource and send it to a textbox on an ASP page from the page's attached VB file.

Both the textbox and datasource exist, but I don't know the proper commands to get the datasource value and then set the textbox value to it.

Dim Text As TextBox = FormView0.FindControl("Header")

Dim SQL As String = "Select [Customer] From [Datasource]"

Datasource.SelectCommand = SQL

Oh yeah, I don't want the textbox control to be databound as shown:

Text

='<%# Eval("Customer") %>'

Hi phoenix,

ASP.NET provides many ways on Data Binding server controls. As you want to bind data to a textbox from your data table, we suggest that you should create a DataSet which contains the data talbe and filter which row and column would be selected, then evaluate the TextBox control's text property with the selected value.

Ofcause there are may other ways for you to refer, pls check these materials:

http://samples.gotdotnet.com/quickstart/aspplus/doc/webdatabinding.aspx (For Data Binding server controls)

http://samples.gotdotnet.com/quickstart/aspplus/doc/webserverctrls.aspx (For the introduction of server controls)

If this does not help you ,pls feel free to mark the post as Not Answered and reply. Thanks.

Read and Delete Mail in SQL 2005

Does anyone have script or a CLR assembly to read and delete email in
SQL2005? I don't want to enable SQL mail or install Outlook.
Thanks in advance
Terry
Hi
You may want to check out
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dncodefun/html/code4fun03282003.asp
John
"Terry" wrote:

> Does anyone have script or a CLR assembly to read and delete email in
> SQL2005? I don't want to enable SQL mail or install Outlook.
> Thanks in advance
> Terry
>

Read and Delete Mail in SQL 2005

Does anyone have script or a CLR assembly to read and delete email in
SQL2005? I don't want to enable SQL mail or install Outlook.
Thanks in advance
TerryHi
You may want to check out
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dncodefun/html/code4fun03282003.asp
John
"Terry" wrote:
> Does anyone have script or a CLR assembly to read and delete email in
> SQL2005? I don't want to enable SQL mail or install Outlook.
> Thanks in advance
> Terry
>

Read and Delete Mail in SQL 2005

Does anyone have script or a CLR assembly to read and delete email in
SQL2005? I don't want to enable SQL mail or install Outlook.
Thanks in advance
TerryHi
You may want to check out
http://msdn.microsoft.com/library/d...fun03282003.asp
John
"Terry" wrote:

> Does anyone have script or a CLR assembly to read and delete email in
> SQL2005? I don't want to enable SQL mail or install Outlook.
> Thanks in advance
> Terry
>

read an xls file from a script task

I'm trying to read an xcel file from within the script task of SSIS. I've seen posts that kind of steer me in the right direction, but none to do exactly what I'm looking for. I am loading data from flat files into a database where the source file has text longer than the target column. In order to get the text to "fit" i would like to apply a series of standard abbreviations, for example replace "DOCTOR" with "DR". I started off by simply writing a series of if-then logic; if length (<source>) > <targetlengh>, then replace <long> with <short>. After writing this for 40+ abbreviations I soon realized that this is not the best way to do this. So I came up with the idea of storing all the abbreviations in a .xls file which would be opened and applied via a script task. The problem is that I'm new to VB.net (actually any flavor of VB) and have not been able to read the abbreviations file as of yet.

This is what I've tried thus far, which results with this error: Exception from HRESULT: 0xC001400B

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

'

' Add your code here

'

Dim local_SQLConnectionManager As New Microsoft.SqlServer.Dts.Runtime.Wrapper.ConnectionManagerAdoNet()

Dim local_SQLConnection As New System.Data.SqlClient.SqlConnection

Dim local_SQLDataReader As System.Data.SqlClient.SqlDataReader

Dim local_SQLCommand As New System.Data.SqlClient.SqlCommand

'local_SQLConnectionManager = Microsoft.SqlServer.Dts.Runtime.Wrapper.cConnections("Name of ConnectionManager")

local_SQLConnectionManager.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Departments\DataServices\Projects\QNXT Conversion\XWalks\DesciptionAbbreviations.xls;Extended Properties=Excel 8.0;HDR=YES;"

local_SQLConnection = CType(local_SQLConnectionManager.AcquireConnection(Nothing), System.Data.SqlClient.SqlConnection)

local_SQLConnection.Open()

local_SQLCommand.Connection = local_SQLConnection

local_SQLCommand.CommandText = "SELECT * from [sheet1$]"

local_SQLDataReader = local_SQLCommand.ExecuteReader()

If local_SQLDataReader.Read Then

'Microsoft.SqlServer.Dts.Runtime.Wrapper.Variable("Just a variable").Value = local_SQLDataReader.GetString(0)

End If

local_SQLDataReader.Close()

End Sub

I think a better way to do this would be to import the XLS file into a SQL table, quite easy to do via SSIS. This forms a reference table in SQL of abbreviations and expanded terms. Then use a Lookup after your Flat File source to return the replacement value based on the reference table. Set the lookup to ignore errors, no lookup value found and then use a derived column to merge the two values into one column.

This will only work if your term is the whole column value.

Off the top of my head I'm also thinking the Term Extraction/Lookup transform may be of use if your terms are in longer fields. Check out the whitepaper on MSDN.

|||

I dont that that would for for me because I need to look at each "record" in the abbreviations.xls file and apply it to a column on the input buffer. Furthermore I also need to only apply the abbreviation if the value i'm shortening is longer than a pre-defined length.

Here's the pseudocode for what I'm tring to do:

shortDescr = row.longDescr

For each xcelRow in xcel file

If length(shortDesc) > row.maxLen then

shortDesc.replace(xcelRow.longWord,xcelRow.abbrevWord)

end if

end loop

If length(shortDesc) > row.maxLen then

shortDesc = shortDesc.substring(0,row.maxLen)

end if

row.outputShortDesc = shortDesc

|||

You can't use SqlClient with the Jet OLE DB Provider. Use the same connection string and similar code with System.Data.OleDb instead.

However I believe that you will need double double quotes around the multi-value string for Extended Properties:

local_OleDbConnectionManager.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Departments\DataServices\Projects\QNXT Conversion\XWalks\DesciptionAbbreviations.xls;Extended Properties=""Excel 8.0;HDR=YES;"""

-Doug

http://msdn2.microsoft.com/en-us/library/ms403358(SQL.90).aspx

|||

Thanks for pointing me in the right direction, i've got it working now

Read all xml files from a folder

I have a script that can read specific files that are in an xml format, but i
need to modifiy it to read all files from a folder. In this case, I do not
know the file names but I do know that I need to read all of them. Does
anyone have a VB activeX script for this for MS SQL server 2000?
Thanks
You would use the Scripting.FileSystemObject to iterate
through the files in folder:
http://www.eggheadcafe.com/articles/20030627b.asp
2004 and 2005 Microsoft MVP C#
Robbe Morris
http://www.masterado.net
Earn $$$ money answering .NET Framework
messageboard posts at EggHeadCafe.com.
http://www.eggheadcafe.com/forums/merit.asp
"Ben" <ben_1_ AT hotmail DOT com> wrote in message
news:F70A712A-6D8C-4C9B-BE36-4EBA01B27A94@.microsoft.com...
>I have a script that can read specific files that are in an xml format, but
>i
> need to modifiy it to read all files from a folder. In this case, I do
> not
> know the file names but I do know that I need to read all of them. Does
> anyone have a VB activeX script for this for MS SQL server 2000?
> Thanks
|||Thanks Robbe. Your post/page/article worked like a charm!!
Ben
"Robbe Morris [C# MVP]" wrote:

> You would use the Scripting.FileSystemObject to iterate
> through the files in folder:
> http://www.eggheadcafe.com/articles/20030627b.asp
> --
> 2004 and 2005 Microsoft MVP C#
> Robbe Morris
> http://www.masterado.net
> Earn $$$ money answering .NET Framework
> messageboard posts at EggHeadCafe.com.
> http://www.eggheadcafe.com/forums/merit.asp
>
> "Ben" <ben_1_ AT hotmail DOT com> wrote in message
> news:F70A712A-6D8C-4C9B-BE36-4EBA01B27A94@.microsoft.com...
>
>
sql

Read All Variables in a Script Task w/o Enumerating Them

I'm having trouble with my configurations. All of my configurations are targeting variables and the various properties are all set to expressions containing the targeted variables. I would like (during debugging) to dump out all of the variables.

Is there a way to access all variables from a script task? It looks like you have to enumerate the variables you want in the ReadOnlyVariables or ReadWriteVariables properties. I'd rather not miss one by forgetting to list it.

No, because there is no way of discovering what variables are available at run-time, and the locking mechanism to get access to the variable and it's value requires a name.

This type of funtionality is only available through the design-time UI interfaces.

|||You might be able to use the OnVariableValueChanged event to capture the variable values as they are set. You do need to make sure that RaiseChangedEvent is true for each variable.|||

Thanks, but the reason for this was to help me understand the details of how the configuration was working. Will OnVariableValueChanged work even if the change was due to loading a configuration?

|||

No, actually it doesn't. I just tested it, and it appears that the event doesn't fire on configurations. Maybe someone from MSFT could comment on why it doesn't? I can guess a few reasons, but I think there are some valid scenarios where you would want to capture values even when they are set from configurations.

Back to your original question, they are adding a Watson-style debugger in 2008 that will dump the current state of the package on demand. That will include the variable values. Not a big help today, but something to look forward to.

|||

Actually, the 2008 version will help once released. Any info on which CTP that feature will be in?

|||CTP 5, from what MSFT said at the PASS Summit.

Read All Variables in a Script Task w/o Enumerating Them

I'm having trouble with my configurations. All of my configurations are targeting variables and the various properties are all set to expressions containing the targeted variables. I would like (during debugging) to dump out all of the variables.

Is there a way to access all variables from a script task? It looks like you have to enumerate the variables you want in the ReadOnlyVariables or ReadWriteVariables properties. I'd rather not miss one by forgetting to list it.

No, because there is no way of discovering what variables are available at run-time, and the locking mechanism to get access to the variable and it's value requires a name.

This type of funtionality is only available through the design-time UI interfaces.

|||You might be able to use the OnVariableValueChanged event to capture the variable values as they are set. You do need to make sure that RaiseChangedEvent is true for each variable.|||

Thanks, but the reason for this was to help me understand the details of how the configuration was working. Will OnVariableValueChanged work even if the change was due to loading a configuration?

|||

No, actually it doesn't. I just tested it, and it appears that the event doesn't fire on configurations. Maybe someone from MSFT could comment on why it doesn't? I can guess a few reasons, but I think there are some valid scenarios where you would want to capture values even when they are set from configurations.

Back to your original question, they are adding a Watson-style debugger in 2008 that will dump the current state of the package on demand. That will include the variable values. Not a big help today, but something to look forward to.

|||

Actually, the 2008 version will help once released. Any info on which CTP that feature will be in?

|||CTP 5, from what MSFT said at the PASS Summit.

Read All Variables in a Script Task w/o Enumerating Them

I'm having trouble with my configurations. All of my configurations are targeting variables and the various properties are all set to expressions containing the targeted variables. I would like (during debugging) to dump out all of the variables.

Is there a way to access all variables from a script task? It looks like you have to enumerate the variables you want in the ReadOnlyVariables or ReadWriteVariables properties. I'd rather not miss one by forgetting to list it.

No, because there is no way of discovering what variables are available at run-time, and the locking mechanism to get access to the variable and it's value requires a name.

This type of funtionality is only available through the design-time UI interfaces.

|||You might be able to use the OnVariableValueChanged event to capture the variable values as they are set. You do need to make sure that RaiseChangedEvent is true for each variable.|||

Thanks, but the reason for this was to help me understand the details of how the configuration was working. Will OnVariableValueChanged work even if the change was due to loading a configuration?

|||

No, actually it doesn't. I just tested it, and it appears that the event doesn't fire on configurations. Maybe someone from MSFT could comment on why it doesn't? I can guess a few reasons, but I think there are some valid scenarios where you would want to capture values even when they are set from configurations.

Back to your original question, they are adding a Watson-style debugger in 2008 that will dump the current state of the package on demand. That will include the variable values. Not a big help today, but something to look forward to.

|||

Actually, the 2008 version will help once released. Any info on which CTP that feature will be in?

|||CTP 5, from what MSFT said at the PASS Summit.

RE: Script task and OLEDB destination Performance

Hi fellows,

Sorry to disturb but just a question. I have a package which extracts all the records from table A and update to table B. These records may range from 100,000 to 500,000 records.

So my question is that whether is it more feasibile/efficient to use script task to pump all the rows into table B from table A or use OLEDB destionation using sql command. Which is more efficient and help me increase my package performance? Thanks again.

Regards,

Ken

If they are inserts, I'd use the OLEDB Destination to insert directly into the target table. If they are updates, use an OLEDB Destination to write the data to a temp table, then use a Execute SQL task after the data flow to issue a batch update.