Issue loading 2 tables out of 40

Astrid o 1

Dec 7, 2020, 4:01 PM

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 26,461 Reputation points

Dec 7, 2020, 5:09 PM
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

Dec 7, 2020, 6:05 PM

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

Dec 7, 2020, 6:09 PM

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 26,461 Reputation points

Dec 7, 2020, 6:58 PM
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

Jan 12, 2021, 3:35 PM

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.

3 answers

Monalv-MSFT 5,906 Reputation points

Dec 8, 2020, 3:27 AM

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?
Please sign in to rate this answer.
Astrid o 1 Reputation point

Dec 8, 2020, 1:48 PM

Thanks for your answer, but it doesn't apply to my case, I don't have any data flow containers.

Yitzhak Khabinsky 26,461 Reputation points

Dec 8, 2020, 2:09 PM

Did you see my comment?
"Deciphering your reply..."

Monalv-MSFT 5,906 Reputation points

Dec 11, 2020, 8:36 AM

Hi @Astrid o ,

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

Best Regards,
Mona
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Tom Phillips 17,746 Reputation points

Jan 12, 2021, 3:57 PM

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.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Astrid o 1 Reputation point

Jan 29, 2021, 1:52 PM

I changed the expression task for a sql task and didnt have any more issue.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Issue loading 2 tables out of 40

3 answers

Your answer