dts package sql query
-
I could use some help getting a dts package to work. I'm trying to load data from an xls file into a temporary table. Using an execute sql task, this script, but it doesn't find the xls file:
drop table hold_data
go
create table hold_data
(lastname nvarchar(50), firstname nvarchar(50), midinit nvarchar(2), ssno nvarchar(20), birthdate datetime, addr1 ntext, city nvarchar(20), state nvarchar(30), zip nvarchar(10), hm_phone nvarchar(25), rname nvarchar(30), department nvarchar(15), empposition nvarchar(100), employeeno nvarchar(20), datehired datetime)
go
insert into hold_data (lastname, firstname, midinit, ssno, birthdate, addr1, city, state, zip, hm_phone, rname, department, empposition, employeeno, datehired)
select last_name, first_name, middle_init, ssno, birthdate, addr1, city, state, zip, hm_phone_nbr, r_name, department, position, employee, date_hired
from "c:\eventpro\eventprodownld_orig.xls"
go
I set up an Excel connection and a Microsoft ODBC connection.
Any ideas will be greatly appreciated! Thanks!
-
Can you verify if the the file exists on the server where sql server is running ? c:\eventpro folder should be on the server and not on the local box from where you are executing the DTS package.
-
Why would you be using an ExecuteSQL task? If you've defined the connections, then just add a datapump between them.
If you're stuck on using the ExecuteSQL task then you'll need to use something like OPENQUERY, etc... (check BOL). The DTS connections are not visable within the SQL statement.
--------------------
Colt 45 - the original point and click interface
-
Whenever dealing with import / export files, I always define them using the long name with the default admin share.
ie - c:\data\import\file.txt as \\mycomputername\c$\data\import\file.txt
This way there is no doubt where the file I'm specifying lies. I do this even when GUI'ing it with a transform data task.
Terrence
-
I got the file to import. I had set up the connections to the xls file and the sql server database, but was trying to put the location of the xls file into the query too. I didn't need to. This worked:
INSERT
INTO hold_data
(lastname, firstname,
midinit, ssnbr, birthdate, addr1, city, state, zip, hm_phone,
rname, department, empposition, employeeno, datehired)
VALUES
(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Thanks for all your help!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply