Having Different Versions of Crystal Reports (8,9) in the same web application

See, We have a web application which is developed using Webclass Designer and currently runs on IIS 4.0 and IIS 5.0. It also contains a Reports Menu thorugh which the end user sees the data. These reports are mainly displayed using Crystal Reports 8.0.0.371. There are around 110 reports. The client wants specifically only couple of these reports to be migrated to version 9.0 leaving the rest. The reports are mainly opened, passed with SQL query etc in a single class file. So this is common for all the reports. Just that the object of this class file is created in every instance. The reference of CRAXDRT.dll of version 8 is added as a reference in this class file so that objects can be created.



Now my question is , is it possible to have two different versions of Crystal Reports in the same application. It means that ideally the support files are different. Also , it will be not possible for the report’s class file present in VB to refer both 8 and 9 at the same time. This is what I feel but as usual the client needs some support docs if this is true. Hence I need ur expert opinion on this.


Any help in this issue ...Please share ur expert opinionsHi,
I wish to do the same, did you manage to solve this problem, or does any1 else have any input?

Having conversion issues - String to Float

I am running into some issues with conversion and would appreciate someone who can help on this.

I need to roll up a column which contains numeric (float) data but it is stored in a varchar field.

I was trying to do the following (please note that Activity field is varchar(50) in MyTable):

SELECT CONVERT(float, (NULLIF(Activity,0))) from MyTable

and i get the following error.

Msg 245, Level 16, State 1, Line 1

Conversion failed when converting the varchar value '-39862.8' to data type int.

I even tried with case statement (as below) but still got same issue.

SELECT CASE ISNUMERIC(NULLIF(Activity,0))

WHEN 1 THEN

CONVERT(float, (NULLIF(Activity,0)))

ELSE 0

END

from MyTable

Thanks,

Ashish

Try using 0.0 rather than 0 in the NULLIF functions. Also, do you mean to use ISNULL, or NULLIF? NULLIF returns a NULL, if the expressions are equal, while ISNULL returns the second argument if the first is NULL.
|||Yes. I want to use ISNULL. I changed it but I still get the error. Even with '0.0'|||What happens when you run the following:

Code Snippet

select *

from MyTable

where isnumeric(activity) = 0

|||SELECT CONVERT(float, (NULLIF(Activity,0))) from MyTable where isnumeric(Activity)=1|||

Are you sure that you want to use NULLIF()? (I would think that ISNULL() would be a better option.)

Also note that the isnumeric() test will PASS because '-39862.8' will always test to be a number.

This works as you want.

Code Snippet

DECLARE @.Activity varchar(50)

SET @.Activity = '-39862.8'

SELECT cast( isnull( @.Activity, 0 ) AS float )

--
-39862.800000000003

|||Good point about isnull(), Arnie.

Having a drilldown report "go back" to original report

Hi,

I have a main/summary report which returns a list of records. Each record on this main report contains a link to a detailed report for more information on that particular record. That all works just fine. But on my detailed report, I want to provide a button to allow the user to easily "go back" to the original report.

I noticed that at the top of my detailed report is a toolbar, with icons to print, search, etc., so this seems like the place where it would be nice to have another icon with the back arrow that would be linked to the original report. The user can use the Back button on the browser, but I think it would be much nicer to provide a Back button on the report itself.

Is there any easy way to go about doing this?
Can anyone give me any pointers?

Thanks,
Beth

The new ReportViewer controls in VS 2005 have this functionality. The button is hidden by default in the ReportViewer web control, but you can enable it by setting ReportViewer.ShowBackButton = true.

|||Oh, my fault - I forgot to mention that I am using SQL Server Reporting Service 2000 and VS 2003.

Is there a different mechanism to use for this configuration?

Thanks,
Beth|||There is no way to do this from within RS 2000.|||Ok, thanks for the info.

Beth|||

One thing I have done is to use a text box on the last report and then then put some text in there, like "Back to Original Report -->" and then navigate to the original report. The users don't seem to mind.

SHP

|||

where to add this code to get the backbutton..Please tell me procedure to add back button by this method..

Thanks a lot.

|||

I've done it on a few of my reports. In my case i save my where clause in a parameter called @.Previous and pass it to the drilldown report. Then in my drilldown report i have a textbox which i have set to call my original report with @.Previous parameter. To make i look more like a button i've given the textbox a picture background.

Basically i am running the original report again and passing it its original parameters. Its not quite like adding a button to the toolbar, but it works. Hope this helps.

|||

Thanks dear. But i know it works at the same time it takes me to the original refreshed report with default parameters. I want the button so that the user can go to the exactly previous page. Also if the back button can appear in the VS then it should come in report manager too.

Please somebody let me know the procedure.

|||

Does anyone know how/where to set the ReportViewer.ShowBackButton = True for reportmanager.

|||While Report Manager does use a version of the viewer control internally, it doesn't expose the viewer API to the end user, so there is no way to enable this button through Report Manager. It is only available on the standalone control.|||

Brian,

Is this something that will be included in a Hotfix / SP? Going back to the parent report by using the Browser back button just causes the parent report to sit there and not process. Obviously a usabilty issue ( I can get around for now by using Jump to URL and forcing a new window to pop-up but it would be better not to have to go that route).

|||

I send my report parameters directly from my UI and pass them all into a parameter called @.Criteria in my report (In my reports i pass @.Criteria into the WHERE clause in the query) ). And this is the parameter i pass to my drill-through report to a parameter @.Previous. My back button then calls the original report with exactly the same parameters (@.Previous is passed into the where clause in the original report).

Of course there is a hard way to do it. Pass all the original report parameters to the drill through report to dummy parameters which aren't used and then when you press the back button send all the same parameters back to get your orginal report.

|||

I'm not sure if this is what everyone is after but perhaps this will help. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1327767&SiteID=1

|||

Dear all

Please provide me the steps to create a drilled down report in report server 2005 . I am a begginer in reportserver ..

regards

Polachan

Having a drilldown report "go back" to original report

Hi,

I have a main/summary report which returns a list of records. Each record on this main report contains a link to a detailed report for more information on that particular record. That all works just fine. But on my detailed report, I want to provide a button to allow the user to easily "go back" to the original report.

I noticed that at the top of my detailed report is a toolbar, with icons to print, search, etc., so this seems like the place where it would be nice to have another icon with the back arrow that would be linked to the original report. The user can use the Back button on the browser, but I think it would be much nicer to provide a Back button on the report itself.

Is there any easy way to go about doing this?
Can anyone give me any pointers?

Thanks,
Beth

The new ReportViewer controls in VS 2005 have this functionality. The button is hidden by default in the ReportViewer web control, but you can enable it by setting ReportViewer.ShowBackButton = true.

|||Oh, my fault - I forgot to mention that I am using SQL Server Reporting Service 2000 and VS 2003.

Is there a different mechanism to use for this configuration?

Thanks,
Beth|||There is no way to do this from within RS 2000.|||Ok, thanks for the info.

Beth|||

One thing I have done is to use a text box on the last report and then then put some text in there, like "Back to Original Report -->" and then navigate to the original report. The users don't seem to mind.

SHP

|||

where to add this code to get the backbutton..Please tell me procedure to add back button by this method..

Thanks a lot.

|||

I've done it on a few of my reports. In my case i save my where clause in a parameter called @.Previous and pass it to the drilldown report. Then in my drilldown report i have a textbox which i have set to call my original report with @.Previous parameter. To make i look more like a button i've given the textbox a picture background.

Basically i am running the original report again and passing it its original parameters. Its not quite like adding a button to the toolbar, but it works. Hope this helps.

|||

Thanks dear. But i know it works at the same time it takes me to the original refreshed report with default parameters. I want the button so that the user can go to the exactly previous page. Also if the back button can appear in the VS then it should come in report manager too.

Please somebody let me know the procedure.

|||

Does anyone know how/where to set the ReportViewer.ShowBackButton = True for reportmanager.

|||While Report Manager does use a version of the viewer control internally, it doesn't expose the viewer API to the end user, so there is no way to enable this button through Report Manager. It is only available on the standalone control.|||

Brian,

Is this something that will be included in a Hotfix / SP? Going back to the parent report by using the Browser back button just causes the parent report to sit there and not process. Obviously a usabilty issue ( I can get around for now by using Jump to URL and forcing a new window to pop-up but it would be better not to have to go that route).

|||

I send my report parameters directly from my UI and pass them all into a parameter called @.Criteria in my report (In my reports i pass @.Criteria into the WHERE clause in the query) ). And this is the parameter i pass to my drill-through report to a parameter @.Previous. My back button then calls the original report with exactly the same parameters (@.Previous is passed into the where clause in the original report).

Of course there is a hard way to do it. Pass all the original report parameters to the drill through report to dummy parameters which aren't used and then when you press the back button send all the same parameters back to get your orginal report.

|||

I'm not sure if this is what everyone is after but perhaps this will help. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1327767&SiteID=1

|||

Dear all

Please provide me the steps to create a drilled down report in report server 2005 . I am a begginer in reportserver ..

regards

Polachan

Having a drilldown report "go back" to original report

Hi,

I have a main/summary report which returns a list of records. Each record on this main report contains a link to a detailed report for more information on that particular record. That all works just fine. But on my detailed report, I want to provide a button to allow the user to easily "go back" to the original report.

I noticed that at the top of my detailed report is a toolbar, with icons to print, search, etc., so this seems like the place where it would be nice to have another icon with the back arrow that would be linked to the original report. The user can use the Back button on the browser, but I think it would be much nicer to provide a Back button on the report itself.

Is there any easy way to go about doing this?
Can anyone give me any pointers?

Thanks,
Beth

The new ReportViewer controls in VS 2005 have this functionality. The button is hidden by default in the ReportViewer web control, but you can enable it by setting ReportViewer.ShowBackButton = true.

|||Oh, my fault - I forgot to mention that I am using SQL Server Reporting Service 2000 and VS 2003.

Is there a different mechanism to use for this configuration?

Thanks,
Beth|||There is no way to do this from within RS 2000.|||Ok, thanks for the info.

Beth|||

One thing I have done is to use a text box on the last report and then then put some text in there, like "Back to Original Report -->" and then navigate to the original report. The users don't seem to mind.

SHP

|||

where to add this code to get the backbutton..Please tell me procedure to add back button by this method..

Thanks a lot.

|||

I've done it on a few of my reports. In my case i save my where clause in a parameter called @.Previous and pass it to the drilldown report. Then in my drilldown report i have a textbox which i have set to call my original report with @.Previous parameter. To make i look more like a button i've given the textbox a picture background.

Basically i am running the original report again and passing it its original parameters. Its not quite like adding a button to the toolbar, but it works. Hope this helps.

|||

Thanks dear. But i know it works at the same time it takes me to the original refreshed report with default parameters. I want the button so that the user can go to the exactly previous page. Also if the back button can appear in the VS then it should come in report manager too.

Please somebody let me know the procedure.

|||

Does anyone know how/where to set the ReportViewer.ShowBackButton = True for reportmanager.

|||While Report Manager does use a version of the viewer control internally, it doesn't expose the viewer API to the end user, so there is no way to enable this button through Report Manager. It is only available on the standalone control.|||

Brian,

Is this something that will be included in a Hotfix / SP? Going back to the parent report by using the Browser back button just causes the parent report to sit there and not process. Obviously a usabilty issue ( I can get around for now by using Jump to URL and forcing a new window to pop-up but it would be better not to have to go that route).

|||

I send my report parameters directly from my UI and pass them all into a parameter called @.Criteria in my report (In my reports i pass @.Criteria into the WHERE clause in the query) ). And this is the parameter i pass to my drill-through report to a parameter @.Previous. My back button then calls the original report with exactly the same parameters (@.Previous is passed into the where clause in the original report).

Of course there is a hard way to do it. Pass all the original report parameters to the drill through report to dummy parameters which aren't used and then when you press the back button send all the same parameters back to get your orginal report.

|||

I'm not sure if this is what everyone is after but perhaps this will help. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1327767&SiteID=1

|||

Dear all

Please provide me the steps to create a drilled down report in report server 2005 . I am a begginer in reportserver ..

regards

Polachan

Handling flat files that do not exist

Hello,

I have a package that contains 22 data flow tasks, one for each flat file that I need to process and import. I decided against making each import a seperate package because I am loading the package in an external application and calling it from there.

Now, everything works beautifully when all my text files are exported from a datasource beyond my control. I have an application that processes a series of files encoded using EBCDIC and I am not always gauranteed that all the flat files will be exported. (There may have not been any data for the day.)

I am looking for suggestions on how to handle files that do not exist. I have tried making a package level error handler (Script task) that checks the error code ("System::ErrorCode") and if it tells me that the file cannot be found, I return Dts.TaskResult = Dts.Results.Sucsess, but that is not working for me, the package still fails. I have also thought about progmatically disabling the tasks that do not have a corresponding flat file, but it seems like over kill.

So I guess my question is this; if the file does not exist, how can I either a) skip the task in the package, or b) quietly handle the error and move on without failing the package?

Thanks!

Lee.

I recall that I have a couple data flows in my package, each within a sequence container and connected them with completion(blue) arrows and even if the first one failed the second one continued on...

|||

That is one solution that I could use. It doesn't seem like it is the "elegant" way to handle it, but it will work.

Thanks for the suggestion.

|||

well

Is dragging red arrow to some alert/send mail task will not serve purpose?

|||

Scoutn wrote:

Hello,

I have a package that contains 22 data flow tasks, one for each flat file that I need to process and import. I decided against making each import a seperate package because I am loading the package in an external application and calling it from there.

Now, everything works beautifully when all my text files are exported from a datasource beyond my control. I have an application that processes a series of files encoded using EBCDIC and I am not always gauranteed that all the flat files will be exported. (There may have not been any data for the day.)

I am looking for suggestions on how to handle files that do not exist. I have tried making a package level error handler (Script task) that checks the error code ("System::ErrorCode") and if it tells me that the file cannot be found, I return Dts.TaskResult = Dts.Results.Sucsess, but that is not working for me, the package still fails. I have also thought about progmatically disabling the tasks that do not have a corresponding flat file, but it seems like over kill.

So I guess my question is this; if the file does not exist, how can I either a) skip the task in the package, or b) quietly handle the error and move on without failing the package?

Thanks!

Lee.

You can use a script task to check whether or not a file exists. This code should help:

File.Exists Method
(http://msdn2.microsoft.com/en-us/library/system.io.file.exists.aspx)

-Jamie

|||

Jamie,

First allow me to say that your blog has been an invaluable resource. I have learned a lot from your articles, thank you!

Utsav, I am not looking to be notified of the error; I just want the task to merrily carry on like nothing took place. I do understand what you meant though, thank you.

Sometimes the flat file will be there and sometimes it won't be. If it isn't, I would still like the task move on to the next with a "Success" result, not just completion. That is why I was thinking of disabling the tasks that do not have corresponding flat files progmatically when I load the package from my application.

I guess I should be asking; when I set Dts.TaskResult = Dts.Results.Success, why does it not actually return success?

(I know there are a couple of ways I can get around this issue, I'm just looking for the easiest without setting the task to continue on "Completion" ;)

Thanks again,
Lee.

|||

Check if this thread has something that can help you.

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

Instead of disabling the task; try to use an expression in the precedence constraint in the control flow.

Rafael Salas

|||Thank you very much. I did search the forum, I guess I was just using the wrong search term.

Perfect!|||

The thread (and the subsequent blog articles) solved my problem. I needed to put each task in a sequence container with their own script task checking for the files; if I used one script task to look for all the files the next task would not run because at least one file would return false.

Thanks again for everyones help!

Handling errors when using ExecuteNonQuery

Let's say that I have a StringCollection named scripts that contains a bunch of DDL statements I want to execute. So I execute the statements using ExecuteNonQuery as follows:

ExecuteNonQuery(scripts)

If one of the DDL statements contained in the StringCollection returns an error, it means that all subsequent statements will not be executed. Even if I use try catch as follows:

try

{

db.ExecuteNonQuery(scripts);

}

catch (Exception ex)

{

Console.WriteLine("Error while recreating objects. See errorlog for more details\r\n");

tw.Write("Error while recreating objects: {0}\r\n", ex.GetBaseException().ToString());

tw.Flush();

}

Is there any way to keep executing the subsequent statements in the StringCollection even if some of the DDL statements return an error?

If you send them one at a time, handle the error that occurs, then send the next script, you could then continue after an error. DDL errors, however, always make me want to stop, because DML usually depends on the DDL being correct.

|||

Thanks for your answer Allen,

I tried what you suggested but I noticed that none of my stored procs kept their settings. For example:

I script my proc as follows:

if (u.Type == "StoredProcedure")

{

ScriptingOptions so = new ScriptingOptions();

StoredProcedure o = (StoredProcedure)svr.GetSmoObject(u);

so.SchemaQualify = true;

so.SchemaQualifyForeignKeysReferences = true;

so.Permissions = true;

so.ExtendedProperties = true;

so.EnforceScriptingOptions = true;

so.NoCollation = true;

foreach (string os in o.Script(so))

{

sc.Add(os.ToString());

}

}

If I try to execute my statement as follows:

foreach (string s in scripts)

{

db.ExecuteNonQuery(s);

}

Then two statementswill be sent to SQL Server and the ANSI_NULL Settings will not necessarily be maintained:

first statement:

SET ANSI_NULLS ON

Second statement:

CREATE PROCEDURE...

The problem being that if the setting for my second connection is SET ANSI_NULLS OFF then my proc will be created with the wrong settings.

Handling Empty Strings In DTS

Hello,
I have a transformation in which the column of data at the flat file source is nine characters long, and typically contains a string of six or seven zeros with a non-zero number in the last two or three characters. If none of the records in that column were an empty string, I think I could get away with this:

DTSDestination("TTLCrd") = CInt(DTSSource("Col004"))

The destination is a SQL Server 2000 table, and the column is of type Integer. What do I do when Col004 is an empty string? I've tried a couple of different IF statements, but they have not worked. Empty strings need to become zero values.

Thank you for your help.

cdun2Avoid using DTS to insert directly into production tables.
Avoid putting logic or code that manipulates data in your DTS package.
Use DTS for moving data from point A to point B. All the other features of DTS (or the new SSIS) are crap, and lead inevitably to bad application design.

Best practice is to use DTS to pipe your data to a staging table and then kick off a stored procedure to process the data in the staging table, verifying and cleansing the data before pushing it into the production tables. The stored procedure will hold all of the data logic, including the COALESCE() function, which will easily convert your NULL values to zeros.|||Thank you for your response!
cdun2|||Best practice is to use DTS to pipe your data to a staging table and then kick off a stored procedure to process the data in the staging table, verifying and cleansing the data before pushing it into the production tables. The stored procedure will hold all of the data logic, including the COALESCE() function, which will easily convert your NULL values to zeros.
So what you say is that you should have staging tables (potentially with all nvarchar field if you for instance import from text files), generate a whole lot of disk activity, before you kick off stored procs to do all the work for you? I cannot see why one would want to do it that way. I'm pretty satisfied with the way SSIS work, and would very much like to know why you discourage use of the SSIS features.|||I'm totally with blindman on this. My question in turn would be why would you want to incorporate data\ business logic into discrete, proprietry DTS\ SSIS packages? Virtually everything else I do in T-SQL unless there is no alternative. Even if I used DTS or SSIS at all it would be to get stuff from outside the database to inside it with as little fuss as possible - nothing more. I see nothing gained throwing these ETL tools at the problem when the standard SS language is perfectly capable of everything I have come across so far.

You probably know though that I go further even than blindman and do not use DTS or SSIS at all.

As far as the disk usage is concerned, from my perspective I work with large batch systems with well specced servers. We are not on a big time or resource pressure when we load so for me it is not a consideration.|||KISS.
By not creating code in my DTS package, I keep all of my logic in one place(the database) and in one language(SQL). That makes debugging much easier.
It also compartmentalizes my process, meaning I could use DTS, or BCP, or SSIS, or ASP, or a friggin' Access macro to load data, and my sproc will process it. I can even have multiple data flows going into the staging table.
My staging tables contain columns that record the source of each record, the time it entered the database, and a column for recording any processing errors. As my sproc cleanses, verifies, and loads the staging data, any discrepancies are noted within the ErrorStatus column. At the end of the process finding any records that failed and the reasons for their failure is a snap, and all I need to do is fix the existing staging records and reset the ErrorStatus column, and then I can rerun the sproc.
ETL has become bread and butter to me now. I can almost write these sprocs and packages with my eyes closed.
ROAC, how easily did your DTS packages upgrade to SSIS?|||As far as the disk usage is concerned, from my perspective I work with large batch systems with well specced servers. We are not on a big time or resource pressure when we load so for me it is not a consideration.
Well, I see. In that case I would do the same. However, there are many MANY companies around, especially in smaller countries, that cannot afford this kind of systems. The gap between for instance an EVA4000 and EVA6000 is huge for many companies, and they have to take disk performance into consideration.|||KISS.
ROAC, how easily did your DTS packages upgrade to SSIS?
Agreed. Keep things simple. If you are having a lot of data sources, I find it more easy working with SSIS than having tons of procs.

When it comes to upgrading, my DTS packages upgraded pretty well, but I know quite a few that did not, which of course is an issue. However, with almost the same arguments you come up with, you could tell to do the work in ASP.NET or Java as well, and have issues when .NET Framework or Java language is upgraded. Or, for that matter, when SQL Syntax changes.

As I said in my last post, please keep in mind that there are smaller countries and companies in the world. What's best for an enterprise is not neccessarily best for a small or medium sized business in Scandinavia or the Baltics. Thus, I think your advices perhaps should be something like "If you can afford ..., you should ...". Got my idea?

I have no problem seeing your points, I just cannot see it as the only solution.|||Well, I see. In that case I would do the same. However, there are many MANY companies around, especially in smaller countries, that cannot afford this kind of systems. The gap between for instance an EVA4000 and EVA6000 is huge for many companies, and they have to take disk performance into consideration.I might add that the organisation I worked for prior to this was certainly not large, nor specialist like my present company, but I stuck to the same philosophy then. Actually - I think I just did :)

The most expensive resource of all is the bum in the seat supporting the hands keying in the code. This bum is likely to know T-SQL if he\ she is working with SQL Server. Why is it cost effective to introduce a GUI based ETL tool into the mix when bog standard T-SQL is perfectly capable? The worst short term ROI an employer gets from me is when I am wrestling with a new language\ gui\ tool etc.. I think the time aspect of my point might be a pressure for using SSIS\ DTS but not the resources - I'm afraid I have no idea what the difference is between EVA4000 and EVA6000 :)|||ROAC, why do you think my method of storing the data in staging tables and then running a sproc against them is going to entail more disk activity/server resources than an SSIS package?
Presumably the amount of data being imported is some fraction of the data that already exists in production, so if the server is beefy enough to handle day-to-day processing it should not choke on running sprocs against staging data. Especially since these are normally run as batch processes during maintenance hours.|||As the data is written do disk twice instead of once, yes it WILL consume more disk resources. If you are lucky enough to do all the job at night, well then you are definitely more lucky than I am. As I said previously, if you can have the extra disk load, your approach is the best. I'm not questioning that. I just want to make it clear, for you and other reders, that your scenario is not the only one around. Other people may have other needs, which will lead to other solutions. Doubling the disk activity required to import data is not always an option. You are lucky enough to have that option, but is it so hard to believe that other people not neccessarily have the same situation as you?

I think you perhaps should open your eyes a bit and look around, because there are solutions out there, behaving quite differently from those you are working with.|||Disk activity, hmmm..
Well, as a consultant who has created production and data warehouse ETL solutions for dozens of companies in many industries I can't say I've run into a situation where that was the deciding factor in the application architecture. But I'll grant such a situation may be possible.

Obviously I don't think my method is the only way to go. There are certainly many implementations using DTS, SSIS, or 3rd party tools (I recently had to suffer through a project where the client used a tool called DataStage).

The problem is, too many inexperienced people jump into creating DTS/SSIS solutions simply because THEY assume that THAT is the only method. When in reality (and I'm not backing down on this), these GUI tools are SELDOM the best method and lead to fragile designs that are difficult to debug or modify.|||BTW - Roac - totally agreed that everything deserves evaluation in the context of the entire circumstance. There are no absolutes - that is why we are having this fun discussion where we are sharing our opinions :)|||Its all good. <\TouchGloves>|||Lol - Roac - I've just remembered my first post addressing you was when I thought you had made a somewhat absolute statement too. What goes around comes around eh? On that one you were in agreement with blindman.|||I sit on the fence in these issues...

There are many things that I've written in DTS/SSIS where there is simple conversion of incoming data, and as long as there is a well defined way to deal with "bad" data this works very well. The thing that most DTS packages seemed to (incorrectly) assume was that all of the incoming data would be processed correctly on the first try.

When there are enough resources and time, the staging table approach works very well. You need to keep in mind that time is often my biggest problem, and that large amounts of data usually take large amounts of time to process. This can make it functionally impossible to stage some kinds of data because the processing window isn't large enough to support physically handling the data multiple times.

I don't have any inherant problem with either approach. Both work, and with the proper discipline for the ETL approach and sufficient resources (disk, time, etc) for the staging approach they can both produce the same results. Unfortunately, both discipline and resources are often scarce, so you need to find the best solution for the problem at hand.

-PatP