Excel File Locked

I'm building a package that has a Data Flow which reads an Excel File using the Excel Source connection - during development I hit errors in steps after the excel read. This is causing the excel file to be locked up and the next run fails on excel connection problem. If I go to Excel I can't open the file either. I have to exit BIDS and then file gets released. Is there any setting / different approach that would prevent this from happening?

thx

I doubt there is a setting - its just unfortunate I guess.

This probably won't happen if you run the package from the command line - so try that!

-Jamie

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: write over existing data

I have a daily package that extracts some data and writes it into an excel file. I want to write over the existing data, but the excel destination only appends the next free location in the worksheet. I tried using a SQL task to grab the file, set all the cells = NULL and then run the rest of the package, thinking it would see the null cells as empty and write in them, but somehow it knows where the previous data ended and keeps appending further down in the workbook.

Does anyone know of a workaround so I do not have to delete and re-create the file everytime?

TIA,
Sabrina

You could use the FileSystem task to delete the file prior to inserting into it. This will (I assume) create a new version of the file.

-Jamie

|||

You can delete the file, but I think the validation of the excel connection manager will fail because the file does not exists; but there is a work around; after deleting the file, you can issue a Create table...using execute sql task in control flow that point to the excel connection manager to create the file. You can see an example here....

|||

Thanks. I've tried deleting the file with a file system task, and that does fail because the destination no longer exists when it runs. The file system task does not have a create file option in it. I was thinking I would have to use a script task to do this?

Rafael, I did not see a link the example you mentioned.

Thanks,

Sabrina

|||

Rafael Salas wrote:

You can delete the file, but I think the validation of the excel connection manager will fail because the file does not exists; but there is a work around; after deleting the file, you can issue a Create table...using execute sql task in control flow that point to the excel connection manager to create the file. You can see an example here....

Will setting "DelayValidation=True" work in this case?|||

BrinaD1 wrote:

Thanks. I've tried deleting the file with a file system task, and that does fail because the destination no longer exists when it runs. The file system task does not have a create file option in it. I was thinking I would have to use a script task to do this?

Rafael, I did not see a link the example you mentioned.

Thanks,

Sabrina

Sorry about the omission, Here is the link:

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

After deliting the file; just use the execute sql task to create it back as explained in the link...

|||

Phil Brammer wrote:

Rafael Salas wrote:

You can delete the file, but I think the validation of the excel connection manager will fail because the file does not exists; but there is a work around; after deleting the file, you can issue a Create table...using execute sql task in control flow that point to the excel connection manager to create the file. You can see an example here....

Will setting "DelayValidation=True" work in this case?

It should do. Perhaps the original poster could try it. Its a property of the data-flow by the way.

-Jamie

|||

Jamie Thomson wrote:

Phil Brammer wrote:

Rafael Salas wrote:

You can delete the file, but I think the validation of the excel connection manager will fail because the file does not exists; but there is a work around; after deleting the file, you can issue a Create table...using execute sql task in control flow that point to the excel connection manager to create the file. You can see an example here....

Will setting "DelayValidation=True" work in this case?

It should do. Perhaps the original poster could try it. Its a property of the data-flow by the way.

-Jamie

If I recall correctly, the DelayValidation=true would woulddo just that; delay the validation; but it won't make the connection manager to create the file if it does not exists...but I am with Jamie...test it and see it.

|||

Rafael Salas wrote:

Jamie Thomson wrote:

Phil Brammer wrote:

Rafael Salas wrote:

You can delete the file, but I think the validation of the excel connection manager will fail because the file does not exists; but there is a work around; after deleting the file, you can issue a Create table...using execute sql task in control flow that point to the excel connection manager to create the file. You can see an example here....

Will setting "DelayValidation=True" work in this case?

It should do. Perhaps the original poster could try it. Its a property of the data-flow by the way.

-Jamie

If I recall correctly, the DelayValidation=true would woulddo just that; delay the validation; but it won't make the connection manager to create the file if it does not exists...but I am with Jamie...test it and see it.

Well, I tried DelayValidation=True and it behaved as expected - still failed. I was able to use the SQL task to create the excel file, thank you Rafael! It is just strange to me that SSIS does not have an option somewhere to overwrite vs append the data.

Thanks!

|||

Its not really anything SSIS can do something about - its a lack of functionality in the Excel Driver. Hopefully there'll be a better one coming one day.

-Jamie

|||

Ok, same project, now with a twist.

I'm using the file system task to delete the file, and the Execute SQL task to re-create the file. The only problem is we want to add more worksheets. I've tried the following with no results:

CREATE TABLE `Perfect Order` (
`GLYEAR` INTEGER,
`GLMONTH` INTEGER,
`PCTPERFECT` DOUBLE PRECISION
);
CREATE TABLE `Days to Activate` (
`GLYEAR` INTEGER,
`GLMONTH` INTEGER,
`DAYS` DOUBLE PRECISION,
`NUMLINES` INTEGER
);
CREATE TABLE `Days Activate to Ship` (
`GLYEAR` INTEGER,
`GLMONTH` INTEGER,
`DAYS` DOUBLE PRECISION,
`NUMLINES` INTEGER
);

Is there a way to do it all in one statement? Or do I need to have multiple Execute SQL tasks with the same Excel connection?

|||

BrinaD1 wrote:

Ok, same project, now with a twist.

I'm using the file system task to delete the file, and the Execute SQL task to re-create the file. The only problem is we want to add more worksheets. I've tried the following with no results:

CREATE TABLE `Perfect Order` (
`GLYEAR` INTEGER,
`GLMONTH` INTEGER,
`PCTPERFECT` DOUBLE PRECISION
);
CREATE TABLE `Days to Activate` (
`GLYEAR` INTEGER,
`GLMONTH` INTEGER,
`DAYS` DOUBLE PRECISION,
`NUMLINES` INTEGER
);
CREATE TABLE `Days Activate to Ship` (
`GLYEAR` INTEGER,
`GLMONTH` INTEGER,
`DAYS` DOUBLE PRECISION,
`NUMLINES` INTEGER
);

Is there a way to do it all in one statement? Or do I need to have multiple Execute SQL tasks with the same Excel connection?

I would expected putting all the create statements in a single Execute Sql Task to work. Perhaps having multiple Execute sql tasks is the solution.

sql

Excel Destination: write over existing data

I have a daily package that extracts some data and writes it into an excel file. I want to write over the existing data, but the excel destination only appends the next free location in the worksheet. I tried using a SQL task to grab the file, set all the cells = NULL and then run the rest of the package, thinking it would see the null cells as empty and write in them, but somehow it knows where the previous data ended and keeps appending further down in the workbook.

Does anyone know of a workaround so I do not have to delete and re-create the file everytime?

TIA,
Sabrina

You could use the FileSystem task to delete the file prior to inserting into it. This will (I assume) create a new version of the file.

-Jamie

|||

You can delete the file, but I think the validation of the excel connection manager will fail because the file does not exists; but there is a work around; after deleting the file, you can issue a Create table...using execute sql task in control flow that point to the excel connection manager to create the file. You can see an example here....

|||

Thanks. I've tried deleting the file with a file system task, and that does fail because the destination no longer exists when it runs. The file system task does not have a create file option in it. I was thinking I would have to use a script task to do this?

Rafael, I did not see a link the example you mentioned.

Thanks,

Sabrina

|||

Rafael Salas wrote:

You can delete the file, but I think the validation of the excel connection manager will fail because the file does not exists; but there is a work around; after deleting the file, you can issue a Create table...using execute sql task in control flow that point to the excel connection manager to create the file. You can see an example here....

Will setting "DelayValidation=True" work in this case?|||

BrinaD1 wrote:

Thanks. I've tried deleting the file with a file system task, and that does fail because the destination no longer exists when it runs. The file system task does not have a create file option in it. I was thinking I would have to use a script task to do this?

Rafael, I did not see a link the example you mentioned.

Thanks,

Sabrina

Sorry about the omission, Here is the link:

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

After deliting the file; just use the execute sql task to create it back as explained in the link...

|||

Phil Brammer wrote:

Rafael Salas wrote:

You can delete the file, but I think the validation of the excel connection manager will fail because the file does not exists; but there is a work around; after deleting the file, you can issue a Create table...using execute sql task in control flow that point to the excel connection manager to create the file. You can see an example here....

Will setting "DelayValidation=True" work in this case?

It should do. Perhaps the original poster could try it. Its a property of the data-flow by the way.

-Jamie

|||

Jamie Thomson wrote:

Phil Brammer wrote:

Rafael Salas wrote:

You can delete the file, but I think the validation of the excel connection manager will fail because the file does not exists; but there is a work around; after deleting the file, you can issue a Create table...using execute sql task in control flow that point to the excel connection manager to create the file. You can see an example here....

Will setting "DelayValidation=True" work in this case?

It should do. Perhaps the original poster could try it. Its a property of the data-flow by the way.

-Jamie

If I recall correctly, the DelayValidation=true would woulddo just that; delay the validation; but it won't make the connection manager to create the file if it does not exists...but I am with Jamie...test it and see it.

|||

Rafael Salas wrote:

Jamie Thomson wrote:

Phil Brammer wrote:

Rafael Salas wrote:

You can delete the file, but I think the validation of the excel connection manager will fail because the file does not exists; but there is a work around; after deleting the file, you can issue a Create table...using execute sql task in control flow that point to the excel connection manager to create the file. You can see an example here....

Will setting "DelayValidation=True" work in this case?

It should do. Perhaps the original poster could try it. Its a property of the data-flow by the way.

-Jamie

If I recall correctly, the DelayValidation=true would woulddo just that; delay the validation; but it won't make the connection manager to create the file if it does not exists...but I am with Jamie...test it and see it.

Well, I tried DelayValidation=True and it behaved as expected - still failed. I was able to use the SQL task to create the excel file, thank you Rafael! It is just strange to me that SSIS does not have an option somewhere to overwrite vs append the data.

Thanks!

|||

Its not really anything SSIS can do something about - its a lack of functionality in the Excel Driver. Hopefully there'll be a better one coming one day.

-Jamie

|||

Ok, same project, now with a twist.

I'm using the file system task to delete the file, and the Execute SQL task to re-create the file. The only problem is we want to add more worksheets. I've tried the following with no results:

CREATE TABLE `Perfect Order` (
`GLYEAR` INTEGER,
`GLMONTH` INTEGER,
`PCTPERFECT` DOUBLE PRECISION
);
CREATE TABLE `Days to Activate` (
`GLYEAR` INTEGER,
`GLMONTH` INTEGER,
`DAYS` DOUBLE PRECISION,
`NUMLINES` INTEGER
);
CREATE TABLE `Days Activate to Ship` (
`GLYEAR` INTEGER,
`GLMONTH` INTEGER,
`DAYS` DOUBLE PRECISION,
`NUMLINES` INTEGER
);

Is there a way to do it all in one statement? Or do I need to have multiple Execute SQL tasks with the same Excel connection?

|||

BrinaD1 wrote:

Ok, same project, now with a twist.

I'm using the file system task to delete the file, and the Execute SQL task to re-create the file. The only problem is we want to add more worksheets. I've tried the following with no results:

CREATE TABLE `Perfect Order` (
`GLYEAR` INTEGER,
`GLMONTH` INTEGER,
`PCTPERFECT` DOUBLE PRECISION
);
CREATE TABLE `Days to Activate` (
`GLYEAR` INTEGER,
`GLMONTH` INTEGER,
`DAYS` DOUBLE PRECISION,
`NUMLINES` INTEGER
);
CREATE TABLE `Days Activate to Ship` (
`GLYEAR` INTEGER,
`GLMONTH` INTEGER,
`DAYS` DOUBLE PRECISION,
`NUMLINES` INTEGER
);

Is there a way to do it all in one statement? Or do I need to have multiple Execute SQL tasks with the same Excel connection?

I would expected putting all the create statements in a single Execute Sql Task to work. Perhaps having multiple Execute sql tasks is the solution.

Excel Destination: write over existing data

I have a daily package that extracts some data and writes it into an excel file. I want to write over the existing data, but the excel destination only appends the next free location in the worksheet. I tried using a SQL task to grab the file, set all the cells = NULL and then run the rest of the package, thinking it would see the null cells as empty and write in them, but somehow it knows where the previous data ended and keeps appending further down in the workbook.

Does anyone know of a workaround so I do not have to delete and re-create the file everytime?

TIA,
Sabrina

You could use the FileSystem task to delete the file prior to inserting into it. This will (I assume) create a new version of the file.

-Jamie

|||

You can delete the file, but I think the validation of the excel connection manager will fail because the file does not exists; but there is a work around; after deleting the file, you can issue a Create table...using execute sql task in control flow that point to the excel connection manager to create the file. You can see an example here....

|||

Thanks. I've tried deleting the file with a file system task, and that does fail because the destination no longer exists when it runs. The file system task does not have a create file option in it. I was thinking I would have to use a script task to do this?

Rafael, I did not see a link the example you mentioned.

Thanks,

Sabrina

|||

Rafael Salas wrote:

You can delete the file, but I think the validation of the excel connection manager will fail because the file does not exists; but there is a work around; after deleting the file, you can issue a Create table...using execute sql task in control flow that point to the excel connection manager to create the file. You can see an example here....

Will setting "DelayValidation=True" work in this case?|||

BrinaD1 wrote:

Thanks. I've tried deleting the file with a file system task, and that does fail because the destination no longer exists when it runs. The file system task does not have a create file option in it. I was thinking I would have to use a script task to do this?

Rafael, I did not see a link the example you mentioned.

Thanks,

Sabrina

Sorry about the omission, Here is the link:

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

After deliting the file; just use the execute sql task to create it back as explained in the link...

|||

Phil Brammer wrote:

Rafael Salas wrote:

You can delete the file, but I think the validation of the excel connection manager will fail because the file does not exists; but there is a work around; after deleting the file, you can issue a Create table...using execute sql task in control flow that point to the excel connection manager to create the file. You can see an example here....

Will setting "DelayValidation=True" work in this case?

It should do. Perhaps the original poster could try it. Its a property of the data-flow by the way.

-Jamie

|||

Jamie Thomson wrote:

Phil Brammer wrote:

Rafael Salas wrote:

You can delete the file, but I think the validation of the excel connection manager will fail because the file does not exists; but there is a work around; after deleting the file, you can issue a Create table...using execute sql task in control flow that point to the excel connection manager to create the file. You can see an example here....

Will setting "DelayValidation=True" work in this case?

It should do. Perhaps the original poster could try it. Its a property of the data-flow by the way.

-Jamie

If I recall correctly, the DelayValidation=true would woulddo just that; delay the validation; but it won't make the connection manager to create the file if it does not exists...but I am with Jamie...test it and see it.

|||

Rafael Salas wrote:

Jamie Thomson wrote:

Phil Brammer wrote:

Rafael Salas wrote:

You can delete the file, but I think the validation of the excel connection manager will fail because the file does not exists; but there is a work around; after deleting the file, you can issue a Create table...using execute sql task in control flow that point to the excel connection manager to create the file. You can see an example here....

Will setting "DelayValidation=True" work in this case?

It should do. Perhaps the original poster could try it. Its a property of the data-flow by the way.

-Jamie

If I recall correctly, the DelayValidation=true would woulddo just that; delay the validation; but it won't make the connection manager to create the file if it does not exists...but I am with Jamie...test it and see it.

Well, I tried DelayValidation=True and it behaved as expected - still failed. I was able to use the SQL task to create the excel file, thank you Rafael! It is just strange to me that SSIS does not have an option somewhere to overwrite vs append the data.

Thanks!

|||

Its not really anything SSIS can do something about - its a lack of functionality in the Excel Driver. Hopefully there'll be a better one coming one day.

-Jamie

|||

Ok, same project, now with a twist.

I'm using the file system task to delete the file, and the Execute SQL task to re-create the file. The only problem is we want to add more worksheets. I've tried the following with no results:

CREATE TABLE `Perfect Order` (
`GLYEAR` INTEGER,
`GLMONTH` INTEGER,
`PCTPERFECT` DOUBLE PRECISION
);
CREATE TABLE `Days to Activate` (
`GLYEAR` INTEGER,
`GLMONTH` INTEGER,
`DAYS` DOUBLE PRECISION,
`NUMLINES` INTEGER
);
CREATE TABLE `Days Activate to Ship` (
`GLYEAR` INTEGER,
`GLMONTH` INTEGER,
`DAYS` DOUBLE PRECISION,
`NUMLINES` INTEGER
);

Is there a way to do it all in one statement? Or do I need to have multiple Execute SQL tasks with the same Excel connection?

|||

BrinaD1 wrote:

Ok, same project, now with a twist.

I'm using the file system task to delete the file, and the Execute SQL task to re-create the file. The only problem is we want to add more worksheets. I've tried the following with no results:

CREATE TABLE `Perfect Order` (
`GLYEAR` INTEGER,
`GLMONTH` INTEGER,
`PCTPERFECT` DOUBLE PRECISION
);
CREATE TABLE `Days to Activate` (
`GLYEAR` INTEGER,
`GLMONTH` INTEGER,
`DAYS` DOUBLE PRECISION,
`NUMLINES` INTEGER
);
CREATE TABLE `Days Activate to Ship` (
`GLYEAR` INTEGER,
`GLMONTH` INTEGER,
`DAYS` DOUBLE PRECISION,
`NUMLINES` INTEGER
);

Is there a way to do it all in one statement? Or do I need to have multiple Execute SQL tasks with the same Excel connection?

I would expected putting all the create statements in a single Execute Sql Task to work. Perhaps having multiple Execute sql tasks is the solution.

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 connection properities -

In my package I need to change the filename name for my input Excel based on a the contents of an external file. I want to read the external file, get the filename/location from this file and then dynamically set this as the property for the Excel Connection. I saw some posts related to this but did not understand which tasks I need to use and in which order.

thanks

My connection string is

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @.[User::ExcelFileName] + ";Extended Properties="EXCEL 8.0;HDR=YES";

Delayed validation is True

I noticed my server name and excel file path are = to my source (=" + @.[User::ExcelFileName] + ") and if I change any of the 3 they all change.

When I run I get this:

TITLE: Package Validation Error

Package Validation Error

ADDITIONAL INFORMATION:

Error at Load training data [Read R013 Excel file [1]]: The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009.

Error at Load training data [DTS.Pipeline]: component "Read R013 Excel file" (1) failed validation and returned error code 0xC020801C.

Error at Load training data [DTS.Pipeline]: One or more component failed validation.

Error at Load training data: There were errors during task validation.

Error at PackageExcel [Connection manager "Excel Connection Manager"]: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Failure creating file.".

(Microsoft.DataTransformationServices.VsIntegration)

BUTTONS:

OK
-

thx

|||

Here is an expression that worked for me, exactly as entered in the Expression box. The double quotes around the Extended Properties value need to be doubled (and escaped). Note that in this case I was using a second user variable for the Extended Properties.

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @.[User::ExcelFile] + ";Extended Properties=\"" + @.[User::ExtProperties] + "\""

-Doug

|||

Thanks for the response

When I paste in your code as is it gets changed automatically to this?:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @.[User::ExcelFile] + ";Extended Properties=\"" + @.[USER::EXTPROPERTIES] + "\"";"Provider=Microsoft.Jet.OLEDB.4.0;

Doesn't like the (") in front of Provider? Any thoughts?

Tom

|||

Not sure how to explain that. But I did copy and paste exactly what appears in the Expression box in the Expression Builder dialog on my Excel Connection Manager's Expressions property...including the opening quote.

It's been a while since I ran that test when this issue first appeared in the forum, and I no longer recall all the variations that I tried.

The tricky quotes around the value of the Extended Properties argument are only required because there's more than 1 argument. If you just use "Excel 8.0" and allow the HDR argument to default to Yes, you can skip those quotes, which may well be your problem:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @.[User::ExcelFileName] + ";Extended Properties=EXCEL 8.0;

-Doug

|||

I removed your variable for ext properties

have this

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @.[User::ExcellFileName] + ";Extended Properties="EXCEL 8.0;HDR=YES";

still getting the error that it can't create file

Is there any way to view what's it's resolving the variable name to before it gets to the step of reading the excel file? I'm thinking that it is still not coming up w/ the correct file name. If I replace the variable w/ the hardcoded path/filename it works.

Thanks for your help

Tom

|||

I did get to see the name resolved and it looks fine

Tom

|||

I changed the variable to be the Provider instead of the Data source - this now gives me an error 'Class not Registered' - It seems like the implementation of the variable name has a syntax problem that formats it incorrectly.

Any suggestions?

Provider=" + @.[User::MyFileName] + ";

thx

|||

I got a bit lost in the thread above, but as an expression your example will not work. There is no opending quote and the semi-colon literal section is not quoted close. Maybe this forum mucks up the format but this should work-

"Provider=" + @.[User::MyFileName] + ";"

|||

Darren

when I tried to wrap the whole string w/ " the UI didn't like it and put a second 'Provider' at the end.

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @.[User::ExcelFile] + ";Extended Properties=\"" + @.[USER::EXTPROPERTIES] + "\"";"Provider=Microsoft.Jet.OLEDB.4.0;

In the above example I'm trying to set the data source w/ the parameter (what I really want) - when I couldn't get that to work I tried to use the parameter w/ Provider to see what would happen there.

Just to clarify what I'm doing (since I'm new to this), I'm setting the connectionstring property of my Excel connection manager

let me know if you have any other suggestions

thanks

|||

Just to be clear, you're entering this expression in the Expression Builder dialog box, right?

And in the Properties window for your Excel Connection Manager, the ConnectionString property itself is either (1) totally blank or (2) contains a complete, hard-coded, valid Jet/Excel connection string WITHOUT any of the expression syntax?

In other words, you're not entering the expression in the ConnectionString property directly?

|||

No I'm not - I had a feeling that I was doing something stupid - first time at using SSIS - I will give it a try - sorry for the confusion

Thx

Excel Connection Manager - Moving between environments

Hi - I have an issue with the Excel Connection Manager when moving between environments (e.g. from DEV to TEST).

1. My Package contains an Excel Connection Manager X and an Excel Source Y

2. X has a hard-coded Excel File Path Value: C:\DEV\FinanceData.xls

X's DelayValidation property = True

Y's ValidateExternalMetaData = False

3. I have 2 Package Configurations:-

3.1 The first one is for "MachineResident" values, such as, drive e.g. D:

3.2 The second one is for "PackageSpecific" values, such as, feed file name

4. I set-up a Script Task to update X's ConnectionString using the values specified in my two package configurations. The ConnectionString is completely different to the hard-coded Excel File Path Value.

5. The Package works fine on my DEV machine

6. Then, I move it to TEST where the folder: C:\DEV\ does NOT exist ... and then when I run the package it fails immediately with:-

"The AcquireConnection method call to the connection manager "FeedFile Excel Connection Manager" failed with error code 0xC0202009."

It seems that the DelayValidation = True setting is not working with the Excel Connection Manager. Is this correct?

Thanks.

Why are you using a Script Task to set the connection string?

Excel 2007 AcquireConnection error

I'm creating a small test package that copies a value from an Excel 2007 worksheet into a SQL 2005 database (SP2). When I do an Execute Task, I get the following error:

SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager \\lorip\MandEScorecards\SQLQueryExample.xlsx failed with error code 0xC0202009.

However, if I do a "Preview" in the Editor for this same Excel Source task that fails, the data comes up as I would expect. What am I missing?

Thanks in advance!

This error code is sometimes related to security issues. How are you running the package?|||

Is this a 64-Bit Server?

If is, there is an Article that show that SQL Server 2005 in 64-Bits don't give support to any OLE DB Transactions especially Excel.

sql

Excel 2007 AcquireConnection error

I'm creating a small test package that copies a value from an Excel 2007 worksheet into a SQL 2005 database (SP2). When I do an Execute Task, I get the following error:

SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager \\lorip\MandEScorecards\SQLQueryExample.xlsx failed with error code 0xC0202009.

However, if I do a "Preview" in the Editor for this same Excel Source task that fails, the data comes up as I would expect. What am I missing?

Thanks in advance!

This error code is sometimes related to security issues. How are you running the package?|||

Is this a 64-Bit Server?

If is, there is an Article that show that SQL Server 2005 in 64-Bits don't give support to any OLE DB Transactions especially Excel.

Example Project/Package for SQl Server 2005 "Text Extraction"

Does any kind person have a simple example Package for undertaking 'Text Extraction' on one or two columns of text data in a SQL Server table (the data is not in Unicode)?

Check out the sample @. http://aspalliance.com/889
With this example - users will be able to import sample data from a flat file to SQL Server 2005 database.

Let us know if you are looking for any other specific extraction examples.

Thanks,
Loonysan

|||The example provides you provided a useful example of how to set up a package to extract data from an existing flat file. Thank you. Would you per chance have an example on how to set up a package to run on an existing SQL Server table to do a "Text Extraction" on a column of data, including doing an initial conversion to Unicode, as ths appears required?|||

What do you mean by "test extraction"? Are you talking about the TERM EXTRACTION component?

-Jamie

|||

Yes, sorry, "Term Extraction"!

|||

Some Good Pointers for 'Term extraction'

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/datasol.asp (Check out the Example on Extracting Attributes)

http://www.sqlserverdatamining.com/DMCommunity/_Tutorials/688.aspx (Need to register for accessing this site - Registration is Free) - Tutorial on Text Classification using SQL Server 2005.

Thanks,
Loonysan

Example of Parameter in DTS Package

Hi,

Please show me a example of how to create and use a Parameter in DTS Package (SQL Server 2000)

Thanks,

Vivek Gupta

Rather than explicitly pass a parameter to the DTS package to pass to some stored procedure, why not create a a parameter table to pass in the value. Suppose you want to pass in an integer, then create a ParamInt table, with columns of Id (Integer non-identity primary key, ParameterName Varchar(50), ParameterValue Int default value 0.

Insert your value (or values) as rows into this table - you stored procedures run by the DTS package can then pickup their parameters from the appropriate row of this table.

HTH

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).

EventHandlers firing events

I ran into a pretty bizzare behavior in SSIS:

I am trying to set up a package with a built-in auditing. It has a OnPreExecute, OnPostExecute and OnError event handlers. I am trying to record when the package starts, completes, and the completion status. Each one of these event handlers has a script task that does the logging. I put in debug message boxes into each event handler script to understand what goes on. So here's the sequence of events:

1. When starting the package the OnPreExecute event fires. Right away it fires the second time. I'm guessing what happens here is the script task within the event handler fires its own OnPreExecute event - that's how the first message really pops up. The second message is generated by the actual package-level OnPreExecute event.

2. I have a condition within the OnPreExecute event handler which might set the task status to failure. You would expect the OnError handler to fire, right?.. Wrong! The package dies without firing either OnError or OnPostExecute event....

3. If i remove the condition in step 2, and force an error in the package body, i get an OnError event, and then 2 OnPostExecute events ( i guess for the same reason as in step 1).

What I'm trying to understand is why in the world my OnPreExecute and OnPostExecute events get fired by their own event handlers, yet when i fire other events within these event handlers the appropriate (other) event handler does not run.

Any ideas will be greatly appreciated.

Chianuri,

Is your package empty, or does it contain a single task? The package container itself will fire an OnPreExecute event as well. Your event handlers won't trigger themselves which is why throwing an error in the event handler doesn't cause the OnError event to fire. More information on Event Handlers can be found here: http://msdn2.microsoft.com/en-us/library/ms140223.aspx.

For the events that you've noted, as well as a number of others, you can audit them directly using the built-in logging functionality (http://msdn2.microsoft.com/en-us/library/ms141212.aspx).

~Patrik

|||

Patrick,

Thanks for the reply. What I described in my post is really a significant oversimplification of the system I am trying to build. The system requires a fairly sofisticated auditing mechanism and the built-in logging is not sufficient (unfortunately).

I would really like to utilize event handlers for all of my auditing needs. For that to happen, I have to be able to detect events happening within event handlers (such as exceptions occuring, etc.). Do you have any suggestions as to how to force one event handler to respond to events happening in another event handler?

Thank you.

-alex

|||

Alex,

You can add event handlers to the tasks contained within event handlers. The limitation you might find is that you cannot select an existing handler to handle the new events. What would you like to have happen when an exception occurs within your event handler?

Note within your event handlers you can set the variable CancelEvent to a non-zero value to tell the task which fired the event to stop running (http://msdn2.microsoft.com/en-us/library/ms140223.aspx).

I'd also like to encourage you to make suggestions on http://connect.microsoft.com if there are scenarios that we currently don't cover.

Thanks,
Patrik

|||

Patrick,

you are absolutely right - event handlers entering an infinite chain of events firing within event handlers is a concern. I think though, that a mechanism preventing that from happening can be designed fairly easily ( a quick and dirty one that comes to mind right away would be limiting the number of times the event handler can execute for a particular container, or explicitly preventing it from handling events fired by an executable it contains).

Thanks for your help. I'll think of a more robust solution and propose it on the connect site, as you suggested

-alex

|||

chianuri wrote:

I would really like to utilize event handlers for all of my auditing needs. For that to happen, I have to be able to detect events happening within event handlers (such as exceptions occuring, etc.). Do you have any suggestions as to how to force one event handler to respond to events happening in another event handler?

Alex,

If you execute PackageB from an "Execute Package Task" in PackageA then all actions in eventhandlers in PackageB will be caught by eventhandlers in PackageA.

Hope that helps.

-Jamie

|||

Thanks, Jamie. Unfortunately, this does not help me. One of the things that I'm trying to achieve here is knowing the sequence of package calls (e.g., I need to know that PackageA called PackageB which called PackageC) and I'm trying to capture the fact that each package starts and completes, as well as the execution status through event handlers... I'll have to think of an alternative.

Btw, I'm a big fan of your blog. Thanks for making it easier for the rest of us.

-alex

|||

chianuri wrote:

Thanks, Jamie. Unfortunately, this does not help me. One of the things that I'm trying to achieve here is knowing the sequence of package calls (e.g., I need to know that PackageA called PackageB which called PackageC) and I'm trying to capture the fact that each package starts and completes, as well as the execution status through event handlers... I'll have to think of an alternative.

Btw, I'm a big fan of your blog. Thanks for making it easier for the rest of us.

-alex

Well perhaps you could employ some sort of naming convention. e.g. Every package is called Pkg*.dtsx

That way you can check the System::SourceName property to see if the event is raised by a package. That way you will know when a package starts/stops.

-Jamie

Eventhandlers don't work if a package is started from a checkpoint - BIG NASTY BUG (I think)

All,

I'd like someone from MS to confirm something for me if possible.

I've just come across a situation where a package that is restarted from a checkpoint fails to execute any of its eventhandlers if those eventhandlers are scoped to a parent container.

Is this a bug or by design? Either way I'm VERY worried about this because this is not in the least bit intuitive. This is the most dangerous bug that I have seen in RTM.

-Jamie

If you want a demo of this happening then download this package: http://blogs.conchango.com/Admin/ImageGallery/blogs.conchango.com/jamie.thomson/20060103Package1.zip

It doesn't need any configuration because there are no connection managers. You can just run it standalone. Run it first time and it will fail but you WILL see msgboxes popped up by the eventhandlers. Go into "Script Task 2" and make the return value "Success" rather than "Failure". Run it a second time. The package will complete successfully but NO msgboxes will pop up from the eventhandlers. They are not executing.

Reply here or email direct with any queries!

[Microsoft follow-up] This needs either fixing or explaining.

I think this is a bug. Event handlers are not executed when a package is restarted from a checkpoint. I am not sure if they are related but breakpoints in the package work fine on package restart from a checkpoint. I will file a bug. Thanks for reporting the issue.|||

Thank you for the reply Kaarthik. Its good to know this has got visibility within the team.

-Jamie

|||

Hello Kaarthik.

I'm interested to know whether there has been any progress on this. Can we expect to see it fixed in katmai?

-Jamie

|||

Jamie Thomson wrote:

Hello Kaarthik.

I'm interested to know whether there has been any progress on this. Can we expect to see it fixed in katmai?

-Jamie

Knock knock. Anyone reading?

-Jamie

|||Hey Jamie, we are hoping to fix this in Katmai. Like any other DCR, a big fix or a new feature it is not guaranteed until the implementation and testing is complete. I will remember to update this thread and ask our PM team to inform you when it makes it to Katmai.|||

Cool. Thank you Kaarthik.

-Jamie

Eventhandlers don't work if a package is started from a checkpoint - BIG NASTY BUG (I think)

All,

I'd like someone from MS to confirm something for me if possible.

I've just come across a situation where a package that is restarted from a checkpoint fails to execute any of its eventhandlers if those eventhandlers are scoped to a parent container.

Is this a bug or by design? Either way I'm VERY worried about this because this is not in the least bit intuitive. This is the most dangerous bug that I have seen in RTM.

-Jamie

If you want a demo of this happening then download this package: http://blogs.conchango.com/Admin/ImageGallery/blogs.conchango.com/jamie.thomson/20060103Package1.zip

It doesn't need any configuration because there are no connection managers. You can just run it standalone. Run it first time and it will fail but you WILL see msgboxes popped up by the eventhandlers. Go into "Script Task 2" and make the return value "Success" rather than "Failure". Run it a second time. The package will complete successfully but NO msgboxes will pop up from the eventhandlers. They are not executing.

Reply here or email direct with any queries!

[Microsoft follow-up] This needs either fixing or explaining.

I think this is a bug. Event handlers are not executed when a package is restarted from a checkpoint. I am not sure if they are related but breakpoints in the package work fine on package restart from a checkpoint. I will file a bug. Thanks for reporting the issue.|||

Thank you for the reply Kaarthik. Its good to know this has got visibility within the team.

-Jamie

|||

Hello Kaarthik.

I'm interested to know whether there has been any progress on this. Can we expect to see it fixed in katmai?

-Jamie

|||

Jamie Thomson wrote:

Hello Kaarthik.

I'm interested to know whether there has been any progress on this. Can we expect to see it fixed in katmai?

-Jamie

Knock knock. Anyone reading?

-Jamie

|||Hey Jamie, we are hoping to fix this in Katmai. Like any other DCR, a big fix or a new feature it is not guaranteed until the implementation and testing is complete. I will remember to update this thread and ask our PM team to inform you when it makes it to Katmai.|||

Cool. Thank you Kaarthik.

-Jamie