Executing osql commands through batch file

Hello

I have a script ,which runs with osql

The script is :

osql -E
declare @.cmd nvarchar(1000)
declare @.cmd2 nvarchar(1000)
declare @.state1 varchar(100)
declare @.message varchar(100)
set @.message = ''
-- Build command to determine state of SQLSERVERAGENT service on Master Server
SET @.CMD = 'create table #state (state varchar(2000))' + char(10) +
'declare @.cmdx varchar(1000)' + char(10) +
'insert into #state EXEC master..xp_servicecontrol ''''QueryState'''', ''''SQLSERVERAGENT'' +
+ char(10) + 'select @.state=state from #state' + char(10) +
'drop table #state'
-- Build command to execute command that determines state of service being monitored
set @.cmd2 = 'declare @.state varchar(100)' + char(10) +
'exec ' + rtrim(@.@.servername) + '.master.dbo.sp_executesql N''' + @.CMD + ''',' +
'N''@.state varchar(100) out'',' +
'@.state out' + char(10) +
'set @.state1 = @.state'
-- Execute command and return state of service being monitored
exec master.dbo.sp_executesql @.cmd2,N'@.state1 varchar(100) out',@.state1 out
-- Is the service that was monitored not
IF (UPPER(@.state1) <> 'RUNNING.')
--if @.state1 <1 'Running.'
begin
-- Display message that primary monitor is down
select @.message = @.message+char(13)+ @.@.servername + ' -' + 'Sql Server Agent Not Running'+char(13)
print 'Master server "' + rtrim(@.@.servername) + '" for monitoring is not available.'
exec master.dbo.xp_smtp_sendmail

It works fine when I run it on the command line prompt.
And I receive a mail , if the server agent is running.

But when I save it as bat file and try to run , it stops and doesnot even give an error.

Can anyone let me know what I can do.

ThanksHello

I have got it working.

Just I need to use :

osql -E -iC:\serveragent.sql -oC:\outputfile.txt

Thanks

ExecuteScalar returns null

I am using the following C# code and T-SQL to get result object from a
SQL Server database. When my application runs, the ExecuteScalar
returns "10/24/2006 2:00:00 PM" if inserting a duplicated record. It
returns null for all other conditions. Does anyone know why? Does
anyone know how to get the output value? Thanks.

-- C# --
aryParams = {'10/24/2006 2pm', '10/26/2006 3pm', 2821077, null};
object oRtnObject = null;
StoredProcCommandWrapper =
myDb.GetStoredProcCommandWrapper(strStoredProcName ,aryParams);
oRtnObject = myDb.ExecuteScalar(StoredProcCommandWrapper);

-- T-SQL --
ALTER PROCEDURE [dbo].[procmyCalendarInsert]
@.pBegin datetime,
@.pEnd datetime,
@.pUserId int,
@.pOutput varchar(200) output
AS
BEGIN
SET NOCOUNT ON;

select * from myCalendar
where beginTime >= @.pBegin and endTime <= @.pEnd and userId = @.pUserId

if @.@.rowcount <0
begin
print 'Path 1'
set @.pOutput = 'Duplicated reservation'
select @.pOutput as 'Result'
return -1
end
else
begin
print 'Path 2'
-- check if upperlimit (2) is reached
select rtrim(cast(beginTime as varchar(30))) + ', ' +
rtrim(cast(endTime as varchar(30)))
,count(rtrim(cast(beginTime as varchar(30))) + ', ' +
rtrim(cast(endTime as varchar(30))))
from myCalendar
group by rtrim(cast(beginTime as varchar(30))) + ', ' +
rtrim(cast(endTime as varchar(30)))
having count(rtrim(cast(beginTime as varchar(30))) + ', ' +
rtrim(cast(endTime as varchar(30)))) =2
and (rtrim(cast(beginTime as varchar(30))) + ', ' +
rtrim(cast(endTime as varchar(30))) =
rtrim(cast(@.pBegin as varchar(20)))+ ', ' + rtrim(cast(@.pEnd as
varchar(20))))

-- If the @.@.rowcount is not equal to 0 then
-- at the time between @.pBegin and @.pEnd the maximum count of 2 is
reached

if @.@.rowcount <0
begin
print 'Path 3'
set @.pOutput = '2 reservations are already taken for the hours'
select @.pOutput as 'Result'
return -1
end
else
begin
print 'Path 4'
--safe to insert
insert dbo.myCalendar(beginTime, endTime,userId)
values (@.pBegin, @.pEnd, @.pUserId)
if @.@.error = 0
begin
print 'Path 4:1 @.@.error=' + cast(@.@.error as varchar(1))
print 'Path 4:1 @.@.rowcount=' + cast(@.@.rowcount as varchar(1))
set @.pOutput = 'Reservation succeeded'
select @.pOutput as 'Result'
return 0
end
else
begin
print 'Path 4:2 @.@.rowcount=' + cast(@.@.rowcount as varchar(1))
set @.pOutput = 'Failed to make reservation'
select @.pOutput as 'Result'
return -1
end
end
end
ENDjs wrote:

Quote:

Originally Posted by

I am using the following C# code

There was no way for you to know it (except maybe by browsing through some
of the previous questions in this newsgroup before posting yours - always a
recommended practice) , but this is a classic ADO newsgroup. ADO.Net bears
very little resemblance to classic ADO so, while you may be lucky enough to
find a dotnet-knowledgeable person here who can answer your question, you
can eliminate the luck factor by posting your question to a group where
those dotnet-knowledgeable people hang out. I suggest
microsoft.public.dotnet.framework.adonet.

But read on:

Quote:

Originally Posted by

and T-SQL to get result object from a
SQL Server database. When my application runs, the ExecuteScalar
returns "10/24/2006 2:00:00 PM" if inserting a duplicated record. It
returns null for all other conditions. Does anyone know why? Does
anyone know how to get the output value? Thanks.
>
-- C# --
aryParams = {'10/24/2006 2pm', '10/26/2006 3pm', 2821077, null};
object oRtnObject = null;
StoredProcCommandWrapper =
myDb.GetStoredProcCommandWrapper(strStoredProcName ,aryParams);
oRtnObject = myDb.ExecuteScalar(StoredProcCommandWrapper);
>
-- T-SQL --
ALTER PROCEDURE [dbo].[procmyCalendarInsert]
@.pBegin datetime,
@.pEnd datetime,
@.pUserId int,
@.pOutput varchar(200) output
AS
BEGIN
SET NOCOUNT ON;
>
select * from myCalendar
where beginTime >= @.pBegin and endTime <= @.pEnd and userId = @.pUserId
>
if @.@.rowcount <0

This is extremely misguided. Not only is it grossly inefficient, retrieving
all the records that meet the requirements, it is also preventing you from
retrieving your output value. SQL Server does not send RETURN and OUTPUT
parameter values to the client until all resultsets are sent. The above
select statement is creating a resultset that wwill be sent to the client.

If you want to verify if records exist, use IF EXISTS, as in

IF EXISTS (select * from myCalendar
where beginTime >= @.pBegin and endTime <= @.pEnd and userId = @.pUserId)

This is more efficient because it does not retrieve a resultset, it only
verifies that the records meeting therequirements exist. If you really want
a count of the records that meet the requirements (which does not seem to be
te case here) you should use:

declare @.cnt int
Set @.cnt= (select count(*) from myCalendar
where beginTime >= @.pBegin and endTime <= @.pEnd and userId = @.pUserId)

Because the result is assigned to a variable, no resultset is created that
needs to be sent to the client.

Quote:

Originally Posted by

begin
print 'Path 1'
set @.pOutput = 'Duplicated reservation'
select @.pOutput as 'Result'
return -1
end
else
begin
print 'Path 2'
-- check if upperlimit (2) is reached
select rtrim(cast(beginTime as varchar(30))) + ', ' +
rtrim(cast(endTime as varchar(30)))
,count(rtrim(cast(beginTime as varchar(30))) + ', ' +
rtrim(cast(endTime as varchar(30))))
from myCalendar
group by rtrim(cast(beginTime as varchar(30))) + ', ' +
rtrim(cast(endTime as varchar(30)))
having count(rtrim(cast(beginTime as varchar(30))) + ', ' +
rtrim(cast(endTime as varchar(30)))) =2
and (rtrim(cast(beginTime as varchar(30))) + ', ' +
rtrim(cast(endTime as varchar(30))) =
rtrim(cast(@.pBegin as varchar(20)))+ ', ' + rtrim(cast(@.pEnd as
varchar(20))))

I'm not sure what the point of the above concatenation is: are you trying to
present a datetime in a particular format? If so, are you aware that

Quote:

Originally Posted by

>
-- If the @.@.rowcount is not equal to 0 then
-- at the time between @.pBegin and @.pEnd the maximum count of 2 is
reached

You do realize that because of the intervening statements, the @.@.rowcount
function returns a different value than was returned the first time you used
it ... ? @.@.error and @.@.rowcount are only useful if used immediately after
the statement you wish to test. New statements cause these functions to
return new values.

Anyways, you already determined above that the records exist. Why bother
checking again?

Quote:

Originally Posted by

>
if @.@.rowcount <0

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

ExecuteNonQuery in SQL 2005

I have an ASP.Net page that runs the following command which is giving me
the following SQL error:
The statement has been terminated.
Subquery returned more than 1 value. This is not permitted when the subquery
follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The code in the page is as follows:
strSQL = "EXEC fd_insFileTaskDefaultsNew " & lngNextFile & ", " &
Request.Form("cboFileTypeID") & ", 0"
cmd = New OleDbCommand(strSQL, conFileData)
lngRows = cmd.ExecuteNonQuery()
It is failing on the last statement and I don't know why. Also, below is
the stored proc code. Can anyone help? Thanks
David
CREATE PROCEDURE dbo.fd_insFileTaskDefaultsNew
(
@.FileNumber int,
@.FileTypeID int,
@.Rows int output
)
AS
/* SET NOCOUNT ON */
INSERT INTO FileTasks
(FileNumber, TaskTitle, TaskDate, PrimaryID, NotifyDate, TaskNotes,
AssignedID)
SELECT @.FileNumber, TaskTitle,
DATEADD(day, TaskDaysOut, GETDATE()), PrimaryID,
CONVERT(char(10), GETDATE(), 101), TaskNotes, AssignedID
FROM FileTaskDefaults
WHERE FileTypeID = @.FileTypeID
RETURN @.RowsTry executing the stored procedure directly from query analyzer and see if
you get the same error?
"David" <dlchase@.lifetimeinc.com> wrote in message
news:uQdZVqTbGHA.1204@.TK2MSFTNGP04.phx.gbl...
> I have an ASP.Net page that runs the following command which is giving me
> the following SQL error:
> The statement has been terminated.
> Subquery returned more than 1 value. This is not permitted when the
subquery
> follows =, !=, <, <= , >, >= or when the subquery is used as an
expression.
> The code in the page is as follows:
> strSQL = "EXEC fd_insFileTaskDefaultsNew " & lngNextFile & ", " &
> Request.Form("cboFileTypeID") & ", 0"
> cmd = New OleDbCommand(strSQL, conFileData)
> lngRows = cmd.ExecuteNonQuery()
> It is failing on the last statement and I don't know why. Also, below is
> the stored proc code. Can anyone help? Thanks
> David
>
> CREATE PROCEDURE dbo.fd_insFileTaskDefaultsNew
> (
> @.FileNumber int,
> @.FileTypeID int,
> @.Rows int output
> )
> AS
> /* SET NOCOUNT ON */
> INSERT INTO FileTasks
> (FileNumber, TaskTitle, TaskDate, PrimaryID, NotifyDate, TaskNotes,
> AssignedID)
> SELECT @.FileNumber, TaskTitle,
> DATEADD(day, TaskDaysOut, GETDATE()), PrimaryID,
> CONVERT(char(10), GETDATE(), 101), TaskNotes, AssignedID
> FROM FileTaskDefaults
> WHERE FileTypeID = @.FileTypeID
> RETURN @.Rows
>|||Yes, same error.
David
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:uqtyUzTbGHA.3992@.TK2MSFTNGP05.phx.gbl...
> Try executing the stored procedure directly from query analyzer and see if
> you get the same error?
> "David" <dlchase@.lifetimeinc.com> wrote in message
> news:uQdZVqTbGHA.1204@.TK2MSFTNGP04.phx.gbl...
> subquery
> expression.
>|||Is FileTaskDefaults a view?
Are there any triggers on FileTasks?
David
"David" <dlchase@.lifetimeinc.com> wrote in message
news:ugsFP3TbGHA.504@.TK2MSFTNGP03.phx.gbl...
> Yes, same error.
> David
> "Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
> news:uqtyUzTbGHA.3992@.TK2MSFTNGP05.phx.gbl...
>|||It doesn't look to me that your output parameter is ever assigned to
any value. Don't you need something like this
SELECT @.FileNumber, TaskTitle,
DATEADD(day, TaskDaysOut, GETDATE()), PrimaryID,
CONVERT(char(10), GETDATE(), 101), TaskNotes, AssignedID
FROM FileTaskDefaults
WHERE FileTypeID = @.FileTypeID
-- assign row count to @.Rows
SELECT @.Rows = @.@.Rowcount|||> RETURN @.Rows
Where does this value get populated? Do you want to use a RETURN, OUTPUT,
or both? I suggest sticking to output parameters for this kind of data, and
not using RETURN. RETURN is meant to return a status code (e.g.
success/failure), not data. This is one of the reasons they're limited to
INTeger datatypes.
A|||That was it! I removed the trigger and it worked. Thank you.
David
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23%236acDUbGHA.4912@.TK2MSFTNGP05.phx.gbl...
> Is FileTaskDefaults a view?
> Are there any triggers on FileTasks?
> David
> "David" <dlchase@.lifetimeinc.com> wrote in message
> news:ugsFP3TbGHA.504@.TK2MSFTNGP03.phx.gbl...
>|||> That was it! I removed the trigger and it worked. Thank you.
I don't see how that is possible, unless either
(a) you didn't post all of the stored procedure code in your original post,
or
(b) you eliminated the error, but you aren't actually verifying that the
stored procedure is correctly returning the rowcount.
Anyway, it sounds like your trigger was written expecting only single-row
row modifications. You should re-visit that logic instead of just throwing
the trigger away, especially if the trigger is not yours and you are not
sure what it was doing.|||David (dlchase@.lifetimeinc.com) writes:
> That was it! I removed the trigger and it worked. Thank you.
And the trigger did not serve any purpose? Yeah, maybe it was just an
old relic, but I get nervous when I hear things like this. Just because
you did not get any error message, does not mean that it worked. If you
removed a trigger that performed some important task to maintain database
integrity, I would not call that working...
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||hopefully the OP removed to trigger temporarily just to confirm that was the
issue and is rewriting the trigger to correct the subquery.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97B6D62F991A4Yazorman@.127.0.0.1...
> David (dlchase@.lifetimeinc.com) writes:
> And the trigger did not serve any purpose? Yeah, maybe it was just an
> old relic, but I get nervous when I hear things like this. Just because
> you did not get any error message, does not mean that it worked. If you
> removed a trigger that performed some important task to maintain database
> integrity, I would not call that working...
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx

execute ssis in sql

Hi,
Using SSIS, how is it possible to execute another ssis package and pass a parameter to it?
For example, I would like to have a sql code that runs the package say "d:\sysappl\CEM\SSIS\CSA.dtsx" and pass a parameter of fileName because the CSA.dtsx package requires a filename.

Something like:
execute "d:\sysappl\CEM\SSIS\CSA.dtsx", varfileName

Thanks

You can use an Execute Process task to execute DTEXEC.exe. If you search for DTEXEC.EXE on Microsoft's site, you'll get a page with all of the parameters necessary to do what you desire.

Or, you can use an Execute Package task along with Parent Package Configurations to pass down a variable value.

Execute SQL Task: Error

I am running a Execute SQL Task which runs a script on a table. It gives me following error:

[Execute SQL Task] Error: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_SingleRow".

Thanks,

Your query in the execute sql task is returning more than one row. Pretty much just what the error states.

If you don't need to return the results of the query to a variable, select "none" instead of "single row" in the ResultSet box. If you do in fact need to return more than one row into a variable, you'll need to select "Full Result Set." The variable you populate with single row needs to match (or be able to cast) the data type of the returned value. If you are using "Full Result Set" you'll need to use a variable of "object" data type and then enumerate through that variable using a foreach loop.|||http://msdn2.microsoft.com/en-us/library/ms141003.aspx

Execute SQL TaSk

I am trying to run a Direct Input SQL query to SELECT MAX value of ColA. This query when I run on Query Window runs fine - means it is NOT a NULL. I get a max value.

When I run this query on a SSIS package outputing to a variable - I get the error. -

[Execute SQL Task] Error: An error occurred while assigning a value to variable "MAXROWKEYID": "The type of the value being assigned to variable "User::MAXROWKEYID" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. ".

I need this output to be used for the subsequent steps which follows this.

I am using Value Type as Double. This was the default when I migrated this DTS package from SQL 2000. I use the proper Single Row for the Result Set and for outputing the Result Set, I use the correct Variable Name.

Help will be greatly appreciated...

Thanks.

SAM.

Obvious first question, what is the type of ColA?

I had some trouble with populating Double variabls recently, I couldn't load it from a field of type decimal. I had to cast the value as a float first.

-Jamie

|||

ColA is my ROWKEYID - it is bigint datatype.

-Sam

|||

Try making the variable type "Int32" or "Int64".

-Jamie

|||

Kept the value type as Double and when I tried to make this as

SELECT CAST(MAX(ROWKEYID) as FLOAT) from Table on SSIS package, it runs fine, successfully, but the value does not change. I mean, when I run on this Query window -the value is different.

But when I try -

SELECT Max(ROWKEYID) FROM Table and making the variable type as Int32 or Int64, the step fails.

-Sam

|||After debugging the task, I found that the value returned by the MAX function on a bigint column is of type String. Thats why your task failed when you used variables of type Int32, Int64 or Double. I see the same behavior when MAX function is called on a decimal column. However, MAX function on a int column returns Int32 type and float column returns a Double type.

Execute Process Task arguments

We are attempting to use SSIS execute process task.

In a cmd.exe session the executable runs with normal arguments:

mycmd < myscript.txt > foo.txt 2>error.txt

We have attempted to several permutations of this without success. We declared variables and assigned them to stdin, stdout, stderr. However, mycmd opens in the SSIS session as if no arguments have been supplied.

Is there some expression that is required to connect the stdin/out/err variables in the Arguments within Execute Process Task. The help/examples are terse.

Redirections using < and > are not arguments, they are special symbols treated by cmd.exe.

To get the same behavior from Execute Process Task, you'll need to read content of myscript.txt into a variable and redirect program input to this variable; redirect output and errors to two other variables, then after program ends save these variables into foo.txt and error.txt.

But the easiest way to solve it is to use the 'cmd.exe' as the task executable, and supply your command (/C mycmd < myscript.txt > foo.txt 2>error.txt) as agruments for cmd.exe (/C added to instruct cmd.exe to executed specified command).|||

Thanks! We somehow missed the /C switch and that solved the problem.

Loading myscript.txt into a variable for use by stdin seems a bit problematic in our situation since it could be several hundred lines long and contain a wide spectrum of characters related to regular expressions that just seem like the perfect opportunity to tip it over.

We may look at the use of variables in the argument and see if we can move the scrum ahead.

|||Hello.

I'm trying to use the "Execute Process" task to compress several ASCII files in a folder in one ZIP archive. The command line for it looks as follows:

c:\programme\7zip\7z.exe a -bd U:\Projekte\TFG\Software\Log_storage\SCD_Demo_isHASH_34.zip SCD_Demo_isHASH_*.log

The result file has size of 2 KB.

When I put this command in the "Arguments" property of the "Execute Process" component (with "/C" switch and cmd.exe as executable), I get an empty ZIP file. What could be a reason for it? Has anyone already experienced such behaviour?

Regards,
Andrey

Execute Package Task with SQL Server Location

Have written a SSIS package which in turn runs a couple of child packages via the Execute Package control flow task. All is fine if I use a Location of File System for the task. However when I deploy to production I'd like the main package and it's child packages to be under SQL Server.

I can set the Execute Package task location to SQL Server after copying my packages into a development SQL box and again it works. However as it's now executing the package from SQL rather than wihin BIDS it doesn't give me the debugging/flow information for the Child package when I run the parent package. It's also a bit of a pain because even though all the packages are part of the same solution if I make a change to a child package I've got to remember to re-save a copy of it to the SQL database.

Have looked into seeing if I can make the location field for the Execute package task configurable so that I could run as a File System package within BIDS but as a SQL package in production but the Location field doesn't seem to be exposed as a property for the Execute Package task so can't set this as an expression or from a configuration.

Does anyone have any advice on using the Executing package task for child packages which are stored in SQL.

What I do, for smaller packages, is to create a boolean variable -- SQLServerExecution -- and then create two flows on the control flow. Each flow does exactly the same thing, except on one flow, there's a precedence constraint that checks for SQLServerExecution to be true (and if so, execute that branch) and the other checks for it to be false.

Setup the false branch's Execute Package tasks to use the file system, and setup the true branch's Execute Package tasks to use SQL Server.

Or, create two master packages -- one for SQL Server packages, the other for file system packages.|||

My only advice is to use the same store type in all the environments. Otherwise support and deployment could become a nightmare as you won't be able to reproduce specific issues.

Sorry, I know that this does not answer your question.

|||

Thank you Phil that makes perfect sense. I'll have a think how easy it is to apply the solution to my parent package.

Think in future I'll also need to lay my C#/OO head to one side when doing SSIS and think a lot more carefully about when to split work off to seperate packages rather than have them as seperate Data Flows/Control flow areas etc within the same package.

|||

It wasn't really a specific question more an "any advice on this" type plea so your advice is most welcome Rafael, thank you.

Execute Package Task always fails.

I have a package (i.e. child package) which runs itself perfectly fine without displaying any error.

However when I embeded this package inside another package (i.e. parent package) using Execute Package Task. The task always fails. It seems strange enough.

The child package has two variables that need to be passed in from parent package.

Can any expert here please help out? Thanks.

- Steve

What are the errors? Look in the output window and turn on some logging.|||

Error 1 Error loading Scheduling_F580021.dtsx: The connection "{34589715-3053-4b26-9769-0b8ecc198d85}" is not found. This error is thrown by Connections collection when the specific connection element is not found. C:\Scheduling_data.dtsx 1 1
here is the error:

I have kept getting this annoying error.

|||

Just doulble check the connection manager that is being used by the Execute PAckage task. I would drop the connection mamanger and add it back from the Execute Package task: COnnection : <New Connection>.

Rafael Salas

|||

Here is what I did to fix it ...

i delete the troubled child package completely, and rewrote it from the sratch. And it started to work.

Just as a reminder:

Please note that the old trouble child package contains some copy-and-pasted tasks (and connections) from parent package. I often use copy-and-paste to develop packages. Seems that there are some problem with it. Not sure if SSIS has a bug or something else.

|||

The problem looks like it was with a task which had been pasted in. Most tasks actually store the connection ID, the "{34589715-3053-4b26-9769-0b8ecc198d85}" GUID, rather than the name, although you generally always see the name. If you see the GUID it normally means that the connection with that ID does not exist, and therefore it cannot translate the ID to the name for display and general use or validation. In otherwords the task refers to a connection that does not exist.

Using the ID over the name means that you can rename connections and stuff keeps working, but conversely means that you cannot just create a connecion with a matching name, and expect it to be the same.

|||

you made a good point.

It should be more cautious to copy-and-paste tasks, connection, or packages for rapid SSIS development. A package works perfectly fine using copy-and-paste task/connection if it runs itself.

This does cause a pain when developing a complex package (parent-child package) if we cannot use copy-and-paste. Is Microsoft SSIS team aware of it or do they have any plan to fix it ?

|||

Personally, I do not see that such pain you are taking about. If you are copying a DataFlow task from one package to another; you would need eventually to check or re-define your connection managers; since you may need the same combination of tasks but different connections. Changing the connection managers inside of a task is just a click and select from a dropdown list task.

In the other hand, if you need more than copying the dataFlow task, meaning you also need the references to the connection managers; then you are better creating a copy of the whole package and then adding and droping tasks/logic as needed

This is just my opinion...

Rafael Salas

|||

I not only copy-paste tasks from parent package to child packages, I copy-paste all connections as well. I used the same connections which configures on the fly based on the ConnectionString. The connectionString is a variable which is assembled and determined at the runtime.

I also copy packages too all times. I use copy-paste on everything for rapid development. Somewhere, the strange errors come up when I tried to build a complex package (like grand-parent package). And it is very hard to debug. I know and have used debugging tools and logging as well. It can be painful without knowing the code behind the scene. I run all of packages with settings (i.e. connections, queries, and tasks) to be configured on the fly.

|||

Perhaps you should look at making some packages re-usable if you do so much copy and paste. It is often easier said than done, but since you say you do it so much and you find this such an issue I'd be surprised if you could not get some reuse.

The benefit of the current behaviour is the ability to rename connections without breaking task references, something that got a lot of feedback during the beta, and for me that is very usefull. Make a suggestion though - http://connect.microsoft.com

|||

As a matter of fact, I have exploited a lot of reuse feature as much as I can.

Here is the scenario: Say, I need to develop 300 packages. I do not want to create a blank package to start with. Often times, I use Copy-and-paste some similar package and rename it. That could save me a lot of time. If I start with an existing package, it may save me a lot of time.

Steve

Execute Package Task

Hi,
I have a DTS package that runs numerous tasks simultaneously for time and
performance reasons, but I would like to add an "Execute Package Task" to
this package so that another package will run, but only after all tasks have
finished in this package. Is there a way to do this?
Thanks!Hi Patrice,
Try connect all your tasks to a dummy task using workflow properties.
Finally connect your final "Execute Package Task" to the dummy task.
Dennis.
"Patrice" wrote:

> Hi,
> I have a DTS package that runs numerous tasks simultaneously for time and
> performance reasons, but I would like to add an "Execute Package Task" to
> this package so that another package will run, but only after all tasks ha
ve
> finished in this package. Is there a way to do this?
> Thanks!