Showing posts with label tasks. Show all posts
Showing posts with label tasks. Show all posts

Monday, March 19, 2012

how to use same variable between the control flow tasks in SSIS 2005?

I want to get the start time of data load and end time after data load and store it in a table which has mapping_id , mapping_name,start_time,end_time.

i use ActiveXScript task to get the start time before data load and store the mapping_id in a global variable,then data flow transformation occurs.

i want to use a global variable to store the mapping id ,so that i can update the end time after data load with that variable.how to do this?

is there any other way, i can get the start and end time of data load (other than the logging information)?

Using an execute SQL task you can do this. One before the data load, and one after.

In the first, you can use System:: StartTime. In the second you can use getdate().

In the first, you'd have to map the parameter, in the second you just use getdate() in the SQL.|||How do you get the mapping id? If you have the mapping id you could use a number of Execute SQL Task shapes in the control flow to update the start and end times at the relevant step.

Greg.

|||What is the mapping_id?

Friday, March 9, 2012

how to use group by (group tasks based on projects)

Hi folks,

I have a Projects , each project have many tasks now i want to display tasks replated to each project:

for example:

Project1-->task1

task2

task3

task4

Project2-->task4

task5

task6

.............................................projectN.....................

how to write query for this

i have 2 tables:

Project .......>columns are projectid

Task->columns are projectid, taskid

|

Select p.projectid, t.Taskid
From Project p
Left Join Task t --For the case of no assigned tasks
ON p.projectid = t.taskid
order by p.projectid, t.taskid

In this case you would not have to use a group by.

Jens K. Suessmeyer.


http://www.sqlserver2005.de

|||its giving taskid 's null|||

Consider this example

Project

1

2

3

Task

1,A

1,B

2,C

I you want all tasks and their projects, you could write

SELECT ProjectID, TaskID

FROM Task

GROUP BY ProjectID, TaskID

ORDER BY ProjectID, TaskID

This would return

1,A

1,B

2,C

but would provide nothing about Project 3 because it has no tasks.

Given the structure of the table (no repeated tasks), you might even get away with

SELECT ProjectID, TaskID

FROM Task

ORDER BY ProjectID, TaskID

However, if you wanted all projects and to include their tasks, if available, then Jens is correct

Select p.projectid, t.Taskid
From Project p
Left Join Task t --For the case of no assigned tasks
ON p.projectid = t.taskid
order by p.projectid, t.taskid

This would return

1, A

1, B

2, C

3, NULL

The NULL on the last row exists because Project 3 is shown (from the Project table) but there is not equivalent task in the Task table.

The word LEFT from LEFT JOIN tells SQL to return all rows from the Project table and, if available, the row data from Tasks too. If there is no row in Task for a Project in Project, then NULL is placed in the task specific columns.

By dropping the word LEFT, it would limit the result to only those projects that had tasks (but the earlier examples are simpler).

If you want to replace NULL with something more useful, using ISNULL, you could write

SELECT p.projectid, ISNULL(t.Taskid, 'No associated tasks')
FROM Project p
LEFT JOIN Task t ON p.projectid = t.taskid
ORDER BY p.projectid, t.taskid

This would now return

1, A

1, B

2, C

3, No associated tasks

regards,

Niall.