Excel file loading in SSIS

Hi All,

We did the development of SSIS packges on 32 bit machine. We have few excel files which is loaded using SSIS.

Now the same was deployed to anothe rmachine(64 bit). This 64-bit machine does not have Microsoft office installed.

And all packages(which loads excel files) failed. Hence can someone answer my following questions:-

1) In order to load excel files using SSIS, is it necessary that Microsoft excel software should be installed on that machine?

2) If answer to above is yes, Can Microsoft excel viewer be used instead of Microsoft office(excel)?

Thanks

Sid

Hi,

I am not an expert in SSIS but i have tried importing excel source data to sql using ssis package in a machine that does not have MS Office installed, but it worked for me.

Thanks,

OM$.

|||

Thanks for reply: Here is my problem:

I run those SSIS packages (which loads excel file) by double clicking it or running it through SSIS editor. It works great.

However if I run it using the command line dtexec.exe then following error arises: -

Error: 2007-08-03 16:53:27.42
Code: 0xC0202009
Source: PkgExtract Connection manager "SRC_Connection"
Description: An OLE DB error has occurred. Error code: 0x80040154.
An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered".
End Error
Error: 2007-08-03 16:53:27.42
Code: 0xC020801C
Source: Data Flow Task - Extract Data Excel Source [1860]
Description: The AcquireConnection method call to the connection manager "SRC_Connection" failed with error code 0xC0202009.
End Error
Error: 2007-08-03 16:53:27.43
Code: 0xC0047017
Source: Data Flow Task - Extract Data DTS.Pipeline
Description: component "Excel Source" (1860) failed validation and returned error code 0xC020801C.
End Error

|||

See your other post on this.

There is no 64-bit driver for Excel. You need to run the 32-bit version of DTEXEC (located in C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn).

|||

Thanks a ton. That was the solution and works perfectly fine

Excel extraction issue

I have a spreadsheet that's designed to look like a form. Does anyone know how I would create a SSIS package to extract specific cell values?

TIA

Have you tried using the Excel Source Adapter (http://msdn2.microsoft.com/en-us/library/ms181175.aspx)

Thanks

Anjan.

Excel Destination Number Conversion Error

In my SSIS project I am populating an Excel spreadsheet with several worksheets. When I define a worksheet in Excel Destination Editor, I use Create Table SQL statement to create a worksheet. Most of the fields are defined as numbers, however when job runs my numeric fields appear as text in Excel with yellow warning sign - 'The number in this cell is formatted as text or preceeded by an apostrophe'. I need my numbers to appear as numbers. Is there a workaround for this?

Are you specifying Import Mode by using IMEX=1 in your connection string? This forces all data to text.

The Excel driver does store what it believes to be text data preceded by the apostrophe, to force the Excel application in turn to recognize it without question as text values. This implies to me that the columns in question contain at least some values that the driver is interpreting as text.

The BOL topic on the Excel Source contains some information that explains some of the behaviors of the Jet Excel driver.

-Doug

|||

Doug,

Thanks a lot for advice. I tried to put IMEX=0 (2) into a connection string, but it did not solve the problem. I wonder what the default is if IMEX parameter is not specified?

Sergey

|||

Sergey,

I was not encouraging the use of Import Mode...on the contrary, this forces everything to text and would presumably add the opening apostrophe everywhere.

You may want to try allowing the Import and Export Wizard to build your "base" package and add necessary Data Conversion tasks etc. for you. Then you can customize the package afterward.

Be careful to check that the driver is recognizing your numeric columns as numeric, by examining the data types of columns in the various components.

-Doug

Excel Destination appends the excel file everytime a package is executed

i have an SSIS package that exports to an excel file. This works fine. the problem is that it appends the data instead of overwriting the file. Is there any way to overwrite the file like you can with a flat file? I have to email the file everyweek and don't want to have to clear it out manually. Any help would be appreciated

Perhaps you can use an Execute SQL statement with a delete statement before the data flow task. That way you delete existing rows before loading the new one.

Update: I just tried that and did not work:

[Execute SQL Task] Error: Executing the query "delete FROM [Sheet1$]" failed with the following error: "Deleting data in a linked table is not supported by this ISAM.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

So, another option may be to have an empty copy of the file that you copy/rename evry time the package run. You can use File system task in control flow for that.

|||

Pamela,

You could also use a Execute SQL task to create a new excel file on every execution of the package. I have posted an step by step guide on that:

http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-_116683388696570741.html

I hope you find it helpful

Excel Destination and Email Task Issue

Hi ,

I have a SSIS package in which at the end i have a excel destination task which imports a table data in excel files and then send it as an attachment using email task but i am facing a issue which is mentioned below -

1. The SSIS package runs as a SQL server agent job once in a day now every time the job runs i need to creat a new excel ( or delete the older one create again ) import data in that excel using Excel destination and then send it as an attachment using email task , How can i dynamically change the excel destination to point to a template file or new file

2. WIth the email task i am able to send only one attachment , i tried seperating the file names using semi colon but it is giving error "You dont have permission to access the file or file does not exists"

Please help....

Ashish

1. Create a template excel file and use it in file system task to copy into another file (new) which is used for attachment. After this, import the data into the excel file. In the send email task attach this file. Make sure the template file and the new file always exist in the required folder.

2. To send multiple attachments, you should use pipe characater | to delimit multiple files.

Ramani

Excel 2007 and SqlServer sp2

hellow all,

i installed SqlServer 2005 sp2 on my server.

now, how do i import excel 2007 files in SSIS? i don't have office 2007 installed on my computer, but i've got some .xslx that i would like to try ro import. i tried to create an OLEDB connection, but i don't know which provider to choose.

anyway, can i do it without the sp2 or office 2007 installed on my server?

thanks for tour reply!

Data Sources/Microsoft Office (2007) - ssis
(http://ssis.wik.is/Data_Sources/Microsoft_Office_(2007))|||

thanks, the link is very helpfull.

i downloaded the example package but when i try to see the connection manager's configuration- i'm getting this massage:

"The specified provider is not supported. Please choose different provider in connection manager."

it is probably because i don't have office 2007 installed on my computer.

is there a way to get only the ACE provider without installing office 2007?

|||BTW, why is my thread icon grey? (while everybody else's is green or red?)|||That is a way to tell you which threads you have already visited (open)

Example code for consuming a SSIS datareader from ASP.net

Hi,
Does anyone have any or know of any demo code that shows how to consume data made available by a SSIS DataReader destination in an ASP.net app.

Thanks in advance.

-Jamie

P.S. Doug if you're reading this, it'd be handy to have some examples such as this in BOL or maybe even something provided with the samples.Re: How do I consume a DataReader Destination in VB.Net? - Microsoft Technical Forums
(http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=64798)

Doug mentions a topic, although I cannot check it right now.|||Cheers Darren.

The link to the article with the code in it is
http://msdn2.microsoft.com/en-us/library/ms215053

-Jamie|||I've struggled with the best description to provide of the issue where you must call DataReader.Read() one last time AFTER the final row of data, to avoid an exception.

Here is the current draft of the text, expanded from the version displayed in the topic that Jamie kind provided the link for:

"The Read method of this implementation of the DataReader returns true one more time after the last row of data has been read. This fact makes it difficult to use the usual code that loops through the DataReader while Read returns true. If your code attempts to close the DataReader or the connection after reading the expected number of rows, without an additional, final call to the Read method, the code will raise an unhandled exception. However if your code attempts to read data on this final iteration through a loop, when Read still returns true but the last row has been passed, the code will raise an unhandled ApplicationException with the message, "The SSIS IDataReader is past the end of the resultset." This behavior is different from that of other DataReader implementations. When using a loop to read through the rows in the DataReader while Read returns true, you need to write code to catch, test, and discard this anticipated ApplicationException on the last successful call to the Read method. Or, if you know in advance the number of rows expected, you can process the rows, then call the Read method one more time before closing the DataReader and the connection."|||Hmm... so much for the MSDN search integrated into BOL. I only have June BOL on hand, and did the online search pick that up, did it hell. Thanks.

Events keep bubbling up, even with Propagate=False

I've been pulling my hair out for the last couple of weeks trying to make my SSIS package more robust. I'm running SQL 05 SP1 on Windows XP SP2. Right now when there's a failure, the error propagates all the way up and my package stops running. Clearly not very robust. I have a heartbeat portion that makes a connection to the database every couple of minutes. Sometimes it loses the connection in the middle of the night, probably due to some maintenance going on. I have created an event handler to log the error, sleep a couple of minutes and set Propagate=False. This works great. When the connection is down, the error is logged, once the connection comes back, the package just keeps going.
I also have an FTP component that receives a file and then needs to send back a handshake file. I tried the same method here, but the event keeps propagating up. I created an event handler for the Send FTP task itself, so that in case the remote FTP server isn't responding, it will error out. The Event Handler code is called and I verified that the value in Propagate is indeed False. And yet the event keeps propagating up and kills the whole package. FailPackageonFailure is set to False, FaileParentonFailure is set to False also.
Any idea what I'm doing wrong? Or is this a bug? Is there a work around?
Any help would be greatly appreciated.

On which eventhandler are you setting System::Propogate=FALSE? The one scoped to the FTP task?

Which container is the other eventhandler scoped to?

If you want your package to continue in the evnt of failure then this is configured within the propreties of the package. it is nothing to do with the configuration of your eventhandlers. I think you will find SSIS to be very robust in this regard when you get it working as you require.

-Jamie

|||

Jamie,

Thanks for the reply. The package has a Main task with a For Each Loop that goes through all files in a directory. It then calls an execute package called ETL-Archive. Within ETL-Archive, it calls execute package ETL. ETL is where the real meat and potatoes is at. This is where the FTP task is. The event handler OnError is scoped to the failing FTP task. This is where I set Propagate=False. However, the event keeps bubbling up to ETL, then to ETL-Archive and then to Main, resulting in complete package failure. Even when I have an OnError event handler at the ETL scope where I again set Propagate = False. I'm not sure I understood your comment about the fact that this is configured within the properties of the package? In this case do you mean ETL or Main? And what should be configured?

Thanks again!

|||

Also, the FTP task throws two errors because I deliberately configured it wrongly:

Error: 0xC0029190 at FTP Handshake back, FTP Task: Variable "FTPRemotePathGC" doesn't start with "/".

Error: 0xC0024107 at FTP Handshake back: There were errors during task validation.

Warning: 0x80019002 at ETL: The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure.

This in turn will fail ETL, which will fail ETL-Archive, which will fail Main...

|||

Hi Jamie,

I just spent 2.5 hours on the phone with Microsoft support, burning up one of my 4 free support tickets that come with MSDN. They confirmed that it is actually a bug. (So I didn't loose one of my 4 support tickets). Apparently some task containers keep propagating Error events up, even though the Propagate system var is set to False. So... the good thing is I know I'm not loosing my mind. The bad thing is that there is no workaround and no fix planned until Katmay. Apparently this bug was fixed in SQL 2005 Beta 3 and reintroduced later. I just installed Build 2153 (http://support.microsoft.com/?id=918222), and the bug is still there.

I'm not sure where to proceed from here. I have an FTP task that I cannot protect with an event handler, so every time my package has to send a little handshake back and the FTP task fails, my whole package bombs. Again... no clue where to proceed from here. How can I ever release such an non-robust package into production?

|||

Do we know if this has actually been submitted as a bug, registered with MS.

I ask because I am seeing the same issue with OnPostExecution EH not just the OnError EH you have described.

I checked on the MS Connect website and searched under the feedback but found no evidence of it ?

P R W.

|||

Well, after a lot of back-and-forth, Microsoft decided to update their documentation on BOL to make this a 'documented feature'. It was written off as 'works as designed'.

They did NOT register it as a bug (In the case I described in the thread, not sure about your case).

|||

"Works as designed"? Did they give a rationale for that?

-Jamie

|||

Have you found in BOL where it states this ?

To me it's a bug because Propagate False should mean no Event Bubbling for ALL Event Handlers not just a select few.

P R W.

|||

This is what I got back from Microsoft on the issue:

Bugs

We have filled 2 documentation bugs on this issue:

Doc: Error in Validate phase of Package Task Bubbles up to Next level event handler even if Propagate variable Set to False.

Doc: Propagate Property Set to False in Child Package Task Does Not Prevent Errors from bubbling up to Parent Package OnError event handler

|||

Jamie,

There were two issues

1) Event happened locally during validate phase

==> According to MS, the Propagate functionality works during the execution phase, not necessarily during the validate phase. Therefore, any errors encountered during the validate phase will still bubble up.

2) When a local event happens in a child package, the event will automatically jump into the calling (Execute Task) package of the parent. The Propagate functionality is completely ignored here. Not sure about the rationale.

|||

Dirk DC wrote:

Jamie,

There were two issues

1) Event happened locally during validate phase

==> According to MS, the Propagate functionality works during the execution phase, not necessarily during the validate phase. Therefore, any errors encountered during the validate phase will still bubble up.

yeah I'm not surprised about that one. I can kind of understand why that is "as designed". It one of those where its as easy to argue for as it is against.

Dirk DC wrote:

2) When a local event happens in a child package, the event will automatically jump into the calling (Execute Task) package of the parent. The Propagate functionality is completely ignored here. Not sure about the rationale.

So Propogate=FALSE doesn't stop events propogating to a parent package? Hmmm...not quite sure about that one. I don't know why it would be designed that way.

I'll have to check it out.

-Jamie

|||Have you tried setting DelayValidation=true on both the package(s), and DelayValiation=true on the FTP task as well?

I know this approach seems counter intuitive (setting DelayValidation more than once). However, DelayValidation at the container level appears to

have cascading run-time effects, and not cascading design time effects.

DelayValidation at the task level appears to have both design-time and

run-time effects.|||Not sure I've tried that. What are the run-time effects of DelayValidation?|||

The run-time effects of DelayValidation="true" are two fold, that I've observed.

1. When set, task validation is invoked 1 time, that's it. The validation occurs after the task's OnPreExecuteEvent has fired, no less.

Of course, if the task is in a loop container, validation is called one time per loop iteration. Conversely, when DelayValidation is false, validation will be invoked upfront (I've seen it called three times for a given task).

2. Cascading effect. DelayValidation cascades from container to contained tasks.

The "exception" to the delay cascade is that when Delay Validation is set to true on an Execute Package Task, that setting has no effect on the number of times Validation is called on the task in the child package. I say "exception", because actually, Execute Package Task is not a container (does not implement IDTSSequence, more precisely).

Events keep bubbling up, even with Propagate=False

I've been pulling my hair out for the last couple of weeks trying to make my SSIS package more robust. I'm running SQL 05 SP1 on Windows XP SP2. Right now when there's a failure, the error propagates all the way up and my package stops running. Clearly not very robust. I have a heartbeat portion that makes a connection to the database every couple of minutes. Sometimes it loses the connection in the middle of the night, probably due to some maintenance going on. I have created an event handler to log the error, sleep a couple of minutes and set Propagate=False. This works great. When the connection is down, the error is logged, once the connection comes back, the package just keeps going.
I also have an FTP component that receives a file and then needs to send back a handshake file. I tried the same method here, but the event keeps propagating up. I created an event handler for the Send FTP task itself, so that in case the remote FTP server isn't responding, it will error out. The Event Handler code is called and I verified that the value in Propagate is indeed False. And yet the event keeps propagating up and kills the whole package. FailPackageonFailure is set to False, FaileParentonFailure is set to False also.
Any idea what I'm doing wrong? Or is this a bug? Is there a work around?
Any help would be greatly appreciated.

On which eventhandler are you setting System::Propogate=FALSE? The one scoped to the FTP task?

Which container is the other eventhandler scoped to?

If you want your package to continue in the evnt of failure then this is configured within the propreties of the package. it is nothing to do with the configuration of your eventhandlers. I think you will find SSIS to be very robust in this regard when you get it working as you require.

-Jamie

|||

Jamie,

Thanks for the reply. The package has a Main task with a For Each Loop that goes through all files in a directory. It then calls an execute package called ETL-Archive. Within ETL-Archive, it calls execute package ETL. ETL is where the real meat and potatoes is at. This is where the FTP task is. The event handler OnError is scoped to the failing FTP task. This is where I set Propagate=False. However, the event keeps bubbling up to ETL, then to ETL-Archive and then to Main, resulting in complete package failure. Even when I have an OnError event handler at the ETL scope where I again set Propagate = False. I'm not sure I understood your comment about the fact that this is configured within the properties of the package? In this case do you mean ETL or Main? And what should be configured?

Thanks again!

|||

Also, the FTP task throws two errors because I deliberately configured it wrongly:

Error: 0xC0029190 at FTP Handshake back, FTP Task: Variable "FTPRemotePathGC" doesn't start with "/".

Error: 0xC0024107 at FTP Handshake back: There were errors during task validation.

Warning: 0x80019002 at ETL: The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure.

This in turn will fail ETL, which will fail ETL-Archive, which will fail Main...

|||

Hi Jamie,

I just spent 2.5 hours on the phone with Microsoft support, burning up one of my 4 free support tickets that come with MSDN. They confirmed that it is actually a bug. (So I didn't loose one of my 4 support tickets). Apparently some task containers keep propagating Error events up, even though the Propagate system var is set to False. So... the good thing is I know I'm not loosing my mind. The bad thing is that there is no workaround and no fix planned until Katmay. Apparently this bug was fixed in SQL 2005 Beta 3 and reintroduced later. I just installed Build 2153 (http://support.microsoft.com/?id=918222), and the bug is still there.

I'm not sure where to proceed from here. I have an FTP task that I cannot protect with an event handler, so every time my package has to send a little handshake back and the FTP task fails, my whole package bombs. Again... no clue where to proceed from here. How can I ever release such an non-robust package into production?

|||

Do we know if this has actually been submitted as a bug, registered with MS.

I ask because I am seeing the same issue with OnPostExecution EH not just the OnError EH you have described.

I checked on the MS Connect website and searched under the feedback but found no evidence of it ?

P R W.

|||

Well, after a lot of back-and-forth, Microsoft decided to update their documentation on BOL to make this a 'documented feature'. It was written off as 'works as designed'.

They did NOT register it as a bug (In the case I described in the thread, not sure about your case).

|||

"Works as designed"? Did they give a rationale for that?

-Jamie

|||

Have you found in BOL where it states this ?

To me it's a bug because Propagate False should mean no Event Bubbling for ALL Event Handlers not just a select few.

P R W.

|||

This is what I got back from Microsoft on the issue:

Bugs

We have filled 2 documentation bugs on this issue:

Doc: Error in Validate phase of Package Task Bubbles up to Next level event handler even if Propagate variable Set to False.

Doc: Propagate Property Set to False in Child Package Task Does Not Prevent Errors from bubbling up to Parent Package OnError event handler

|||

Jamie,

There were two issues

1) Event happened locally during validate phase

==> According to MS, the Propagate functionality works during the execution phase, not necessarily during the validate phase. Therefore, any errors encountered during the validate phase will still bubble up.

2) When a local event happens in a child package, the event will automatically jump into the calling (Execute Task) package of the parent. The Propagate functionality is completely ignored here. Not sure about the rationale.

|||

Dirk DC wrote:

Jamie,

There were two issues

1) Event happened locally during validate phase

==> According to MS, the Propagate functionality works during the execution phase, not necessarily during the validate phase. Therefore, any errors encountered during the validate phase will still bubble up.

yeah I'm not surprised about that one. I can kind of understand why that is "as designed". It one of those where its as easy to argue for as it is against.

Dirk DC wrote:

2) When a local event happens in a child package, the event will automatically jump into the calling (Execute Task) package of the parent. The Propagate functionality is completely ignored here. Not sure about the rationale.

So Propogate=FALSE doesn't stop events propogating to a parent package? Hmmm...not quite sure about that one. I don't know why it would be designed that way.

I'll have to check it out.

-Jamie

|||Have you tried setting DelayValidation=true on both the package(s), and DelayValiation=true on the FTP task as well?

I know this approach seems counter intuitive (setting DelayValidation more than once). However, DelayValidation at the container level appears to

have cascading run-time effects, and not cascading design time effects.

DelayValidation at the task level appears to have both design-time and

run-time effects.|||Not sure I've tried that. What are the run-time effects of DelayValidation?|||

The run-time effects of DelayValidation="true" are two fold, that I've observed.

1. When set, task validation is invoked 1 time, that's it. The validation occurs after the task's OnPreExecuteEvent has fired, no less.

Of course, if the task is in a loop container, validation is called one time per loop iteration. Conversely, when DelayValidation is false, validation will be invoked upfront (I've seen it called three times for a given task).

2. Cascading effect. DelayValidation cascades from container to contained tasks.

The "exception" to the delay cascade is that when Delay Validation is set to true on an Execute Package Task, that setting has no effect on the number of times Validation is called on the task in the child package. I say "exception", because actually, Execute Package Task is not a container (does not implement IDTSSequence, more precisely).

Events keep bubbling up, even with Propagate=False

I've been pulling my hair out for the last couple of weeks trying to make my SSIS package more robust. I'm running SQL 05 SP1 on Windows XP SP2. Right now when there's a failure, the error propagates all the way up and my package stops running. Clearly not very robust. I have a heartbeat portion that makes a connection to the database every couple of minutes. Sometimes it loses the connection in the middle of the night, probably due to some maintenance going on. I have created an event handler to log the error, sleep a couple of minutes and set Propagate=False. This works great. When the connection is down, the error is logged, once the connection comes back, the package just keeps going.
I also have an FTP component that receives a file and then needs to send back a handshake file. I tried the same method here, but the event keeps propagating up. I created an event handler for the Send FTP task itself, so that in case the remote FTP server isn't responding, it will error out. The Event Handler code is called and I verified that the value in Propagate is indeed False. And yet the event keeps propagating up and kills the whole package. FailPackageonFailure is set to False, FaileParentonFailure is set to False also.
Any idea what I'm doing wrong? Or is this a bug? Is there a work around?
Any help would be greatly appreciated.

On which eventhandler are you setting System::Propogate=FALSE? The one scoped to the FTP task?

Which container is the other eventhandler scoped to?

If you want your package to continue in the evnt of failure then this is configured within the propreties of the package. it is nothing to do with the configuration of your eventhandlers. I think you will find SSIS to be very robust in this regard when you get it working as you require.

-Jamie

|||

Jamie,

Thanks for the reply. The package has a Main task with a For Each Loop that goes through all files in a directory. It then calls an execute package called ETL-Archive. Within ETL-Archive, it calls execute package ETL. ETL is where the real meat and potatoes is at. This is where the FTP task is. The event handler OnError is scoped to the failing FTP task. This is where I set Propagate=False. However, the event keeps bubbling up to ETL, then to ETL-Archive and then to Main, resulting in complete package failure. Even when I have an OnError event handler at the ETL scope where I again set Propagate = False. I'm not sure I understood your comment about the fact that this is configured within the properties of the package? In this case do you mean ETL or Main? And what should be configured?

Thanks again!

|||

Also, the FTP task throws two errors because I deliberately configured it wrongly:

Error: 0xC0029190 at FTP Handshake back, FTP Task: Variable "FTPRemotePathGC" doesn't start with "/".

Error: 0xC0024107 at FTP Handshake back: There were errors during task validation.

Warning: 0x80019002 at ETL: The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure.

This in turn will fail ETL, which will fail ETL-Archive, which will fail Main...

|||

Hi Jamie,

I just spent 2.5 hours on the phone with Microsoft support, burning up one of my 4 free support tickets that come with MSDN. They confirmed that it is actually a bug. (So I didn't loose one of my 4 support tickets). Apparently some task containers keep propagating Error events up, even though the Propagate system var is set to False. So... the good thing is I know I'm not loosing my mind. The bad thing is that there is no workaround and no fix planned until Katmay. Apparently this bug was fixed in SQL 2005 Beta 3 and reintroduced later. I just installed Build 2153 (http://support.microsoft.com/?id=918222), and the bug is still there.

I'm not sure where to proceed from here. I have an FTP task that I cannot protect with an event handler, so every time my package has to send a little handshake back and the FTP task fails, my whole package bombs. Again... no clue where to proceed from here. How can I ever release such an non-robust package into production?

|||

Do we know if this has actually been submitted as a bug, registered with MS.

I ask because I am seeing the same issue with OnPostExecution EH not just the OnError EH you have described.

I checked on the MS Connect website and searched under the feedback but found no evidence of it ?

P R W.

|||

Well, after a lot of back-and-forth, Microsoft decided to update their documentation on BOL to make this a 'documented feature'. It was written off as 'works as designed'.

They did NOT register it as a bug (In the case I described in the thread, not sure about your case).

|||

"Works as designed"? Did they give a rationale for that?

-Jamie

|||

Have you found in BOL where it states this ?

To me it's a bug because Propagate False should mean no Event Bubbling for ALL Event Handlers not just a select few.

P R W.

|||

This is what I got back from Microsoft on the issue:

Bugs

We have filled 2 documentation bugs on this issue:

Doc: Error in Validate phase of Package Task Bubbles up to Next level event handler even if Propagate variable Set to False.

Doc: Propagate Property Set to False in Child Package Task Does Not Prevent Errors from bubbling up to Parent Package OnError event handler

|||

Jamie,

There were two issues

1) Event happened locally during validate phase

==> According to MS, the Propagate functionality works during the execution phase, not necessarily during the validate phase. Therefore, any errors encountered during the validate phase will still bubble up.

2) When a local event happens in a child package, the event will automatically jump into the calling (Execute Task) package of the parent. The Propagate functionality is completely ignored here. Not sure about the rationale.

|||

Dirk DC wrote:

Jamie,

There were two issues

1) Event happened locally during validate phase

==> According to MS, the Propagate functionality works during the execution phase, not necessarily during the validate phase. Therefore, any errors encountered during the validate phase will still bubble up.

yeah I'm not surprised about that one. I can kind of understand why that is "as designed". It one of those where its as easy to argue for as it is against.

Dirk DC wrote:

2) When a local event happens in a child package, the event will automatically jump into the calling (Execute Task) package of the parent. The Propagate functionality is completely ignored here. Not sure about the rationale.

So Propogate=FALSE doesn't stop events propogating to a parent package? Hmmm...not quite sure about that one. I don't know why it would be designed that way.

I'll have to check it out.

-Jamie

|||Have you tried setting DelayValidation=true on both the package(s), and DelayValiation=true on the FTP task as well?

I know this approach seems counter intuitive (setting DelayValidation more than once). However, DelayValidation at the container level appears to

have cascading run-time effects, and not cascading design time effects.

DelayValidation at the task level appears to have both design-time and

run-time effects.|||Not sure I've tried that. What are the run-time effects of DelayValidation?|||

The run-time effects of DelayValidation="true" are two fold, that I've observed.

1. When set, task validation is invoked 1 time, that's it. The validation occurs after the task's OnPreExecuteEvent has fired, no less.

Of course, if the task is in a loop container, validation is called one time per loop iteration. Conversely, when DelayValidation is false, validation will be invoked upfront (I've seen it called three times for a given task).

2. Cascading effect. DelayValidation cascades from container to contained tasks.

The "exception" to the delay cascade is that when Delay Validation is set to true on an Execute Package Task, that setting has no effect on the number of times Validation is called on the task in the child package. I say "exception", because actually, Execute Package Task is not a container (does not implement IDTSSequence, more precisely).

Events keep bubbling up, even with Propagate=False

I've been pulling my hair out for the last couple of weeks trying to make my SSIS package more robust. I'm running SQL 05 SP1 on Windows XP SP2. Right now when there's a failure, the error propagates all the way up and my package stops running. Clearly not very robust. I have a heartbeat portion that makes a connection to the database every couple of minutes. Sometimes it loses the connection in the middle of the night, probably due to some maintenance going on. I have created an event handler to log the error, sleep a couple of minutes and set Propagate=False. This works great. When the connection is down, the error is logged, once the connection comes back, the package just keeps going.
I also have an FTP component that receives a file and then needs to send back a handshake file. I tried the same method here, but the event keeps propagating up. I created an event handler for the Send FTP task itself, so that in case the remote FTP server isn't responding, it will error out. The Event Handler code is called and I verified that the value in Propagate is indeed False. And yet the event keeps propagating up and kills the whole package. FailPackageonFailure is set to False, FaileParentonFailure is set to False also.
Any idea what I'm doing wrong? Or is this a bug? Is there a work around?
Any help would be greatly appreciated.

On which eventhandler are you setting System::Propogate=FALSE? The one scoped to the FTP task?

Which container is the other eventhandler scoped to?

If you want your package to continue in the evnt of failure then this is configured within the propreties of the package. it is nothing to do with the configuration of your eventhandlers. I think you will find SSIS to be very robust in this regard when you get it working as you require.

-Jamie

|||

Jamie,

Thanks for the reply. The package has a Main task with a For Each Loop that goes through all files in a directory. It then calls an execute package called ETL-Archive. Within ETL-Archive, it calls execute package ETL. ETL is where the real meat and potatoes is at. This is where the FTP task is. The event handler OnError is scoped to the failing FTP task. This is where I set Propagate=False. However, the event keeps bubbling up to ETL, then to ETL-Archive and then to Main, resulting in complete package failure. Even when I have an OnError event handler at the ETL scope where I again set Propagate = False. I'm not sure I understood your comment about the fact that this is configured within the properties of the package? In this case do you mean ETL or Main? And what should be configured?

Thanks again!

|||

Also, the FTP task throws two errors because I deliberately configured it wrongly:

Error: 0xC0029190 at FTP Handshake back, FTP Task: Variable "FTPRemotePathGC" doesn't start with "/".

Error: 0xC0024107 at FTP Handshake back: There were errors during task validation.

Warning: 0x80019002 at ETL: The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure.

This in turn will fail ETL, which will fail ETL-Archive, which will fail Main...

|||

Hi Jamie,

I just spent 2.5 hours on the phone with Microsoft support, burning up one of my 4 free support tickets that come with MSDN. They confirmed that it is actually a bug. (So I didn't loose one of my 4 support tickets). Apparently some task containers keep propagating Error events up, even though the Propagate system var is set to False. So... the good thing is I know I'm not loosing my mind. The bad thing is that there is no workaround and no fix planned until Katmay. Apparently this bug was fixed in SQL 2005 Beta 3 and reintroduced later. I just installed Build 2153 (http://support.microsoft.com/?id=918222), and the bug is still there.

I'm not sure where to proceed from here. I have an FTP task that I cannot protect with an event handler, so every time my package has to send a little handshake back and the FTP task fails, my whole package bombs. Again... no clue where to proceed from here. How can I ever release such an non-robust package into production?

|||

Do we know if this has actually been submitted as a bug, registered with MS.

I ask because I am seeing the same issue with OnPostExecution EH not just the OnError EH you have described.

I checked on the MS Connect website and searched under the feedback but found no evidence of it ?

P R W.

|||

Well, after a lot of back-and-forth, Microsoft decided to update their documentation on BOL to make this a 'documented feature'. It was written off as 'works as designed'.

They did NOT register it as a bug (In the case I described in the thread, not sure about your case).

|||

"Works as designed"? Did they give a rationale for that?

-Jamie

|||

Have you found in BOL where it states this ?

To me it's a bug because Propagate False should mean no Event Bubbling for ALL Event Handlers not just a select few.

P R W.

|||

This is what I got back from Microsoft on the issue:

Bugs

We have filled 2 documentation bugs on this issue:

Doc: Error in Validate phase of Package Task Bubbles up to Next level event handler even if Propagate variable Set to False.

Doc: Propagate Property Set to False in Child Package Task Does Not Prevent Errors from bubbling up to Parent Package OnError event handler

|||

Jamie,

There were two issues

1) Event happened locally during validate phase

==> According to MS, the Propagate functionality works during the execution phase, not necessarily during the validate phase. Therefore, any errors encountered during the validate phase will still bubble up.

2) When a local event happens in a child package, the event will automatically jump into the calling (Execute Task) package of the parent. The Propagate functionality is completely ignored here. Not sure about the rationale.

|||

Dirk DC wrote:

Jamie,

There were two issues

1) Event happened locally during validate phase

==> According to MS, the Propagate functionality works during the execution phase, not necessarily during the validate phase. Therefore, any errors encountered during the validate phase will still bubble up.

yeah I'm not surprised about that one. I can kind of understand why that is "as designed". It one of those where its as easy to argue for as it is against.

Dirk DC wrote:

2) When a local event happens in a child package, the event will automatically jump into the calling (Execute Task) package of the parent. The Propagate functionality is completely ignored here. Not sure about the rationale.

So Propogate=FALSE doesn't stop events propogating to a parent package? Hmmm...not quite sure about that one. I don't know why it would be designed that way.

I'll have to check it out.

-Jamie

|||Have you tried setting DelayValidation=true on both the package(s), and DelayValiation=true on the FTP task as well?

I know this approach seems counter intuitive (setting DelayValidation more than once). However, DelayValidation at the container level appears to

have cascading run-time effects, and not cascading design time effects.

DelayValidation at the task level appears to have both design-time and

run-time effects.|||Not sure I've tried that. What are the run-time effects of DelayValidation?|||

The run-time effects of DelayValidation="true" are two fold, that I've observed.

1. When set, task validation is invoked 1 time, that's it. The validation occurs after the task's OnPreExecuteEvent has fired, no less.

Of course, if the task is in a loop container, validation is called one time per loop iteration. Conversely, when DelayValidation is false, validation will be invoked upfront (I've seen it called three times for a given task).

2. Cascading effect. DelayValidation cascades from container to contained tasks.

The "exception" to the delay cascade is that when Delay Validation is set to true on an Execute Package Task, that setting has no effect on the number of times Validation is called on the task in the child package. I say "exception", because actually, Execute Package Task is not a container (does not implement IDTSSequence, more precisely).

Event Handler Refresh

I am current using Event Handlers in an SSIS package, but when adding a new EventHandler type eg. OnPreExecute, to an executable within the package, I am unable to see this new EventHandler defined in the hierarchical listing on the left hand combo box (named Executables).

Can't seem to find a Refresh option anywhere. I've only been able to see these newly created EventHandlers in the hierarchical listing by closing and re-opening the package.

Is this a 'feature' or am I missing a Refresh option somewhere.

Thanks.

Check the scope of the eventhandler you have created. Is it possible you are looking in the wrong place in the Package Explorer?

-Jamie

|||

Hi Jamie,

I have checked the scope and I am not looking in the incorrect place.

I wasn't not looking in Package Explorer though, but in the Executables drop-down box on the Event Handlers tab.

However the problem for me is replicated in Package Explorer also. Would you confirm that you are able to duplicate the problem ?

P R W.

|||

I don't seem to be able to repro your issue.

You said you were not looking at the Package Explorer, were you in the Event Handler diagram, then? If yes, and you did set the "Executables" drop down to the correct item, did you also set the "Event Handler" drop down (on the right) to, say"OnPreExecute" if that is the event? Since the default there is "OnError"

If you still get this, pls describe your SQL build version (RTM, SP1?) and your detailed steps. We'll see how we can help.

thanks

wenyang

|||

Hi P R W,

I'm not positive, but I think I saw this before I applied SQL Server 2005 SP1. Have you applied SP1?

Hope this helps,

Andy

Event based scheduling of SSIS packages

Hi,

Is it possible to do an event based scheduling of SSIS packages from SQL Server Agent? Like schedule a package to run when a file is available in a particular folder.

Does anyone have any similar experiences to share?

Regards,

Emil

Please have a look at the links provided by Daniel Crowell

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

This should help.

Thanks

Subhash Subramanyam.

Evenly splitting output in SSIS

I would like to split the data stream so that 1 copy can be sent to a lookup task, and the other can be sent as input to another target.

There doesn't seem to be a way to do this in SSIS; but maybe I should be considering other options?

Can anyone suggest a way to copy data to two different sources?I think you want to use the multicast component.|||Boy, I had experimented with that before and completely forgot about it. Thanks!