DTS FTP problem

Is there any way to enter file names in the DTS FTP task even if the files are not presently there in the remote machine?I always implement FTP via t-sql e.g.
http://www.nigelrivett.net/s_ftp_PutFile.html

But in dts you can access the object model in an activex script and change the filename.

DTS Copy SQL Server Task

I want to copy a database on a remote server to a local instance of SQL Server. I created a package that has the Copy SQL Server Task in it, but it seems to fail half way through every time i execute it.

I can view the local machine error log which simply reports that the table transfer was attempted.. but no explanation of why it fails. I don't have access to the host error log files either, so I have no idea why it fails.

I once almost got the 'Copy SQL Server Task' to work, but it would create duplicate records in the destination database, which I didn't want. It seems choosing the intuitive options like 'replace existing data' and choosing the table I want copied over.. does not work. I am not 100% sure the 2 databases are referentially identical either.. so it would be nice to know how to do a copy by first deleting the entire database on the destination machine, then recreating it all.

Can anyone give me some advice on which options I need to choose to achieve a proper database copy? Or, if you could show me a tutorial online that could explain how all the options work, and how I can debug what's causing my current method to fail, would be nice.

BrentWell I got it to work. It turns out that you can double click the error message to get some more details as it occurs. Strangely every account I had tried to use on my local server did not have permission to do the copying.. but only for certain attributes. So, basically by trial and error, I selected the right configuration to only copy the tables to the destination database (the accounts seemed to have permission to do certain activities).

It sure would be nice to find some info on the meaning of all the options in help or online somewhere.. if anyone knows of such a resource, they should post it.|||Thanks me!

I forgot how to view DTS backup log errors, and found my own old post to show me... double click the error in Ent. Manager!

This is what i needed to fix the problem!

DTS - Strange Behavior

Hi,
I've created quite a few DTS packages that open up VPN connections to remote
SQL Servers, transfer data to local SQL Servers, writes audit records, then
close the VPN connections. Everything has been working well. Saturday all
of the packages (scheduled as jobs) began to fail. Friday we did do some
physical server migrations but the SQL Servers were not touched. Nothing
else has changed. When I examined the DTS packages I noticed that if I
execute the package manually none of the tasks ever execute; they stay in a
"Waiting" state with a yellow icon. This occurs for both Meta Data and SQL
Server stored packages. If I open the package in design mode, I get the
same "Waiting" behavior. Eventually an error is returned in either case
which states:
Error Source: Microsoft OLE DB Provider for SQL Server
Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Server does
not exist or access is denied.
After the error for each step it lists "Not Run" with a white X in a black
circle. In the past successful execution of the package would list success
(green icon) for each step moving down through the package as each step
succeeded (as expected).
The funny thing is if I open the package up in design mode and execute any
individual step, the the step executes successfully. I.e., individual steps
work but not the whole DTS package whether opened in design mode or not.
If I create a new test DTS package, the package works as expected. If I
save one of the existing DTS packages out to a DTS file and import it back
into DTS and save it under a different name, same failure behavior as listed
above. If I create a new DTS package and copy/paste the steps individually
(from a failing package) into a new package and then save it, the package
works as expected.
Any ideas? I need to get this working again because were talking about
millions of records. And preferably, I would rather not spend two days
recreating the same packages over again.
Thanks
Jerry
PS - The double post is in leu of time constraints.Also,
This is Windows 2000 SP4 SQL Server 2000 SP4. No errors are reported in the
DTS error log.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23A1ebigFGHA.3700@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've created quite a few DTS packages that open up VPN connections to
> remote SQL Servers, transfer data to local SQL Servers, writes audit
> records, then close the VPN connections. Everything has been working
> well. Saturday all of the packages (scheduled as jobs) began to fail.
> Friday we did do some physical server migrations but the SQL Servers were
> not touched. Nothing else has changed. When I examined the DTS packages
> I noticed that if I execute the package manually none of the tasks ever
> execute; they stay in a "Waiting" state with a yellow icon. This occurs
> for both Meta Data and SQL Server stored packages. If I open the package
> in design mode, I get the same "Waiting" behavior. Eventually an error is
> returned in either case which states:
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Server does
> not exist or access is denied.
> After the error for each step it lists "Not Run" with a white X in a black
> circle. In the past successful execution of the package would list
> success (green icon) for each step moving down through the package as each
> step succeeded (as expected).
> The funny thing is if I open the package up in design mode and execute any
> individual step, the the step executes successfully. I.e., individual
> steps work but not the whole DTS package whether opened in design mode or
> not.
> If I create a new test DTS package, the package works as expected. If I
> save one of the existing DTS packages out to a DTS file and import it back
> into DTS and save it under a different name, same failure behavior as
> listed above. If I create a new DTS package and copy/paste the steps
> individually (from a failing package) into a new package and then save it,
> the package works as expected.
> Any ideas? I need to get this working again because were talking about
> millions of records. And preferably, I would rather not spend two days
> recreating the same packages over again.
> Thanks
> Jerry
> PS - The double post is in leu of time constraints.
>|||Also,
This is Windows 2000 SP4 SQL Server 2000 SP4. No errors are reported in the
DTS error log.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23A1ebigFGHA.3700@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've created quite a few DTS packages that open up VPN connections to
> remote SQL Servers, transfer data to local SQL Servers, writes audit
> records, then close the VPN connections. Everything has been working
> well. Saturday all of the packages (scheduled as jobs) began to fail.
> Friday we did do some physical server migrations but the SQL Servers were
> not touched. Nothing else has changed. When I examined the DTS packages
> I noticed that if I execute the package manually none of the tasks ever
> execute; they stay in a "Waiting" state with a yellow icon. This occurs
> for both Meta Data and SQL Server stored packages. If I open the package
> in design mode, I get the same "Waiting" behavior. Eventually an error is
> returned in either case which states:
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Server does
> not exist or access is denied.
> After the error for each step it lists "Not Run" with a white X in a black
> circle. In the past successful execution of the package would list
> success (green icon) for each step moving down through the package as each
> step succeeded (as expected).
> The funny thing is if I open the package up in design mode and execute any
> individual step, the the step executes successfully. I.e., individual
> steps work but not the whole DTS package whether opened in design mode or
> not.
> If I create a new test DTS package, the package works as expected. If I
> save one of the existing DTS packages out to a DTS file and import it back
> into DTS and save it under a different name, same failure behavior as
> listed above. If I create a new DTS package and copy/paste the steps
> individually (from a failing package) into a new package and then save it,
> the package works as expected.
> Any ideas? I need to get this working again because were talking about
> millions of records. And preferably, I would rather not spend two days
> recreating the same packages over again.
> Thanks
> Jerry
> PS - The double post is in leu of time constraints.
>|||Got it. For future views of this post:
The issue lied in the fact that logging for the package execution was going
to another SQL Server that was taken down (testing box). Updating the
logging to the prod box (which should have occurred at the begining) fixed
the issue.
Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23A1ebigFGHA.3700@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've created quite a few DTS packages that open up VPN connections to
> remote SQL Servers, transfer data to local SQL Servers, writes audit
> records, then close the VPN connections. Everything has been working
> well. Saturday all of the packages (scheduled as jobs) began to fail.
> Friday we did do some physical server migrations but the SQL Servers were
> not touched. Nothing else has changed. When I examined the DTS packages
> I noticed that if I execute the package manually none of the tasks ever
> execute; they stay in a "Waiting" state with a yellow icon. This occurs
> for both Meta Data and SQL Server stored packages. If I open the package
> in design mode, I get the same "Waiting" behavior. Eventually an error is
> returned in either case which states:
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Server does
> not exist or access is denied.
> After the error for each step it lists "Not Run" with a white X in a black
> circle. In the past successful execution of the package would list
> success (green icon) for each step moving down through the package as each
> step succeeded (as expected).
> The funny thing is if I open the package up in design mode and execute any
> individual step, the the step executes successfully. I.e., individual
> steps work but not the whole DTS package whether opened in design mode or
> not.
> If I create a new test DTS package, the package works as expected. If I
> save one of the existing DTS packages out to a DTS file and import it back
> into DTS and save it under a different name, same failure behavior as
> listed above. If I create a new DTS package and copy/paste the steps
> individually (from a failing package) into a new package and then save it,
> the package works as expected.
> Any ideas? I need to get this working again because were talking about
> millions of records. And preferably, I would rather not spend two days
> recreating the same packages over again.
> Thanks
> Jerry
> PS - The double post is in leu of time constraints.
>

DTEXEC for remote execute

I have a legacy extraction ("E") .NET 1.1 application that is still going to be the driving force for our ETL process. We are going to be utilizing SSIS for "T" and "L". Now, the "E" phase is running on an application server where we have .NET 1.1 framework installed and working. The SSIS packages are running on a separate SQL Server. The problem here is - how do we call the SSIS package and be able to pass in the right parameters from this .NET app that runs on a separate box? We would like to use DTEXEC to call the remote SSIS packages through Integrated Security. The SSIS packages are stored as File System packages.How about calling the SSIS package(s) via a stored procedure?|||Ok, I was wondering if there was a more efficient way of executing the SSIS package from the remote server without having to go via a stored procedure.|||

yosonu wrote:

Ok, I was wondering if there was a more efficient way of executing the SSIS package from the remote server without having to go via a stored procedure.

Search this forum for the two words "dtexec" and "remote."

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:
>