Dynamic packages and maxconcurrentexecutables

I have recently prototyped a system, in which I use meta data to build a package of execute package tasks.

Essentially, in a Script task I do the following:

1) Create a new package in memory.

2) I add variables, logging to this package.

3) To allow for precedences I create a sequence container for any and all execute pacakge tasks that can run in parallel.

4) To help with parameters specific to the execute package task, I add another sequnce container for the individual excute package task.

5) Finally I add the actual execute package task.

6) Save the package to local disk.

7) Execute this pacakge.

I hadn't been setting explicitly setting the MaxConcurrentExecutables, but upon opening the saved copy of the last package I built and executed it has the default -1 setting.

My problem is that while this in-memory package is running it appears to be only running a single executalbe at a time. I'm going to try setting maxconcurrentexecutable to 4 or some other number to see if I get some parallel execution going on.

The real question is "Is there a limitation on using dynamic packages that limits them to only run a single executable at a time?". I haven't found anything in BOL that leads me to believe there is, but It was very obvious that only one executable would run at a time when I test this out.

How are you testing this? If you run the dynamic generated package directly, do multiple executables run?|||Right now my testing involves watching the staging tables that I have. I expect to see them start populating with data somewhat simultaneously.|||Try opening the generated package, and running it directly through BIDS. There are a number of reasons why a package might not run concurrent executables (system resources, etc), but if it works in BIDS, then we can narrow it down to a code issue.

Dynamic Logging Properties in MS SQL DTS packages

Since I can't seem tofind the Microsoft SQL 2000 forum, I will post this here:

I currently have logging enable on several of my packages.

However, we are still in development of our packages and are reaching upwards

of 100 and logging will eventually need to be active on all of them. In

production, there will still be a development server and a production server,

both with different server names and user id/pwd.

I am looking for a way to dynamically change the logon information for the

logging so that we do not have to have someone go through and manually change

the options. I have tried using Dynamic Properties Task, but this only works on

the 2nd run of the package.

--

As a second question: can anyone explain to me why the errordescription field

in sysdtssteplog is cut short?

Here's the DTS forum:
http://groups.google.com/group/microsoft.public.sqlserver.dts/topics?lnk=srg

dynamic export to excel from web

hi there,

this is my first time really using DTS packages and am trying to export some data to an excel file through a jsp page. This isn't my main problem tho...

the main problem is that i've coded the activeX script to dynamically name the file, as well as add the column headings in the excel file. The problem arises when I go to the data transform task and in the Destination tab, I can't select the worksheet that my code apparently creates.

Naturally it won't run because it will return me an error saying that the destination doesn't exist.

Any thoughts?

Thanks in advance.Are you using SQL 2000? If so, the easiest way would be to use the dynamic properites task, and set the destination of the "transform data task" to a global variable which is your sheet name. Then each time you run the package, the desination is set the the sheet or table name you have created.

Hope this helps.|||That's great help! Thanks!

My next questions are:
1. Is it possible to pop-up a Save As dialog box when you run the DTS? I tried the msoFileDialogSaveAs but of course it doesn't work because it's not an Office app.

2. Is there a way to set global variables in jsp? I've seen code for asp, but haven't found any sample codes.

Thanks again.

Originally posted by SHICKS
Are you using SQL 2000? If so, the easiest way would be to use the dynamic properites task, and set the destination of the "transform data task" to a global variable which is your sheet name. Then each time you run the package, the desination is set the the sheet or table name you have created.

Hope this helps.|||I have a question for you. Does it have to be an excel file? Can it be a Comma Seperated File, which can be viewed in excel. If it can, I would not even use DTS, I would just create a stored proc, and execute it in java and then write the records to a a .csv comma seperated file. Then you can create a link to the file location for download.

I don't know much about java, and how to interface DTS with it. I can only offer suggestions.|||I don't know if JSP supports it, but we use ADO to save a stream of data as XML. Then using an XSL-T transform, we set the file up just about any way the user wants it (comm-delimited, fixed width, different delimiters, etc).

hmscott

Originally posted by SHICKS
I have a question for you. Does it have to be an excel file? Can it be a Comma Seperated File, which can be viewed in excel. If it can, I would not even use DTS, I would just create a stored proc, and execute it in java and then write the records to a a .csv comma seperated file. Then you can create a link to the file location for download.

I don't know much about java, and how to interface DTS with it. I can only offer suggestions.|||Shicks: The problem with creating in a csv file is that if you have a large number field and you try to open the file in Excel, it won't retain the format of the number. I've had this happen on many occasion.

Here's my current situation:
I've now been able to create a dts package that exports data to an excel file. For security reasons, I saved the package as a .dts file on our server to be called by the webserver when run.

The question is.. can I set the global variables of the package by referencing the file itself rather than the package on the server? Also, can this be done in java?

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.