Dynamic destination address in SSIS packages

Hi,

I am using VS.net 2003 as a front end and SQL server 2005 backend.

i am creating SSIS packages for Datatransformation programically in .NET.

but the package created is compatible to the previous version of SQL server ie SQL server 2000.

So i need to migrate it in SSIS package compatible to SQL server 2005.

it is migrate also using Data Transformation migration wizard.

But i want to migrate my DTS package programically or by using stored procedure.

Is there any stored procedure or any code is there from which i can migrate DTS into SSIS ?

Thank you

Hi Sanjay,

For what I've read from microsofts webpages about migrating from DTS to SSIS it is far from a simpel process that can be done 100% automatic - some task (especially activex tasks) can not be migrated without human interviention - there is a migration tool that can help you identify what problems you will have and how to solve them. The program is called "Microsoft SQL Server 2005 Upgrade Advisor"

Regards
Simon
|||

Thank you Simon for the replay,

Is there any process from which i can change the desitination address in the package dynamically,If i create my package using business development intelligent studio integrated service.

Every time the destination changes by any means when we require.

or should i am able to create SSIS packages using VS.NET 2003 compatible to SQL server 2005?

Thank you

Sanjay

|||

Hi sanjay,

In Visual Studio 2003, We cannot create or even open SSIS Packages.

I do not understand what do you mean by "change the desitination address in the package dynamically"

Thanks

Subhash Subramanyam

|||

i dont create or even open SSIS packages in VS 2003,

But i am able to run SSIS in SQL server 2005 by scheduling him in SQL jobs..

As i am using SQL server 2005 so i am able to create the SSIS package in SQL server 2005 Integration service.

I am creating it for the data transformation task, but my destination changes.

ie. i want send data from one server to multiple server.

But the data sent through the server is different.ie, no server are getting the same data.

ie. i want to desgin one to many tronsformation in SSIS.

My source server is constant always but the destination server address is saved dynamically from the programme in made in VS2003.

it means the address of destination are multiple and changes whenever it required to.

how i can do it?

I hope you understand my problem.

Thank you.

|||

Sanjay wrote:

i dont create or even open SSIS packages in VS 2003,

But i am able to run SSIS in SQL server 2005 by scheduling him in SQL jobs..

As i am using SQL server 2005 so i am able to create the SSIS package in SQL server 2005 Integration service.

This is not quite right - you are able to RUN SSIS packages from within SQL Server 2005 Intergration Services. If you want to create packages that are not trivial (like the export function in Management Studio) you need to use Visual Studio 2005 (VS2005). If you don't have it, you should be able to install it from the "Clients Components" along with Management Studio.

Sanjay wrote:

I am creating it for the data transformation task, but my destination changes.

ie. i want send data from one server to multiple server.

This is possible with VS2005. You can use dynamically destinations - ex. get values from a table or flat file.

Sanjay wrote:

My source server is constant always but the destination server address is saved dynamically from the programme in made in VS2003.

How is the address saved?
|||

should i am able to run DTS package without migrating into SSIS by scheduling him in SQL jobs in SQL server 2005?

|||

Sanjay wrote:

should i am able to run DTS package without migrating into SSIS by scheduling him in SQL jobs in SQL server 2005?

You can create a SQL Agent Job with a CmdExec (command line) step type. You just need to provide the command line to execute your DTS package.

|||

Sanjay wrote:

should i am able to run DTS package without migrating into SSIS by scheduling him in SQL jobs in SQL server 2005?

Under "SQL Server Management Studio" if you connect to the SQL server you will find: Management>Legacy>Data Transformation Services

Right click the folder at choose import - if you have it as a file.

If you can run it directly from the filesystem I don't remember but you can try it out.