edit a DTS package in sql 2005?

How do you edit a DTS package that has been migrated to SQL 2005 from SQL
2000? I can see the DTS package by connecting to SSIS on the SQL 2005 box but
can't quite figure out how to edit it.
What are the steps to follow to do this? If it's possible.
Thanks!Per the SQL Server 2K5 Upgrade Advisor
You can use SQL Server 2005 tools to edit your existing DTS packages.
However, upgrading or uninstalling the last instance of SQL Server 2000 on a
computer removes the components required to support this feature. You can
retain or restore these components by installing the special Web download,
â'SQL Server 2000 DTS Designer Components,â' before or after you upgrade or
uninstall SQL Server 2000.
"mp3nomad" wrote:
> How do you edit a DTS package that has been migrated to SQL 2005 from SQL
> 2000? I can see the DTS package by connecting to SSIS on the SQL 2005 box but
> can't quite figure out how to edit it.
> What are the steps to follow to do this? If it's possible.
> Thanks!|||thanks!
"Mark H" wrote:
> Per the SQL Server 2K5 Upgrade Advisor
> You can use SQL Server 2005 tools to edit your existing DTS packages.
> However, upgrading or uninstalling the last instance of SQL Server 2000 on a
> computer removes the components required to support this feature. You can
> retain or restore these components by installing the special Web download,
> â'SQL Server 2000 DTS Designer Components,â' before or after you upgrade or
> uninstall SQL Server 2000.
>
> "mp3nomad" wrote:
> > How do you edit a DTS package that has been migrated to SQL 2005 from SQL
> > 2000? I can see the DTS package by connecting to SSIS on the SQL 2005 box but
> > can't quite figure out how to edit it.
> >
> > What are the steps to follow to do this? If it's possible.
> >
> > Thanks!|||I tried installing this component and I'm getting error messages trying to
open the legacy DTS package. I also have the backward compatibility
components installed as well. Is there something I'm missing here?
What are the steps to follow to move a DTS package from SQL 2000 to SQL 2005
and then edit the package on the SQL 2005 server?
"Mark H" wrote:
> Per the SQL Server 2K5 Upgrade Advisor
> You can use SQL Server 2005 tools to edit your existing DTS packages.
> However, upgrading or uninstalling the last instance of SQL Server 2000 on a
> computer removes the components required to support this feature. You can
> retain or restore these components by installing the special Web download,
> â'SQL Server 2000 DTS Designer Components,â' before or after you upgrade or
> uninstall SQL Server 2000.
>
> "mp3nomad" wrote:
> > How do you edit a DTS package that has been migrated to SQL 2005 from SQL
> > 2000? I can see the DTS package by connecting to SSIS on the SQL 2005 box but
> > can't quite figure out how to edit it.
> >
> > What are the steps to follow to do this? If it's possible.
> >
> > Thanks!

edit a DTS package in sql 2005?

How do you edit a DTS package that has been migrated to SQL 2005 from SQL
2000? I can see the DTS package by connecting to SSIS on the SQL 2005 box but
can't quite figure out how to edit it.
What are the steps to follow to do this? If it's possible.
Thanks!
Per the SQL Server 2K5 Upgrade Advisor
You can use SQL Server 2005 tools to edit your existing DTS packages.
However, upgrading or uninstalling the last instance of SQL Server 2000 on a
computer removes the components required to support this feature. You can
retain or restore these components by installing the special Web download,
“SQL Server 2000 DTS Designer Components,” before or after you upgrade or
uninstall SQL Server 2000.
"mp3nomad" wrote:

> How do you edit a DTS package that has been migrated to SQL 2005 from SQL
> 2000? I can see the DTS package by connecting to SSIS on the SQL 2005 box but
> can't quite figure out how to edit it.
> What are the steps to follow to do this? If it's possible.
> Thanks!
|||thanks!
"Mark H" wrote:
[vbcol=seagreen]
> Per the SQL Server 2K5 Upgrade Advisor
> You can use SQL Server 2005 tools to edit your existing DTS packages.
> However, upgrading or uninstalling the last instance of SQL Server 2000 on a
> computer removes the components required to support this feature. You can
> retain or restore these components by installing the special Web download,
> “SQL Server 2000 DTS Designer Components,” before or after you upgrade or
> uninstall SQL Server 2000.
>
> "mp3nomad" wrote:
|||I tried installing this component and I'm getting error messages trying to
open the legacy DTS package. I also have the backward compatibility
components installed as well. Is there something I'm missing here?
What are the steps to follow to move a DTS package from SQL 2000 to SQL 2005
and then edit the package on the SQL 2005 server?
"Mark H" wrote:
[vbcol=seagreen]
> Per the SQL Server 2K5 Upgrade Advisor
> You can use SQL Server 2005 tools to edit your existing DTS packages.
> However, upgrading or uninstalling the last instance of SQL Server 2000 on a
> computer removes the components required to support this feature. You can
> retain or restore these components by installing the special Web download,
> “SQL Server 2000 DTS Designer Components,” before or after you upgrade or
> uninstall SQL Server 2000.
>
> "mp3nomad" wrote:

edit a DTS package in sql 2005?

How do you edit a DTS package that has been migrated to SQL 2005 from SQL
2000? I can see the DTS package by connecting to SSIS on the SQL 2005 box bu
t
can't quite figure out how to edit it.
What are the steps to follow to do this? If it's possible.
Thanks!Per the SQL Server 2K5 Upgrade Advisor
You can use SQL Server 2005 tools to edit your existing DTS packages.
However, upgrading or uninstalling the last instance of SQL Server 2000 on a
computer removes the components required to support this feature. You can
retain or restore these components by installing the special Web download,
“SQL Server 2000 DTS Designer Components,” before or after you upgrade o
r
uninstall SQL Server 2000.
"mp3nomad" wrote:

> How do you edit a DTS package that has been migrated to SQL 2005 from SQL
> 2000? I can see the DTS package by connecting to SSIS on the SQL 2005 box
but
> can't quite figure out how to edit it.
> What are the steps to follow to do this? If it's possible.
> Thanks!|||thanks!
"Mark H" wrote:
[vbcol=seagreen]
> Per the SQL Server 2K5 Upgrade Advisor
> You can use SQL Server 2005 tools to edit your existing DTS packages.
> However, upgrading or uninstalling the last instance of SQL Server 2000 on
a
> computer removes the components required to support this feature. You can
> retain or restore these components by installing the special Web download,
> “SQL Server 2000 DTS Designer Components,” before or after you upgrade
or
> uninstall SQL Server 2000.
>
> "mp3nomad" wrote:
>|||I tried installing this component and I'm getting error messages trying to
open the legacy DTS package. I also have the backward compatibility
components installed as well. Is there something I'm missing here?
What are the steps to follow to move a DTS package from SQL 2000 to SQL 2005
and then edit the package on the SQL 2005 server?
"Mark H" wrote:
[vbcol=seagreen]
> Per the SQL Server 2K5 Upgrade Advisor
> You can use SQL Server 2005 tools to edit your existing DTS packages.
> However, upgrading or uninstalling the last instance of SQL Server 2000 on
a
> computer removes the components required to support this feature. You can
> retain or restore these components by installing the special Web download,
> “SQL Server 2000 DTS Designer Components,” before or after you upgrade
or
> uninstall SQL Server 2000.
>
> "mp3nomad" wrote:
>sql

Dynamically selecting table in DTS

Hello,

I have to design a DTS package (not SSIS ) in which i want to select the destination table dynamically. Can any one help me out.

Thanks

MV

Are you trying to do this in a bulk insert task or data pump? If you're using the bulk insert task, you can set a connection and the properties of the bulk insert task to be dynamic by using the dynamic properties task. If you're using the data pump, it's slightly more complex. You'll have to use an ActiveX Script task to also set the properties of the destination table to concatenate whatever database you'd like to load to the table name. It's been a while since I looked at the code but it isn't too bad (about 4 lines or so). OR, you can just convert to SSIS :).

Brian

|||

Thanks

i got the way

Dynamically changing a backup file name

Hi all
I have read in many posts that DTS is not usually used for doing backups (a
scheduled job is preferred). However if I want to dynamically change the
name of the backup file depending on the date e.g. mydata_september.bak
during the month of september and then mydata_november.bak in november, how
would I go about doing that?
Dale1.Simple way is use the SQL Server Maintenance plan. It
automatically appends the Year,month,Day and time with the
database name.
Example. The backup file name for pubs database will be
pubs_db_200310132200.BAK.
2. If you want to use the datetime with your format, you
have to write a program.
Sample Script:
--
Declare
@.CurrentDateTime varchar(20),
@.dbname varchar(20),
@.dbbackupname varchar(40)
Begin
Select @.dbname=db_name()
Print @.dbname
Select @.CurrentDateTime = substring(DATENAME(month, getdate
()),1,3) +
cast(DATEPART(day, GETDATE()) as
varchar(2))+
cast(DATEPART(hh, GETDATE()) as
varchar(2)) +
cast(DATEPART(mi, GETDATE()) as
varchar(2))
Select @.dbbackupname = @.dbname+@.CurrentDateTime
select @.dbbackupname ='E:\' + @.dbbackupname + '.bak'
backup database @.dbname to disk=@.dbbackupname WITH
NOUNLOAD , RETAINDAYS = 2, DIFFERENTIAL, SKIP , STATS =10, FORMAT
Print @.CurrentDateTime
End
Warning: Test the script before you use. Use at your own
risk.
- SQLVarad(MDCBA-1999,MCSE-1999)
>--Original Message--
>Hi all
>I have read in many posts that DTS is not usually used
for doing backups (a
>scheduled job is preferred). However if I want to
dynamically change the
>name of the backup file depending on the date e.g.
mydata_september.bak
>during the month of september and then
mydata_november.bak in november, how
>would I go about doing that?
>Dale
>
>.
>|||Thanks, will give it a try
Dale
"SQLVarad" <SQLVarad@.hotmail.com> wrote in message
news:01f101c398d9$706888d0$a601280a@.phx.gbl...
> 1.Simple way is use the SQL Server Maintenance plan. It
> automatically appends the Year,month,Day and time with the
> database name.
> Example. The backup file name for pubs database will be
> pubs_db_200310132200.BAK.
> 2. If you want to use the datetime with your format, you
> have to write a program.
> Sample Script:
> --
> Declare
> @.CurrentDateTime varchar(20),
> @.dbname varchar(20),
> @.dbbackupname varchar(40)
> Begin
> Select @.dbname=db_name()
> Print @.dbname
> Select @.CurrentDateTime = substring(DATENAME(month, getdate
> ()),1,3) +
> cast(DATEPART(day, GETDATE()) as
> varchar(2))+
> cast(DATEPART(hh, GETDATE()) as
> varchar(2)) +
> cast(DATEPART(mi, GETDATE()) as
> varchar(2))
> Select @.dbbackupname = @.dbname+@.CurrentDateTime
> select @.dbbackupname ='E:\' + @.dbbackupname + '.bak'
> backup database @.dbname to disk=@.dbbackupname WITH
> NOUNLOAD , RETAINDAYS = 2, DIFFERENTIAL, SKIP , STATS => 10, FORMAT
> Print @.CurrentDateTime
> End
> Warning: Test the script before you use. Use at your own
> risk.
> - SQLVarad(MDCBA-1999,MCSE-1999)
> >--Original Message--
> >Hi all
> >
> >I have read in many posts that DTS is not usually used
> for doing backups (a
> >scheduled job is preferred). However if I want to
> dynamically change the
> >name of the backup file depending on the date e.g.
> mydata_september.bak
> >during the month of september and then
> mydata_november.bak in november, how
> >would I go about doing that?
> >
> >Dale
> >
> >
> >.
> >

Dynamic Variables in DTS packages.

Hi,
I have built a DTS package to load a file into the database. I want the
package to be such that it can be run on any database. So, I tried
defining a Global Variable by creating a "Global Variable Task" and
creating a global variable for the "Initial Catalog" Property. But when
I try to run this package from the command line using the dtsrun
utility, the global variable is discarded and data is loaded into the
Database specified when I create the DTS. Is there a way to resolve
this? I also tried assigning it an Environmental Variable instead of
the Global Variable but it didn't work.
Thanks,
KPHi
There is no Global Variables task! Do you have a dynamic properties task to
assign the global variable to the property? Do you have workflow in place to
force the dynamic properties task to execute before the task for which the
properties are changed?
John
"kpraoasp@.yahoo.com" wrote:

> Hi,
> I have built a DTS package to load a file into the database. I want the
> package to be such that it can be run on any database. So, I tried
> defining a Global Variable by creating a "Global Variable Task" and
> creating a global variable for the "Initial Catalog" Property. But when
> I try to run this package from the command line using the dtsrun
> utility, the global variable is discarded and data is loaded into the
> Database specified when I create the DTS. Is there a way to resolve
> this? I also tried assigning it an Environmental Variable instead of
> the Global Variable but it didn't work.
> Thanks,
> KP
>

Dynamic Variables in DTS packages.

Hi,
I have built a DTS package to load a file into the database. I want the
package to be such that it can be run on any database. So, I tried
defining a Global Variable by creating a "Global Variable Task" and
creating a global variable for the "Initial Catalog" Property. But when
I try to run this package from the command line using the dtsrun
utility, the global variable is discarded and data is loaded into the
Database specified when I create the DTS. Is there a way to resolve
this? I also tried assigning it an Environmental Variable instead of
the Global Variable but it didn't work.
Thanks,
KPHi
There is no Global Variables task! Do you have a dynamic properties task to
assign the global variable to the property? Do you have workflow in place to
force the dynamic properties task to execute before the task for which the
properties are changed?
John
"kpraoasp@.yahoo.com" wrote:
> Hi,
> I have built a DTS package to load a file into the database. I want the
> package to be such that it can be run on any database. So, I tried
> defining a Global Variable by creating a "Global Variable Task" and
> creating a global variable for the "Initial Catalog" Property. But when
> I try to run this package from the command line using the dtsrun
> utility, the global variable is discarded and data is loaded into the
> Database specified when I create the DTS. Is there a way to resolve
> this? I also tried assigning it an Environmental Variable instead of
> the Global Variable but it didn't work.
> Thanks,
> KP
>

Dynamic Transformations

Hi

I have a dts that is creating a table with not a fixed number of columns. The number of colums depend on a couple of factors based on the data that I'm pulling from other tables.

After some processing I need to dump all the data in the "dynamic" table into an excel doc. My problem is with the transformations within the transform data task. I don't know how many fields I will have in my table and this needs to be mapped to columns within the excel doc. Is it possible to programmatically define the transformations within an activeX script or what can I do.

Thanks

Johnnie

SSIS does not support dynamic metadata. The best that could be done is to have a package for each dynamic type (if this is feasible, i.e. there are a finite number of dynamic types) and then have a master package that picks which of these "dynamic" packages to execute.

Matt

Dynamic transformation mapping in DTS

Hi,

I need to export data from SQL tables to AS400 files(the SQL table has the
same file name and column names as the file on the AS400) .
I created a DTS that has the following tasks: dynamic properties task, SQL
server connection, transform data task and a other connection(ODBC data
source).
I'm using global variables to dynamically set the source and destination
tables names on the transform data task. The problem is the transformations
are not automatically mapped and I get an error message when the
DTS package is executed with a source and destination that has
different columns than the ones specified in the transformation.

Any ideas or possible workaround would be greatly appreciated.
Thank you very much.http://www.sqlservercentral.com/columnists/bknight/convertdb2.asp

HTH|||Hello,

Thank you for your reply, however I read the article for the link you supplied
and it doesn't solve my problem. My fault. I will elaborate and try to be clearer.

As you know, I need to export data from SQL tables to AS400 files(the SQL table has the same structure and column names as the file on the AS400).
I created a DTS that has 2 global variables for the source file and the destination table. I'm supplying a different source and destination file name every time I execute the DTS package(executed from a stored procedure).
In other words, on the first execution of the DTS, the global variables SQL server table A(source) and AS400 file B(destination) will be passed. On the second run, the variables will contain table name C and file name D. So on and so forth.
I need to re-create mapping every time the package is executed because I don't know the column names ahead of time.

In a nutshell, the data transformation task allows you to dynamically specify
a source and destination table name, but consequently, doesn't automatically generate the column mappings.

I don't think the problem lies in the fact that it's an SQL server to AS400 data transfer, but rather, how to create auto mappings based on a given source and destination.

Sorry if I'm being repetitive and I hope all this clarifies my issue.

Still hoping for a solution or workaround!
Thanks once again.|||In my experience Dynamic Mapping is not possible.

Can you link the databases and dynamically write the insert statement?

Can you create a vbsript to create a text file that always has the same layout but different sql source?

Then do your load on AS400 the same way?

Allow each system to control process on itself.

Just some possible suggestions.|||I'm afraid you're right about dynamic mapping not being possible.

Also, you seem to have some good suggestions to a workaround, however
you would need to elaborate because I don't know how to implement them.

Can you provide one or more examples?

Appreciate the response.