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 through CRecordset

Hi,
In my VC++ 6.0 application, I have a database with 3 different
tables say Client,Task, and Algorithm. I want to access this three
tables through CRecordset. As far as i know i need to derive three
different classes for each table, from CRecordset. Can anyone please
help me so that by deriving a single class from CRecordset I will be
able to access the three tables.You could always create a view in SQL Server that joins the three tables and
then use CRecordset over this view, this would be the easiest way to set
this up.
For example:
create view MyThreeTables as
select * from table1, table2, table3 where table1.id=table2.id and
table1.id=table3.id
The above SQL statement is oversimplified but it should help you get
started, you need to have some proper join condition setup for the tables to
work together.
Matt Neerincx [MSFT]
This posting is provided "AS IS", with no warranties, and confers no rights.
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.
"Ashish choudhari" <ashishtchaudhari@.gmail.com> wrote in message
news:1126867931.595557.42760@.g47g2000cwa.googlegroups.com...
> Hi,
> In my VC++ 6.0 application, I have a database with 3 different
> tables say Client,Task, and Algorithm. I want to access this three
> tables through CRecordset. As far as i know i need to derive three
> different classes for each table, from CRecordset. Can anyone please
> help me so that by deriving a single class from CRecordset I will be
> able to access the three tables.
>

Accessing database through CRecordset

Hi,
In my VC++ 6.0 application, I have a database with 3 different
tables say Client,Task, and Algorithm. I want to access this three
tables through CRecordset. As far as i know i need to derive three
different classes for each table, from CRecordset. Can anyone please
help me so that by deriving a single class from CRecordset I will be
able to access the three tables.
You could always create a view in SQL Server that joins the three tables and
then use CRecordset over this view, this would be the easiest way to set
this up.
For example:
create view MyThreeTables as
select * from table1, table2, table3 where table1.id=table2.id and
table1.id=table3.id
The above SQL statement is oversimplified but it should help you get
started, you need to have some proper join condition setup for the tables to
work together.
Matt Neerincx [MSFT]
This posting is provided "AS IS", with no warranties, and confers no rights.
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.
"Ashish choudhari" <ashishtchaudhari@.gmail.com> wrote in message
news:1126867931.595557.42760@.g47g2000cwa.googlegro ups.com...
> Hi,
> In my VC++ 6.0 application, I have a database with 3 different
> tables say Client,Task, and Algorithm. I want to access this three
> tables through CRecordset. As far as i know i need to derive three
> different classes for each table, from CRecordset. Can anyone please
> help me so that by deriving a single class from CRecordset I will be
> able to access the three tables.
>

accessing components and task from scripting task

Hi, I have to researching of how to accesss package tasks and component using the SqlServer.Dts.Runtime class and so far, I havent found any solution. For example, if you package has a scriptiong task and a data flow task(<- which contains a data source component). Is it possible to use the scripting component to access the data source component in the data flow task and manipulate its properites like sqlcommand etc.

Emmanuel

An easier and more direct way to do this is to store the sqlcommand for the datasource in a package variable and set the variable value in the script task or even at the time of executing the package.

|||I know that can be done using variables and expressions and all that but i want to know where its possible to access component and dask of a package in a scripting task using a SqlServer.Runtime library. I know you can pass variable around be I want to know how to control component properties in a scripting task or component if possible. If anyone know how to do that or can direct me to the right resource, I will be glad. Thanks.|||Nope, the tasks and components can't directly access each other at runtime.

Accessing a package's variables from within a custom log provider...or not...

Hi,

Given that Task.Validate() exposes the package's VariableDispenser, but LogProviderBase.Validate() doesn't...

http://msdn2.microsoft.com/fr-fr/library/microsoft.sqlserver.dts.runtime.task.validate.aspx

http://msdn2.microsoft.com/fr-fr/library/microsoft.sqlserver.dts.runtime.logproviderbase.validate.aspx

...I guess that simply means that I can't access a package's variables within a custom log provider? Can anyone comment/confirm? Any other options/routes to achieving the same..?

We live in hope,

Tamim.

I have recently looked at this and would agree, there is no way to use variables in a log provider. There are no hooks available in any of the base class methods or any parameters that you are exposed to in a log provider. Would be nice if you could though, as it would give you more control to create your own message content, but as it stands this is not viable.|||

Thanks Darren - I was pretty sure there was no alternative, so it's good to have that ratified by yourself. There is however one cheeky/not-so-neat way round: the 'source' for some events, e.g. PackageStart, gives the package name. (Of course this is an exception for only 1 system variable, with no wider applicability).

Cheers,

Tamim.

Accessing a Microsoft Access database from within Visual C++

Hi there guys, I am currently trying to achieve a seemingly simple task in VC++ 2005. I have made a very simple form in Microsoft Access which I wish to serve as the beginnings of something greater. I created a db in MS Access named links.mdb containing on table-> Table1. Table1 contains 1 column, "Links", and i wish to read these strings into variables in my Visual C++ Windows Forms Application.

What I have done so far...

In Visual C++, I clicked on Data->Add new data source, and followed the wizard to add the microsoft access database to my application by the name, "linksDataSet". I can see the table in my left hand "Data Sources" pane in VC++. All I need to know is how to access my database from here so that I can read these strings stored in my table. Also, would be possible to schedule my application to log on to a http server and retrieve these links every time the application is executed? How would I go about doing this?

Thank you very much for your time
Regards
Linden.Umm... hi, my topic has been moved into this forum, even though I don't think it belongs here because my question is VC++ database related but can anyone help me? I would greatly appreciate it.|||44 Views and not one reply? Is this not a familiar concept?|||This is ridiculous! Why is this forum here? It obviously serves no purpose.|||

To read a Microsoft Access database table from VC++ 2005:

1. First I created a new Windows Console project in VC++.

2. Then Project | Add Class... then go under ATL and choose ATL OLEDB Consumer.

3. Click Data Source and choose Microsoft Jet 4.0 OLEDB Provider, Next>>> then type in database name.

4. Click OK, another dialog comes up, choose your table, it will create a single class for your table.

Then the code to read the data is like so:

#include "stdafx.h"

#include "Table1.h"

int _tmain(int argc, _TCHAR* argv[])

{

CoInitialize(NULL); // Be sure to initialize COM somewhere in your app one time...

CTable1 table1;

HRESULT hr = table1.OpenAll();

for(;;)

{

hr = table1.MoveNext();

if (S_OK != hr) break;

printf("table1.f1=%lu\n", table1.m_f1);

printf("table1.f2=%S\n", table1.m_f2);

}

return 0;

}

|||I am currently working in a windows forms application, how would the code change?

Access to TaskHost from derived Task class.

Hi,

for some reasons, I have to get access to the TaskHost during validation and execution. For example, I wanna know, if my task is within a container or not (parent is Sequence).

For the UI during design time, there is the TaskHost parameter. What about the execution time or during validation?

Any hints?

Thanks.

Thorsten

tviel wrote:

Hi,

for some reasons, I have to get access to the TaskHost during validation and execution. For example, I wanna know, if my task is within a container or not (parent is Sequence).

For the UI during design time, there is the TaskHost parameter. What about the execution time or during validation?

Any hints?

Thanks.

Thorsten

I might be wrong about this, but I believe that that a Task object can be cast as a TaskHost object at design time and run time.

I hope this helps.

|||

If you look at the two objects and what they inherit or implement, you can see that will never happen.

You can get the Task from a TaskHost using the TaskHost.InnerObject property but not the other way. The TaskHost is what deals in the world of containers, the task is the guts that sits below all that to do the actual work.

Your task will always be in a container, as even the package is a container. For example, Sequence , Package and TaskHost all inherit from EventsProvider, which in turn inherits from DtsContainer. Is you test ever going to be valid, even if we could get to the task host in the task.

Why do you need to do this? Perhaps we could offer an alternative solution is you explain the underlying requirement.

|||

Hi Darren,

thanks for your reply, maybe there is another alternative for generating a solution.

I try to configure (in Design-Time) all tasks of a sequencecontainer by one. Let's say I have some custom tasks, who all have the same properties. For normal use, you can configure the tasks by a custom UI. To check, if the taskHost.Parent is of Type Sequence works, as the custom UI sets the input fields to readonly, when using the tasks in a sequenc container.

Now I wanna provide a custom tasks, having a UI for the same variables as the upper tasks. Adding to the sequence container, and setting the values, it should update all tasks in the container with the user values read.

For so, i have the ability to configure some values of similar tasks by one.

Hope this makes things clear.

Thanks.

PS: AFAIK there is no collection in DtsContainer to iterate over the child tasks.

|||

I am wanting to do something similar to allow me to have access to the TaskHost. I have a controll flow task and I want it to be able to read the initial start time from the outer-most container. I would also like to get the path of the Package that this task is in.

Example:

I call dtexec to run Package_a.dtsx
Package_a.dtsx calls Package_b.dtsx
Package_b.dtsx has MyCustomTask.

I want MyCustomTask to know 1) the start time of Package_a.dtsx and 2) the disk path of Package_b.dtsx

Thanks,

Graham

|||Even the package itself does not "know" the disk path it was loaded from. The package can be loaded from SQL server, or constructed in memory using API without saving it at all, or loaded from file and then completely modified, etc, so the disk path does not always makes sense at all. So there is no way for a task to find out the location from where the package was loaded. If you need some package marker, use a package-level variable instead.|||

Hi,

understandable.

But, I wanna provide a custom task "communicating" with other tasks of itself in a simple manner. Using variables needs the designer to set this variables by hand or committing new variables during Prompt, due to the missing ability to generate variables programmatically and silent. This is a possible error source and not very intuitive.

OK, how about my second way? Is there an unknown (for me) way to get the list of tasks of a sequence during design time. Found nothing.

Thanks

Thorsten

|||

tviel wrote:

OK, how about my second way? Is there an unknown (for me) way to get the list of tasks of a sequence during design time. Found nothing.

This part is very simple: the Sequence has Executables property that returns list of task hosts or child containers.

http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.sequence.executables.aspx

|||

Hi Michael,

fine, that should do the trick for me. For now, I phone my oculist to get my eyes checked :-)

Thanks

Thorsten

|||

What about the other part of my question - Can I find out the start time of the initial package while within the Execute method of a Task? Could a connection manager help with that?

Thank you,

Graham

|||

If by initial package you mean the package that hosts the task whose Execute method does the work, then yes, look at the system variable StartTime. That is the package start time.

If the initial package is a parent package that calls a child package, then no, since the child package really has no knowledge of the parent, the parent could be anybody there is no interface that defines parent details. See Michael's post. You could help things though by passing in the parent variable to the chuld. Use a parent package configuration to do this perhaps? Store it in a variable and the n read that variable in the task.

|||

Figured it out. It is sort of like you said, Darren. If you define a static variable on your Task class, you can assign to it in the Execute method with a value obtained at design time. The example below shows how to get the Name of the top most package (e.g. using the scenario from my earlier post, that would be Package_A )

class CustomTask : Task
{
private static string topMostPackage = null;

private string _package;
//This property is accessed at design time
//through the UI class's TaskHost.
public string PackageName{
get{return _packageName;}
set{_packageName = value;}
}

public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents
componentEvents, IDTSLogging log, object transaction)
{
DTSExecResult result = DTSExecResult.Success;
_executionPIT = DateTime.Now;

//this will only be true on the first task that executes
if (string.IsNullOrEmpty(topMostPackage))
topMostPackage = _packageName;
}
}

Access to TaskHost from derived Task class.

Hi,

for some reasons, I have to get access to the TaskHost during validation and execution. For example, I wanna know, if my task is within a container or not (parent is Sequence).

For the UI during design time, there is the TaskHost parameter. What about the execution time or during validation?

Any hints?

Thanks.

Thorsten

tviel wrote:

Hi,

for some reasons, I have to get access to the TaskHost during validation and execution. For example, I wanna know, if my task is within a container or not (parent is Sequence).

For the UI during design time, there is the TaskHost parameter. What about the execution time or during validation?

Any hints?

Thanks.

Thorsten

I might be wrong about this, but I believe that that a Task object can be cast as a TaskHost object at design time and run time.

I hope this helps.

|||

If you look at the two objects and what they inherit or implement, you can see that will never happen.

You can get the Task from a TaskHost using the TaskHost.InnerObject property but not the other way. The TaskHost is what deals in the world of containers, the task is the guts that sits below all that to do the actual work.

Your task will always be in a container, as even the package is a container. For example, Sequence , Package and TaskHost all inherit from EventsProvider, which in turn inherits from DtsContainer. Is you test ever going to be valid, even if we could get to the task host in the task.

Why do you need to do this? Perhaps we could offer an alternative solution is you explain the underlying requirement.

|||

Hi Darren,

thanks for your reply, maybe there is another alternative for generating a solution.

I try to configure (in Design-Time) all tasks of a sequencecontainer by one. Let's say I have some custom tasks, who all have the same properties. For normal use, you can configure the tasks by a custom UI. To check, if the taskHost.Parent is of Type Sequence works, as the custom UI sets the input fields to readonly, when using the tasks in a sequenc container.

Now I wanna provide a custom tasks, having a UI for the same variables as the upper tasks. Adding to the sequence container, and setting the values, it should update all tasks in the container with the user values read.

For so, i have the ability to configure some values of similar tasks by one.

Hope this makes things clear.

Thanks.

PS: AFAIK there is no collection in DtsContainer to iterate over the child tasks.

|||

I am wanting to do something similar to allow me to have access to the TaskHost. I have a controll flow task and I want it to be able to read the initial start time from the outer-most container. I would also like to get the path of the Package that this task is in.

Example:

I call dtexec to run Package_a.dtsx
Package_a.dtsx calls Package_b.dtsx
Package_b.dtsx has MyCustomTask.

I want MyCustomTask to know 1) the start time of Package_a.dtsx and 2) the disk path of Package_b.dtsx

Thanks,

Graham

|||Even the package itself does not "know" the disk path it was loaded from. The package can be loaded from SQL server, or constructed in memory using API without saving it at all, or loaded from file and then completely modified, etc, so the disk path does not always makes sense at all. So there is no way for a task to find out the location from where the package was loaded. If you need some package marker, use a package-level variable instead.|||

Hi,

understandable.

But, I wanna provide a custom task "communicating" with other tasks of itself in a simple manner. Using variables needs the designer to set this variables by hand or committing new variables during Prompt, due to the missing ability to generate variables programmatically and silent. This is a possible error source and not very intuitive.

OK, how about my second way? Is there an unknown (for me) way to get the list of tasks of a sequence during design time. Found nothing.

Thanks

Thorsten

|||

tviel wrote:

OK, how about my second way? Is there an unknown (for me) way to get the list of tasks of a sequence during design time. Found nothing.

This part is very simple: the Sequence has Executables property that returns list of task hosts or child containers.

http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.sequence.executables.aspx

|||

Hi Michael,

fine, that should do the trick for me. For now, I phone my oculist to get my eyes checked :-)

Thanks

Thorsten

|||

What about the other part of my question - Can I find out the start time of the initial package while within the Execute method of a Task? Could a connection manager help with that?

Thank you,

Graham

|||

If by initial package you mean the package that hosts the task whose Execute method does the work, then yes, look at the system variable StartTime. That is the package start time.

If the initial package is a parent package that calls a child package, then no, since the child package really has no knowledge of the parent, the parent could be anybody there is no interface that defines parent details. See Michael's post. You could help things though by passing in the parent variable to the chuld. Use a parent package configuration to do this perhaps? Store it in a variable and the n read that variable in the task.

|||

Figured it out. It is sort of like you said, Darren. If you define a static variable on your Task class, you can assign to it in the Execute method with a value obtained at design time. The example below shows how to get the Name of the top most package (e.g. using the scenario from my earlier post, that would be Package_A )

class CustomTask : Task
{
private static string topMostPackage = null;

private string _package;
//This property is accessed at design time
//through the UI class's TaskHost.
public string PackageName{
get{return _packageName;}
set{_packageName = value;}
}

public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents
componentEvents, IDTSLogging log, object transaction)
{
DTSExecResult result = DTSExecResult.Success;
_executionPIT = DateTime.Now;

//this will only be true on the first task that executes
if (string.IsNullOrEmpty(topMostPackage))
topMostPackage = _packageName;
}
}

Access to SQL server

Hi,

I have to write an VB.NET application which imports data from an Access database to an Ms SQL server. Is there any code for this kind of task.

Thanks.

Take a look at this post

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=320478&SiteID=1

Connection string to retrieve from Access DB is as follows

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Test.mdb;"

Hope this helps

Access Sql server using Javascript

Hi All,

I have a task need to update Sql server database whenever the user click "close" button in browser. It seemed I can't capture this event using asp.net. How can I access Sql server database in Javascript?
Thanks.

Quote:

Originally Posted by lindy

Hi All,

I have a task need to update Sql server database whenever the user click "close" button in browser. It seemed I can't capture this event using asp.net. How can I access Sql server database in Javascript?
Thanks.

Two Ideas for you:

1. Change your close button to use ASP.Net

2. Use AJAX to call a page that will update your information.

access sql server 2005 via http

Hi

Is it possible to access sql server 2005 via http and do some management and administration task? if possible how?

Thanks in advance,

Larry

Hi larry,

I suggest you configure a windows 2000 or 2003 VPN or cisco VPN
to actually access the Sql server box via http
this is i think more secure.

You can however access sql server via SQL server Endpoint
but i suggest you do configure a VPN
since it is more secure

regards,
joey|||

http://www.microsoft.com/downloads/details.aspx?FamilyID=c039a798-c57a-419e-acbc-2a332cb7f959&displaylang=en closely related to SQL 2000 or MSDE, but you are looking for SQL 2005 instance so as suggested endpoings are good to go, http://www.developer.com/db/article.php/3390241 and http://davidhayden.com/blog/dave/archive/2006/03/31/2897.aspxfyi..

http://codebetter.com/blogs/raymond.lewallen/archive/2005/06/23/65089.aspx tooq

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 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 for the user

Dear Friends
I want to create a user for one database in the server so that he can do all
the admin task such as Backup, Restore, Modification for table, Proceduers,
Views and Functions.
But should not have access to another databases. Please suggest how i can do
the same.
Best regardsHi,
Assign the DB_OWNER database fixed role to the user. This will allow him the
admin tasks in that particular database.
Thanks
Hari
SQL Server MVP
"Sharad2005" <niitmalad@.yahoo.co.uk> wrote in message
news:16CA54CC-25A9-49FE-A9B4-001C0E0ED96E@.microsoft.com...
> Dear Friends
> I want to create a user for one database in the server so that he can do
> all
> the admin task such as Backup, Restore, Modification for table,
> Proceduers,
> Views and Functions.
> But should not have access to another databases. Please suggest how i can
> do
> the same.
> Best regards
>

Access for the user

Dear Friends
I want to create a user for one database in the server so that he can do all
the admin task such as Backup, Restore, Modification for table, Proceduers,
Views and Functions.
But should not have access to another databases. Please suggest how i can do
the same.
Best regards
Hi,
Assign the DB_OWNER database fixed role to the user. This will allow him the
admin tasks in that particular database.
Thanks
Hari
SQL Server MVP
"Sharad2005" <niitmalad@.yahoo.co.uk> wrote in message
news:16CA54CC-25A9-49FE-A9B4-001C0E0ED96E@.microsoft.com...
> Dear Friends
> I want to create a user for one database in the server so that he can do
> all
> the admin task such as Backup, Restore, Modification for table,
> Proceduers,
> Views and Functions.
> But should not have access to another databases. Please suggest how i can
> do
> the same.
> Best regards
>

Access for the user

Dear Friends
I want to create a user for one database in the server so that he can do all
the admin task such as Backup, Restore, Modification for table, Proceduers,
Views and Functions.
But should not have access to another databases. Please suggest how i can do
the same.
Best regardsHi,
Assign the DB_OWNER database fixed role to the user. This will allow him the
admin tasks in that particular database.
Thanks
Hari
SQL Server MVP
"Sharad2005" <niitmalad@.yahoo.co.uk> wrote in message
news:16CA54CC-25A9-49FE-A9B4-001C0E0ED96E@.microsoft.com...
> Dear Friends
> I want to create a user for one database in the server so that he can do
> all
> the admin task such as Backup, Restore, Modification for table,
> Proceduers,
> Views and Functions.
> But should not have access to another databases. Please suggest how i can
> do
> the same.
> Best regards
>

Access Denied when trying to execute a ssis task

Hi,

I sometimes come accross this error when I attempt to execute an isolated task in the control flow. What is funny is that I am still able to debug the package.

It eventually resolves after a while. What could it be?

Thanks

Philippe

TITLE: Microsoft Visual Studio

Access Denied. (Exception from HRESULT: 0x80030005(STG_E_ACCESSDENIED))

BUTTONS:

OK

Are you using source control?

You usually get this error if you execute something that isn't checked out.

-Jamie

|||

Yep, I do use VSS 6

I have a hard time understanding a few things with VSS, i.e. I was running a package fine in BIDS but it failed in SSMS, The package seemed checked-in but the solution was checked out in another location under the same userid.

I did force the undo check out and it eventually went fine. Now, SSMS is running the version I want.

I yet have to understand exactly how VSS Works. Right now, I am confused.

Philippe

|||The point is that you shouldn't have to check an SSIS package out of source control (we use TFS here) in order to execute it. This mis-feature is still present 15 months after Philippe's message, and still very frustrating!|||

I agree.

Have you reported this at Connect?

-Jamie

|||

Hi,

I have dropped VSS. I now use my company "official" source control, CVS, along with a couple third party tools like Tortoise, Smart CVS and CVSSCC which gives me some integration right from dev tools.

All that source control stuff is still really not user friendly, because of that I keep multiple backups of my stuff.

This has saved my life a couple times.

I thought the point of using any source control was to free the developer of any concern about these things and let him focus on development. I was wrong.

My IT chose CVS only because it is free, not because it is good, on the top of that they run an outdated unix version on a very old box.

I have a hard time with this while it is supposed to be "safer".

I need to be really carefull with this, not feeling safe anyhow.

Does any one has a success story to share as far as source control of any kind is concerned?

- Multiple developers on the same project

- Deployment of specifc versions by a dba team

- roll back to previous versions

- branches management

- never loosing any file/version on your working folder

- no fuss with roots/modules

- no issues with caps/no caps version fo the same module

- always get the project to the right root/module

- able to easilly do spring cleaning of the repository

Thanks,

Philippe

Access Denied when trying to execute a ssis task

Hi,

I sometimes come accross this error when I attempt to execute an isolated task in the control flow. What is funny is that I am still able to debug the package.

It eventually resolves after a while. What could it be?

Thanks

Philippe

TITLE: Microsoft Visual Studio

Access Denied. (Exception from HRESULT: 0x80030005(STG_E_ACCESSDENIED))

BUTTONS:

OK

Are you using source control?

You usually get this error if you execute something that isn't checked out.

-Jamie

|||

Yep, I do use VSS 6

I have a hard time understanding a few things with VSS, i.e. I was running a package fine in BIDS but it failed in SSMS, The package seemed checked-in but the solution was checked out in another location under the same userid.

I did force the undo check out and it eventually went fine. Now, SSMS is running the version I want.

I yet have to understand exactly how VSS Works. Right now, I am confused.

Philippe

|||The point is that you shouldn't have to check an SSIS package out of source control (we use TFS here) in order to execute it. This mis-feature is still present 15 months after Philippe's message, and still very frustrating!|||

I agree.

Have you reported this at Connect?

-Jamie

|||

Hi,

I have dropped VSS. I now use my company "official" source control, CVS, along with a couple third party tools like Tortoise, Smart CVS and CVSSCC which gives me some integration right from dev tools.

All that source control stuff is still really not user friendly, because of that I keep multiple backups of my stuff.

This has saved my life a couple times.

I thought the point of using any source control was to free the developer of any concern about these things and let him focus on development. I was wrong.

My IT chose CVS only because it is free, not because it is good, on the top of that they run an outdated unix version on a very old box.

I have a hard time with this while it is supposed to be "safer".

I need to be really carefull with this, not feeling safe anyhow.

Does any one has a success story to share as far as source control of any kind is concerned?

- Multiple developers on the same project

- Deployment of specifc versions by a dba team

- roll back to previous versions

- branches management

- never loosing any file/version on your working folder

- no fuss with roots/modules

- no issues with caps/no caps version fo the same module

- always get the project to the right root/module

- able to easilly do spring cleaning of the repository

Thanks,

Philippe

Access denied to files

Hi,

Im trying to "use" files during my flow in two diferent kind of components (send email task and custom transformation), but the error i get is similar: access denied, file doesnt exists, is locked by another proccess or not enough rigths.

-In custom transofrmation i am trying to write to a file with the next code:

Dim sw As StreamWriter

If (Not File.Exists(".\test.txt")) Then

sw = File.CreateText(".\test.txt")

Else

sw = File.AppendText(".\test.txt")

End If

sw.Write("Numero de Registros en Empresas")

sw.WriteLine(Row.CuentaRegEmpresas)

sw.Write("Numero de Registros en Reporta")

sw.WriteLine(Row.CuentaRegReporta)

sw.Write("Numero de Registros de Facturas ampliadas")

sw.WriteLine(Row.CuentaRegFAmp)

sw.WriteLine()

- In send email tranformation i am trying to attach a different file (the log of the process) , but the error is that i dont have rights to access. If i try to send another file this error disappears...

Both files (test.txt and log.txt) have total control rights to all users, and arent locked or opened by any other process during the execution.

Edit: Thats the error trace i get:

en System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)

en System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy)

en System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, FileOptions options)

en System.IO.StreamWriter.CreateFile(String path, Boolean append)

en System.IO.StreamWriter..ctor(String path, Boolean append, Encoding encoding, Int32 bufferSize)

en System.IO.StreamWriter..ctor(String path, Boolean append)

en System.IO.File.AppendText(String path)

en ScriptComponent_14cd101f436a42b18dc68087869033b2.ScriptMain.Entrada0_ProcessInputRow(Entrada0Buffer Row)

en ScriptComponent_14cd101f436a42b18dc68087869033b2.UserComponent.Entrada0_ProcessInput(Entrada0Buffer Buffer)

en ScriptComponent_14cd101f436a42b18dc68087869033b2.UserComponent.ProcessInput(Int32 InputID, PipelineBuffer Buffer)

en Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)

You mention three different errors, can you be clear about which error happens when? Please post the exact error message as well.

Can you also explain more about the tasks, and the order they are executed within your package.

The code snippet above seems incomplete, and also rather strang, perhaps if you posted the full code it would make more sense. Based on what you have posted I'd say it is wrong, as opening the file for each row is a bad idea, and you do not close the file.

As an aside the syntax seems complicated, to append to a file, just use the constructor overload -

Code Snippet

StreamWriter stream = new StreamWriter("C:\Test.txt", true);

stream.WriteLine("Test");

stream.Close();

|||

Hi Darren,

Your are right, the problem of the custom transformation was that i did not close the StreamWriter, now it's working fine.

The other problem is that, at the end of the process i want to send the log file, but it seems like the process locks the file and cant be attached in the email... am i wrong? i will try to send the email in other package inside the same project... Its possible to send parameters to the new package?

Any other idea to send the log via email just at the end of the process?

Thanks

Edit: I have proved this solution and it doesnt work... log file cant be send using a different package inside the project.

|||Why is the file locked? If you are in control of writing the file, then you should have closed and therefore released all locks to the file, so the file should be available to send. You must have something open still. Ultimately you can use something like Process Moniotor of File? (I forget to find who has the lock), the old SysInternals tools, now MS.|||The file that is locked is the log of the process... i have read in other post that is not possible to send it becuase the own process locks it, so i decide to send via email only the errors stored in @.[System::ErrorDescription]

variable when an error event is raised.

thanks