February 8, 2007 at 1:36 pm
Guys,
I have txt file with following values
fname lname
______________
Tom Gray
Tim Junk
I want to be able set a job to import this into NAME table 10pm every night
NAME table has the following columns
FNAME VARCHAR(20)
LNAME VARCHAR(20)
DNAME VARCHAR(50)
after I load the data I concatenate FNAME and LNAME into DNAME. I want to setup a job then to export out DNAME column
is there any way this can be acconplished using dts job
Thanks
February 8, 2007 at 1:45 pm
In the title you said import/export using csv file but in the content you use a text file.
Assume you have an input text file with fname, Lname and you want to export the Dname to another text file, is this what you want?
You can put all in an DTS package.
A connection of input text file A.
A connection of output text file B.
SQL Server connection SQL.
Step 1 - input text file A to SQL table NAME using transfer data task.
Step 2 - update the table NAME to concatenate into DNAME using execute SQL task.
Step 3 - using transfer data task again from SQL Server to text file B.
DONE !!
February 8, 2007 at 2:28 pm
Loner,
Thank you for your response,
I want to be able to do this in sql server 2000, and hence I posted under forum too. I tried to use BCP utility but I fail during the first step itself
when I try to execute this I get the below message even though the txt file seem to be okay.
BULK INSERT Colonial..Collin_images FROM 'c:\source.txt'
WITH (
DATAFILETYPE = 'char',
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
Server: Msg 4832, Level 16, State 1, Line 1
Bulk Insert: Unexpected end-of-file (EOF) encountered in data file.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows returned 0x80004005: The provider did not give any information about the error.].
The statement has been terminated.
any suggestions?
Thanks
February 8, 2007 at 2:58 pm
From the error message, it seemed that your data file had a problem. Did you check your data file and make sure it does not have EOF mark somewhere?
What happened when you used 'bcp', it should work too ?
DECLARE @sql VARCHAR(8000)
SET @sql = ''
SET @sql = 'bcp Colonial..Collin_images IN "c:\source.txt" -c -t";" -T'
EXEC master..xp_cmdshell @sql
February 8, 2007 at 3:25 pm
when I the above bcp code I get the following error, I am not sure if my server settings are wrong
any ideas??
SQLState = 08001, NativeError = 17
Error = [Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server does not exist or access denied.
SQLState = 01000, NativeError = 2
Warning = [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen (Connect()).
NULL
February 9, 2007 at 2:25 am
The above error message caused by problem with your connection string....does not recognize server name.....
Why dont you create a DTS Job in SQL Enterpise Manager to load the data and then set up job to run it daily using xp_cmdshell e.g. ......
Execute master..xp_cmdshell 'dtsrun -SYOUR_SERVER -UYOUR_LOGIN -PYOUR_PASSWORD -NYOUR_DTS_JOB '
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply