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.

Dyanamic stored procedures

Hi all,
I'm building a search function and I need do it using stored procedures. I'm searching a table on 'Name' 'email address' and 'key words'. (basically 3 columns). The user must be able to use it as
SELECT ......... WHERE (NAME LIKE @.name) AND/OR (Email LIKE @.email) AND/OR ..........
Can some one tell me how to handle this in a stored procedure. By the way is this approch correct. Please tell me if there is a better method to tackle this situation.
Thanks,
-VJ

I always generate a dynamic SQL statement based on what the user has entered and then execute it or use sp_executesql to run it. Some will say that dynamic SQL is bad because the execution plan doesn't get cached like normal procs, but you can get better performance with dynamic SQL sometimes because since the query criteria can change dramtically, the cached execution plan may be a bad one. Always bets to time and view the execution plan in QA, but my experience is that dynamic SQL within the stored proc works best when there are varying search parameters.

|||If it's just 3 columns you're validating, you should just use case statements instead of utilizing dynamic sql.
selelct * from table where -- normal stuff
Name Like (
CASE
WHEN @.name IS NOT NULL THEN @.name
ELSE Name
END
)

-----
A lot of times, I'll validate a string for nulls and empty strings, so I'll usually have this function in most of my databases.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION [dbo].[IsEmptyString]
(
@.String VarChar(2000)
)
RETURNS BIT AS
BEGIN
DECLARE @.ReturnVar BIT
IF (@.String IS NOT NULL AND @.String <> '') -- you can trim if you are avoiding whitespaces.
SET @.ReturnVar = 0
ELSE
SET @.ReturnVar = 1

RETURN @.ReturnVar
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

|||Test it both ways. I've done it with case statements and gotten terrible performance compared with dynamic SQL. It all depends on the situation. Having 3 or 6 or 20 columns really isn't the issue; it's how the procedure performs when varying numbers or combinations of the criteria are entered. I've seen demos where the dynamic SQL blew away using case or isnull.|||You're right that it's completely situational in terms of performance,and many will do their best to keep the actual logic in code as much aspossible. From personal experience, iterations through decentsized databases (2 million records in each of the major tables), a casestatement will perform fairly well as compared to utilizing dynamicsql. It helps further when you require calculations to be madeper record to assist in it's selection criteria (Distance for example),and the one of the factors is based on the parameters. In caseslike this, a case statement tends to outweigh a dump of string to beexecuted in a stored procedure.
Create Procedure KraGiE.ExecuteString
@.SQL varchar (2000)
AS
EXECUTE( @.SQL )
GO
For some reason, that makes me cringe, and I'm sure many database administrators will plainly say "no."