February 25, 2008 at 11:27 am
Hello,
I am using SQL 2000 sp4.
I need help with a DTS Package so here are the details.
Each week we receive a text file from a customer that is uploaded into the database. This file contains many records that may or may not have already been uploaded into the database, so I need to create a net change file based on the file the customer sends.
Each week the file name is different but the location of the file is the same from week to week.
We keep track of the file upload process, file name, and location in a table.
In my DTS package I have a connection to the SQL Server.
I then created an Execute SQL Task and run a Query that gets the upload id and filename from the weekly uploads table and inserts them into a table in a different db on the same server, this is done to isolate the table so nothing gets modified on the production db until the net change file has been produced.
Here is the net change table DDL that the upload id and file name get inserted into.
CREATE TABLE [dbo].[tblNet_Change] (
[NetChaneID] [int] IDENTITY (1, 1) NOT NULL ,
[UploadID] [int] NOT NULL ,
[FileName] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Completed] [bit] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblNet_Change] WITH NOCHECK ADD
PRIMARY KEY CLUSTERED
(
[NetChaneID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblNet_Change] ADD
CONSTRAINT [DF__tblNe__Compl__77BFCB91] DEFAULT (0) FOR [Completed]
GO
Here is an insert for 1 row of data.
INSERT INTO tblNet_Change (UploadID, FileName, Completed) VALUES (266,'\dropoff\MjA2NDk2_1282007-80001-AM-47.txt')
The reason for one row of data is we get only one file each week so there will only be one new row each week that gets inserted into the tblNet_Change table.
I have an Active X task that I plan on using for the file moving and renaming.
So what I need is a way to get the upload id and the filename into the variables in the Active x task. I have tried to assign variables in the Execute SQL Task by using the “?” but I keep getting an error message that says in part “Access violation”.
If anyone can show me how I would really appreciate it.
Thanks
Gary
February 26, 2008 at 6:56 am
Hi Gary,
I assume that there is a query in the Exec SQL Task that returns one row. Get rid of all ?
Click on the parameters button, then the Output Parameters tab. Click the Row Value radio buttong. Enter the column name from the query in the Paramters list under Paramter Mapping and pick the global variable to map too.
Norman Kelm
DTS Package Search
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply