April 19, 2010 at 5:58 am
HI
I have to merge two text (.txt) files which are output files created by a production process run on sql server 2005 databases. The text files (precisely for only two prefixed conditions) have to be combined as one text file which is placed on the file server for each environment. The process is a production process and the output text files will be created on different file servers for each run.The merging of the two text files should take place in a batch processing and the logic needs to be implemented through execution of a stored procedure from the batch (windows batch). I know that xp_cmdshell untility has to be used in order to carry out this task. But I am not aware of the approach to carry out this task. The following scenario needs to be considered while designing the stored procedure
1.Identify all the suffixed files (for eample, XXX_AA.txt and XXX_BB.txt) which exist inside a particular folder (XXX) and files in it are XXX_AA.txt and XXX_BB.txt
2.Once required files under each folder are identified, merge the two text files into a single text file.
3.This process should be carried out for all the folders in file server.
Please help!!!
April 19, 2010 at 6:32 am
Seems like an unnecessary complication if all you're doing is merging files. Why not do it in C#, or VB or something?
If it's an intellectual exercise, read the following link: -
I'm sure you'll be able to modify the code to do what you're after.
April 19, 2010 at 12:12 pm
Thanks for the reply...
But the requirement wants the solution to be implemented through execution of stored procedure only. And executing a extended stored procedure will enhance the usability in my other application. I would restrain myself from using C# or VB as it would not benefit my requirement. Could anyone please suggest a workaround using the stored procedure approach!!!
April 19, 2010 at 12:45 pm
The easiest way would be to to xp_cmdshell for it:
exec xp_cmdshell 'copy c:\temp\test1.txt + c:\temp\test2.txt c:\temp\test3.txt /y'
But, like with some other solutions which look simple at a first glance there is a hidden downside...
The biggest one here is: if you enable xp_cmdshell, you will dramatically increase the vulnerability of your system. Basically, if someone has access to xp_cmdshell and sufficient privileges on the server, that person could do anything, including move/delete files/folders or map drives just to name a few of the more harmful. So it needs to be decided very carefully, if the cost benefit ratio still qualifies this as a valid solution.
Another option would be to load the files into staging tables and use bcp to export the merged result. Depending on the file size this might be an alternative... I wouldn't use it for large files though (maybe the new FILESTREAM data type in SS2K8 would help here but I don't know since never used it.
April 19, 2010 at 10:59 pm
Have you considered using an SSIS package?
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 20, 2010 at 2:19 am
Paul White NZ (4/19/2010)
Have you considered using an SSIS package?
That's what I was getting at 🙂
Depending on the file sizes, you'd either just need to read the whole of each file into a stream and merge the streams in output, or if the files are too large then read in chunks of the file at a time and merge into a new file.
April 20, 2010 at 2:23 am
skcadavre (4/20/2010)
Paul White NZ (4/19/2010)
Have you considered using an SSIS package?That's what I was getting at 🙂
Were you? I assumed you were talking about a SQLCLR solution - which was my other thought.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 20, 2010 at 2:29 am
Thanks for your suggestion..
But I am focussed on implementing SQLCLR solution rather trying to do this task using SSIS. Could you provide any thoughts on how to implement using the SQLCLR approach??
April 20, 2010 at 2:34 am
Paul White NZ (4/19/2010)
Were you? I assumed you were talking about a SQLCLR solution - which was my other thought
After years of having the DBA here beat me into submission, that is no longer an option I ever consider. . .
April 20, 2010 at 4:48 am
Could you please suggest what I should be doing??
Is SSIS the only way to proceed futher??
April 20, 2010 at 5:22 am
If this was a task I'd been given, I'd be writing an SSIS package with a foreach loop and a C# script component. Our DBA would never allow SQLCLR, for reasons unknown to me as I'm a .net developer and not a DBA.
April 20, 2010 at 5:33 am
skcadavre (4/20/2010)
Our DBA would never allow SQLCLR, for reasons unknown to me as I'm a .net developer and not a DBA.
Like many DBAs, it probably comes down to fear, and ignorance 😀
@Splinter: I'm not sure what you're asking for here - but if you need help writing .NET code for this, you're probably better off asking someone you know to help - the implementation details in C# or VB are a bit much for a SQL Server forum question...probably.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 20, 2010 at 6:47 am
Should you choose to do it as an SSIS package, this C# should get you started. It's untested and written without a compiler so I can't be certain all of the syntax is exact.
public void Main()
{
string fileName = Dts.Variables["User::CurrentFile"].Value.ToString();
'Grab the current file from the SSIS variable
string output = @"C:\Tempewfile.txt";
'Set the output file name
int bytesRead = 0;
'Set the current bytes read to 0
byte[] buffer = new byte[40960];
'Buffer size set to 40kb
'Feel free to try something else as the speed benefit
'from Buffer size is dependant on the machine in use
FileStream inputFile = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.Read);
'Create the file reader stream
FileStream outputFile = null;
'Create the file writer stream
if ((!(fileName == output)))
'We only perform these tasks if the current file is not the output file
{
outputFile = new FileStream(output, FileMode.OpenOrCreate, FileAccess.Write);
'create the writer filestrean
if (System.IO.File.Exists(output))
'If the output file exists we perform this
{
outputFile.Close();
'Close the current filestream
outputFile = new FileStream(output, FileMode.Append, FileAccess.Write);
'Open a filestream as an appender instead
outputFile.Seek(0, SeekOrigin.End);
'Find the end of the file
}
}
else
{
return;
}
while ((bytesRead = inputFile.Read(buffer, 0, buffer.Length)) > 0)
'Make bytesRead equal to the data currently stored on the buffer
'While this is more than 0 we perform this
{
outputFile.Write(buffer, 0, bytesRead);
'Write the contents of the buffer to the output file
}
inputFile.Close();
outputFile.Close();
}
}
}
-Edit- Can't spell compiler
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply