DTS Connection Server

Hi everyone:

I'm making aDTS for SQL Server 2000 and I need to know if there is a way to set the Connection taskServer property to something like'local' or something like it so I don't have to change the Server property everytime I Install and run the DTS on a different server than the one where I built it.

Thanks.

To refer to local default SQL instance, you can use either '.' or '[local]; if you want to refer to named instance, you should add the instance name: '.\MyInstance'

|||Thanks for the reply Lori_Jay, but either '.' or '[local]' seem to work in the DTS Server Connection. To make it clearer I wrote '.' and '[local]' (without the quotes) in the Server TextBox (also a dropdown) and it didn't work cause the server was not found. It is only working with the exact name of the DataBase server.|||'.' or '[local]' that used in DTS Server Connection can only refers to SQL2000default instace. If you move the DTS package to a SQL2005 default instance, the connection will fail because the connection Provider used by the old DTS can not access SQL2005. In this case you can change the Data Source type to 'SQL Native Client' then try again.

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.

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.

DSO properties?

RemoteRepositoryConnectionString
A string property that defines a connection to a remote Analysis Services
repository.
RepositoryConnectionString
A string property that defines the connection to the metadata repository.
what are they really?
are they the cube database or the data source database which is used by Cube?
> RemoteRepositoryConnectionString
> A string property that defines a connection to a remote Analysis Services
> repository.
> RepositoryConnectionString
> A string property that defines the connection to the metadata repository.
> what are they really?
> are they the cube database or the data source database which is used by Cube?
The repository database is the one that holds the cube structures and
metadata. This database is used by Analysis Manager and the DSO library.
It really only comes into play when building and processing cubes and
dimensions.
As someone pointed out in a post recently, you can query cubes without
having this database at all.
There are two different connection strings because when you are on the
server you can access this database using "localhost" or a reference to
"C:\...", depending on whether you are using Access or SQL to hold your
repository. When you access AS remotely using Analysis Manager you need
to connect to the server by its network name, or to the access database
via a network share (which is setup when you install and Analysis
Manager knows about)
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell
|||Do you have any examples for these two? I could not find any in 2005 BOL.
Thanks,
Guangming
"Darren Gosbell" wrote:

>
> The repository database is the one that holds the cube structures and
> metadata. This database is used by Analysis Manager and the DSO library.
> It really only comes into play when building and processing cubes and
> dimensions.
> As someone pointed out in a post recently, you can query cubes without
> having this database at all.
> There are two different connection strings because when you are on the
> server you can access this database using "localhost" or a reference to
> "C:\...", depending on whether you are using Access or SQL to hold your
> repository. When you access AS remotely using Analysis Manager you need
> to connect to the server by its network name, or to the access database
> via a network share (which is setup when you install and Analysis
> Manager knows about)
> --
> Regards
> Darren Gosbell [MCSD]
> <dgosbell_at_yahoo_dot_com>
> Blog: http://www.geekswithblogs.net/darrengosbell
>
|||They come from the AS2K server. Bring up Analysis Manager, right-click on
the server and select "Edit Repository Connection String" or something like
that :-)
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI Systems Team
SQL BI Product Unit (Analysis Services)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Word 2003 memory Leakage" <Word2003memoryLeakage@.discussions.microsoft.com >
wrote in message news:EC5E30E2-D181-4BA1-AFDD-61C93E0CB04C@.microsoft.com...[vbcol=seagreen]
> Do you have any examples for these two? I could not find any in 2005 BOL.
> Thanks,
> Guangming
> "Darren Gosbell" wrote:

DSO properties?

RemoteRepositoryConnectionString
A string property that defines a connection to a remote Analysis Services
repository.
RepositoryConnectionString
A string property that defines the connection to the metadata repository.
what are they really?
are they the cube database or the data source database which is used by Cube
?> RemoteRepositoryConnectionString[vbcol=s
eagreen]
> A string property that defines a connection to a remote Analysis Services
> repository.
> RepositoryConnectionString
> A string property that defines the connection to the metadata repository.
> what are they really?
> are they the cube database or the data source database which is used by Cube?[/vbc
ol]
The repository database is the one that holds the cube structures and
metadata. This database is used by Analysis Manager and the DSO library.
It really only comes into play when building and processing cubes and
dimensions.
As someone pointed out in a post recently, you can query cubes without
having this database at all.
There are two different connection strings because when you are on the
server you can access this database using "localhost" or a reference to
"C:\...", depending on whether you are using Access or SQL to hold your
repository. When you access AS remotely using Analysis Manager you need
to connect to the server by its network name, or to the access database
via a network share (which is setup when you install and Analysis
Manager knows about)
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell|||Do you have any examples for these two? I could not find any in 2005 BOL.
Thanks,
Guangming
"Darren Gosbell" wrote:

>
> The repository database is the one that holds the cube structures and
> metadata. This database is used by Analysis Manager and the DSO library.
> It really only comes into play when building and processing cubes and
> dimensions.
> As someone pointed out in a post recently, you can query cubes without
> having this database at all.
> There are two different connection strings because when you are on the
> server you can access this database using "localhost" or a reference to
> "C:\...", depending on whether you are using Access or SQL to hold your
> repository. When you access AS remotely using Analysis Manager you need
> to connect to the server by its network name, or to the access database
> via a network share (which is setup when you install and Analysis
> Manager knows about)
> --
> Regards
> Darren Gosbell [MCSD]
> <dgosbell_at_yahoo_dot_com>
> Blog: http://www.geekswithblogs.net/darrengosbell
>|||They come from the AS2K server. Bring up Analysis Manager, right-click on
the server and select "Edit Repository Connection String" or something like
that :-)
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI Systems Team
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Word 2003 memory Leakage" <Word2003memoryLeakage@.discussions.microsoft.com>
wrote in message news:EC5E30E2-D181-4BA1-AFDD-61C93E0CB04C@.microsoft.com...[vbcol=seagreen]
> Do you have any examples for these two? I could not find any in 2005 BOL.
> Thanks,
> Guangming
> "Darren Gosbell" wrote:
>