Hi,
I am new to DTS programming. Project I am working on
requires me to create a stored procedure (sql server 2000)
that would import data from a fixed length records text
file to a table using DTS package. Based on primary key
input parameter, stored procedure should update existing
record if found, or insert a new one.
I created a dts package using the DTS designer and added
Text File data source, Microsoft OLEDBProvider for
SQLServer destination connection. I have a stored
procedure to update or insert records. I am looking for
the way to feed records from the text file source to the
ExecuteSQLTask. I created package global variables and was
hoping to write file source data to the global variables
and then use them as input parameters to the
ExecuteSQLTask. But I can't find the way to assign file
data to the variables.

I would appreciate your help in this matter.

Natalie

I personally would BULK INSERT the text file into a SQL Server working
table. I would then use TSQL statements to do the manipulations

--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Natalie" <nlitvino (AT) cogeco (DOT) ca> wrote

In article <02e101c3a730$03887850$a501280a (AT) phx (DOT) gbl>, Natalie
<nlitvino (AT) cogeco (DOT) ca> writes

To link a text file source to a SQL connection you need to use a data
transfer capable task, not the Execute SQL Task. This will allow you to
perform row by row processing, calling the procedure with the 1 row of
data at a time.

Try the Data Driven Query task. In the transform script pass out the
values you wish to use as you procedure input values, then map the
parameter to your procedure in the SQL. Have a read of Books Online
first to get a better understanding of how the task works.

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org