|
The type of the database (and the version) is essential when asking this level of information as the security functionality varies between database systems.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I'm using SQL Server 2005, Stanndard Edition. I've googled for long and still not getting any solution.
|
|
|
|
|
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
|
|
|
|
|
Mycroft Holmes wrote: so we build ELT processes.
Agreed.
|
|
|
|
|
|
|
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.
|
|
|
|
|
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!
|
|
|
|
|
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
|
|
|
|
|
Hi all! I would like to know how to get a list of permissions for a specific role. I am able to get the permissions for a user using
fn_my_permissions
to view permissions for a user but I want to know how to view the permissions for a specific role. Thanks.
|
|
|
|
|
Start here[^]. Should help.
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum
me, me, me
|
|
|
|
|
I want to import data from excel to sql server 2005. I am using C# for this purpose. I successfully exported the data of general format
to varchar. But, the problem arises while exporting the data of date type.
I have used date format in excel and want to export it in sql server 2005's datetime field.
The code that I have used is:-
SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
sqlBulk.DestinationTableName = "Data_Master_Inventory";
sqlBulk.ColumnMappings.Add("VendorRegistrationNo", "VendorRegistrationNo");
sqlBulk.ColumnMappings.Add("ProductCode", "ProductCode");
sqlBulk.ColumnMappings.Add("SerialNo", "SerialNo");
sqlBulk.ColumnMappings.Add("VendorProvidedSerialNo", "VendorProvidedSerialNo");
sqlBulk.ColumnMappings.Add("ModelName", "ModelName");
sqlBulk.ColumnMappings.Add("ProductCategoryCode", "ProductCategoryCode");
sqlBulk.ColumnMappings.Add("InventoryDate", "InventoryDate");
sqlBulk.WriteToServer(dReader);
Please provide me necessary solution.
|
|
|
|
|
|
sir
i have installed the sql server 2008 developer edition on windows xp3 successfully,
,along with reporting services. and i have configure reporting services configuration manager through wizard and completed successfully and the services are running ie sql server reporting services(MSSQLSERVER) state is running.
but when i goes from start---> all programs---> microsoft sqlserver2008 iam not getting sql server business intelligence. how to get can you help me please
|
|
|
|
|
i have project with with database file in sql 2000 this is library.sql.how can i open this file.
|
|
|
|
|
You can open it in Notepad or Visual Studio or SQL Server Management Studio at least. Right-click and go Open With. If you don't know this really basic stuff...
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum
me, me, me
|
|
|
|
|
I am a student of SQL Server. I have been using the SQL Server 2008R2 Enterprise trial version. The trial version has now expired. I just purchased the SQL Server 2008R2 developers version for $50.00. My question is should I install the Developers version over the trial version or try and delete the trial version first before installing the Developers version. 
|
|
|
|
|
I would remove the enterprise version. SQL Server has a history of problems with installed versions.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
It is better to backup your entire existing databases and uninstall the expired version. Then install the new one with the licence key.
Wonde Tadesse
MCTS
|
|
|
|
|
Hi All,
I want to update multiple columns of multiple rows of one table. Single/Multiple column update for single/Multiple row is quite easy but i want to update the rows of one table on the basis of rows of other table.
Here I demonstrate that what i want with single query
Table1 - History
Fields and Data
Date SId Country Price High Low ........
1 Dec 1 US 2 3 1 values...
1 Dec 2 US 3 4 2 values...
1 Dec 3 US 4 5 3 values...
1 Dec 4 US 5 6 4 values...
2 Dec 1 US 7 8 5 values...
2 Dec 2 US 8 9 6 values...
2 Dec 3 US 9 10 7 values...
2 Dec 4 US 10 11 8 values...
Table2 _ LatestPrice
Fields and Data
SId Country Price High Low ........
1 US 1 2 1 values...
2 US 1 2 1 values...
3 US 1 2 1 values...
4 US 1 2 1 values...
I want to update the LatestPrice table data on the base of field SID and Country from History table data.Here i pick the only highest date data from History table. Now i want to update data in LatestPrice table. so there are two approaches
One way is the pick one by one record from resultset and update the LatestPrice on the base of field SID and Country. so there are mulitple queries depends on the no. of records in resultset
Other way is write singe query in this way that they find automatically SId and Country and update the data.
So i was unable to write single query for update the Data.
Please suggest that how can i do this taks
any help will be appreciated
modified 3-Dec-11 6:52am.
|
|
|
|
|
you can try the following syntax
Update LatestPrice set price = LatestPricesFromHistory.price,...other columns From
(
here goes query to find highest date data from History table
) as LatestPricesFromHistory
where LatestPrice.SID = LatestPricesFromHistory.SID and LatestPrice.Country = LatestPricesFromHistory.Country
"Insanity is doing the same thing over and over again but expecting different results.” — Rita Mae Brown
modified 4-Dec-11 23:11pm.
|
|
|
|
|
Hi Varsha,
i tried as following
UPDATE LatestPrice SET price = LatestPricesFromHistory.price,
FROM ( SELECT *
FROM history
WHERE Date1 = (
SELECT max( Date1 )
FROM history ) ) AS LatestPricesFromHistory
WHERE LatestPrice.Sid = LatestPricesFromHistory.SId and LatestPrice.Country = LatestPricesFromHistory.Country
but it give me following error
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'From { select * from history where Date1=(select max(Date1) from history' at line 1.
Please suggest
|
|
|
|
|
I think MySQL does not support Update-Select.
I have used this syntax in SQL server 2005.
try this one for MySQL :
Update LatestPrice,
( Your Query for finding Latest Prices goes here) as LatestPriceFromHistory
Set price = LatestPriceFromHistory.price
where LatestPrice.SID=LatestPriceFromHistory.SID and LatestPrice.Country = LatestPriceFromHistory.Country
"Insanity is doing the same thing over and over again but expecting different results.” — Rita Mae Brown
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
