Automate Suggestion

Question

Post reply

Automate Suggestion

Ghanta

Hall of Fame

Points: 3999
More actions
March 31, 2007 at 8:30 am

#68101

Hi guys,

THanks for taking time to comming to this thread and any suggestion is appreciated.

We have clients databases with HR data in sql server 2000. Presently, we use DTS packages (every client has his own package) to update their HR feed every month. What should be the best practice so that burden is minimized from our data-team? The file format sent by the clients are usually text files, but the columns may differ per clients' need. However, the database structure for all clients are same. Any suggestion on this is greatly appreciated.

Thanks,

SB

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Loner SSC-Insane Points: 21279 More actions · Answer 1

Loner

SSC-Insane

Points: 21279

April 3, 2007 at 2:06 pm

#698396

What do you mean by the columns are different for different clients but the database structure is the same?

Do they share the same table?

Ghanta Hall of Fame Points: 3999 More actions · Answer 2

Thanks Loner. The database structure is same and we have different db for every client. The data file they send us are not of same format and data they provide depends on the feature of the application they have bought or are using. I guess the best way to proceed on this is to provide them again with data-specifications and data mapping documents so that they send us one kind of file... Our company has worked with whatever they had provided in the past and now this is the problem as the client-tale has grown so much in the recent years.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

Yep... you're correct... require a particular format. I recommend TAB delimited files so that quotes and commas can be included as part of the data, if the customer desires.

And, I'm not sure why you think you need DTS to do this...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Loner SSC-Insane Points: 21279 More actions · Answer 4

You can still make it as dynamic as possible, writing a procedure to do it is much easier. I just spent a few minutes to think about it so it may not be perfect. Also if you can at least ask all the clients to sent the text file to you in delimited or fixed length format. It would help.

Client_table - ClientID, ClientFileID, FileName, FileLocation

Client_File_table - ClientFileID, FieldName, DataType, Sequence, Matching Database/Table Name, Matching Column Name.

ClientFileID is primary key in Client_file_table. ClientFileID in Client_table is foreign key in Client_file_table.

In the procedure, you can construct a dynamic bcp statement to get the client file to a staging table and then put it in the real table.

SET @SQL = 'CREATE TABLE TempClientTable ('

SELECT @SQL + FieldName + ' ' + DataType + ','

FROM Client_file_table

WHERE ClientFileID = ?

ORDER BY Sequence

SET @SQL = SUBSTRING(@SQL, 1, LEN(@SQL) -1) + ')'

EXEC (@SQL)

SELECT @TextFileName = FileLocation + FileName

FROM Client_table WHERE ClientFileID = ?

SET @SQL1 = 'bcp TempClientTable IN ' + @TextFileName + ' -t"," -T'

EXEC(@SQL1)

After you get the TempClientTable, then you can load into whatever database the client belongs.

Remember to drop the temp table at the end.

DROP TABLE TempClientTable

Ghanta Hall of Fame Points: 3999 More actions · Answer 5

Ghanta

Hall of Fame

Points: 3999

April 5, 2007 at 9:31 pm

#698970

Thanks guys for taking time and responding to my questions. It gives me a good starting point.

David McFarland SSChampion Points: 11815 More actions · Answer 6

I wouldn't rule out a bcp based solution with format files for each client. You could then call it, automatically switching out format files based on which client you're processing. I'm not seeing any reason that it couldn't be completely automated, and would also make it fairly easy to change one client's format if they upgraded/changed the software they use to create the files.

Edit: I see Loner's already suggested this. I saw a bunch of T-SQL code, and didn't see the bcp calls. I will note that the format files are a piece of cake once you've done a couple of them, but might be a bit intimidating at first. The nice thing is you can handle all sorts of weirdness with them.

Loner SSC-Insane Points: 21279 More actions · Answer 7

Loner

SSC-Insane

Points: 21279

April 10, 2007 at 6:35 am

#699504

The bcp call is in dynamic sql of part of the codes.

SET @SQL1 = 'bcp TempClientTable IN ' + @TextFileName + ' -t"," -T'

EXEC(@SQL1)

I was thinking about using format file but that meant each new client, you need to create a new format file. To me that is not totally automate process.

David McFarland SSChampion Points: 11815 More actions · Answer 8

Loner, while I agree that it would be ideal if they could force their clients to use a standard format, I've been in too many situations where that was simply not an option. That's actually one of the reasons why I leaned towards using bcp in the first place, as you had already suggested.

While it requires a different format file for each client (and if you can get some clients to go along with standardization, they could even share one), everything else could be completely automated and standardized, while still allowing the flexibility that one gets from ad hoc importing methods.