Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
fmardani
Constraint Violating Yak Guru
433 Posts |
 Posted - 2006-03-14 : 06:27:34
|
| Hi,I would like to create something i.e. a Job so that the .txt files can be imported into a sql server 2005 database tables.The system has to be flexible so that I can tell it where the .txt file (Source) is and to which database I want to import to.This system has to sit outside sql server, so I guess I can not use something like DTS ???Any ideas please?Thanks |
|
|
mwjdavidson
Aged Yak Warrior
735 Posts |
Posted - 2006-03-14 : 07:15:42
|
| Have a look at Bulk Copy Program (BCP). Although it ships with SQL Server, it is a separate application.Mark |
 |
|
|
fmardani
Constraint Violating Yak Guru
433 Posts |
Posted - 2006-03-14 : 07:45:12
|
| How do I get to BCP in sql server 2005 |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-03-14 : 08:11:44
|
| also refer to here http://msdn2.microsoft.com/en-us/library/ms175937(SQL.90).aspx----------------------------------'KH' |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-03-14 : 08:24:09
|
| DTS isn't part of sql server - it's just distributed with it. Think of it as a application language like VB.For v2005 it's been re-written as integration services but whether you want to or should use it is another question.For your requirement I would have thought that bulk insert would be a simpler solution.Have a look athttp://www.nigelrivett.net/SQLTsql/ImportTextFiles.htmlwhich will import all fiels that arrive in a folder (with a file mask).You can just change it to accept table and database as a parameter.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
fmardani
Constraint Violating Yak Guru
433 Posts |
Posted - 2006-03-14 : 08:26:01
|
| The whole idea is:What we need to do is make sure we can run this job without needing to use SQL wizards.And that we can run it on different source data into different databases.I wonder if this is possible in DTS ?Thanks |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-03-14 : 08:43:08
|
| Sure it's possible with dts but much simpler with the code I linked.For dts you would set up global parameters for the database/folder and set them when running the package (from dtsrun probably). In the package use the values to alter the tasks before they run.But as I said tsql is a lot simpler.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
fmardani
Constraint Violating Yak Guru
433 Posts |
Posted - 2006-03-14 : 09:20:08
|
| I am reading the website that you sent.Are you sure that this method allows to transfer any .txt to any table. I think this method is for one table based only. Am I right? thank |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-03-14 : 10:23:38
|
| It needs a bit of amendment for that.If you're interested I could add that to the site today probably.It would probably be a useful thing to add but I wouldn't do it without a reason.Easiest would be the straight bulk insert into a database/table as a parameter to the sp if that would fulfill your need.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
import job