Accessing DataReader data in Script Task

I have a Data Flow task that sends its results to a DataReader destination. My Data Flow is then connected to a Script Task. I'm trying to figure out how to access the data stored in that DataReader from my Script Task, but having no luck. Any help would be greatly appreciated.

OK, I've figured out how to do this, but using a Recordset, not a Datareader.

1. Pass whatever data you want to process in your Data Flow to a Recordset destination, and store it in an SSIS variable. In my case, the variable is called "ConsumerIDSet", and it's storing only one row called "ConsumerID".

2. In the Control Flow, add a Script Task. Connect the output of your Data Flow to the input of the Script Task.

3. Make sure the variable you're storing the Recordset into is availabe to the Script Task (add it to the ReadOnlyVariables list).

4. Open the VSA script designer, and make sure to add a reference to ADODB (Project...Add Reference...select ADODB).

Here's the script:

Public Sub Main()
Dim dbConnect As New OleDbConnection("[connect string]")
Dim dbCommand As New OleDbCommand
Dim consumerID As String
Dim rs As ADODB.Recordset

rs = CType(Dts.Variables("ConsumerIDSet").Value, ADODB.Recordset)

Try
dbConnect.Open()

Do While Not rs.EOF
consumerID = rs("ConsumerID").Value.ToString

dbCommand.CommandText = _
"UPDATE tblname SET extracted = 1 WHERE consumerid = " + consumerID
dbCommand.Connection = dbConnect
dbCommand.ExecuteNonQuery()

rs.MoveNext()
Loop

Dts.TaskResult = Dts.Results.Success

Catch
Dts.TaskResult = Dts.Results.Failure

Finally
dbConnect.Close()

End Try
End Sub

I'm not an experienced developer, so that might not be the best way to do it, but it works. BTW, I'm connecting to an Oracle database which is why I'm using OleDbConnection, so you'll have to adjust your connection type.

I'm still curious as to whether this can be done with a DataReader, so if you have any suggestions, let me know!

|||You might try cracking it with a ForEach loop.
http://sqljunkies.com/WebLog/knight_reign/archive/2005/03/25/9588.aspx
K|||The DataReader Destination is designed to expose the result of the whole package as data source to some external (to the package) application, say Reporting Services, or your custom application. It is not really designed for reading data from inside of the package itself.

As you've found out the RecordSet Destination is designed exactly for your goal - for saving the data into record set that you can then use inside the same package. In addition, as Kirk wrote, we provide other ways to interact with data collected by RecordSet Destination, e.g. for each loop can iterate over it.

If you explain the bigger picture, we might be able to provide better suggestion on the design of the package.|||I was using the ForEach loop, and running the script task inside that. But that meant opening and closing a database connection on each iteration of the loop. It makes more sense to me to do the loop inside of the script itself.

Regarding the bigger picture...I've been asked to dig in to SSIS as a possible replacement for our current ETL tool, which is closed, proprietary, and expensive. We do quite a bit of interaction with Oracle, mostly involving extracting and formatting consumer data. My job is to learn SSIS quickly so we can figure out how best to apply it to our current processes. It's a bit early to say exactly what I'm trying to accomplish--I'm not sure yet! As I learn it better and figure out how we're going to use it I may have more specific scenarios.

Thank you for the help!|||Let us know how we can help.
K

Accessing database

In a useless wrox press book I'm currently being tortured by there is the script below, it is suppose to simply open and close a connection to the northwind SQL Server 2000 database but it doesn't work:

<%@.Import namespace="System.Data"%>
<%@.Import namespace="System.Data.SqlClient"%>
<script runat="server" language="c#">
void Page_Load()
{
string strConnection = "user id=sa;password=;";
strConnection += "initial catalog=northwind;server=MIKE;";
strConnection += "Connect Timeout=30";

data_src.Text = strConnection;

SqlConnection objConnection = new SqlConnection(strConnection);

try
{
objConnection.Open();
con_open.Text = "Connection opened successfully.<br />";
objConnection.Close();
con_close.Text = "Connection closed <br />";
}
catch (Exception e)
{
con_open.Text = "Connection failed to open.<br />";
con_close.Text = e.ToString();
}
}
</script>
<html>
<body>
<h4>Testing the data connection <asp:label id="data_src" runat="server" /></h4>
<asp:label id="con_open" runat="server" /><br />
<asp:label id="con_close" runat="server" /><br />
</body>
</html
It returns the following error:

System.Data.SqlClient.SqlException: Login failed for user 'sa'. at System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) at System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) at System.Data.SqlClient.SqlConnection.Open() at ASP.sql_connection_aspx.Page_Load()

The book also states that if the script doesn't work we should try replacing the user id and password line with this line:

string strConnection = "Integrated Security=SSPI;";

But this doesn't work either. So needless to say I have no idea what's wrong, can anyone help as unfortunately the psychic powers Wrox Press clearly believe I possess aren't working. I have no idea whether this should work straight out like this or whether some configuration or something needed to be done first. I'm totally stuck, please help.

ThanksProbably the username and password for the database server you are trying to access is not valid. SqlServer defaults to a blank password for sa but generally that's one of the first things changed so that the server is more secure.

Is this on your own machine or is it remote? In either case you need to find a valid username and password for accessing the database. If you can get into Enterprise Manager, check in the Security section under Logins. Either use an existing one or create a new one. If it's a remote server then check with whomever is responsible for it.

Access To Dts.Variables Causes Exception

Hey all...I'm pretty new to SSIS packages and things are coming along nicely. My problem is accessing variables in script tasks.

I've created two variables (package scope, strings, readonly = false) - aDetailFiles and strDetailFile

Within a Foreach Loop I loop through a folder. I've added a script task and within that I try the following:

Dts.Variables("User::aDetailFiles").Value = Dts.Variables("User:trDetailFile").Value.ToString() + "|" + Dts.Variables("User::aDetailFiles").Value.ToString()

Everytime this script executes I get the following errors:

at Microsoft.SqlServer.Dts.Runtime.Variables.get_Item(Object index)
at ScriptTask_feac87c947ce4431a4fee0ba0e13631d.ScriptMain.Main() in dts://Scripts/ScriptTask_feac87c947ce4431a4fee0ba0e13631d/ScriptMain:line 25

I have searched this error message but the only thing I found was to set the ReadWrite variable property but I don't know where to set that.

Expresion property set as false also.

Any ideas? Thanks.

Right Click the script task, press F4, this will show the properties of the script task, there you will have properties called ReadOnlyVariables, ReadWriteVariables. You can set the variables name in this based on your requirement.
These variables will be available in the script, else they dont have visibility inside the script task and an error will be thrown at run time.

Thanks

Access Script Task

Hi,

How to access the Package inside the Script Taks. for example

IF intStatus = 1 Then

//Execute Package1

Else

//Execute Package2

End IF

in the above sample what is the property to get the Package1 object in script

Thanks & Regards

Jegan.T

You should use the Execute Package Task in order to execute other packages. Any reason why this doesn't work for you?

-Jamie

|||

If i use Execute Package i have to supply all the connection string details.we have the requirement is like this we have to excute the package based on a routine or function execution .its more like using batch file in Data Stage.

Jegan.T

|||

Sorry, I don't know anything about DataStage.

The connection string for the package tells you where it is located. Surely you need the same information in order to execute from the Script Task?

-Jamie

|||

Hi Jamie

Thanks for your reply . but the Script Task does not have any provison for connection string .

i have the design like this in the control flow

Script Task -> DataFlow1 --> DataFlow2

we have to write a routine in the script editor which will decied which DataFlow it has to execute

Script Editor

--

Public Sub Main()

'Code to Invoke DataFlow1 or DataFlow2
End Sub

How to achive this in the script editor ?

Thanks

Jegan.T

|||

I'm confused. At the start of this thread you said you were trying to execute a package.

NOW you say you are trying to decide which data-flow to execute.

The two are completely different so which is it? Perhaps you can explain exactly what it is you are trying to do.

If what you are trying to do is conditionally execute a data-flow then you are going about it the wrong way. You do not need to use a script task - use conditional precedence constraits which are described here: http://www.sqlis.com/default.aspx?306

-Jamie

Access read only variables in Script Component in a Dataflow

I have a set of comma separated variables in a Script Component list. I want to access them in Script code and use them to build string in the code.Use "Me.Variables.variableName"|||I was I am trying to do the same thing you mentioned but I am the following error

[Script Component [1463]] Error: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. > Microsoft.SqlServer.Dts.Pipeline.ReadOnlyVariablesNotAvailableException: The collection of variables locked for read access is not available at this point. at Microsoft.SqlServer.Dts.Pipeline.ScriptComponent.get_ReadOnlyVariables() at ScriptComponent_720e2ab81e00498aa9bf2e9d8af40422.Variables.get_LogPath() in dts://Scripts/ScriptComponent_720e2ab81e00498aa9bf2e9d8af40422/ComponentWrapper:line 72 at ScriptComponent_720e2ab81e00498aa9bf2e9d8af40422.ScriptMain..ctor() in dts://Scripts/ScriptComponent_720e2ab81e00498aa9bf2e9d8af40422/ScriptMain:line 78 End of inner exception stack trace at System.RuntimeTypeHandle.CreateInstance(RuntimeType type, Boolean publicOnly, Boolean noCheck, Boolean& canBeCached, RuntimeMethodHandle& ctor, Boolean& bNeedSecurityCheck) at System.RuntimeType.CreateInstanceSlow(Boolean publicOnly, Boolean fillCache) at System.RuntimeType.CreateInstanceImpl(Boolean publicOnly, Boolean skipVisibilityChecks, Boolean fillCache) at System.Activator.CreateInstance(Type type, Boolean nonPublic) at System.RuntimeType.CreateInstanceImpl(BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Object[] activationAttributes) at System.Activator.CreateInstance(Type type, BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Object[] activationAttributes) at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.CreateUserComponent()|||Script Component or Script Task?|||Script Component
|||

Rohit Ghule wrote:

Script Component

Does your script component have all of the Imports?

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
Inherits UserComponent

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim MaximumKey As Int32 = Me.Variables.MaxKey ' Grab value of MaxKey which was passed in via ReadOnlyVariables

Row.MaxKey = MaximumKey 'Assign the output field of "MaxKey" to the value of the passed in variable
End Sub

End Class|||Searching this forum turned up more information: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=201158&SiteID=1

I think the key is to look at your variables in the Public Overrides Sub section.|||

Most methods in a script task are overrides of base class methods, so you need a bit more info. The PreExecute, which is public, and overridden, so starts with "Public Overrides PreExecute(.." supports the variable manager stuff, but may not always be what you want. If you really need to access variable at a row level then you can, see this thread-

Re: R/W access problem with var in script Component - MSDN Forums
(http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=956181&SiteID=1)

Access MaximumErrorCount From script Task

Is it possible to get or set the value of MaximumErrorCount through a script task ?

I tried to assign User variables to MaximumErrorCount but could not succeed.

Any type of help will be appriciated.

Thanks

Gautam

I found a indirect way to do it.

I wanted to set the maximumerrorcount same as for loop count. So I used one expression to bind MaximumErrorCount property to user defined variable and it worked for me.

Still if anybody can help me set this property directly using object model through script task, that will be appreciated.

|||

It cannot be set via a script task.

There is an easier way though. You can set it using an expression on the property. Reply here if you don't know how to do this.

-Jamie

|||

Thanks Jamie for your reply. I am now using the expression on the property to set this.

|||

This seems like a silly question to ask, but I'm trying to do something similar, only how are you getting the count from the Foreach loop? I'm using a Foreach loop with an ADO Enumerator.

Honestly, I'd like to ignore MaximumErrorCount all together. Is there a simpler way to set this? I expect that my package can fail for every iteration if there is no data to process, that's a perfectly acceptable outcome for my package.

|||

I also wanted to ignore the MaximumErrorCount, so I bind the MaximumErrorCount property to @.[User::Count].

To populate User variable "Count", I used "Execute SQL Task" outside the loop. In this task, I set the Result set to "Single row" and used SQL statement as "Select Count(*) AS Count FROM Test_Table" .

I do not know if there is a simple way to do this or not.

Enjoy...

|||

Thanks for the idea. Certainly not a very clean way of solving the problem in my case as the loop is iterating over a result set. So I'll need two SQL tasks, one for the data and one for the count.

I'd be nice if there was a simpler way. My current solution has been to just set the Max error count to a staticly high number.

Access MaximumErrorCount From script Task

Is it possible to get or set the value of MaximumErrorCount through a script task ?

I tried to assign User variables to MaximumErrorCount but could not succeed.

Any type of help will be appriciated.

Thanks

Gautam

I found a indirect way to do it.

I wanted to set the maximumerrorcount same as for loop count. So I used one expression to bind MaximumErrorCount property to user defined variable and it worked for me.

Still if anybody can help me set this property directly using object model through script task, that will be appreciated.

|||

It cannot be set via a script task.

There is an easier way though. You can set it using an expression on the property. Reply here if you don't know how to do this.

-Jamie

|||

Thanks Jamie for your reply. I am now using the expression on the property to set this.

|||

This seems like a silly question to ask, but I'm trying to do something similar, only how are you getting the count from the Foreach loop? I'm using a Foreach loop with an ADO Enumerator.

Honestly, I'd like to ignore MaximumErrorCount all together. Is there a simpler way to set this? I expect that my package can fail for every iteration if there is no data to process, that's a perfectly acceptable outcome for my package.

|||

I also wanted to ignore the MaximumErrorCount, so I bind the MaximumErrorCount property to @.[User::Count].

To populate User variable "Count", I used "Execute SQL Task" outside the loop. In this task, I set the Result set to "Single row" and used SQL statement as "Select Count(*) AS Count FROM Test_Table" .

I do not know if there is a simple way to do this or not.

Enjoy...

|||

Thanks for the idea. Certainly not a very clean way of solving the problem in my case as the loop is iterating over a result set. So I'll need two SQL tasks, one for the data and one for the count.

I'd be nice if there was a simpler way. My current solution has been to just set the Max error count to a staticly high number.

Access equivelant

This was the script for access:
oRS.Open "Select * from AlertHistory Where Date
=#"&DateValue(LogTimeStamp)&"#;",oConn
does anyone know the equivalent for sql server?
Cheers!
Try replacing # with single quote (').
Anith

Access Denied from Subscriber.

I get the following message from SQL Server Agent:
The schema script
'\\LUKE\Replication\unc\LUKE$HORIZON_Promise_Promi se\20060113160017\Location_1.sch'
could not be propagated to the subscriber.
(Source: Merge Replication Provider (Agent); Error number: -2147201001)
------
The process could not read file
'\\LUKE\Replication\unc\LUKE$HORIZON_Promise_Promi se\20060113160017\Location_1.sch'
due to OS error 5.
(Source: BRIANT\HORIZON (Agent); Error number: 0)
------
Access is denied.
(Source: (OS); Error number: 5)
------
SQL Server on the Server and MSDE 2000 on the workstation are running
under a domain account.
The workstation has full access to the
\\LUKE\Replication\unc\LUKE$HORIZON_Promise_Promis e\20060113160017\
folder.
Any help would be greatly appreciated.
Ensure that the sql server agent on the subscriber has read rights to the
share \\LUKE\Replication and read and list folder rights to the physical
folder that maps to this drive and all child objects.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"klineb" <briankline@.hotmail.com> wrote in message
news:1137200691.533291.11060@.g14g2000cwa.googlegro ups.com...
>I get the following message from SQL Server Agent:
> The schema script
> '\\LUKE\Replication\unc\LUKE$HORIZON_Promise_Promi se\20060113160017\Location_1.sch'
> could not be propagated to the subscriber.
> (Source: Merge Replication Provider (Agent); Error number: -2147201001)
> ------
> The process could not read file
> '\\LUKE\Replication\unc\LUKE$HORIZON_Promise_Promi se\20060113160017\Location_1.sch'
> due to OS error 5.
> (Source: BRIANT\HORIZON (Agent); Error number: 0)
> ------
> Access is denied.
> (Source: (OS); Error number: 5)
> ------
> SQL Server on the Server and MSDE 2000 on the workstation are running
> under a domain account.
> The workstation has full access to the
> \\LUKE\Replication\unc\LUKE$HORIZON_Promise_Promis e\20060113160017\
> folder.
> Any help would be greatly appreciated.
>
|||Hilary,
Both the sever and workstation are using the same Domain Account. The
Domain account is a member of the Administrators group on each machine.
I have verified that the account has permission on all drives on the
system.
When I set each workstation to use the domain account, I made the
changes through EM on the server.
Is there anything that I am missing?
Thanks in advance.
Brian