Question on SSIS and .NET 1.1

We are migrating our databases from SQL Server 2000 on Win2k to SQL Server 2005 on Win 2k3 machines. Our applications useDTS packages , like creating dynamically in the (application) front end dynamically and sometimes just passing global parameters.

Now with SQL Server 2005 , we have decided to move to SSIS, as SQL Server 2005 has limited support for DTS. Now we were using a com+ assembly from microsoft for using these DTS packages from the front-end(Application). Can we accomplish the same with SSIS using applications developed in .Net Framework 1.1? Are there any work arounds? If not how can we accomplish this in 1.1 framework. Does SSIS expose any API that can be leveraged using .NET 1.1?

Did anyone face any other problems apart from these trying to make 1.1 apps talk to SQL 2005?

I guess this is more of a question to Microsoft experts, or programmers who already had similar experience already migrating to 2005.

It is hard to find information online on this subject. If anyone has valuable information regarding this, please reply with links..

Thanks in advance,

S

My understanding is that a .NET 1.1 application cannot interoperate directly with .NET 2.0 libraries such as the SSIS API. A web service should fit the bill nicely, though as far as just executing packages and passing in variables. If you're actually generating packages, that logic would probably need to be moved completely into the service which would also help insulate your application against future SSIS API changes. Your .NET 1.1 app could talk to the .NET 2.0 service, which could in turn automate the SSIS APIs.

In general, a .NET 1.1 application should not have much trouble making the switch from SQL Server 2000 to 2005, especially if you're using managed providers and calling stored procedures. If the app is calling the database with direct SQL queries, then you may have some minor syntax issues. If you're using procedures the Upgrade Advisor should be able to notify you of any problems, otherwise you'll have to test thoroughly.|||

In addition to nice Jay's wrap up, some mode ideas:

http://blogs.msdn.com/michen/archive/2007/03/22/running-ssis-package-programmatically.aspx

|||Thanks for all the replies..

Question on packages deployment

Hi, all experts here,

Thank you very much for your kind attention.

I am having a question on the deployment of the packages to integration services server. Why after I have deployed the whole solution, but my packages are not actually deployed to the integration services server?

Also, after we add the package manually to the integration services server, to users outside of local host, how could we add permissions for them to execute the packages?

Thanks a lot in advance for your help and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

Hi Helen999888,

I'm not sure how you are deploying the packages or what you mean when you say "add the package manually". Can you provide some detail about your current procedure?

I like to use the Deployment Utility for deployment. In Solution Explorer, right-click the project name and click Properties. On the Deployment Utility page, set the CreateDeploymentUtility property to True. The next time you build the project, a Deployment subdirectory will be created under the bin directory in the project path. Inside this directory will be at least two files: one for each package in the solution and another named the same as the project with the extension: SSISDeploymentManifest. If you double-click the SSISDeploymentManifest file, the SSIS Deployment Wizard starts and will walk you through deploying the package(s) in your project.

There are many ways to execute SSIS packages:

Manually - in either Business Intelligence Development Studio or using DTExecUI. Command Line - using DtExec. SQL Agent Job - using the SQL Server Integration Services step type.|||

Hi, Andy,

Thanks a lot for your advices and help.

With best regards,

Yours sincerely,

Question on moving packages to another server

I am trying to move a package to another server, the package is on the file system and I made certain that the folders are the same and the paths match.

However when executing on the other server I get an error 0xC0012050 whil loadin package file...package failed validation from the Execute Package task. The package cannot run.

The path in the error is correct and the package name is correct.

This is one package executing 2 other packages.

Also then when it tried to run the package I received the "package cannot run" error - I received the ODBC error 08001 error during OPENP in DBOpen ERROR [08001] PLAN ALREADY EXISTS.

Am I getting the second error because I received the first error that the Package cannot run?

How do I determine why the package cannot run on the other server?

MaryOS wrote:

Am I getting the second error because I received the first error that the Package cannot run?

How do I determine why the package cannot run on the other server?

It may be the other way around; a problem with one of the connections may be causing the validation to fail. I don't know about that specific error; but make sure all the connection managers are valid.

Question on Deployment

hello everyone,

I have a question on deployment of SSIS.

My team plans to develop a lot of SSIS packages.

I want my deployment environment to be as centralized as possible, but the processing environment to be distributed.

Here is what I had in mind. I have one server running SQL Server DB and SQL Server Agent. I have all the SSIS packages deployed on this server and schedule them as jobs in SQL Server Agent.

Now I have two other machines just running SSIS service (no other component). When the agent executes the packages, he should use either of the two machines. (Load balancing would be good, or I can manually assign the packages to the machines too).

is this possible? recommended?

Another approach would be that each SSIS Server, also has SQL Server and Agent installed. And respective packages are installed and scheduled on their respective servers. But here I have to maintain the packages and schedules on 2 machines.

regards,
Abhishek.

MSDN Student wrote:

hello everyone,

I have a question on deployment of SSIS.

My team plans to develop a lot of SSIS packages.

I want my deployment environment to be as centralized as possible, but the processing environment to be distributed.

Here is what I had in mind. I have one server running SQL Server DB and SQL Server Agent. I have all the SSIS packages deployed on this server and schedule them as jobs in SQL Server Agent.

Now I have two other machines just running SSIS service (no other component). When the agent executes the packages, he should use either of the two machines. (Load balancing would be good, or I can manually assign the packages to the machines too).

is this possible? recommended?

Another approach would be that each SSIS Server, also has SQL Server and Agent installed. And respective packages are installed and scheduled on their respective servers. But here I have to maintain the packages and schedules on 2 machines.

regards,
Abhishek.

You cannot execute packages remotely so the first scenario you outlined is not possible.

-Jamie

|||

OK. How about this one

On one machine I have SQL Server installed where I deploy all my SSIS packages. On the other two machines I have SQL Agent and SSIS Service installed.

The two machines will read the common SQL Server repository to read SSIS packages and execute them on the respective machines.

I will still be able to keep all the packages in one central SQL Server location, but I will have to configure the SQL Jobs on both the SSIS servers.

Will this work?

regards,
Abhishek.

|||

Yep, that will work fine.

I have to ask why do you want to do this? Unless you are re-using exactly the same (version) package across several machines, it seems like the central dependency doesn't offer that much.