DTS "The Parameter Is Incorrect" Error

I'm running SqlServer 2000 and am trying to write a DTS. For some reason I keep getting that error for my Dynamic Property Task and not sure why. I've tried giving my Package a password like was mentioned in another thread on here, but that didn't work either.

I'm trying to dynamically set a Data Source for an Excel connected, using a query.

Query:

DECLARE @.directory VARCHAR(1000)
SET @.directory = 'C:\'
EXEC [Legacy].[dbo].[spr_GetNewestExcel] @.directory

The Store Procedure:

CREATE PROCEDURE spr_GetNewestExcel
(
@.directory VARCHAR(1000)
)
AS
BEGIN
DECLARE @.cmdParam VARCHAR(8000)

--Temp Table that saves the output of a DOS command.
CREATE TABLE #output
(
output VARCHAR(300)
)

--Temp Table that saves the output of a "dir" DOS output, in parts.
CREATE TABLE #files
(
fileDate VARCHAR(20),
fileSize VARCHAR(20),
fileName VARCHAR(100)
)

--Sets the command parameter
SET @.cmdParam = 'dir "' + @.directory + '"'

--Inserts the output of the above DOS command into a table
INSERT INTO #output
EXEC master..xp_cmdshell @.cmdParam

--Parses the #output table for the files with the correct extension and breaks it up into
--Date, Size, and Name.
INSERT INTO #files
SELECT
SUBSTRING(output, 0, 21) As fileDate,
LTRIM(SUBSTRING(output, 21, 18)) As fileSize,
SUBSTRING(output, 40, LEN(output))
FROM #output
WHERE output LIKE '%.xls'

--Displays the values
SELECT TOP 1
CASE
WHEN SUBSTRING(@.directory, LEN(@.directory), 1) = '\' THEN @.directory + fileName
ELSE @.directory + '\' + fileName
END AS fileName
FROM #files ORDER BY fileDate DESC

DROP TABLE #output
DROP TABLE #files
END

The Stored Procedure runs great on its own, it displays a list of filenames with the .xls extension in the desired directory, which is what I want. It should put the first in the list((if there is more than one)) as the datasource, but it doesn't.

When I go to the "Add/Edit Assignment" window for it, and push "Refresh" to preview it, it gives me a "The Parameter Is Incorrect".

Any help would be much appreciated, this has had me stumped for quite some time.

peace,
CR
you're in the wrong forum. this forum is about ssis, not dts.|||fair enough, I saw other threads similar to this one, so I posted here. And since the description mentions data transforms, seemed fitting. Though feel free to point me in the right direction since the other boards don't seem anywhere near as fitting as this one. Least not to me.|||Well after a while I finally determined what was wrong, but I must say, what a vague error.

Anyway, apparently Dynamic Properties don't like when you use Inserts, that is all.

Thanks for reading!

peace,
CR|||

There is no forum for DTS but the old newsgroup is still alive and well: microsoft.public.sqlserver.dts

-Jamie

|||

There is no forum for DTS but the old newsgroup is still alive and well: microsoft.public.sqlserver.dts

-Jamie

|||

I spect that you understand my english. About this problem it isn′t that Dynamic Properties don't like Inserts the problem is the messages that SQL return when you make an insert. You have to add to your code "SET NOCOUNT ON " to ommit the messages.