Pre-execute Hangs at 50%

Hello,

I did a search and found no answers. I have a simple project; one data flow task reading from one DB2 tabel and writing to another. My OLEDB source is a sql command that returns 97,000 rows and my OLEDB destination's data access mode is "table or view".

For my OLEDB source SQL command, I run it through the "build query" panel and I get all 97,000 rows back within 90 seconds everytime.

I don't know what is so different since I currently do SQL commands that bring back 2.5 million rows from DB2 with no effort in SSIS. I even took my data flow task and placed into a known good solution - but still hangs at 50% forever. I tried attaching to SSIS server and executing through there, again no luck. Still hung!

Any ideas? Thanks.

On your OLE DB Source component in the data flow, set its property, "ValidateExternalMetadata" to false and see if that helps.

One other thing to try is to set the property of the DB2 connection manager object, "DelayValidation" to true.|||

Thanks for your reply Phil.

I went ahead to set both properties accordingly and reran. After 16 minutes of running it was still on the pre-execute phase stuck at 50%.

Just to test I changed my DB2 SQL to FETCH ROW FIRST ONLY and it still hangs at the pre-execute 50% level. So obviously it's not the number of rows causing the problem.

What I don't understand is using the "preview" panel in the OLEDB source, I get the first row in about 6-7 seconds.

Thanks.

|||Do you have any other connection managers defined in the package? Validation will attempt to validate all connections.

What DB2 driver are you using?|||

No, that is the only connection I have in my package/solution.

I am using "Native OLE DB\IBM OLE DB Provider for DB2".

|||

Is there any way to see a log of where it's getting hung? Maybe its my OLEDB Desitnation...since everything is perfect with my source.

Any ideas on how to verify my idea? Thanks.

|||

I went ahead and changed my destination to a temperary SQL 2005 table and now it works fine...so It is my destination. It must be failing on something (connection/rights?) and not telling me why?|||

It seems MS really dropped the ball on this one.

I'm almost certain that it's failing on a unique_index insert error. Why is it hiding the error message and letting SSIS hang FOREVER? Why does it do the SELECT and INSERT before the "real" execution of my package? This really defeats the purpose!!!

oh well - thanks for your help.

|||

Zach84 wrote:

It seems MS really dropped the ball on this one.

I'm almost certain that it's failing on a unique_index insert error. Why is it hiding the error message and letting SSIS hang FOREVER? Why does it do the SELECT and INSERT before the "real" execution of my package? This really defeats the purpose!!!

oh well - thanks for your help.

Be careful... You aren't using a Microsoft driver. It isn't doing any selects or inserts -- it's just trying to validate metadata and such.

You could always try the Microsoft OLE DB for DB2 driver, which I've had better luck with.

PrecompileScriptIntoBinaryCode errors

Hi,

I have several script tasks. Every now and then I get an error that the task can't precompile the script into binary code, or something to that effect. It's very intermittent, so it doesn't really seem to have any rhyme or reason.

For example, I have a package that has been running perfectly for weeks now, but just TODAY it decided to give me a precompile error. So I changed the "PrecompileScriptIntoBinaryCode" setting to "false", and that solved the problem.

I don't get why this is happening. It seems like a bug. It makes me wonder if I should set all scripts to "false."

Thanks

sadie519590 wrote:

Hi,

I have several script tasks. Every now and then I get an error that the task can't precompile the script into binary code,

Does it give any more info? Like WHY it can't compile it?

-Jamie

|||Not that I remember. I can't reproduce this error at will. I just know that it happens with the script task once in a while, for reasons that aren't clear or logical, and seemingly random. I do think this is a bug.|||

Here is the error:

Error 1 Validation error. myTask : The task is configured to pre-compile the script, but binary code is not found. Please visit the IDE in Script Task Editor by clicking Design Script button to cause binary code to be generated. CashRec - BMO.dtsx 0 0

|||So do that and go in and hit the space bar somewhere in the script. Then close out of it and try again.|||

Tried that. But didn't work.

Why would that fix it? Seems strange to me....

I had this error happen AGAIN. This is what caused it: I went into the script, highlighted some code, then copied it to the clipboard for use in another place. Then I exited the script using the cancel button. NOTHING CHANGED.

Next thing I know, the script task is giving me the recompile error. This does seem buggy, doesn't it?

|||

Ugh - this keeps happening! All I do is go into the script, highlight some rows, then copy the data to the clipboard. Nothing changes in the script itself.

Then, once I'm out of the script, it displays the error. Changing the precompile to "false" gets rid of the problem.

But I don't think this problem should happen to begin with... it seems really flaky.

I had a package hang a job up for over 24 hours (it just ran and ran and never completed) due to a script error of this nature. This is not good because it doesn't even send an error message.

|||Do you have SP2 installed?|||

No.

If this was my server, it would have.

|||

sadie519590 wrote:

No.

If this was my server, it would have.

Then this likely applies to you:

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

PrecompileScriptIntoBinaryCode errors

Hi,

I have several script tasks. Every now and then I get an error that the task can't precompile the script into binary code, or something to that effect. It's very intermittent, so it doesn't really seem to have any rhyme or reason.

For example, I have a package that has been running perfectly for weeks now, but just TODAY it decided to give me a precompile error. So I changed the "PrecompileScriptIntoBinaryCode" setting to "false", and that solved the problem.

I don't get why this is happening. It seems like a bug. It makes me wonder if I should set all scripts to "false."

Thanks

sadie519590 wrote:

Hi,

I have several script tasks. Every now and then I get an error that the task can't precompile the script into binary code,

Does it give any more info? Like WHY it can't compile it?

-Jamie

|||Not that I remember. I can't reproduce this error at will. I just know that it happens with the script task once in a while, for reasons that aren't clear or logical, and seemingly random. I do think this is a bug.|||

Here is the error:

Error 1 Validation error. myTask : The task is configured to pre-compile the script, but binary code is not found. Please visit the IDE in Script Task Editor by clicking Design Script button to cause binary code to be generated. CashRec - BMO.dtsx 0 0

|||So do that and go in and hit the space bar somewhere in the script. Then close out of it and try again.|||

Tried that. But didn't work.

Why would that fix it? Seems strange to me....

I had this error happen AGAIN. This is what caused it: I went into the script, highlighted some code, then copied it to the clipboard for use in another place. Then I exited the script using the cancel button. NOTHING CHANGED.

Next thing I know, the script task is giving me the recompile error. This does seem buggy, doesn't it?

|||

Ugh - this keeps happening! All I do is go into the script, highlight some rows, then copy the data to the clipboard. Nothing changes in the script itself.

Then, once I'm out of the script, it displays the error. Changing the precompile to "false" gets rid of the problem.

But I don't think this problem should happen to begin with... it seems really flaky.

I had a package hang a job up for over 24 hours (it just ran and ran and never completed) due to a script error of this nature. This is not good because it doesn't even send an error message.

|||Do you have SP2 installed?|||

No.

If this was my server, it would have.

|||

sadie519590 wrote:

No.

If this was my server, it would have.

Then this likely applies to you:

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

Precompile Script task programatically

I have code which generates packages programatically, and script task is a part of the control flow. I've succeded to set source code programatically, but I do not know how to put binary code, because I need to have my script task precompiled.

Just setting PreCompile = true does not solve this problem

Thanks in advance.

Borko

Unfortunately, due to interactions with VSA (the script editing environment) this is not possible in this version.

Precedence puzzle

Hello folks,

I have probably a dumb newbie question but I can't find the answer anywhere.

On my Control Flow design pane I have two objects: a SQL task object and a Data flow task object. The first 'points' to the second. From my digging I believed that by indicating with the arrow from 1 to 2 that 1 would execute to finish before 2 was started.

My SQL taks is to truncate a table to receive the new data coming from the data flow task object. Instead 2 executes first and then 1. You can imagine producing an empty table was not my goal for this package.

Can anybody give me a clue?

Thanks....

Jim

That's very strange. If there is a precedence constraint pointing FROM the Exec SQL Task TO the Data-flow Task then the Exec SQL Task should execute first.

Are you sure that is how it is setup?

-Jamie

|||

Yes, that's the setup. Do you have to do anything other than drag the arrow from the Execute SQL Task to the Data Flow Task in order for precedence to be followed?

Jim

|||Are you sure they are executed in this order? How do you determine this?

I would try setting pre-execute breakpoint in designer (select the task, click F9, repeat for second task) and see what is the execution order, what happens to the tables after each step, etc.|||

Two clues tell me the steps are running out of order. First watching the process pane as it executes always shows the SQL task executing second and the end result of the package run is an empty data set.

I will try the break point to see what happens. Thanks for the tip.

|||Unfortunately, progress pane can be confusing. It groups the events from tasks under one node, and the order is determined by the order of the first events from the task. So if data flow task is validated first, it will appear above the SQL task (the validation order is not affected by precedence constraints).|||Just curious. Does your source table have any data?
Make sure its not empty.|||

That sounds like what I was seeing and just got paranoid. I set break pionts as suggested in an earlier post and confirmed the order of execution.

Thanks to all for your help!

Precedence Constraint Expressions

Hi all

I've got a package that checks the length of chars in a flatfile and if it's correct it executes the dataflow task.

How can i write an expression in the precedense constraint editor to skip the dataflow task if there is an error in the script task?

Thank you

Set a variable in the script task and then test the variable in the precedence constraint.|||

Something similar to:

Code Snippet

If [File is Valid condition] Then

Dts.Variables.Item("User::ValidFile").Value = 1

Else

Dts.Variables.Item("User::ValidFile").Value = 0

End If

in the script task (mark the variable as ReadWrite in the script task properties) and

Code Snippet

@.ValidFile==1

in the precedence constraint. You can set the expression by double-clicking the precendence constraint and choosing Expression and Constraint in the Evaluation operation property.

|||

Thanks for all your help Phil and JWelch.

Pre execute Failure on Look up task

Hi,

I have an OLE DB Source. The output of OLEDB source is connected to a look up task. The output of the lookup task goes into OLE DB Destination. When executed..

I am getting this error:

DTS.Pipeline: component "Lookup" (2834) failed the pre-execute phase and returned error code 0x8007000E.

Any idea why pre-execute phase failure occurs?

TIN,
Anand

0x8007000E is out of memory error. How big is the reference table? Is Lookup running in Full Cache mode? Can you try running it in Partial Cache mode?|||Hi,

You are probably right. I have a data flow task before this specific task which contains 15 Look Up Tasks. I am presuming that memory is not released when needed.

The reference table is very small. It contains around 220 records.

Also, I have 2 GB memory available on this machine.

-Anandsql

Pre and PostExecute Event handler called more than once

I have a task configured on the post execute event handler of a package expecting this task to be executed only once after the completion of all the other tasks in the package. But I found the task configured on the post execute event of the package getting executed as many times as the number of tasks in the package + 1 . Is there any workaround for this problem?

Yes,

The reason this happens is that events "bubble-up" from a container to all parent containers and are caught by any eventhandlers that exist on those parent containers.

To stop this behaviour you'll need to create an eventhandler scoped to each task's OnPreExecute & OnPostExecute eventhandlers and set [@.System::Propogate]=FALSE. This will stop the event "bubbling-up".

-Jamie

Pre and Post Exec Event Handler For a Package

I have a simple test package to play with Event Handler. I got 2 Exec SQL Task and one File System Task. I have set the OnPreExecute and OnPostExecute at package level along with OnPreExecute on SQL and File System Task. These event handlers were loading the package, task name along with start time, machine and user in my audit table (using Exec SQL Task).

OnPreExecute did work fine for SQL and File System Task. They only had one record in the Audit table.

OnPreExecute and OnPostExecute for the package itseld did NOT work. When I say it doesn't work, it logs it to my audit table so many times. I was expecting one record in my audit table for OnPreExecute and one record for OnPostExecute at Package Level. But I was worng. It has inserted into my audit table 14 times instead of twice.

Any ideas? Or am I doing something wrong?

I know I can fix this by workaround. I can remove OnPreExecute and OnPostExecute Event Handler at Package level and insert Exec SQL Task at the beginning and end of the package. This will fix it, but I was wondering why it doesnt work?

Thanks
SuthaI don't quite know why you're getting it 14 times but this will be something to do with the same event getting caught in multiple places.
Every time the File System Task (for example) executes it will fire the eventhandler scoped to the task AND the eventhandler scoped to the package. So your event actually gets handled twice.

-Jamie|||Yeah i concur from our observations that this seems to be what is happening but surely this is a bit of a design issue ....

I want to know from the post execute that the package is starting (or has started), i don't want the startup events from each component thrown up to the post execute event on the whole package because they don't have any relevance at this level.

What do you think ?

Mark Hill|||Jamie

I tried it on a brand new package as well. I can re-produce it. As Mark states that it looks like an design issue. Anyone from MS can shed any light on this?

Thanks
Sutha|||

You can easily debug it like that:
- place a script in package's OnPreExecute event handler
- set a breakpoint on the task (F9)
- run in debugger
- when debugger brakes on the breakpoint add SourceName variable to the Watch window.

Now, every time you hit the breakpoint you will see the name of the task/container that raised the event.

I believe that all OnPreExecute events in your package get propagated to the package level. That's why you see so many of them. If it is not what you expect - you can change it setting Propagate system variable on event handlers to False

PPV's, Loops & Child Packages - Bug or By Design

It would appear that if a Child package is called more than once from a Parent using the 'Execute Package' task, then after the first execute the Parent Package Variables are not applied to child package. I.E we build dimensions in a master database and these are then loaded to a number of topic specific datamarts. We simply pass Parent variables to the child that hold source & target connection strings, the first time the package is called the correct database is accessed, subsequent Executes ignore the variables and use the original values. Manipulating the (our) event queue to run the package once results in the correct behaviour

Are packages cached when they are called from a Parent? if so is there a flag that I have missed to force a reload each time a child is executed?

This has just become a big problen for us so any guidance would greatly appreciated.

Paul

I have not come across this problem before. I suggest opening a support issue for this - it will likely need some debugging.

How are you calling the child package multiple times? In a loop?

Donald

|||

Our master package is data driven, from a database.

Dimensions are build / modified in a Master database and then are moved into a number of topic specific datamarts. A dimension like CALENDAR natutally resides in most datamarts, we have a series of packages (one per Dimension) that are reponsible for moving new/changed data to the datamart (they are lightening quick). The child packages take 2 Parent variables SourceConnectionString and TargetConnectionString, these child packages are called from the parent package using the 'Execute Package' task.

This approach has worked well untill we got to serious testing of the system. When the Parent package runs properly it is quite likely that the calendar will move to several places in one execution. The first call to the Child package uses the parent variables correctly, subsequent calls to the child (event if 10 or 15 other packages have been called in the meantime) ignore the new settings of the variables and continue to adress the first datamart setting, consequently we have missing rows, the package does not fail, so the dependancy is met (we have dependacy info in our metadata) and the the Fact build crashes due to missing rows.

This serious stuff for us, we aim to go live at the end of this month

I too may have to hand over the armband....

Paul

|||

I do think you'll need to open a support call for the original issue.

However, I wonder if you could work around this. One way may be to use a different configuration type, such as SQL Server, or registry setting or environment variable.

Your master package could use a script to write out the value to that location and child packages would pick it up from there.

This would be very similar to using the parameter files that other ETL tools use for these scenarios.

Donald

If you do have to hand over the armband, try not to cry about it on TV.

|||

Sorry, my fault.

The orginal version of the package was using Event Handlers, I removed these some time ago, but missed one which was setting an action as complete. So no bug in SSIS just my carlessness.

Just a thought, it would be really nice if icons on the design surface had some indication that and Event Handler was lurking underneath.

Paul

|||

Fantastic idea. You should log it at Microsoft Connect.

It would also be great if there were a visual clue that properties had expressions on them!

-Jamie

Possible bug with SSIS File System Task

I'm having an issue with a file system task & I'm not sure whether it is user error on my part or a bug. I'm using a SQL Task to create a transaction log backup & I'm saving the name of the file in a result set which I then am mapping to a package level user variable. After that runs I'm trying to copy this .BAK file to another folder using the file system task. I'm setting the following properties on the file system task.

Isdestinationpathvariable: False

Then I have entered the static directory for the file move.

Operation: Tried it with both copy file & move file.

Issourcepathvariable: True

Sourceconnection: User::File_name

After setting this I immediately get a validation warning telling me the source directory cannot be empty. If I try to run it, it fails. The weird thing is that if I set up a connection manager to a flat file & pass my user variable in as the connection string to this connection. Then set the Issourcepathvariable to false & the Sourceconnection to this connection manager it works.

I also have gotten it to work by substituting an ftp task in place of the file system task. The ftp task has no problem when I set the Islocalpathvariable to true & then pass my variable to Localvariable property. This is why I believe there is some sort of issue with the file system task. Has anyone seen this before? Is there some sort of problem with the way I'm setting it up?

Thank you

You might be missing the path of your source connection. Make sure you are including the path of the source file as a part of the File_name variable.

Rafael Salas

|||I tried it with both a mapped drive letter in the path & a unc path. No luck with either. Also the path I put into the variable works for the ftp task. Thank you though.