I have a requirement like this?

Question

I have a requirement like this?

IN_Sandeep

SSCertifiable

Points: 6259
More actions
June 27, 2008 at 11:43 am

#209003

hi all,
I need to transfer few text file from my local server to remote server.
and also I need to put some validation before transfer from local server to remote server.
Validation like this...
-- check file should be text/csv file.
1 - if null or blank exist then its should replace by 0.
2 - column should separated by one tab space.
3 - one space should be after each record.
etc...
My question is this achieve by batch file or not?
Actually i wants to make one batch file which will copy all the text file from local server to remote server and i am able to transfer the file..but is it possible to validate all by the batch file or not..?
if yes then guide me..
if not then should i use DTS package.....
give me hints..so i can follow this...
Cheers!
Sandy.
--

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

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

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 1

Validation of the text files should be done either during the creation of the text file, during the import of the text file, or, preferably, both.

And, yes... the copy of text files from one server to another should probably be done by a batch file but, in a pinch, could be controlled by issuing batch commands to the shell.

You could also use DTS or SSIS to achieve such tasks... I don't really know how to do it using those because I always use somethng else, but I do know that it can be done with some relative ease.

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

My gut would be - copy through batch file, but validate some other method (probably .NET console app to tap into some parsing muscle.)

If all you're looking to do is to validate the data once it's copied, neither DTS nor SSIS would be particularly appropriate (since the most straightforward way for them to validate said data file would be to perform an actual import, which would be a lot more than just simply validating). Consider a .NET console app to validate.

Now if you ARE looking at doing an import, then SSIS is probably your best bet, since it has good error detection and handling (much improved over DTS). But at that point - why the copy first?

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

IN_Sandeep SSCertifiable Points: 6259 More actions · Answer 3

Jeff Moden
------------------------------------------------------------------------
Validation of the text files should be done either during the creation of the text file, during the import of the text file, or, preferably, both.
And, yes... the copy of text files from one server to another should probably be done by a batch file but, in a pinch, could be controlled by issuing batch commands to the shell.
You could also use DTS or SSIS to achieve such tasks... I don't really know how to do it using those because I always use something else, but I do know that it can be done with some relative ease.

hey jeff,

what do you prefer to achieve this??

can you tell me..

you said..

I don't really know how to do it using those because I always use something else,

so what else means..

see I have a requirement like copy all the text file which contains the master data or you can say transaction data in terms of rows and column format..

but i am not sure how it can be validate by batch file...

I have gone through some links from google but it seems to not be possible...

Can you suggest me how can it possible it..

Note: I am not going to load it to table ...

As I clear to transfer only from one server to remote server which should be periodically...

I mean automated process...

Can you tell me what process you followed..

Cheers!

Sandy.

--

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 4

Basically, I use batch files to do the transfers like Matt suggested. You're moving files... why not let the thing that handles files the best do it... namely the file handling of the operating system... Batch files with either Move or Copy commands. Once the data is moved, use Bulk Insert or BCP to load the files into staging tables and validate there.

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

IN_Sandeep SSCertifiable Points: 6259 More actions · Answer 5

Matt, jeff..

Can you clear me more..

Can you suggest any of ways to achieve this..?

Cheers!

Sandy.

--

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 6

Actual validation of the file contents and structure using a batch file is not going to be easy (I'm not going to say it's entirely not possible, but it's going to be a LOT harder than you need it to be that way). You would be a LOT better off writing something else to actually DO the validation, which then gets called by the batch file.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 7

Sandy (6/27/2008)
Matt, jeff..
Can you clear me more..
Can you suggest any of ways to achieve this..?
Cheers!
Sandy.

Already did... but I'll say it again here... write a batch file to move the files... write BCP or Bulk Insert code to import the data into a staging table... validate the data in the staging table... move the validated data from the staging table to the final table.

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

IN_Sandeep SSCertifiable Points: 6259 More actions · Answer 8

Hi Jeff Moden & Matt,:)

After few hours of work, I am successfully complete the job..

As per requirement and your's suggestion, I created one batch file in my Development server and DTS package in my Remote Server. by using the batch file i am able to run the Remote DTS package and Upload the file in Remote Server shared Folder.

Before transferring the data files (.txt/.csv), I am calling one more DTS file which will validate the data file and If my validation is ok then it will pass a return value to my main DTS package and as per the Success value I am moving the data file to the Remote Server.

What I found, we must have the user credential on the Remote server as well as the Server login to the remote server to complete the work.

Thanks all for your suggestion..:)

Cheers!

Sandy.

--