Best practise/architecture question

I need to load a lot of Excel, CSV, ... etc. files. These files have hundreds of columns and I need to validate the data. Some are simple range type checking, some are more complex checking involve multiple columns.

There may have several hundreds of such rules. And I may need to let the program to automatically correct some invalid data in the future.

Where to implement it in SSIS?
Or just load the files without any checking (all type to text), and checking using T-SQL?

(BTW, I don't have biztalk server).

Thanks in advance.

Read more >> Options >>

SSIS has seevral components in the data flow that can help you in the data cleansing/transformation: Derived column, script tasks, etc; so if you have to meve the data from point a to point B; you could apply all the transformation rules as a part of that process.

Based on the litle information I got, I would try with first with SSIS.

|||

I will use SSIS problem. However, I don't want to "hard code" all these rules using SSIS component because

1. There are so many rules. And rules may need to be updated.

2. User will manage the rules. It's not possible to teach them to use VS.Net to update the SSIS package

Maybe a script component to call a C# assembly, which maintain parse rules in a, for example, XML file....

|||

We have a similar issue - we have about 6-8 flat file types, with 50-80 columns each and one-lots of rules for each column. The approach I'm taking is a hybrid. I'm first loading into a catch-all (all characters) staging database, and then using isdate, isnumeric, etc. in TSQL to then pull the data back out into SSIS to do some of the things that SSIS is good at. Where isdate & isnumeric fails on the extract, I'm putting a generic error value in using a CASE in my select (either -1, or '9999-12-31' for dates) so I can at least get the data cleaned before it heads back into SSIS & the production database. From there I'm doing my multiple column validation, or other "softer" error handling. My guess is you will not be able to give the users something to configure absolutely every validation. For us it's going to be a trade-off - I'm going to enforce the high-level/blatant validations up front and do things that aren't going to change often, while giving the user the ability to configure range validations, etc.

|||

Yes, I was thinking doing the same thing. However, it sounds it ruins the purpose of using SSIS as an ETL tool.

But it sounds not easy to use SSIS as a configurable ETL tool.

|||

ydbn wrote:

Yes, I was thinking doing the same thing. However, it sounds it ruins the purpose of using SSIS as an ETL tool.

But it sounds not easy to use SSIS as a configurable ETL tool.

I think your scenario represent the same challenge regardless the ETL tool you choose. Do you have an example of your ideal solution using a different tool? If you shared that with us, I am pretty sure we could help in getting something similar in SSIS terms.

BTW, what about creating a custom task that gets the rules from a table per every file type? You could store the transformations rules in a table where they can be easily updated.

There are more than one way to tackle this problem; but flexibility won't come for free; if you want something robust an reusable, get ready to spend some time in the design table.

sql

best place to declare/load XSL for use in CLR StoredProc?

How to efficiently load XSL documents which will be used in a CLR SP. I want
to avoid loading it every time the SP is invoked.
Thanks,
ChrisHello ChrisHarrington" charrington-at-activeinterface.com,

> How to efficiently load XSL documents which will be used in a CLR SP.
> I want to avoid loading it every time the SP is invoked.
How about in an table, passing it as a parameter?
Thanks,
Kent Tegels, DevelopMentor
http://staff.develop.com/ktegels/|||Kent,
Thanks for responding. This CLR SP stuff in new to me. Could you elaborate
on your suggestion? Do you mean storing the XSL in an XML column in a table?
Thanks,
Chris
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad7452758c85c8a7518f6a0@.news.microsoft.com...
> Hello ChrisHarrington" charrington-at-activeinterface.com,
>
> How about in an table, passing it as a parameter?
> --
> Thanks,
> Kent Tegels, DevelopMentor
> http://staff.develop.com/ktegels/
>|||> Thanks for responding. This CLR SP stuff in new to me. Could you
> elaborate on your suggestion? Do you mean storing the XSL in an XML column
in a
> table?
I'll post an example in my blog shortly.
Thanks,
Kent Tegels, DevelopMentor
http://staff.develop.com/ktegels/|||Thanks - that would be very helpful.
Chris
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad7454618c85ce91388c3f0@.news.microsoft.com...
> in a
> I'll post an example in my blog shortly.
>
> --
> Thanks,
> Kent Tegels, DevelopMentor
> http://staff.develop.com/ktegels/
>|||Hello Chris,
Been a bit too busy to post, but here's the gist of it. First, we need a
SQLCLR function that actually does the transformation. Here's that:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Xml;
using System.Xml.Xsl;
using System.IO;
namespace DM.Examples
{
public partial class XmlLibrary
{
[SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic =
false, IsPrecise = false, SystemDataAccess = SystemDataAccessKind.None)]
[return: SqlFacet(IsFixedLength = false, IsNullable = true, MaxSize
= -1)]
public static SqlXml ApplyTransform(SqlXml Data, SqlXml StyleSheet)
{
// on null return null, just in case.
if (Data.IsNull || StyleSheet.IsNull)
return SqlXml.Null;
// Buffer the transformed xml
MemoryStream ms = new MemoryStream();
XmlWriter xw = XmlWriter.Create(ms);
// Load and transform
XslCompiledTransform ctx = new XslCompiledTransform(false);
ctx.Load(StyleSheet.CreateReader());
ctx.Transform(Data.CreateReader(), xw);
// return the result, assuming XML compliant output
return new SqlXml(ms);
}
}
}
here's some code I wrote to test that:
declare @.d xml, @.s xml
select @.d = (select productID as '@.dbid',ProductNumber as '@.productID',Name
as 'name',Color as 'color',ListPrice as 'listPrice',Size as 'Size',SizeUnitM
easureCode
as 'sizeCode',style as 'style' from adventureworks.production.product where
not(coalesce(discontinuedDate,'2999-12-31') = 1) and FinishedGoodsFlag =
1 for xml path('product'),root('products'),element
s xsinil,type)
select @.s = bulkcolumn from openrowset(bulk 'c:\simple.xslt',single_clob)
as p
select dbo.ApplyTransform(@.d,@.s)
the "c:\simple.xlst" is left as an excercise for the reader.
Cheers,
Kent Tegels, DevelopMentor
http://staff.develop.com/ktegels/|||Hi Kent,
Thanks for the code sample. But what I am really stumped on is how to have
the xsl available as a class static so that it doesn't have to be loaded and
compiled every time the SP is invoked. Any thoughts?
Chris
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad745b3b8c85df2965c9c40@.news.microsoft.com...
> Hello Chris,
> Been a bit too busy to post, but here's the gist of it. First, we need a
> SQLCLR function that actually does the transformation. Here's that:
> using System;
> using System.Data;
> using System.Data.SqlClient;
> using System.Data.SqlTypes;
> using Microsoft.SqlServer.Server;
> using System.Xml;
> using System.Xml.Xsl;
> using System.IO;
> namespace DM.Examples
> {
> public partial class XmlLibrary
> {
> [SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic =
> false, IsPrecise = false, SystemDataAccess = SystemDataAccessKind.None)]
> [return: SqlFacet(IsFixedLength = false, IsNullable = true, MaxSize
> = -1)]
> public static SqlXml ApplyTransform(SqlXml Data, SqlXml StyleSheet)
> {
> // on null return null, just in case.
> if (Data.IsNull || StyleSheet.IsNull)
> return SqlXml.Null;
> // Buffer the transformed xml
> MemoryStream ms = new MemoryStream();
> XmlWriter xw = XmlWriter.Create(ms);
> // Load and transform
> XslCompiledTransform ctx = new XslCompiledTransform(false);
> ctx.Load(StyleSheet.CreateReader());
> ctx.Transform(Data.CreateReader(), xw);
> // return the result, assuming XML compliant output
> return new SqlXml(ms);
> }
> }
> }
> here's some code I wrote to test that:
> declare @.d xml, @.s xml
> select @.d = (select productID as '@.dbid',ProductNumber as
> '@.productID',Name as 'name',Color as 'color',ListPrice as 'listPrice',Size
> as 'Size',SizeUnitMeasureCode as 'sizeCode',style as 'style' from
> adventureworks.production.product where
> not(coalesce(discontinuedDate,'2999-12-31') = 1) and FinishedGoodsFlag = 1
> for xml path('product'),root('products'),element
s xsinil,type)
> select @.s = bulkcolumn from openrowset(bulk 'c:\simple.xslt',single_clob)
> as p
> select dbo.ApplyTransform(@.d,@.s)
> the "c:\simple.xlst" is left as an excercise for the reader.
> Cheers,
> Kent Tegels, DevelopMentor
> http://staff.develop.com/ktegels/
>

Benchmarking Tools

Hello
Does anyone know of any benchmarking tools to perform load testing and
performance benchmarking on SQL 2005 and SQL 2000 on 32-bit and
64-bit?
I was looking at Benchmark Factory from Quest Software but it does not
seem to work with 64-bit.
Thanks
Sameer
> I was looking at Benchmark Factory from Quest Software but it does not
> seem to work with 64-bit.
You should verify with Quest to determine whether that's the case. My
understanding is that it's a client app, and you don't have to run it on the
server itself (shouldn't run it on server as a matter of fact). You can
always run it on a 32-bit machine adn access a remote SQL instance. So I'd be
surprised if it doesn't work with an x64 SQL2005 instance.
Linchi
"Sameer" wrote:

> Hello
> Does anyone know of any benchmarking tools to perform load testing and
> performance benchmarking on SQL 2005 and SQL 2000 on 32-bit and
> 64-bit?
> I was looking at Benchmark Factory from Quest Software but it does not
> seem to work with 64-bit.
> Thanks
> Sameer
>

Benchmarking Tools

Hello
Does anyone know of any benchmarking tools to perform load testing and
performance benchmarking on SQL 2005 and SQL 2000 on 32-bit and
64-bit?
I was looking at Benchmark Factory from Quest Software but it does not
seem to work with 64-bit.
Thanks
Sameer
> I was looking at Benchmark Factory from Quest Software but it does not
> seem to work with 64-bit.
You should verify with Quest to determine whether that's the case. My
understanding is that it's a client app, and you don't have to run it on the
server itself (shouldn't run it on server as a matter of fact). You can
always run it on a 32-bit machine adn access a remote SQL instance. So I'd be
surprised if it doesn't work with an x64 SQL2005 instance.
Linchi
"Sameer" wrote:

> Hello
> Does anyone know of any benchmarking tools to perform load testing and
> performance benchmarking on SQL 2005 and SQL 2000 on 32-bit and
> 64-bit?
> I was looking at Benchmark Factory from Quest Software but it does not
> seem to work with 64-bit.
> Thanks
> Sameer
>

Benchmarking Tools

Hello
Does anyone know of any benchmarking tools to perform load testing and
performance benchmarking on SQL 2005 and SQL 2000 on 32-bit and
64-bit?
I was looking at Benchmark Factory from Quest Software but it does not
seem to work with 64-bit.
Thanks
Sameer> I was looking at Benchmark Factory from Quest Software but it does not
> seem to work with 64-bit.
You should verify with Quest to determine whether that's the case. My
understanding is that it's a client app, and you don't have to run it on the
server itself (shouldn't run it on server as a matter of fact). You can
always run it on a 32-bit machine adn access a remote SQL instance. So I'd b
e
surprised if it doesn't work with an x64 SQL2005 instance.
Linchi
"Sameer" wrote:

> Hello
> Does anyone know of any benchmarking tools to perform load testing and
> performance benchmarking on SQL 2005 and SQL 2000 on 32-bit and
> 64-bit?
> I was looking at Benchmark Factory from Quest Software but it does not
> seem to work with 64-bit.
> Thanks
> Sameer
>