How to execute a single step in a SSIS package?

Hi all,

I am new to SQL Server 2005 (but many years in SQL Server and .NET), and I am sort of having everything figured out for my company. However, one thing that still bothering me is that:

In the old Server 2000, you can execute a single step in a DTS package by right click the step and then click execute step.

In the new server 2005, I can only execute the whole package from the Management Tool and Edit the pack from VS 2005. Is there also a way for me to execute a single step in a SSIS package?

This is important for us, many thanks!

You can execute a single step from Visual Studio by right-clicking on the step and choosing Execute Task.|||Thanks!

I found more, you must start a new project and then add some existing dts packages into that. After that things will become all good! I just do not understand that why you cannot directly work on a single package without a project!

Thanks
|||

And you have to build that DTS package. This a lot of extra steps over SQL 2000 DTS.

|||

Andymcdba1 wrote:

And you have to build that DTS package. This a lot of extra steps over SQL 2000 DTS.

Build? What do you mean, build? You don't have to build.|||

I have always received an error message when I tried that. I presumed it was because the task I was trying to execute didn't have a "success" message from the preceding task.

I'm sorry that I don't have the text of the error message at the moment. But it was something that made me believe the error was a result of trying to execute a task in the middle of a series of tasks.

You don't have such problems when you try to run tasks that have dependencies on other tasks?

Dan

|||A precedence constraint shouldn't cause a problem. However, if the task you are executing is dependent on something produced in a previous step (such as a text file), then the task will fail.|||

Mr. Welch,

Thanks for the info. I will pay more attention to the error message. If I cannot figure it out, I will post as much information as possible. I won't be back in the office until Monday afternoon.

The task I was trying to execute is one that copies tables from one "database.schema" to another "database.schema". The task is performed by calling a stored procedure in an SSIS "SQL Task" object. There is nothing there other than an "exec my_procedure_name" line and a "GO" line.

I couldn't see any reason that the task would fail to execute, other than a precedence constraint. So I jumped to that conclusion as the reason for failure.

When I pasted the "exec" and "go" lines into an SS05 query window, with the same SQL Server login as is being used in the SSIS connection, it ran just fine.

I hope I can solve this one, since I miss the DTS package ability to execute a single task in an "on success..." series.

Dan

|||

DanR1 wrote:

The task I was trying to execute is one that copies tables from one "database.schema" to another "database.schema". The task is performed by calling a stored procedure in an SSIS "SQL Task" object. There is nothing there other than an "exec my_procedure_name" line and a "GO" line.

Dan

Don't use the "GO" lines. Leave them out. Separate multiple exec statements with a carriage return.|||

Phil,

Thanks for the suggestion. I'll give that a try on Monday.

Dan

|||

Phil,

Thanks for your suggestion. Removing the "GO" line stopped the "ACCESS DENIED" error message from appearing, and allowed the single task to run without any problem.

Dan

How to execute a single step in a SSIS package?

Hi all,

I am new to SQL Server 2005 (but many years in SQL Server and .NET), and I am sort of having everything figured out for my company. However, one thing that still bothering me is that:

In the old Server 2000, you can execute a single step in a DTS package by right click the step and then click execute step.

In the new server 2005, I can only execute the whole package from the Management Tool and Edit the pack from VS 2005. Is there also a way for me to execute a single step in a SSIS package?

This is important for us, many thanks!

You can execute a single step from Visual Studio by right-clicking on the step and choosing Execute Task.|||Thanks!

I found more, you must start a new project and then add some existing dts packages into that. After that things will become all good! I just do not understand that why you cannot directly work on a single package without a project!

Thanks|||

And you have to build that DTS package. This a lot of extra steps over SQL 2000 DTS.

|||

Andymcdba1 wrote:

And you have to build that DTS package. This a lot of extra steps over SQL 2000 DTS.

Build? What do you mean, build? You don't have to build.|||

I have always received an error message when I tried that. I presumed it was because the task I was trying to execute didn't have a "success" message from the preceding task.

I'm sorry that I don't have the text of the error message at the moment. But it was something that made me believe the error was a result of trying to execute a task in the middle of a series of tasks.

You don't have such problems when you try to run tasks that have dependencies on other tasks?

Dan

|||A precedence constraint shouldn't cause a problem. However, if the task you are executing is dependent on something produced in a previous step (such as a text file), then the task will fail.|||

Mr. Welch,

Thanks for the info. I will pay more attention to the error message. If I cannot figure it out, I will post as much information as possible. I won't be back in the office until Monday afternoon.

The task I was trying to execute is one that copies tables from one "database.schema" to another "database.schema". The task is performed by calling a stored procedure in an SSIS "SQL Task" object. There is nothing there other than an "exec my_procedure_name" line and a "GO" line.

I couldn't see any reason that the task would fail to execute, other than a precedence constraint. So I jumped to that conclusion as the reason for failure.

When I pasted the "exec" and "go" lines into an SS05 query window, with the same SQL Server login as is being used in the SSIS connection, it ran just fine.

I hope I can solve this one, since I miss the DTS package ability to execute a single task in an "on success..." series.

Dan

|||

DanR1 wrote:

The task I was trying to execute is one that copies tables from one "database.schema" to another "database.schema". The task is performed by calling a stored procedure in an SSIS "SQL Task" object. There is nothing there other than an "exec my_procedure_name" line and a "GO" line.

Dan

Don't use the "GO" lines. Leave them out. Separate multiple exec statements with a carriage return.|||

Phil,

Thanks for the suggestion. I'll give that a try on Monday.

Dan

|||

Phil,

Thanks for your suggestion. Removing the "GO" line stopped the "ACCESS DENIED" error message from appearing, and allowed the single task to run without any problem.

Dan

How to exclude zero values when sorting?

I have a datagrid with a "sort" field I want to use to sort the rows in ascending order. However, I want values with a 0 or NULL value to be displayed last. I can't figure out how to do a sort (preferably in the SQL) that returns the empty values last. Is this possible?This isn't pretty but why don't you give it a go:
SELECT * FROM mytable WHERE myfield > 0 ORDER BY myfield ASC
UNION SELECT * FROM mytable WHERE myfield = 0

Regards
Fredr!k|||Unfortunately, this isn't valid syntax becuase ORDER BY must be at the end of the query. I get "Incorrect syntax near the keyword 'UNION'." when I try

SELECT * FROM Photo WHERE PhotoOrder > 0 ORDER BY PhotoOrder ASC UNION SELECT * FROM Photo WHERE PhotoOrder = 0|||To use the UNION operator in SQL Server all your Data types must be the same and the same order in both tables, but UNION is restrictive because it performs an Implicit DISTINCT by eliminating DUPLICATES. So if eliminating duplicates is not important try UNION ALL, if it still fails the it is INNER JOIN if both tables are equal or OUTER JOIN if they are not equal. Hope this helps.

Kind regards,
Gift Peddie|||I'm confused - how is this relevant to my question?|||I got what I wanted with
"ORDER BY IsNull(PhotoORDER, 1000)"

Unfortunately, zeroes will still sort first, but I can NULLify them on data entry|||You might try:

ORDER BY
 CASE WHEN ISNULL(PhotoOrder,0) = 0 THEN 2 ELSE 1 END,
 CASE WHEN ISNULL(PhotoOrder,0) <> 0 THEN PhotoOrder

Terri|||I was only replying your UNION error not your original post. I will try to be clear in the future.

Kind regards,
Gift Peddiesql

how to do interactive sorting in sql reporting services 2000

I'm trying to configure a report to sort when a Field(colunm) header is
clicked. I have know idea how to do this. Any help appreciated.
Thanks,
JimHii Jim,
This a new feature of RS 2005, which can be set by using the UserSort
properties on a textbox. For example, select a column header textbox
on your report, then right click and select properties. On the
Interactive Sort tab, check the box to enable the sort and set the
specific expression and scope for the sort.
I'm not aware of any similar feature in RS 2000, but you could use
parameter(s) to prompt a user at report run time. Not interactive, but
at least flexible.
HTH
Matt A
Reporting Services Newsletter at www.reportarchitex.com
Jims wrote:
> I'm trying to configure a report to sort when a Field(colunm) header is
> clicked. I have know idea how to do this. Any help appreciated.
> Thanks,
> Jim