Integration Services or C# code
-
Hello, I will be creating a process that imports time series data from CSV files into an MSSQL database. After the data from the CSV file is processed, the CSV file is moved to an archive folder. If there are multiple CSV files in the processing folder, the process will import them as a batch. The name(s) of the CSV file(s) will include date information. At the start of a new day, a new CSV file will be created, I'll use some kind of naming convention for the file to indicate what date it's for. During the day, the CSV file will be appended to. I am pondering whether I should: - Create a SSIS task to perform this process, and schedule it to run every X minutes - Create a C# Console application that monitors the processing folder, for mods to an existing file, and additional .csv files (for batch processing) What do you think the advantages/disadvantages of either approach are? Which one would you use? Also, can I use LINQ in any way to read the CSV data? I'm using MSSQL 2005. Thanks a lot for any pointers/suggestions/best practices. Richard Rogers
-
Hello, I will be creating a process that imports time series data from CSV files into an MSSQL database. After the data from the CSV file is processed, the CSV file is moved to an archive folder. If there are multiple CSV files in the processing folder, the process will import them as a batch. The name(s) of the CSV file(s) will include date information. At the start of a new day, a new CSV file will be created, I'll use some kind of naming convention for the file to indicate what date it's for. During the day, the CSV file will be appended to. I am pondering whether I should: - Create a SSIS task to perform this process, and schedule it to run every X minutes - Create a C# Console application that monitors the processing folder, for mods to an existing file, and additional .csv files (for batch processing) What do you think the advantages/disadvantages of either approach are? Which one would you use? Also, can I use LINQ in any way to read the CSV data? I'm using MSSQL 2005. Thanks a lot for any pointers/suggestions/best practices. Richard Rogers
This is purely a business decision and should not be answered by the developer. We have done both, for different requirements, and I am still ambiguous as to which is the best solution. Except I would never create a console app, ours is winform so the user can interact with the process. One thing I have learned over the years is NOT to use ETL but to move the T (transform) to after the load so we build ELT processes.
Never underestimate the power of human stupidity RAH
-
This is purely a business decision and should not be answered by the developer. We have done both, for different requirements, and I am still ambiguous as to which is the best solution. Except I would never create a console app, ours is winform so the user can interact with the process. One thing I have learned over the years is NOT to use ETL but to move the T (transform) to after the load so we build ELT processes.
Never underestimate the power of human stupidity RAH
Mycroft Holmes wrote:
so we build ELT processes.
Agreed.
Light moves faster than sound. That is why some people appear bright, until you hear them speak. List of common misconceptions
-
Hello, I will be creating a process that imports time series data from CSV files into an MSSQL database. After the data from the CSV file is processed, the CSV file is moved to an archive folder. If there are multiple CSV files in the processing folder, the process will import them as a batch. The name(s) of the CSV file(s) will include date information. At the start of a new day, a new CSV file will be created, I'll use some kind of naming convention for the file to indicate what date it's for. During the day, the CSV file will be appended to. I am pondering whether I should: - Create a SSIS task to perform this process, and schedule it to run every X minutes - Create a C# Console application that monitors the processing folder, for mods to an existing file, and additional .csv files (for batch processing) What do you think the advantages/disadvantages of either approach are? Which one would you use? Also, can I use LINQ in any way to read the CSV data? I'm using MSSQL 2005. Thanks a lot for any pointers/suggestions/best practices. Richard Rogers
My 2 cents are to build a Windows service which uses a "FileWatcher" to monitor the directory and process the CSV file. Check this article out ... How to implement a simple filewatcher Windows service in C#[^]
-
My 2 cents are to build a Windows service which uses a "FileWatcher" to monitor the directory and process the CSV file. Check this article out ... How to implement a simple filewatcher Windows service in C#[^]
Mycroft, Jörgen, and David, Thank you all for your responses. This is a short project for a small consulting firm. I am the only coder, and I'm afraid to say I'm also the business analyst. The issues I perceive are: 1. Using SSIS 2005, I cannot "monitor" a folder for new or updated CSV files. The File System Task component does not appear to be capable of doing this. 2. I have written a very simple SSIS package to copy the data from a csv file with a fixed file name. Even if there were a component that monitors a folder with a filemask like (*.csv), how would I pass the filename to the Flat File Source component? 3. I would only want to move the csv file to the archiving folder if the import of the data completed with a return code of zero, which includes calling a stored proc on the server. What is the standard way of informing the user that an error occurred in a SSIS task? Is all this possible in a SSIS package? Thanks, Richard
-
Mycroft, Jörgen, and David, Thank you all for your responses. This is a short project for a small consulting firm. I am the only coder, and I'm afraid to say I'm also the business analyst. The issues I perceive are: 1. Using SSIS 2005, I cannot "monitor" a folder for new or updated CSV files. The File System Task component does not appear to be capable of doing this. 2. I have written a very simple SSIS package to copy the data from a csv file with a fixed file name. Even if there were a component that monitors a folder with a filemask like (*.csv), how would I pass the filename to the Flat File Source component? 3. I would only want to move the csv file to the archiving folder if the import of the data completed with a return code of zero, which includes calling a stored proc on the server. What is the standard way of informing the user that an error occurred in a SSIS task? Is all this possible in a SSIS package? Thanks, Richard
I cannot contribute to the SSIS solution because I have no experience with it. I guess even developing a Console Application is outside your comfort zone? A data importer really wouldn't take much to develop. It wouldn't be pretty, but once you write it, you could use windows scheduler to check the directory every few minutes.
-
I cannot contribute to the SSIS solution because I have no experience with it. I guess even developing a Console Application is outside your comfort zone? A data importer really wouldn't take much to develop. It wouldn't be pretty, but once you write it, you could use windows scheduler to check the directory every few minutes.
Hi David, I'm actually very inclined to write a console app, or maybe even a service. It's the guy who is directing my work who is kind of against it, for no apparent reason. I don't have the capability of writing the code at the Client site, where I'm working. I do have VS 2010 installed here at home though, so I may give it a whirl. I tend to think that it would be pretty! A simple and elegant solution. Thanks!
-
This is purely a business decision and should not be answered by the developer. We have done both, for different requirements, and I am still ambiguous as to which is the best solution. Except I would never create a console app, ours is winform so the user can interact with the process. One thing I have learned over the years is NOT to use ETL but to move the T (transform) to after the load so we build ELT processes.
Never underestimate the power of human stupidity RAH
Hi Mycroft, I don't know if you saw my responses, later in this thread. http://www.codeproject.com/Messages/4097394/Re-Csharp-code.aspx[^] Thanks for your advice.
-
Mycroft, Jörgen, and David, Thank you all for your responses. This is a short project for a small consulting firm. I am the only coder, and I'm afraid to say I'm also the business analyst. The issues I perceive are: 1. Using SSIS 2005, I cannot "monitor" a folder for new or updated CSV files. The File System Task component does not appear to be capable of doing this. 2. I have written a very simple SSIS package to copy the data from a csv file with a fixed file name. Even if there were a component that monitors a folder with a filemask like (*.csv), how would I pass the filename to the Flat File Source component? 3. I would only want to move the csv file to the archiving folder if the import of the data completed with a return code of zero, which includes calling a stored proc on the server. What is the standard way of informing the user that an error occurred in a SSIS task? Is all this possible in a SSIS package? Thanks, Richard
Hmm you need to look at a number of aspects as it sounds like you are a single person operator doing small systems. I have been there so! How are you going to support the processes, remotely or turn up when they fail, can you client do some of the support himself (IE restart an SSIS package/job). Corollary, how critical is the load. Another issue, does your client expect to play in your dirt patch, will he want to learn from your SSIS, is he one of those to want to inspect the code? And do you want him to. This pros and cons, he can help support himself and he can also screw up the system dramatically. Personally I always move the file BEFORE processing it, I do the processing from the archive folder. I also prefer a winforms app as I then have complete control and can walk the user through a restart/reload etc. This is a preference only as both SSIS and app can do all the functionality required (SSIS can poll the folder every #n seconds/minutes using the job scheduler)
Never underestimate the power of human stupidity RAH