I would like to make a package that would copy data from a table only if table is not empty. I know how to do count and how to make a package for copying data but problem is that Source can't have any inputs so I don't know how to do it. Any suggestions?
Asked
Active
Viewed 1,400 times
0
-
Source Can't Have any Inputs??? – joshua Feb 07 '12 at 11:25
-
I wanted to say that you can't drag green line from a package to a source... or I am wrong and I am missing something? – ilija veselica Feb 07 '12 at 11:38
3 Answers
1
I don't understand your comment about dragging a "green line from a package to a source" but instead of trying to determine in advance if the table is empty, just do your copy anyway and then see how many rows were copied:
- Create a package variable for the rowcount
- Populate the variable using the rowcount transformation
- Use an expression in the precedence constraint to check the variable: if it's greater than zero then continue executing the rest of your package
Pondlife
- 15,992
- 6
- 37
- 51
-
If table is not empty than I have to truncate destionation, otherwise leave as it is. What I meant is that it's not possible to use for example Conditional Split component (that would check if table is empty), drag precedence constraint from it to an OLE DB Source from where I would pick data to copy. – ilija veselica Feb 07 '12 at 12:44
0
@Pondlife I don't think you can use precedence constraint on the data flow task, can you? I believe you can use it only on the control flow.
I would add a "Execute SQL Task" with the count, sending the result to a variable and from this task, I would drag the green arrow to the Data Flow task that makes the copy and on this arrow I would add the expression on the precedence constraint.
Diego
- 34,802
- 21
- 91
- 134
-
you're right about the precedence constraint but it doesn't really matter: once you have the rowcount in a variable, you can use it anywhere a variable can be used in either data or control flows. Your proposal would achieve the same thing as mine in a slightly different way. – Pondlife Feb 07 '12 at 13:24
0
As you have correctly noted, a data flow source does not accept input so one cannot perform logic in the dataflow to determine whether this task should run.
Cannot create connector.
The destination component does not have any available inputs for use in creating a path.
However, there's nothing stopping you from setting up this logic in your control flow. I would use a query that hits the DMVs for a fast rowcount on the destination system, filtered to only the tables I wished to replicate.
Armed with the list of empty tables, it'd probably depend how I'd handle it. For a small number of tables, I'd define N dataflows all with a do nothing script task as a precedent and then use an expression on table name to enable a path, much like I did on this question.
If there are many tables, I'd define a package per table and then invoke execute package task with the package name built dynamically based on the empty table name.
