check sql licence: package

Showing posts with label package. Show all posts

Thursday, March 22, 2012

Class not registered error

When I try to run DTS Package in SQL 2000, it reports a error - Class not registered?

What is the solution to this problem?

You might try the DTS forum - http://groups.google.com/group/microsoft.public.sqlserver.dts?lnk=srg

This forum is for SSIS.

Sunday, March 11, 2012

Choose Provider (SQL or Oracle) at Deployment Time

Is it possible to design a package with one connection manager who's name remains static, but the actual provider changes at deployment time?

For example, I have two connection managers, source and target. Each of these, depending on the environment, may use any combination of native SQL Server, or Oracle.

When I create a connection manager, the provider is specified at design time. Is it possible, using the confguration files, to allow the administrator to determine the provider at deployment time, such that the Control Flow and Data Flow tasks can use the connection mangers without knowing the provider, or more importantly, only one version of the package need be maintained?

Thanks,

Rick

Provided the data types were the same, yes, you might be able to get away with updating the ConnectionString property, however going from Oracle to SQL Server will undoubtedly cause you metadata problems. I'm not sure on that approach though. (Oracle numeric data types come to mind as a problem mapping to SQL Server)

You should probably create two data flows (or as many as you need) and then use expression constraints on your control flow to determine which "flow" to use.|||

RickGaribay.NET wrote:

Is it possible to design a package with one connection manager who's name remains static, but the actual provider changes at deployment time?
For example, I have two connection managers, source and target. Each of these, depending on the environment, may use any combination of native SQL Server, or Oracle.
When I create a connection manager, the provider is specified at design time. Is it possible, using the confguration files, to allow the administrator to determine the provider at deployment time, such that the Control Flow and Data Flow tasks can use the connection mangers without knowing the provider, or more importantly, only one version of the package need be maintained?
Thanks,
Rick

Yes, this is possible. The provider is within the ConenctionString property which can be changed at execution-time using configurations or property expressions.

More on property expressions: http://blogs.conchango.com/jamiethomson/archive/tags/Expressions/default.aspx

-Jamie

|||

Phil Brammer wrote:
Provided the data types were the same, yes, you might be able to get away with updating the ConnectionString property, however going from Oracle to SQL Server will undoubtedly cause you metadata problems. I'm not sure on that approach though.

Probbaly only if you are using data-flows - which need not be the case.

Phil Brammer wrote:
You should probably create two data flows (or as many as you need) and then use expression constraints on your control flow to determine which "flow" to use.

This is a good idea. You can make the connection string property conditional as well using property expressions (see my earlier post).

-Jamie

Thursday, March 8, 2012

Child packages : Failed to acquire connection

Hi!

I am having a problem with a Parent package that invokes Child SSIS packages.

The Child packages have EncryptSensitiveWithPassword as their security setting.

I have placed the PackagePassword for each Child in the Parent package.

Each Child package contains a SQL Server Authentication username and password; they are not using Windows Authentication for the SQL Server login.

Here are what seem to be the relevant entries from a log file when the failure occurs:

Useriagnostic,XXXX,YYYYY\xxxx,Microsoft OLE DB Provider for SQL Server,GUID1,GUID2,06/01/2007 11:14:04 AM,06/01/2007 11:14:04 AM,0,0x,ExternalRequest_pre: The object is ready to make the following external request: 'IDataInitialize::GetDataSource'.
Useriagnostic,XXXX,YYYYY\xxxx,Microsoft OLE DB Provider for SQL Server,GUID1,GUID2,06/01/2007 11:14:04 AM,06/01/2007 11:14:04 AM,0,0x,ExternalRequest_post: 'IDataInitialize::GetDataSource succeeded'. The external request has completed.
Useriagnostic,XXXX,YYYYY\xxxx,Microsoft OLE DB Provider for SQL Server,GUID1,GUID2,06/01/2007 11:14:04 AM,06/01/2007 11:14:04 AM,0,0x,ExternalRequest_pre: The object is ready to make the following external request: 'IDBInitialize::Initialize'.
Useriagnostic,XXXX,YYYYY\xxxx,Microsoft OLE DB Provider for SQL Server,GUID1,GUID2,06/01/2007 11:14:04 AM,06/01/2007 11:14:04 AM,0,0x,ExternalRequest_post: 'IDBInitialize::Initialize failed'. The external request has completed.
OnError,XXXX,YYYYY\xxxx,SQLTask1 in ChildPackage1,GUID3,GUID2,06/01/2007 11:14:04 AM,06/01/2007 11:14:04 AM,-1073573396,0x,Failed to acquire connection "Microsoft OLE DB Provider for SQL Server". Connection may not be configured correctly or you may not have the right permissions on this connection.

OnError,XXXX,YYYYY\xxxx,ChildPackage1,GUID4,GUID2,06/01/2007 11:14:04 AM,06/01/2007 11:14:04 AM,-1073573396,0x,Failed to acquire connection "Microsoft OLE DB Provider for SQL Server". Connection may not be configured correctly or you may not have the right permissions on this connection.

When I run the Child package by itself, it works without any errors. So the problem seems to be related to the fact that it is being called by the parent, and somehow the "security settings" for the parent are not quite the same as the settings when I run the Child package by itself.

What might I be doing wrong?

I am using File System storage for my packages, on a shared network drive.

Just to check - you are setting the password in the Execute Package task, right? My apologies if this is obvious, just wanted to confirm.

|||

Yes, I have set the password in each Execute Package task -- and the error message is not about an inability to open the package (for which there appear to be a number of error messages available). Instead, it is one that seems to indicate an inability to login to SQL Server with the SQL Server Authentication incorporated in the package.

I have read some web pages (e.g., the link below) that refer to the Integrated Security of a SQL Server connection. I see what appears to be some XML for that in the Child packages (but none in the Parent package) -- and the value that I found was "True". I tried changing it to "SSPI", but then I couldn't even run the child package by itself, nor when called by the Parent.

http://blogs.msdn.com/suryaj/archive/2006/05/09/594039.aspx

|||

Is it possible to use the Debugger in Visual Studio 2005 to study the connection parameter values that are present when a Parent package invokes a Child package?

Dan

|||

I'm going to take a look at using the script Jamie Thompon wrote, as found at

http://blogs.conchango.com/jamiethomson/archive/2005/10/10/2253.aspx

I hope it will give me some clues as to why the connection cannot be acquired when the SSIS package is run as a child package.

|||

John,

Using Jamie Thomson's "script task" I could see that you were correct: my problem was still with the package password. I must have typed it incorrectly, or something.

I am sorry to have dismissed your suggestion so quickly -- but I didn't see it until I was off on vacation and happened to logon and notice your post. So I didn't have SQL Server at my disposal to investigate further. You have my sincere apologies.

Dan

|||No problem - I'm glad you were able to resolve it.

Child packages : Failed to acquire connection

Hi!

I am having a problem with a Parent package that invokes Child SSIS packages.

The Child packages have EncryptSensitiveWithPassword as their security setting.

I have placed the PackagePassword for each Child in the Parent package.

Each Child package contains a SQL Server Authentication username and password; they are not using Windows Authentication for the SQL Server login.

Here are what seem to be the relevant entries from a log file when the failure occurs:

What might I be doing wrong?

I am using File System storage for my packages, on a shared network drive.

Just to check - you are setting the password in the Execute Package task, right? My apologies if this is obvious, just wanted to confirm.

|||

http://blogs.msdn.com/suryaj/archive/2006/05/09/594039.aspx

|||

Is it possible to use the Debugger in Visual Studio 2005 to study the connection parameter values that are present when a Parent package invokes a Child package?

Dan

|||

I'm going to take a look at using the script Jamie Thompon wrote, as found at

http://blogs.conchango.com/jamiethomson/archive/2005/10/10/2253.aspx

I hope it will give me some clues as to why the connection cannot be acquired when the SSIS package is run as a child package.

|||

John,

Using Jamie Thomson's "script task" I could see that you were correct: my problem was still with the package password. I must have typed it incorrectly, or something.

Dan

|||No problem - I'm glad you were able to resolve it.

Child Package Fails when called from parent

So I have a parent package that calls another package using the Execute Package Task. When I run the child it runs fine but when I run it from the parent i get this msg...Any ideas?

Error: 0xC00220E4 at Execute VR Account Load: Error 0xC0012050 while preparing to load the package. Package failed validation from the ExecutePackage task. The package cannot run.

Pls take a look at this post to see whether the investigations and solutions there helps. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=241941&SiteID=1

thanks

wenyang

|||

Wenyang,

Thanks for the reply but the solution did not work. I might have found a bug here because the child package uses package configurations and even though I have disabled the package config on the child when I execute it from the parent the output window says that it is trying to load the package configurations. Not sure if this has anything to do with it.

Information: 0x40016040 at VR Load Account: The package is attempting to configure from SQL Server using the configuration string ""localhost.CALLMIS";"[dbo].[SSIS Configurations]";"MISLoads_ServerName";".

Thx

|||

Disregard last msg I posted. It appears the problem was that there was a bad connection guid or something like that still hanging around in the child? I recreated the package and it appears to be working now...

Not my idea of fun...

Child package ConnectionManager visibility

Hopefully a simple question about parent-child package relationship. For this example, let's say I have a simple setup - one parent package: parent.dtsx, and one child package: child.dtsx. The parent package calls the child package via the ExecutePackage Task.

If I add an OleDB ConnectionManager to the parent package called MySqlConnectionManager, should I be able to reference this connection via a script task (or custom component) from my child package? I realize that I will have a problem doing this at design time, but I thought I could get around it with the script task or custom component. That said, when I look in the Connections collection at run-time from within my child package, I do not see the parent package's MySqlConnectionManager. Am I missing something, or is this the way it was intended to work?

Thanks,

David

David,

I suspect you cannot do this. Connection managers can only be used in the package in which they reside - even if you're using a script task.

-Jamie

|||

Jamie,

Thanks for the response. I must say it is somewhat disappointing, though I think I have a work around for my situation. That said, I would still be interested in hearing a rationale for why this is the case. It seems to me like it breaks the container hierarchy paradigm.

David

|||

Well I can see why you think this but remember that connection managers don't follow container scope like variables do so the same rules don't apply.

Having said that, there were plans to scope conenction managers to the container hierarchy but it couldn't be done in time (or something). Reading between the lines its something they (well...kirk Haselden) wanted to do but it was down the priority list.

-Jamie

|||

Thanks again, Jamie. Hopefully this will be implemented at some point in the future.

Related, I found the opposite to be true when dealing with log providers. Interestingly, the connections collection of the parent package DOES appear to be available to child package log providers (I have built a custom log provider in which this appears to be true). It strikes me as bizzare that the functionality I want is there for log providers, but not for the package tasks. That may be due, however, to gaps in my understanding of parent-child package relationships.

Child Package cannot always be found

Hi folks, please can you help.

I have an Integration Services file package that calls another file package. The child package will always reside in the same directory as the parent package. In the connection manager for the child package I have simply specified the name of the child package not the full directory path so that the child package path is a relative path to the parent package. This all works perfectly when running the package from within Visual Studio however when running it using dtexecui it sometimes works and other times it fails with the error “the system cannot find the file specified”. Does anyone know what the reason for this inconsistency is? Alternatively is there a way to get the package path during runtime and dynamically set the path using the task expressions?

I can't explain why it sometimes works and other times does not.

There is no way to get the package path at runtime although i think this would be quite a nice feature. Possibly available in a system variable.

The way I get around this problem is documented here: http://blogs.conchango.com/jamiethomson/archive/2006/01/05/2559.aspx

Hope that makes sense.

-Jamie

|||

The reason relative paths don't work is because they are relative to the location of the application that runs the package. So, if the application you're running is in the right location, it will work. If not, the relative path points to a location that doesn't exist or at least, where the file doesn't exist.

|||

Kirk you are absolutely right but the inconsistency of it was throwing me off. Fortunately I have now figured that out! As it turns out it is the way in which the package gets run that causes it to work or not work. When executing the package by double-clicking on it to bring up dtexecui it works perfectly, however when running dtexec or dtexecui from the command line it does not work! Makes perfect sense now that I can see that there is consistency!

Jamie I can’t seem to access your link at the moment but I will have a look at it because it sounds like it may be the only an alternative.

Thanks for your responses guys.

Wednesday, March 7, 2012

Checkpoints problem in parallel tasks

Hi,

I have a master package with a sequence container with around 10 execute package tasks (for child packages), all in parallel. Checkpoints has been enabled in the master package. For the execute package tasks FailParentOnFailure is set to true and for the sequence container FailPackageOnFailure is set to true.

The problem i am facing is as follows. One of the parallel tasks fails and at the time of failure some of the parallel tasks (say set S1) are completed succesfully and few are still in execution (say set S2) which eventually complete successfully. The container fails after all the tasks complete execution and fails the package. When the package is restarted the task which failed is not executed, but the tasks in set S2 are executed.

If FailPackageOnFailure is set to true and whatever be the FailParentOnFailure value for the execute package task, in case of restart the failed package is executed but the tasks in set S2 are also executed.

Please let me know if there is any setting that only the failed task executes on restart.

Thanks in advance

Essentially, you want to track the outcome of parallel execute package tasks, and only re-execute those which have failed. The problem with using checkpoint files to accomplish this, is that checkpoint files don't track the status of parallel containers after the first task failure associated with FailPackageOnFailure happens.

What that means, if you have 10 parallel EPTs (execute package tasks), and any one of them has a task failure, none of the subsequently completed EPT tasks, whether they succeed or fail, have their outcomes written to the checkpoint file. So, on package restart, "post last checkpoint file write" tasks will run again.

An easier approach might be to put a for loop around each EPT, and loop until successful, using a variable scoped at the For Loop as a"Go/No Go" decision maker, and max retry count.

However, if you want to do it in SSIS using a restart mechanism, you could roll your own checkpointing mechanism.

Such a mechanism would mean creating an OnTaskFailed event handler which would track the failed EPT SourceID/SourceName's (read TaskID/TaskName).

Then add an OnPostExecute event handler to determine those EPTs which succeeded by inference (they didn't fail). Add in a final "On Completetion" script task to append the successful TaskIDs to a configuration file which would then be read in automatically on subsequent execution.

Lastly, you'd set the the Disable property on each EPT to something like FINDSTRING(@.SuccessfulTaskIDs,@.System::TaskID,1) > 0. You can do it that way, but its not point and click by any stretch.

CheckPoints and On Completion

I have a package that has 4 Script Tasks that are placed sequentially.

I have Task1--> Task2-->Task3-->Task4

The arrows between them are OnCompletion Arrows as opposed to the Standard OnSuccess arrows.Even if Task2 failed, it would still execute 3 and 4

the catch is that i want it such that when i run the first time and task 2 fails, then all the tasks except task2 should run which is fine, but when i rerun it. I want it such that it realises that task 2 had failed earlier, so it runs just task2.... if both 2 and 4 had failed then it should just run 2 and 4

i tired to implement it with check points, but the problemn is that if it fails at task2 it stops at task2 and does not continue to execute tasks 3 &4... when u rerun it starts at 2 but like i said i would like 3 & 4 to have completed the previous run...

Any suggestions would be helpful

Thanks for any help in advance..

smathew

So do you really need the condition to start the next task? It seems that if you would just like to rerun the tasks that failed you don't REALLY have any constraints on when the tasks should start... Or is this wrong?

If that is correct you can probably just get rid of the constraints and have four unconnected tasks which would (should?) rerun the correct tasks via the checkpoints...

|||

well the scripts call child packages, where the child packages are the same, but they call with different variables, so if they are called in parallel and they execute parallel, then since they load the same set of tables, it could end up in a blocking situation..

smathew

|||

Just an idea, try saving the status of relevant tasks (2, 4) in variable(s) and save those variable(s) in the config file or a flat file at the start read the prevously saved status from that file and based on the value of the variable branch as required.

|||

Hi,

Interesting problem. There's a possible solution here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=464381&SiteID=1

Its a pretty funky little method - very clever indeed.

-Jamie

|||

That is just about the exact solution I came up with after looking at this for the past hour or two...

No fun GUI point and click, but probably the best you will do with this type of situation.

Checkpoint restart

I have a package that uses checkpoint restart. It is resposible for truncatings many sets of tables and then loading them. There are several ExecuteSQL tasks to truncate the tables and several corresponding data flows to accomplish the loads.

If a load fails I want the corresponding truncate task to be part of the restart otherwise duplicate data may be loaded. Normally, SSIS will start at the failed task. I read something about containers that led me to think that if I put the truncate & matching load pair in a sequence container that the container would be the restart point, but either I read it wrong or it's not working that way.

Anybody know how to accomplish what I want to do?

Gordon,

That's an interesting question - I'd like to think that it is possible - but I don't know.

There is a workaround - its a bit messy but it will work. A ForEach Loop is the unit of restartability, rather than the containers inside it so if you set up a ForEach loop that only looped once with a matching Execute SQL Task and data-flow inside it - they would both execute on restart.

-Jamie

|||Another way is to have matching Execute SQL Task and data-flow in a child package...|||

Thanks Jamie,

I'll give that a try after I try something else first. I'm gonna try and get the data flow to completely rollback by putting it in a transaction using the Required setting. That way I won't have to rerun the truncate step. My first attemp raised a DTC error just like the ones you have documented in the past. These are corporate servers so I don't know if firewalls are at play between the servers in question. Anyway, I've read your comments on that and have forwarded them to our tech guys.

Gordy

|||

Did you ever solve this problem? I have exactly the same issue, need to re-run a truncate when a dependent task fails. I tried adding an error handler for OnTaskFailure for the dependent task that ran the truncate and that works - the first time the package is run. When it is restarted all is well, but a second restart fails because the task executed by the error handler is marked as successful executed!

Regarding Jamie's approach: I cannot figure out how to make the ForEachLoop container "loop just once". All the samples seem to be oriented around files & record sets.

|||

Mark Challen wrote:

Did you ever solve this problem? I have exactly the same issue, need to re-run a truncate when a dependent task fails. I tried adding an error handler for OnTaskFailure for the dependent task that ran the truncate and that works - the first time the package is run. When it is restarted all is well, but a second restart fails because the task executed by the error handler is marked as successful executed!

This is an old thread and upon re-reading it today I realised I had submitted something on Connect about it and got a relly good response from Craig Guyer:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126489

If you think this would be a good feature to have, click through, comment and vote.

Mark Challen wrote:

Regarding Jamie's approach: I cannot figure out how to make the ForEachLoop container "loop just once". All the samples seem to be oriented around files & record sets.

Use the 'ForEach Item' enumerator and just put one item in the collection.

-Jamie

|||

I added my vote.

Tried the ForEachLoop with one item and it works like a champ. Thanks, you saved my bacon!

|||

GordonMoll wrote:

I read something about containers that led me to think that if I put the truncate & matching load pair in a sequence container that the container would be the restart point, but either I read it wrong or it's not working that way.
Anybody know how to accomplish what I want to do?

The container approach does work but you'll have to set the options correctly.

Try this:

Put the truncate & matching load pair in the container|||Ravi, I would love for this to work, but cannot get the "container restart" behavior you're describing. Here's what I'm seeing instead.

Follow your instructions (sequence container FailPackageOnFailure = True and contained tasks FailParentOnFailure = true). After a failure in the load task, the package restart does indeed occur from the beginning of the sequence container, but none of the contained tasks run at all.

First, is this what you're seeing?
Second, must the sequence container (or package) have its TransactionOption set to 'Required' as well, for the container to be the restart point?|||

Jaegd,

Looks like you have your tasks checkpointed in the scenario you mentioned. Is that correct?

For this to work, your task should not be checkpointed.

i.e. on the tasks, set 'FailPackageOnFailure' to 'False'.

This approach does work for me regardless of whether the container is transacted or not.

In the meantime, I'll see where I can post an example package.

|||The contained tasks' only non-default setting was 'FailParentOnFailure' = True. If you could post an example package to a blog or a file sharing site, that would be great.

In the meantime, here's a blog entry which contains the code for C# console app that will generate a package (thanks to Ivolva). The package contains a single sequence container, with two contained tasks, the second of which will fail half the time, and configured with the sequence container set the FailPackageOnFailure=true, and the tasks only non-default setting to FailParentOnFailure=true.

http://jaegd.spaces.live.com/blog/cns!A9CD33B14B4B8353!117.entry

Here's the xml for the package as well, since we can't add attachments on this forum.

<?xml version="1.0"?><DTS:Executable xmlns:DTS="www.microsoft.com/SqlServer/Dts" DTS:ExecutableType="MSDTS.Package.1"><DTS:Property DTS:Name="PackageFormatVersion">2</DTS:Property><DTS:Property DTS:Name="VersionComments"></DTS:Property><DTS:Property DTS:Name="CreatorName">FPS\jaegd</DTS:Property><DTS:Property DTS:Name="CreatorComputerName">FPS</DTS:Property><DTS:Property DTS:Name="CreationDate" DTS:DataType="7">12/9/2006 12:53:33 PM</DTS:Property><DTS:Property DTS:Name="PackageType">5</DTS:Property><DTS:Property DTS:Name="ProtectionLevel">1</DTS:Property><DTS:Property DTS:Name="MaxConcurrentExecutables">-1</DTS:Property><DTS:Property DTS:Name="PackagePriorityClass">0</DTS:Property><DTS:Property DTS:Name="VersionMajor">1</DTS:Property><DTS:Property DTS:Name="VersionMinor">0</DTS:Property><DTS:Property DTS:Name="VersionBuild">8</DTS:Property><DTS:Property DTS:Name="VersionGUID">{BE15E67D-BFF8-4ED0-BA4E-FFAF36D3B304}</DTS:Property><DTS:Property DTS:Name="EnableConfig">0</DTS:Property><DTS:Property DTS:Name="CheckpointFileName">AtomicSequence.xml</DTS:Property><DTS:Property DTS:Name="SaveCheckpoints">-1</DTS:Property><DTS:Property DTS:Name="CheckpointUsage">1</DTS:Property><DTS:Property DTS:Name="SuppressConfigurationWarnings">0</DTS:Property><DTS:Property DTS:Name="ForceExecValue">0</DTS:Property><DTS:Property DTS:Name="ExecValue" DTS:DataType="3">0</DTS:Property><DTS:Property DTS:Name="ForceExecutionResult">-1</DTS:Property><DTS:Property DTS:Name="Disabled">0</DTS:Property><DTS:Property DTS:Name="FailPackageOnFailure">0</DTS:Property><DTS:Property DTS:Name="FailParentOnFailure">0</DTS:Property><DTS:Property DTS:Name="MaxErrorCount">1</DTS:Property><DTS:Property DTS:Name="ISOLevel">1048576</DTS:Property><DTS:Property DTS:Name="LocaleID">1033</DTS:Property><DTS:Property DTS:Name="TransactionOption">1</DTS:Property><DTS:Property DTS:Name="DelayValidation">0</DTS:Property>
<DTS:LoggingOptions><DTS:Property DTS:Name="LoggingMode">0</DTS:Property><DTS:Property DTS:Name="FilterKind">1</DTS:Property><DTS:Property DTS:Name="EventFilter" DTS:DataType="8"></DTS:Property></DTS:LoggingOptions>
<DTS:Executable DTS:ExecutableType="STOCK:SEQUENCE"><DTS:Property DTS:Name="ForceExecValue">0</DTS:Property><DTS:Property DTS:Name="ExecValue" DTS:DataType="3">0</DTS:Property><DTS:Property DTS:Name="ForceExecutionResult">-1</DTS:Property><DTS:Property DTS:Name="Disabled">0</DTS:Property><DTS:Property DTS:Name="FailPackageOnFailure">1</DTS:Property><DTS:Property DTS:Name="FailParentOnFailure">0</DTS:Property><DTS:Property DTS:Name="MaxErrorCount">1</DTS:Property><DTS:Property DTS:Name="ISOLevel">1048576</DTS:Property><DTS:Property DTS:Name="LocaleID">-1</DTS:Property><DTS:Property DTS:Name="TransactionOption">1</DTS:Property><DTS:Property DTS:Name="DelayValidation">0</DTS:Property>
<DTS:LoggingOptions><DTS:Property DTS:Name="LoggingMode">0</DTS:Property><DTS:Property DTS:Name="FilterKind">1</DTS:Property><DTS:Property DTS:Name="EventFilter" DTS:DataType="8"></DTS:Property></DTS:LoggingOptions>
<DTS:Executable DTS:ExecutableType="Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask, Microsoft.SqlServer.ScriptTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" DTS:ThreadHint="0"><DTS:Property DTS:Name="ExecutionLocation">0</DTS:Property><DTS:Property DTS:Name="ExecutionAddress"></DTS:Property><DTS:Property DTS:Name="TaskContact"></DTS:Property><DTS:Property DTS:Name="ForceExecValue">0</DTS:Property><DTS:Property DTS:Name="ExecValue" DTS:DataType="3">0</DTS:Property><DTS:Property DTS:Name="ForceExecutionResult">-1</DTS:Property><DTS:Property DTS:Name="Disabled">0</DTS:Property><DTS:Property DTS:Name="FailPackageOnFailure">0</DTS:Property><DTS:Property DTS:Name="FailParentOnFailure">1</DTS:Property><DTS:Property DTS:Name="MaxErrorCount">1</DTS:Property><DTS:Property DTS:Name="ISOLevel">1048576</DTS:Property><DTS:Property DTS:Name="LocaleID">-1</DTS:Property><DTS:Property DTS:Name="TransactionOption">1</DTS:Property><DTS:Property DTS:Name="DelayValidation">0</DTS:Property>
<DTS:LoggingOptions><DTS:Property DTS:Name="LoggingMode">0</DTS:Property><DTS:Property DTS:Name="FilterKind">1</DTS:Property><DTS:Property DTS:Name="EventFilter" DTS:DataType="8"></DTS:Property></DTS:LoggingOptions><DTS:Property DTS:Name="ObjectName">SCR Simulate Truncate</DTS:Property><DTS:Property DTS:Name="DTSID">{F4FE90C9-C8DE-4B40-8A6D-63EAE102B563}</DTS:Property><DTS:Property DTS:Name="Description">Script Task</DTS:Property><DTS:Property DTS:Name="CreationName">Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask, Microsoft.SqlServer.ScriptTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91</DTS:Property><DTS:Property DTS:Name="DisableEventHandlers">0</DTS:Property><DTS:ObjectData><ScriptProject Name="ScriptTask_8bd63ea4fc1f416f8d4f57f696fab4f0" Language="Microsoft Visual Basic .NET" EntryPoint="ScriptMain" SaveBinaries="False" ReadOnlyVariables="" ReadWriteVariables=""><ProjectItem Name="dts://Scripts/ScriptTask_8bd63ea4fc1f416f8d4f57f696fab4f0/ScriptMain.vsaitem">
<![CDATA[' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.

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

Public Class ScriptMain

' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.

Public Sub Main()
'
' Add your code here
'
Dts.TaskResult = Dts.Results.Success
End Sub

End Class]]></ProjectItem><ProjectItem Name="dts://Scripts/ScriptTask_8bd63ea4fc1f416f8d4f57f696fab4f0/ScriptTask_8bd63ea4fc1f416f8d4f57f696fab4f0.vsaproj">
<![CDATA[<VisualStudioProject>
<VisualBasic
Version = "8.0.50727.42"
MVID = "{00000000-0000-0000-0000-000000000000}"
ProjectType = "Local"
ProductVersion = "8.0.50727"
SchemaVersion = "2.0"
>
<Build>
<Settings
DefaultNamespace = "ScriptTask_8bd63ea4fc1f416f8d4f57f696fab4f0"
OptionCompare = "0"
OptionExplicit = "1"
OptionStrict = "1"
ProjectName = "ScriptTask_8bd63ea4fc1f416f8d4f57f696fab4f0"
ReferencePath = "C:\WINDOWS\assembly\GAC_MSIL\Microsoft.SqlServer.ManagedDTS\9.0.242.0__89845dcd8080cc91\;C:\WINDOWS\assembly\GAC_MSIL\Microsoft.SqlServer.ScriptTask\9.0.242.0__89845dcd8080cc91\"
TreatWarningsAsErrors = "false"
WarningLevel = "1"
RootNamespace = "ScriptTask_8bd63ea4fc1f416f8d4f57f696fab4f0"
>
<Config
Name = "Debug"
DefineConstants = ""
DefineDebug = "true"
DefineTrace = "true"
DebugSymbols = "true"
RemoveIntegerChecks = "false"
/>
</Settings>
<References>
<Reference
Name = "System"
AssemblyName = "System"
/>
<Reference
Name = "System.Data"
AssemblyName = "System.Data"
/>
<Reference
Name = "Microsoft.SqlServer.ScriptTask"
AssemblyName = "Microsoft.SqlServer.ScriptTask"
/>
<Reference
Name = "Microsoft.SqlServer.ManagedDTS"
AssemblyName = "Microsoft.SqlServer.ManagedDTS"
/>
</References>
<Imports>
<Import Namespace = "Microsoft.VisualBasic" />
</Imports>
</Build>
<Files>
<Include>
<File
RelPath = "ScriptMain"
BuildAction = "Compile"
ItemType = "2"
/>
<VSAAppGlobal
VSAAppGlobalName = "Dts"
ItemType = "1"
VSAAppGlobalType = "Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptObjectModel"
/>
</Include>
</Files>
<Folders>
<Include/>
</Folders>
</VisualBasic>
</VisualStudioProject>]]></ProjectItem></ScriptProject></DTS:ObjectData></DTS:Executable>
<DTS:Executable DTS:ExecutableType="Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask, Microsoft.SqlServer.ScriptTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" DTS:ThreadHint="0"><DTS:Property DTS:Name="ExecutionLocation">0</DTS:Property><DTS:Property DTS:Name="ExecutionAddress"></DTS:Property><DTS:Property DTS:Name="TaskContact"></DTS:Property><DTS:Property DTS:Name="ForceExecValue">0</DTS:Property><DTS:Property DTS:Name="ExecValue" DTS:DataType="3">0</DTS:Property><DTS:Property DTS:Name="ForceExecutionResult">-1</DTS:Property><DTS:Property DTS:Name="Disabled">0</DTS:Property><DTS:Property DTS:Name="FailPackageOnFailure">0</DTS:Property><DTS:Property DTS:Name="FailParentOnFailure">1</DTS:Property><DTS:Property DTS:Name="MaxErrorCount">1</DTS:Property><DTS:Property DTS:Name="ISOLevel">1048576</DTS:Property><DTS:Property DTS:Name="LocaleID">-1</DTS:Property><DTS:Property DTS:Name="TransactionOption">1</DTS:Property><DTS:Property DTS:Name="DelayValidation">0</DTS:Property>
<DTS:LoggingOptions><DTS:Property DTS:Name="LoggingMode">0</DTS:Property><DTS:Property DTS:Name="FilterKind">1</DTS:Property><DTS:Property DTS:Name="EventFilter" DTS:DataType="8"></DTS:Property></DTS:LoggingOptions><DTS:Property DTS:Name="ObjectName">SCR Simulate Load</DTS:Property><DTS:Property DTS:Name="DTSID">{F1D5BC15-E01D-4EC9-B273-F7629DD5E478}</DTS:Property><DTS:Property DTS:Name="Description">Script Task</DTS:Property><DTS:Property DTS:Name="CreationName">Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask, Microsoft.SqlServer.ScriptTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91</DTS:Property><DTS:Property DTS:Name="DisableEventHandlers">0</DTS:Property><DTS:ObjectData><ScriptProject Name="ScriptTask_c345ea39a2e9477b9a56e3f3ae6a400f" Language="Microsoft Visual Basic .NET" EntryPoint="ScriptMain" SaveBinaries="False" ReadOnlyVariables="" ReadWriteVariables=""><ProjectItem Name="dts://Scripts/ScriptTask_c345ea39a2e9477b9a56e3f3ae6a400f/ScriptTask_c345ea39a2e9477b9a56e3f3ae6a400f.vsaproj">
<![CDATA[<VisualStudioProject>
<VisualBasic
Version = "8.0.50727.42"
MVID = "{00000000-0000-0000-0000-000000000000}"
ProjectType = "Local"
ProductVersion = "8.0.50727"
SchemaVersion = "2.0"
>
<Build>
<Settings
DefaultNamespace = "ScriptTask_c345ea39a2e9477b9a56e3f3ae6a400f"
OptionCompare = "0"
OptionExplicit = "1"
OptionStrict = "1"
ProjectName = "ScriptTask_c345ea39a2e9477b9a56e3f3ae6a400f"
ReferencePath = "C:\WINDOWS\assembly\GAC_MSIL\Microsoft.SqlServer.ManagedDTS\9.0.242.0__89845dcd8080cc91\;C:\WINDOWS\assembly\GAC_MSIL\Microsoft.SqlServer.ScriptTask\9.0.242.0__89845dcd8080cc91\"
TreatWarningsAsErrors = "false"
WarningLevel = "1"
RootNamespace = "ScriptTask_c345ea39a2e9477b9a56e3f3ae6a400f"
>
<Config
Name = "Debug"
DefineConstants = ""
DefineDebug = "true"
DefineTrace = "true"
DebugSymbols = "true"
RemoveIntegerChecks = "false"
/>
</Settings>
<References>
<Reference
Name = "System"
AssemblyName = "System"
/>
<Reference
Name = "System.Data"
AssemblyName = "System.Data"
/>
<Reference
Name = "Microsoft.SqlServer.ScriptTask"
AssemblyName = "Microsoft.SqlServer.ScriptTask"
/>
<Reference
Name = "Microsoft.SqlServer.ManagedDTS"
AssemblyName = "Microsoft.SqlServer.ManagedDTS"
/>
</References>
<Imports>
<Import Namespace = "Microsoft.VisualBasic" />
</Imports>
</Build>
<Files>
<Include>
<File
RelPath = "ScriptMain"
BuildAction = "Compile"
ItemType = "2"
/>
<VSAAppGlobal
VSAAppGlobalName = "Dts"
ItemType = "1"
VSAAppGlobalType = "Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptObjectModel"
/>
</Include>
</Files>
<Folders>
<Include/>
</Folders>
</VisualBasic>
</VisualStudioProject>]]></ProjectItem><ProjectItem Name="dts://Scripts/ScriptTask_c345ea39a2e9477b9a56e3f3ae6a400f/ScriptMain.vsaitem">
<![CDATA[Imports System
Imports System.Data
Imports System.Math
Imports System.Security.Cryptography
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain
Private r As New RNGCryptoServiceProvider()
Private randomNumber(0) As Byte
Private randInt As Integer

Public Sub Main()
Dts.TaskResult = Dts.Results.Success
r.GetBytes(randomNumber)
randInt = Convert.ToInt32(randomNumber(0))
If randInt Mod 2 = 0 Then
Dts.TaskResult = Dts.Results.Failure
End If
End Sub

End Class]]></ProjectItem></ScriptProject></DTS:ObjectData></DTS:Executable>
<DTS:PrecedenceConstraint><DTS:Property DTS:Name="Value">0</DTS:Property><DTS:Property DTS:Name="EvalOp">2</DTS:Property><DTS:Property DTS:Name="LogicalAnd">-1</DTS:Property><DTS:Property DTS:Name="Expression"></DTS:Property><DTS:Executable IDREF="{F4FE90C9-C8DE-4B40-8A6D-63EAE102B563}" DTS:IsFrom="-1"/><DTS:Executable IDREF="{F1D5BC15-E01D-4EC9-B273-F7629DD5E478}" DTS:IsFrom="0"/><DTS:Property DTS:Name="ObjectName">Constraint</DTS:Property><DTS:Property DTS:Name="DTSID">{EF54EA09-00A5-40A0-B192-78EF3F7E731C}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PrecedenceConstraint><DTS:Property DTS:Name="ObjectName">SEQ Atomic</DTS:Property><DTS:Property DTS:Name="DTSID">{39E92202-B557-4E08-9155-6ABAC07535BE}</DTS:Property><DTS:Property DTS:Name="Description">Sequence Container</DTS:Property><DTS:Property DTS:Name="CreationName">STOCK:SEQUENCE</DTS:Property><DTS:Property DTS:Name="DisableEventHandlers">0</DTS:Property></DTS:Executable><DTS:Property DTS:Name="ObjectName">Package3</DTS:Property><DTS:Property DTS:Name="DTSID">{22A10402-108C-4F32-89B3-EBD3C48990D5}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName">MSDTS.Package.1</DTS:Property><DTS:Property DTS:Name="DisableEventHandlers">0</DTS:Property></DTS:Executable>

Checkpoint restart

Anybody know how to accomplish what I want to do?

Gordon,

That's an interesting question - I'd like to think that it is possible - but I don't know.

-Jamie

|||Another way is to have matching Execute SQL Task and data-flow in a child package...|||

Thanks Jamie,

Gordy

|||

Regarding Jamie's approach: I cannot figure out how to make the ForEachLoop container "loop just once". All the samples seem to be oriented around files & record sets.

|||

Mark Challen wrote:

Did you ever solve this problem? I have exactly the same issue, need to re-run a truncate when a dependent task fails. I tried adding an error handler for OnTaskFailure for the dependent task that ran the truncate and that works - the first time the package is run. When it is restarted all is well, but a second restart fails because the task executed by the error handler is marked as successful executed!

This is an old thread and upon re-reading it today I realised I had submitted something on Connect about it and got a relly good response from Craig Guyer:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126489

If you think this would be a good feature to have, click through, comment and vote.

Mark Challen wrote:

Regarding Jamie's approach: I cannot figure out how to make the ForEachLoop container "loop just once". All the samples seem to be oriented around files & record sets.

Use the 'ForEach Item' enumerator and just put one item in the collection.

-Jamie

|||

I added my vote.

Tried the ForEachLoop with one item and it works like a champ. Thanks, you saved my bacon!

|||

GordonMoll wrote:

I read something about containers that led me to think that if I put the truncate & matching load pair in a sequence container that the container would be the restart point, but either I read it wrong or it's not working that way.
Anybody know how to accomplish what I want to do?

The container approach does work but you'll have to set the options correctly.

Try this:

Put the truncate & matching load pair in the container|||Ravi, I would love for this to work, but cannot get the "container restart" behavior you're describing. Here's what I'm seeing instead.

First, is this what you're seeing?
Second, must the sequence container (or package) have its TransactionOption set to 'Required' as well, for the container to be the restart point?|||

Jaegd,

Looks like you have your tasks checkpointed in the scenario you mentioned. Is that correct?

For this to work, your task should not be checkpointed.

i.e. on the tasks, set 'FailPackageOnFailure' to 'False'.

This approach does work for me regardless of whether the container is transacted or not.

In the meantime, I'll see where I can post an example package.

|||The contained tasks' only non-default setting was 'FailParentOnFailure' = True. If you could post an example package to a blog or a file sharing site, that would be great.

http://jaegd.spaces.live.com/blog/cns!A9CD33B14B4B8353!117.entry

Here's the xml for the package as well, since we can't add attachments on this forum.

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

Public Class ScriptMain

Public Sub Main()
'
' Add your code here
'
Dts.TaskResult = Dts.Results.Success
End Sub

Public Class ScriptMain
Private r As New RNGCryptoServiceProvider()
Private randomNumber(0) As Byte
Private randInt As Integer

Checkpoint restart

Anybody know how to accomplish what I want to do?

Gordon,

That's an interesting question - I'd like to think that it is possible - but I don't know.

-Jamie

|||Another way is to have matching Execute SQL Task and data-flow in a child package...|||

Thanks Jamie,

Gordy

|||

Regarding Jamie's approach: I cannot figure out how to make the ForEachLoop container "loop just once". All the samples seem to be oriented around files & record sets.

|||

Mark Challen wrote:

Did you ever solve this problem? I have exactly the same issue, need to re-run a truncate when a dependent task fails. I tried adding an error handler for OnTaskFailure for the dependent task that ran the truncate and that works - the first time the package is run. When it is restarted all is well, but a second restart fails because the task executed by the error handler is marked as successful executed!

This is an old thread and upon re-reading it today I realised I had submitted something on Connect about it and got a relly good response from Craig Guyer:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126489

If you think this would be a good feature to have, click through, comment and vote.

Mark Challen wrote:

Regarding Jamie's approach: I cannot figure out how to make the ForEachLoop container "loop just once". All the samples seem to be oriented around files & record sets.

Use the 'ForEach Item' enumerator and just put one item in the collection.

-Jamie

|||

I added my vote.

Tried the ForEachLoop with one item and it works like a champ. Thanks, you saved my bacon!

|||

GordonMoll wrote:

I read something about containers that led me to think that if I put the truncate & matching load pair in a sequence container that the container would be the restart point, but either I read it wrong or it's not working that way.
Anybody know how to accomplish what I want to do?

The container approach does work but you'll have to set the options correctly.

Try this:

Put the truncate & matching load pair in the container|||Ravi, I would love for this to work, but cannot get the "container restart" behavior you're describing. Here's what I'm seeing instead.

First, is this what you're seeing?
Second, must the sequence container (or package) have its TransactionOption set to 'Required' as well, for the container to be the restart point?|||

Jaegd,

Looks like you have your tasks checkpointed in the scenario you mentioned. Is that correct?

For this to work, your task should not be checkpointed.

i.e. on the tasks, set 'FailPackageOnFailure' to 'False'.

This approach does work for me regardless of whether the container is transacted or not.

In the meantime, I'll see where I can post an example package.

|||The contained tasks' only non-default setting was 'FailParentOnFailure' = True. If you could post an example package to a blog or a file sharing site, that would be great.

http://jaegd.spaces.live.com/blog/cns!A9CD33B14B4B8353!117.entry

Here's the xml for the package as well, since we can't add attachments on this forum.

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

Public Class ScriptMain

Public Sub Main()
'
' Add your code here
'
Dts.TaskResult = Dts.Results.Success
End Sub

Public Class ScriptMain
Private r As New RNGCryptoServiceProvider()
Private randomNumber(0) As Byte
Private randInt As Integer

Thursday, March 22, 2012

Class not registered error

Sunday, March 11, 2012

Choose Provider (SQL or Oracle) at Deployment Time

Thursday, March 8, 2012

Child packages : Failed to acquire connection

Child packages : Failed to acquire connection

Child Package Fails when called from parent

Child package ConnectionManager visibility

Child Package cannot always be found

Wednesday, March 7, 2012

Checkpoints problem in parallel tasks

CheckPoints and On Completion

Checkpoint restart

Checkpoint restart

Checkpoint restart

check sql licence

Blog Archive

About Me