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

Beginning SSIS - Sharepoint and Excel

Hi all, I have never used SSIS before, but am looking to use it in this aspect:

1) A user uploads an Excel file into Sharepoint. This will be through a Document Library in a Sharepoint page.

2) When this file is uploaded, I'd like SSIS to notice there is a new file and process it - it will pull information from the Excel file, put it into a database, and - if this is possible - delete the new file.

3) This is iffy. Can SSIS then generate an InfoPath document from the information stored in the database? If not, I can just have a InfoPath query form.

I'd just like to know if this is possible. If you have any helpful links, please let me know - I would greatly appreciate it!

Thanks,

James

James,

2) SSIS can do all of this. You can use the FileWatcher task as provided on SQLIS.com. Deleting the file is easy.

3) No idea. If there is an API for InfoPath then it should be possible.

-Jamie

|||Thanks for the reply, Jamie. I'll take a look at that.|||

Alright, well I've taken a look and I'm having some issues -

In Control Flow, I have the file watcher watching a directory for any new Excel files and the output of the filewatcher is User::NewExcelFile, which I understand will be a string with the information of the type "file.xls".

I also have an Excel connection manager which points to the format of the Excel file that will be used. Then I have a SQL server destination that is mapped to the database the information will be put to. How does the File Watcher control flow task pass the information of the filename to the Excel/SQL data flow tasks? They don't allow variable inputs.

Thanks,

James

|||

The Excel source component uses an Excel Connection Manager which has a property called ExcelFilePath that contains the name and location of the file.

You can set this property using a property expression. (Select the connection manager, press F4, expand 'Expressions' in the properties pane). Set it to the value returned by the File Watcher.

-Jamie

|||

Thanks Jamie, that works - I assume as File watcher notices a new file, it triggers the following control/data flow.

Is there a way to set the Excel file's name to be something dynamic? For example, to watch for an Excel file of any name, not just the name of the Excel file specified in the Excel connection manager? File watcher appears to only watch a path, which it can pass to the connection manager when triggered by a new file upload, but the Excel Connection Manager needs a static Excel name.

And what is the task for deleting the Excel upon completion of the service?

Thanks for your time! I am new to SSIS and coming to grips with it :)

-James

EDIT:

Sorry, let me rephrase:

I set the File Watcher Output Variable Name to "User::NewExcelFile" - I assume that this contains either the path to the containing folder OR the containing folder + the name of the file.

If it is the path only, then how can I set the Excel Connection manager to get the name of the file as well?

And if it is the path and name, how do I parse the @.User::NewExcelFile to put the file path in ExcelFilePath and the name in Name?

Also, when the ExcelFilePath is set to @.User::NewExcelFile, I cannot set and save the file path in the Excel Connection Manager. It clears the field - this does not let me map the Excel field to the Sql destination.

|||

James,

The output variable will contain the path and the name of the file.

You need to put the whole thing (the path and the name) into ExcelFilePath property. So there's no need to parse it. The Name property is just the name of the connection manager. Its irrelevant.

Regarding the last point, the reason the field is cleared is because there is nothing in @.User::NewExcelFile. Put a path in that variable to an excel file that has the same metadata as the file you will be processing at runtime. This value is only used at design-time as it will be overwritten at runtime by the FileWatcher Task.

-Jamie

|||

I used file system watcher to read excel on my pc it worked fine, but when I tried to read the excel from SharePoint it did't work. The FileWatcher box showing the yellow color for long time that I had to stop the ssis.

So my question what is the cause of this. Do i need to set something or am i missing something? Please help.

Beginning SSIS - Sharepoint and Excel

Hi all, I have never used SSIS before, but am looking to use it in this aspect:

1) A user uploads an Excel file into Sharepoint. This will be through a Document Library in a Sharepoint page.

2) When this file is uploaded, I'd like SSIS to notice there is a new file and process it - it will pull information from the Excel file, put it into a database, and - if this is possible - delete the new file.

3) This is iffy. Can SSIS then generate an InfoPath document from the information stored in the database? If not, I can just have a InfoPath query form.

I'd just like to know if this is possible. If you have any helpful links, please let me know - I would greatly appreciate it!

Thanks,

James

James,

2) SSIS can do all of this. You can use the FileWatcher task as provided on SQLIS.com. Deleting the file is easy.

3) No idea. If there is an API for InfoPath then it should be possible.

-Jamie

|||Thanks for the reply, Jamie. I'll take a look at that.|||

Alright, well I've taken a look and I'm having some issues -

In Control Flow, I have the file watcher watching a directory for any new Excel files and the output of the filewatcher is User::NewExcelFile, which I understand will be a string with the information of the type "file.xls".

I also have an Excel connection manager which points to the format of the Excel file that will be used. Then I have a SQL server destination that is mapped to the database the information will be put to. How does the File Watcher control flow task pass the information of the filename to the Excel/SQL data flow tasks? They don't allow variable inputs.

Thanks,

James

|||

The Excel source component uses an Excel Connection Manager which has a property called ExcelFilePath that contains the name and location of the file.

You can set this property using a property expression. (Select the connection manager, press F4, expand 'Expressions' in the properties pane). Set it to the value returned by the File Watcher.

-Jamie

|||

Thanks Jamie, that works - I assume as File watcher notices a new file, it triggers the following control/data flow.

Is there a way to set the Excel file's name to be something dynamic? For example, to watch for an Excel file of any name, not just the name of the Excel file specified in the Excel connection manager? File watcher appears to only watch a path, which it can pass to the connection manager when triggered by a new file upload, but the Excel Connection Manager needs a static Excel name.

And what is the task for deleting the Excel upon completion of the service?

Thanks for your time! I am new to SSIS and coming to grips with it :)

-James

EDIT:

Sorry, let me rephrase:

I set the File Watcher Output Variable Name to "User::NewExcelFile" - I assume that this contains either the path to the containing folder OR the containing folder + the name of the file.

If it is the path only, then how can I set the Excel Connection manager to get the name of the file as well?

And if it is the path and name, how do I parse the @.User::NewExcelFile to put the file path in ExcelFilePath and the name in Name?

Also, when the ExcelFilePath is set to @.User::NewExcelFile, I cannot set and save the file path in the Excel Connection Manager. It clears the field - this does not let me map the Excel field to the Sql destination.

|||

James,

The output variable will contain the path and the name of the file.

You need to put the whole thing (the path and the name) into ExcelFilePath property. So there's no need to parse it. The Name property is just the name of the connection manager. Its irrelevant.

Regarding the last point, the reason the field is cleared is because there is nothing in @.User::NewExcelFile. Put a path in that variable to an excel file that has the same metadata as the file you will be processing at runtime. This value is only used at design-time as it will be overwritten at runtime by the FileWatcher Task.

-Jamie

|||

I used file system watcher to read excel on my pc it worked fine, but when I tried to read the excel from SharePoint it did't work. The FileWatcher box showing the yellow color for long time that I had to stop the ssis.

So my question what is the cause of this. Do i need to set something or am i missing something? Please help.

beginner in SQL Server 2000

Hi,
i'm a beginner in sql server 2000 and i have a question.
i want to store word, excel, pdf etc documents in order to search them
for specific content. i don't want to get as result lines of the
documents but the names of them.What data type i must use? can i search
and for Metadata information and how? i will really appreciate any
answer.
Thanks in advance.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
use the image datatype.
Have a look at
http://www.indexserverfaq.com/blobs.htm for more information on how to do
this.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"bill stam" <balisx@.in.gr> wrote in message
news:OWRhJIeGFHA.2156@.TK2MSFTNGP09.phx.gbl...
> Hi,
> i'm a beginner in sql server 2000 and i have a question.
> i want to store word, excel, pdf etc documents in order to search them
> for specific content. i don't want to get as result lines of the
> documents but the names of them.What data type i must use? can i search
> and for Metadata information and how? i will really appreciate any
> answer.
> Thanks in advance.
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||Bill,
Yes, you can store word, excel, pdf etc documents in order to search them
for specific content (as in Full-text) the contents of the MS Word files
stored in an SQL Table's FT-enable IMAGE column. As you're are using SQL
Sever 2000, you will need to add a "file extension" column to your table and
populate it with the file extension value, for example "doc" or ".doc" for
MS Word documents. Note, this "file extension" column must be defined as
sysname, char(3) or varchar(4) in order for the MSSearch service to
correctly identify the file type to be indexed.
Additionally, below is a SQL script example of using TextCopy.exe (ships
with SQL Server 2000) to import MS Word documents into SQL Sever table and
IMAGE column and then using SQLFTS CONTAINS or FREETEXT to search the
contents of that MS word document:
use pubs
go
if exists (select * from sysobjects where id = object_id('FTSTable'))
drop table FTSTable
go
CREATE TABLE FTSTable (
KeyCol int IDENTITY (1,1) NOT NULL
CONSTRAINT FTSTable_IDX PRIMARY KEY CLUSTERED,
TextCol text NULL,
ImageCol image NULL,
ExtCol char(3) NULL, -- can be either sysname or char(3)
TimeStampCol timestamp NULL
) ON [PRIMARY]
go
-- Insert data... (Note: Initalizing IMAGE column with 0xFFFFFFFF for use
with TextCopy.exe)
INSERT FTSTable values('Test TEXT Data for row 1', 0xFFFFFFFF, 'doc', NULL)
go
-- Select data
SELECT * from FTSTable
go
declare @.query varchar(200)
-- Insert MS_Word_document.doc into Row 5 !!
-- NOTE: Ensure the correct path for textcopy.exe!!
set @.query = 'D:\MSSQL80\MSSQL$SQL80\Binn\textcopy /s '+@.@.servername+' /u sa
/p<password> /d pubs /t FTSTable /c ImageCol /f
D:\SQLFiles\Shiloh\<MS_Word>.doc /i /k 5000 /w "where KeyCol=1"'
print @.query
exec master..xp_cmdshell @.query
go
-- Select data
SELECT * from FTSTable
go
-- FTI
use pubs
go
exec sp_fulltext_database 'enable' -- only do this once!
go
-- Drop FTI, if necessary...
-- exec sp_fulltext_table 'FTSTable','drop'
-- exec sp_fulltext_Catalog 'FTSCatalog','drop'
exec sp_fulltext_catalog 'FTSCatalog','create'
exec sp_fulltext_table 'FTSTable','create','FTSCatalog','FTSTable_IDX'
exec sp_fulltext_column 'FTSTable','ImageCol','add', 0x0409, 'ExtCol'
exec sp_fulltext_column 'FTSTable','TextCol','add'
exec sp_fulltext_table 'FTSTable', 'activate'
go
-- Start FT Indexing...
exec sp_fulltext_catalog 'FTSCatalog','start_full'
go
-- Wait for FT Indexing to complete and check NT/Win2K Application log for
success/errors..
-- Search for search_word_here in MS_Word
select KeyCol, ImageCol from FTSTable where
contains(*,'<search_word_in_MS_Word_here>') order by KeyCol
go
-- Search for search_word_here in MS_Word file...
select KeyCol, ImageCol from FTSTable where
freetext(*,'<search_word_in_MS_Word_here>') order by KeyCol
go
-- Remove FT Indexes & Catalog & table..
exec sp_fulltext_table 'FTSTable','drop'
exec sp_fulltext_Catalog 'FTSCatalog','drop'
drop table FTSTable
Note, that to FT Search the Metadata info contained in the documents, such
as author, title, you wll need to programmaticlly extract this information
and store it in a textual column and then FT Index these columns in addition
to the document column.
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"bill stam" <balisx@.in.gr> wrote in message
news:OWRhJIeGFHA.2156@.TK2MSFTNGP09.phx.gbl...
> Hi,
> i'm a beginner in sql server 2000 and i have a question.
> i want to store word, excel, pdf etc documents in order to search them
> for specific content. i don't want to get as result lines of the
> documents but the names of them.What data type i must use? can i search
> and for Metadata information and how? i will really appreciate any
> answer.
> Thanks in advance.
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!