Issue loading 2 tables out of 40

Question

Issue loading 2 tables out of 40

Astrid o 1

Hi,

I have a dynamics SSIS package, that gets a list of tables and procs from another table on the database and check the size of each table to see what tables to load first.

my problem is that out of the 40+ tables, it doesn't load 2 big tables. The biggest table contains 187,511,514 rows and the other table has 21,444,505 rows (basically it doesn't load the two biggest tables.

I imagine that has to do with the buffer or something, any ideas on what I can do?

Yitzhak Khabinsky 20,551 Reputation points

2020-12-07T17:09:34.92+00:00
Please update your question and provide more details:

SSIS version?

What task is failing?

Any error messages?

What database source and destination?

What driver is in use?
Astrid o 1 Reputation point

2020-12-07T18:05:10.06+00:00

I use visual studio 2017, no task is failings, just those two tables are being skipped. No error messages, as everything loads except that. The destination is the sql server and it just needs to run a procedure, not to load data from one place to another.

Astrid o 1

I get the tables with the amount of rows inserted on a table, then I run a query to get the top 1 table.

DECLARE @TmpTable TABLE (FullProcName varchar(50));  
DECLARE @PackageCount int;  
   
WITH cte as (  
   SELECT  TOP 1 FullProcName  
   FROM  [etl].[SSISPackageQueueHubs]  WITH (rowlock, readpast)  
   ORDER BY NumberOfRows DESC  
      )  
DELETE FROM cte  
output deleted.FullProcName INTO @TmpTable  
  
SELECT @PackageCount = count(*) FROM @TmpTable  
if @PackageCount = 1  
BEGIN  
  
 SELECT FullProcName FROM @TmpTable  
END  
ELSE  
BEGIN  
  
 SELECT '' as FullProcName  
END

after than I have an expression task that takes that table name and run a query.

@[User::SQLCommand] = "exec "  + @[User::Pachage1] + " @LoadDate = " + "'" + (DT_WSTR, 30)@[User::LoadDate]+ "'"

Yitzhak Khabinsky 20,551 Reputation points

2020-12-07T18:58:43.19+00:00
Deciphering your reply:

SSIS version?
still unknown

What task is failing?
Execute SQL Task

Any error messages?
None

What database source and destination?
SQL Server of unknown version

What driver is in use?
N/A

So far, it seems that SQL Stored Procedure is failing. And it has nothing to do with SSIS.
Astrid o 1 Reputation point

2021-01-12T15:35:57.653+00:00

Hi,
Sorry it took long to reply. I was not dealing with this issues.
To your questions.

I am using
visual studio 2017, version 15.6.4 with integration services version 14.0.3002.92

nothing fails, and I dont get any error, it just doesnt process those two tables when it is done dynamically.

it is needs to run a query on the sql server, and i am version 2017.

and i use an expression task to run the query. I am not sure what drive it uses. maybe i should try an execute sql task.

Yitzhak Khabinsky 20,551 Reputation points

2020-12-07T17:09:34.92+00:00

Please update your question and provide more details:

SSIS version?

What task is failing?

Any error messages?

What database source and destination?

What driver is in use?
Astrid o 1 Reputation point

2020-12-07T18:05:10.06+00:00

I use visual studio 2017, no task is failings, just those two tables are being skipped. No error messages, as everything loads except that. The destination is the sql server and it just needs to run a procedure, not to load data from one place to another.
Astrid o 1 Reputation point

2020-12-07T18:09:33.56+00:00

I get the tables with the amount of rows inserted on a table, then I run a query to get the top 1 table.

DECLARE @TmpTable TABLE (FullProcName varchar(50)); DECLARE @PackageCount int; WITH cte as ( SELECT TOP 1 FullProcName FROM [etl].[SSISPackageQueueHubs] WITH (rowlock, readpast) ORDER BY NumberOfRows DESC ) DELETE FROM cte output deleted.FullProcName INTO @TmpTable SELECT @PackageCount = count(*) FROM @TmpTable if @PackageCount = 1 BEGIN SELECT FullProcName FROM @TmpTable END ELSE BEGIN SELECT '' as FullProcName END

after than I have an expression task that takes that table name and run a query.

@[User::SQLCommand] = "exec " + @[User::Pachage1] + " @LoadDate = " + "'" + (DT_WSTR, 30)@[User::LoadDate]+ "'"
Yitzhak Khabinsky 20,551 Reputation points

2020-12-07T18:58:43.19+00:00

Deciphering your reply:

SSIS version?
still unknown

What task is failing?
Execute SQL Task

Any error messages?
None

What database source and destination?
SQL Server of unknown version

What driver is in use?
N/A

So far, it seems that SQL Stored Procedure is failing. And it has nothing to do with SSIS.
Astrid o 1 Reputation point

2021-01-12T15:35:57.653+00:00

Hi,
Sorry it took long to reply. I was not dealing with this issues.
To your questions.

I am using
visual studio 2017, version 15.6.4 with integration services version 14.0.3002.92

nothing fails, and I dont get any error, it just doesnt process those two tables when it is done dynamically.

it is needs to run a query on the sql server, and i am version 2017.

and i use an expression task to run the query. I am not sure what drive it uses. maybe i should try an execute sql task.

Answer 1

Monalv-MSFT 5,691

Hi @Astrid o ,

my problem is that out of the 40+ tables, it doesn't load 2 big tables. The biggest table contains 187,511,514 rows and the other table has 21,444,505 rows (basically it doesn't load the two biggest tables.

May I know which were the largest rows that you can load the tables?

We can use Conditional Split Transformation in Data Flow Task to divide these two big tables into several small tables with proper rows.

Best Regards,
Mona

----------

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues in November--What can I do if my transaction log is full?
Hot issues in November--How to convert Profiler trace into a SQL Server table?

Astrid o 1 Reputation point

2020-12-08T13:48:47.697+00:00

Thanks for your answer, but it doesn't apply to my case, I don't have any data flow containers.
Yitzhak Khabinsky 20,551 Reputation points

2020-12-08T14:09:39.01+00:00

Did you see my comment?
"Deciphering your reply..."
Monalv-MSFT 5,691 Reputation points

2020-12-11T08:36:36.12+00:00

Hi @Astrid o ,

Please don't hesitate to contact us if you have anything to update.

Best Regards,
Mona

Answer 2

Most likely you have a logic problem in your code. Since you are using tons of conditions, it is impossible to guess. It is likely not skipping the big tables. You are using %5 to split your list and not accounting for something.

The first thing to do is add some logging of what exactly each "Package*" is processing. That will give you a clue as to if the tables are even reaching that point.

Answer 3

Astrid o 1

I changed the expression task for a sql task and didnt have any more issue.

Issue loading 2 tables out of 40

3 answers

Activity