Conversion of DTS to SSIS command Line

I am trying to convert a command line using the dtexecui utility. I need to pass three parameters ; account number ,begin and end date to project.

What am i doing wrong ?

DTEXEC /DTS "\File System\Archive Data" /SERVER SRV2 /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW \package /SET "Account_Number";"'00001'" /SET "File_Name";"'C:\Inetpub\wwwroot\output\Archive\'" /SET "Begin_Date";"'04/03/2006'" /SET "End_Date";"'04/04/2006'"

Error I get

Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Started: 9:52:49 AM
Warning: 2006-04-05 09:52:51.58
Code: 0x80012018
Source: Archive Data
Description: The configuration entry, "Account_Number", has an incorrect form
at because it does not begin with the package delimiter. Prepend "\package" to t
he package path.
End Warning
Warning: 2006-04-05 09:52:51.58
Code: 0x80012017
Source: Archive Data
Description: The package path referenced an object that cannot be found: "Acc
ount_Number". This occurs when an attempt is made to resolve a package path to a
n object that cannot be found.
End Warning
DTExec: Could not set Account_Number value to '00001'.
Started: 9:52:49 AM
Finished: 9:52:51 AM
Elapsed: 2.172 seconds

Your command line is not correct as each set command needs a package path starting with \package just as the error message indicates. As I don't know tasks these properties belong to I can't give you the exact path but in general the set option should look something like "\Package.rest_of_path_to_property". You can use the configurations on the package to identify what the package path should look like. You should also remove the \package from the command line outside of the set because that is invalid.

HTH,

Matt

Conversion of DTS Application to SSIS Application - SourceConnectionId and SourceObjectName

Hello All,

I am trying to convert an application created using DTS classes to SSIS object model. I have found following code in the application.

For x As Integer = 1 To mTask.Properties.Count

If mTask.Properties.Item(x).Name = "SourceConnectionID" Then

CnID = mTask.Properties.Item(x).Value

Exit For

End If

Next

For x As Integer = 1 To mPkg.DTSPackage.Connections.Count

If mPkg.DTSPackage.Connections.Item(x).ID = CnID Then

Return mPkg.DTSPackage.Connections.Item(x).Name

End If

Next

Return mTask.Properties.Item("SourceObjectName").Value

How can I retrieve or Set "SourceConnectinId", "SourceObjectName" of a data flow task in SSIS

Please help me to solve this problem.

Thanks in advance

Subin

Hello All,

How can I retrieve source and destination connection information of a data flow task through code (c#) . My dataflow task have one flat file source, script task and oledb destination. In SQL Server 2000 DTS, there is a SourceConnectionId property to get connection information. Which SSIS property is equivalent to SourceConnectionId of DTS?.

Please help me

Thanks in advance

Subin

|||Subin,
I've merged these two posts from you as they are the same topic. Please don't start a new thread for the same topic you've already posted about.

Thanks,
Phil Brammer|||

Subin wrote:

Hello All,

How can I retrieve source and destination connection information of a data flow task through code (c#) . My dataflow task have one flat file source, script task and oledb destination. In SQL Server 2000 DTS, there is a SourceConnectionId property to get connection information. Which SSIS property is equivalent to SourceConnectionId of DTS?.

Please help me

Thanks in advance

Subin

There is no apples-to-apples equivalent. DTS and SSIS have very different object models.

You will have to iterate over the SSIS object model exactly as you did in DTS. This post should help:

Building Packages Programatically

http://blogs.conchango.com/jamiethomson/archive/2007/03/28/SSIS_3A00_-Building-Packages-Programatically.aspx

As Phil says - there is no need to post a thread more than once unless it has gone unanswered and therefore hidden many pages back.

-Jamie

|||Components reference connections through the RuntimeConnectionCollection collection, a property of the IDTSComponentMetaData90 class.

Controlling log file size

What's the best practice to keep log file size low. I have some databases
that run DTS packages to import data every night. The logs get huge, 17
gigs. I have an Arcserve agent backing them up, but it does not seem to
shrink them.
Any ideas?
Thanks
Greg
What recovery model are you using? Do you care about your transaction logs?
If you don't care about the data within the logs you can set your recovery
model to simple. You can also perform a BACKUP LOG <databasename> WITH
NO_LOG between each export to clear the transaction log.
NOTE: these steps will break transaction log backup/restore. Make sure that
you know what you are doing and how it will impact your database backups
before setting the recovery model or truncating the transaction log.
Keith
"Greg Richards" <grichards@.matrixwebs.com> wrote in message
news:uyL9M0FYEHA.3520@.TK2MSFTNGP10.phx.gbl...
> What's the best practice to keep log file size low. I have some databases
> that run DTS packages to import data every night. The logs get huge, 17
> gigs. I have an Arcserve agent backing them up, but it does not seem to
> shrink them.
> Any ideas?
> Thanks
> Greg
>

Controlling log file size

What's the best practice to keep log file size low. I have some databases
that run DTS packages to import data every night. The logs get huge, 17
gigs. I have an Arcserve agent backing them up, but it does not seem to
shrink them.
Any ideas?
Thanks
GregWhat recovery model are you using? Do you care about your transaction logs?
If you don't care about the data within the logs you can set your recovery
model to simple. You can also perform a BACKUP LOG <databasename> WITH
NO_LOG between each export to clear the transaction log.
NOTE: these steps will break transaction log backup/restore. Make sure that
you know what you are doing and how it will impact your database backups
before setting the recovery model or truncating the transaction log.
--
Keith
"Greg Richards" <grichards@.matrixwebs.com> wrote in message
news:uyL9M0FYEHA.3520@.TK2MSFTNGP10.phx.gbl...
> What's the best practice to keep log file size low. I have some databases
> that run DTS packages to import data every night. The logs get huge, 17
> gigs. I have an Arcserve agent backing them up, but it does not seem to
> shrink them.
> Any ideas?
> Thanks
> Greg
>

Controlling log file size

What's the best practice to keep log file size low. I have some databases
that run DTS packages to import data every night. The logs get huge, 17
gigs. I have an Arcserve agent backing them up, but it does not seem to
shrink them.
Any ideas?
Thanks
GregWhat recovery model are you using? Do you care about your transaction logs?
If you don't care about the data within the logs you can set your recovery
model to simple. You can also perform a BACKUP LOG <databasename> WITH
NO_LOG between each export to clear the transaction log.
NOTE: these steps will break transaction log backup/restore. Make sure that
you know what you are doing and how it will impact your database backups
before setting the recovery model or truncating the transaction log.
Keith
"Greg Richards" <grichards@.matrixwebs.com> wrote in message
news:uyL9M0FYEHA.3520@.TK2MSFTNGP10.phx.gbl...
> What's the best practice to keep log file size low. I have some databases
> that run DTS packages to import data every night. The logs get huge, 17
> gigs. I have an Arcserve agent backing them up, but it does not seem to
> shrink them.
> Any ideas?
> Thanks
> Greg
>

Controlling job steps using ActiveX Script

Hi,
we have to setup a job that will execute a DTS Package to enrich a file.
But we don't want to execute the DTS if the file is not there.
So how can I use an ActiveX Script to control whether the DTS Package is executed?
thanks
Philip
Philip,
Why can't you just check for existence of the file in the DTS package,
if it's not there, exit the package?
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Philip wrote:
> Hi,
> we have to setup a job that will execute a DTS Package to enrich a file.
> But we don't want to execute the DTS if the file is not there.
> So how can I use an ActiveX Script to control whether the DTS Package is executed?
> thanks
> Philip
|||Hi,
Well, how can I exit the package gracefully with no errors - there are 8 steps, so I'd need to exit straightaway?
thanks
Philip
"Mark Allison" wrote:

> Philip,
> Why can't you just check for existence of the file in the DTS package,
> if it's not there, exit the package?
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> Philip wrote:
>
|||Hi,
Well, how can I exit the package gracefully with no errors - there are 8 steps, so I'd need to exit straightaway?
thanks
Philip
"Mark Allison" wrote:

> Philip,
> Why can't you just check for existence of the file in the DTS package,
> if it's not there, exit the package?
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> Philip wrote:
>
|||Philip
CREATE FUNCTION dbo.fn_file_exists(@.filename VARCHAR(300))
RETURNS INT
AS
BEGIN
DECLARE @.file_exists AS INT
EXEC master..xp_fileexist @.filename, @.file_exists OUTPUT
RETURN @.file_exists
END
GO
-- test
SELECT dbo.fn_file_exists('c:\a.txt')
PS. You can define a first step of the job to identify whether or not the
file exist
"Philip" <Philip@.discussions.microsoft.com> wrote in message
news:416843B5-27F1-4BCB-9D32-44C73558F2CC@.microsoft.com...
> Hi,
> we have to setup a job that will execute a DTS Package to enrich a file.
> But we don't want to execute the DTS if the file is not there.
> So how can I use an ActiveX Script to control whether the DTS Package is
executed?
> thanks
> Philip

Controlling job steps using ActiveX Script

Hi,
we have to setup a job that will execute a DTS Package to enrich a file.
But we don't want to execute the DTS if the file is not there.
So how can I use an ActiveX Script to control whether the DTS Package is exe
cuted?
thanks
PhilipPhilip,
Why can't you just check for existence of the file in the DTS package,
if it's not there, exit the package?
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Philip wrote:
> Hi,
> we have to setup a job that will execute a DTS Package to enrich a file.
> But we don't want to execute the DTS if the file is not there.
> So how can I use an ActiveX Script to control whether the DTS Package is e
xecuted?
> thanks
> Philip|||Hi,
Well, how can I exit the package gracefully with no errors - there are 8 ste
ps, so I'd need to exit straightaway?
thanks
Philip
"Mark Allison" wrote:

> Philip,
> Why can't you just check for existence of the file in the DTS package,
> if it's not there, exit the package?
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> Philip wrote:
>|||Hi,
Well, how can I exit the package gracefully with no errors - there are 8 ste
ps, so I'd need to exit straightaway?
thanks
Philip
"Mark Allison" wrote:

> Philip,
> Why can't you just check for existence of the file in the DTS package,
> if it's not there, exit the package?
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> Philip wrote:
>|||Philip
CREATE FUNCTION dbo.fn_file_exists(@.filename VARCHAR(300))
RETURNS INT
AS
BEGIN
DECLARE @.file_exists AS INT
EXEC master..xp_fileexist @.filename, @.file_exists OUTPUT
RETURN @.file_exists
END
GO
-- test
SELECT dbo.fn_file_exists('c:\a.txt')
PS. You can define a first step of the job to identify whether or not the
file exist
"Philip" <Philip@.discussions.microsoft.com> wrote in message
news:416843B5-27F1-4BCB-9D32-44C73558F2CC@.microsoft.com...
> Hi,
> we have to setup a job that will execute a DTS Package to enrich a file.
> But we don't want to execute the DTS if the file is not there.
> So how can I use an ActiveX Script to control whether the DTS Package is
executed?
> thanks
> Philip

Controlling job steps using ActiveX Script

Hi,
we have to setup a job that will execute a DTS Package to enrich a file.
But we don't want to execute the DTS if the file is not there.
So how can I use an ActiveX Script to control whether the DTS Package is executed?
thanks
PhilipPhilip,
Why can't you just check for existence of the file in the DTS package,
if it's not there, exit the package?
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Philip wrote:
> Hi,
> we have to setup a job that will execute a DTS Package to enrich a file.
> But we don't want to execute the DTS if the file is not there.
> So how can I use an ActiveX Script to control whether the DTS Package is executed?
> thanks
> Philip|||Hi,
Well, how can I exit the package gracefully with no errors - there are 8 steps, so I'd need to exit straightaway?
thanks
Philip
"Mark Allison" wrote:
> Philip,
> Why can't you just check for existence of the file in the DTS package,
> if it's not there, exit the package?
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> Philip wrote:
> > Hi,
> >
> > we have to setup a job that will execute a DTS Package to enrich a file.
> >
> > But we don't want to execute the DTS if the file is not there.
> >
> > So how can I use an ActiveX Script to control whether the DTS Package is executed?
> >
> > thanks
> >
> > Philip
>|||Hi,
Well, how can I exit the package gracefully with no errors - there are 8 steps, so I'd need to exit straightaway?
thanks
Philip
"Mark Allison" wrote:
> Philip,
> Why can't you just check for existence of the file in the DTS package,
> if it's not there, exit the package?
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> Philip wrote:
> > Hi,
> >
> > we have to setup a job that will execute a DTS Package to enrich a file.
> >
> > But we don't want to execute the DTS if the file is not there.
> >
> > So how can I use an ActiveX Script to control whether the DTS Package is executed?
> >
> > thanks
> >
> > Philip
>|||Philip
CREATE FUNCTION dbo.fn_file_exists(@.filename VARCHAR(300))
RETURNS INT
AS
BEGIN
DECLARE @.file_exists AS INT
EXEC master..xp_fileexist @.filename, @.file_exists OUTPUT
RETURN @.file_exists
END
GO
-- test
SELECT dbo.fn_file_exists('c:\a.txt')
PS. You can define a first step of the job to identify whether or not the
file exist
"Philip" <Philip@.discussions.microsoft.com> wrote in message
news:416843B5-27F1-4BCB-9D32-44C73558F2CC@.microsoft.com...
> Hi,
> we have to setup a job that will execute a DTS Package to enrich a file.
> But we don't want to execute the DTS if the file is not there.
> So how can I use an ActiveX Script to control whether the DTS Package is
executed?
> thanks
> Philip

Continued DTS support with what SQL Server Version/Edition

We currently have access to another department's SQL server. However they are taking that server offline within a few months, and we will need to migrate our data and DTS packages to a new solution before then. We have about 30 databases and 20 somewhat simple DTS packages, but only a few users need access. Funding is very tight.

SQL 2005's support of DTS is somewhat of a mystery to me. Docs state with 2000 Enterprise client tools installed + Express edition, that I can run DTS packages on Express edition. But the docs do not state the procedure for doing so, and I cannot register the Express server in the Enterprise manager(states must be using 2005 version of Enterprise manager). It seems I can Design a DTS package that resides on the 2000 server and specify a connection to the Express server, but the DTS package would not run, stating always that the table was in use, and I haven't explored it any further. May have just been a quirk.

I'm trying to conquer this in two pieces:

Cheapest solution for graphical design of DTS packages, or some techniology similar(I think I understand that this is called SSIS in 2005?). This would be needed by 1-4 users.

SQL 2005 Developer Edition for each user, only about $50 per user.

MSDE for each user, but can this house DTS packages and be connected to by enterprise manager?

Cheapest solution for data access and ability to run DTS packages by 2-10 users.

If SQL Express Edition is used to house data, can the DTS packages be distributed in some way that allows the users to run them? I.E. exported as VB code or some sort of file that can be loaded by an Access 2002 or VB.NET frontend.

Can all users have Developer Edition or Desktop edition and run the DTS package from it, but have the DTS package connect to the Express Edition server where the data is housed? Or would this be a violation of what the Developer Edition is intended to be used for?

Any other suggested scenarios are welcome.

Are we allowed to continue using the 2000 Enterprise manager after we no longer have access to a licensed SQL 2000 Enterprise server?

The feature comparison for the different 2005 editions doesn't mention DTS, but I know it works for some editions if the add on components are installed. I'm just not sure which versions support this.

1. For development, SQL 2005 dev edition includes sql server 2005 database engine. No msde is required. The server will persist DTS 2000 packages. You cannot connect via 2000 tools, but you can via included 2005 tools. For editing DTS 2000 packages, you need the DTS Designer download, located on msdn as part of the sql 2005 feature pack.

2. The DTS 2000 object model is free. It can be redistributed from the 2000 install disk, or is also available as part of the backward compatibility redist in the feature pack. Once installed on a machine, DTS packages may be run from the command line or from the agent, loading packages resident on 2000 or 2005 servers, or on the file system.

You don't have a need for 2000 enterprise manager here, as your 2005 dev edition will suffice for development work. Howvever, if you want to save your production packages on sql server you will need to attend to licensing for that server. I think msde or express will work, but am not sure. As an alternative, you can save your packages on the file system. There they can be edited by 2000 or 2005 tools.

Consume DataReaderDest from asp.net page?

I have seen the other posts about how to use Microsoft.SqlServer.Dts.DtsClient to run a package and get back the DataReader results. But this fails when run from a client mahcine that does not have SSIS installed. I want to have this page on a web server run the package on a remote Sql Server machine and get back the results but have so far failed. Any one got this working?

protected void Page_Load(object sender, EventArgs e)
{
string path = @."C:\Documents and Settings\Brandon\My Documents\Visual Studio 2005\Projects\Integration Services Project5\Integration Services Project5\FuzzyLookup.dtsx";

DtsConnection connection = new DtsConnection();
connection.ConnectionString = string.Format(@."-f ""{0}""", path);
connection.Open();

DtsCommand command = new DtsCommand(connection);
command.CommandText = "DataReaderDest";

IDataReader reader = command.ExecuteReader(CommandBehavior.Default);

DataSet set = new DataSet();
set.Load(reader, LoadOption.OverwriteChanges, reader.GetSchemaTable().TableName);

_grid.DataSource = set;
_grid.DataBind();

connection.Close();

}

You have to install the SSIS components on the machine where you are executing the SSIS package (which does require a license of SQL Server).

Consume DataReaderDest from asp.net page?

I have seen the other posts about how to use Microsoft.SqlServer.Dts.DtsClient to run a package and get back the DataReader results. But this fails when run from a client mahcine that does not have SSIS installed. I want to have this page on a web server run the package on a remote Sql Server machine and get back the results but have so far failed. Any one got this working?

protected void Page_Load(object sender, EventArgs e)
{
string path = @."C:\Documents and Settings\Brandon\My Documents\Visual Studio 2005\Projects\Integration Services Project5\Integration Services Project5\FuzzyLookup.dtsx";

DtsConnection connection = new DtsConnection();
connection.ConnectionString = string.Format(@."-f ""{0}""", path);
connection.Open();

DtsCommand command = new DtsCommand(connection);
command.CommandText = "DataReaderDest";

IDataReader reader = command.ExecuteReader(CommandBehavior.Default);

DataSet set = new DataSet();
set.Load(reader, LoadOption.OverwriteChanges, reader.GetSchemaTable().TableName);

_grid.DataSource = set;
_grid.DataBind();

connection.Close();

}

You have to install the SSIS components on the machine where you are executing the SSIS package (which does require a license of SQL Server).