August 25, 2008 at 12:13 am
Dear All,
In one of our porject, ineventory table is updated from a CSV file. At present it's done using a Cold fusion file which runs automaticaly in every one hour. This gives lot of of problems. Mostly it gives "QUERY TIME OUT" error.
Is it possible to make a job in SQL server, which will take data directly from a CSV file (ofcourse in particular format) and Insert/Update tables in database.?. Is it possible..?
So that I can avoid the execution ColdFusion file which might eat considerable resources.
Thanks in advance,
Santhosh Nair.
August 25, 2008 at 1:35 am
You can use DTS (Data Transformation Service) to do that. You can create source from csv file and then create destination node of your table. Setting the arrow between the source and destination for the column mapping or other operation. Note that DTS is for SQL Server 2000. There is a similar thing in SQL Server 2005 (I forget the name :p because I use SQL Server 2000).
Ivan Budiono
August 25, 2008 at 1:44 am
Hello,
In SQL 2005 it is SSIS that replaces DTS.
For somebody who is unfamiliar with SSIS it might be easiest to use the Import and Export Wizard to perform the task once, as the wizard then gives you the option to save the import steps as an SSIS package.
You can then create a schedule job via SQL Server Agent to execute the SSIS package regularly.
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
August 25, 2008 at 3:51 am
I tried to save it as package and got the message that it's saved succesfully. But am not able to that package anywhere in sql server. It's neither in Maintenance Plans Nor in Jobs..
Please help...
Santhosh Nair.
August 25, 2008 at 4:16 am
Hello,
When saved to SQL Server (rather than the File System) the SSIS Package will be stored in the MSDB Database.
You can select the Package when you create the Scheduled Job: In the New Job Step, select a Type of "SQL Server Integration Services Package", then chose the SQL Server where you stored the Package, you can then click on the Browse button (3 dots) to select it.
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
August 25, 2008 at 5:05 am
Thanks....I can see it now...
Thanks a lot.
August 25, 2008 at 7:25 am
By the way, to address the original problem... time out errors are generated at the application / provider level, not by the SQL Server. Chances are the connection string of the client application does not have the TIMEOUT=0 option, meaning the default timeout has been chosen by the provider. If its cold fusion, chances are its ODBC.
August 25, 2008 at 8:58 am
Precisely...but the fun is that, same page works wihout any problem when executed in another server.
August 27, 2008 at 9:25 am
When specifing from cold fusion, the request time out is set by default. Timeout property can be implemented and value can be expanded to aviod timeout problems.
i was getting timeout errors from my application written in asp.net application and i fixed it using timeout property
August 27, 2008 at 2:50 pm
I'd still check the connection strings. Each server probably makes a single connection to the database so chances are the connection strings are different on the two servers.
Had the same problem in a BEA Weblogic server farm a couple years back. Two of the servers out of 20 were reporting database timeouts periodically. Closer inspection of the provider on the two boxes showed neither had a timeout parameter specified, where the other 18 had TIMEOUT=0
August 27, 2008 at 11:12 pm
Thank you.. will check it once again..
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply