locked
Pagination in the SQL query inside the data flow task - OLE DB Source RRS feed

  • Question


  • Hi,
    I have a requirement. Source is a database table and target is  csv file.The maximum number of records in the target should be 500. Lets say, if a table has ,say, 400 records, then I want one single file.

    If the table has 800 records, then I want 2 files one with 500 and the another one with 300. 

    Now, I have a sample with 5001 records and  I want 10 files 500 records each and 1 file with 1 record.


    Now, in SSIS 2016, I have created a storeed procedure which gives the number of files to be created and I kept in a variable. RecordThreshold variable is also there in the package. ie- Now, I have 
    User:NoOfFiles =11
    User:RecordThreshold=500 variables in my ssis.

    Now, i have a for loop container and  inside, there is a the dataflow task, in the ole db database source, i have a query.. Select * from <tabe>...Now, how can i pass User:RecordThreshold  parameter in the where clause, so that it will generate records from 1 to 500 for the first time , 501-1000 for the second time and so on..basically like a pagination.

    Please help!

    Thursday, July 25, 2019 11:41 AM

Answers

  • select * from table where col in (" + @[User:RecordThreshold] +")

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Marked as answer by Revathy Menon Thursday, July 25, 2019 6:34 PM
    Thursday, July 25, 2019 12:40 PM
  • In T-SQL you can use the OFFSET-FETCH Operator for pagination, see SELECT - ORDER BY Clause (Transact-SQL) => offset_fetch

    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Thursday, July 25, 2019 1:50 PM
  • Hi Revathy Menon,

    Please run the following SQL in SSMS.

    It does exactly what you looking for through pure T-SQL.

    It should be relatively easy to adjust it to run in SSIS Loop Container.

    SQL:

    -- DDL and data population, start
    DECLARE @tbl TABLE (
       ID INT PRIMARY KEY
       , [Description] VARCHAR(100) NOT NULL
       );
    
    INSERT INTO @tbl
    VALUES (1, 'One')
       , (2, 'Two')
       , (3, 'Three')
       , (4, 'Four')
       , (5, 'Five')
       , (6, 'Six')
       , (7, 'Seven')
       , (8, 'Eight')
       , (9, 'Nine')
       , (10, 'Ten')
       , (11, 'Elleven')
       , (12, 'Twelve')
       , (13, 'Thirteen');
    -- DDL and data population, end
    
    DECLARE @FileNo INT = 1
       , @NumberOfFilesTotal DECIMAL = 3
       , @RowsPerFile INT
       , @RowTotal INT;
    
    -- To calculate # of rows per each file
    SET @RowTotal = (SELECT COUNT(*) FROM @tbl);
    SET @RowsPerFile = CEILING(@RowTotal/@NumberOfFilesTotal);
    
    WHILE @FileNo <= @NumberOfFilesTotal
    BEGIN
       SELECT * FROM @tbl
       ORDER BY ID
          OFFSET (@FileNo - 1) * @RowsPerFile ROWS
          FETCH NEXT @RowsPerFile ROWS ONLY;
    
       SET @FileNo += 1;
    END;

    • Marked as answer by Revathy Menon Thursday, July 25, 2019 6:34 PM
    Thursday, July 25, 2019 1:59 PM
  • Thanks Yitzhak. Its a already running PROD package. So, I have to use the same variables/formats the package already has.. Thanks for the understanding.

    The EMployee table has 1200 records

    This below code always brings me 500 records...same records...
    but what my requirement is, first time it should fetch 500 records and second time 501-1000 and third time 1001-1200. Please help

    Declare  @RecordThreshold int;  
    Declare  @NoOfFiles int
    Set @RecordThreshold=500; 
    Set @NoOfFiles=3;

    SELECT * FROM       
    [dbo].[EMPLOYEE]
    ORDER BY [EMPLOYEEID]
       OFFSET  (@NoOfFiles-1) * @RecordThreshold ROWS
      FETCH NEXT @RecordThreshold ROWS ONLY;


    Thursday, July 25, 2019 3:30 PM
  • Hi Revathy Menon,

    You need to increment @NoOfFiles variable from 1 to 3 in the SSIS Loop Container.

    For Loop Container in SSIS


    Thursday, July 25, 2019 5:04 PM

All replies

  • select * from table where col in (" + @[User:RecordThreshold] +")

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Marked as answer by Revathy Menon Thursday, July 25, 2019 6:34 PM
    Thursday, July 25, 2019 12:40 PM
  • In T-SQL you can use the OFFSET-FETCH Operator for pagination, see SELECT - ORDER BY Clause (Transact-SQL) => offset_fetch

    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Thursday, July 25, 2019 1:50 PM
  • Hi Revathy Menon,

    Please run the following SQL in SSMS.

    It does exactly what you looking for through pure T-SQL.

    It should be relatively easy to adjust it to run in SSIS Loop Container.

    SQL:

    -- DDL and data population, start
    DECLARE @tbl TABLE (
       ID INT PRIMARY KEY
       , [Description] VARCHAR(100) NOT NULL
       );
    
    INSERT INTO @tbl
    VALUES (1, 'One')
       , (2, 'Two')
       , (3, 'Three')
       , (4, 'Four')
       , (5, 'Five')
       , (6, 'Six')
       , (7, 'Seven')
       , (8, 'Eight')
       , (9, 'Nine')
       , (10, 'Ten')
       , (11, 'Elleven')
       , (12, 'Twelve')
       , (13, 'Thirteen');
    -- DDL and data population, end
    
    DECLARE @FileNo INT = 1
       , @NumberOfFilesTotal DECIMAL = 3
       , @RowsPerFile INT
       , @RowTotal INT;
    
    -- To calculate # of rows per each file
    SET @RowTotal = (SELECT COUNT(*) FROM @tbl);
    SET @RowsPerFile = CEILING(@RowTotal/@NumberOfFilesTotal);
    
    WHILE @FileNo <= @NumberOfFilesTotal
    BEGIN
       SELECT * FROM @tbl
       ORDER BY ID
          OFFSET (@FileNo - 1) * @RowsPerFile ROWS
          FETCH NEXT @RowsPerFile ROWS ONLY;
    
       SET @FileNo += 1;
    END;

    • Marked as answer by Revathy Menon Thursday, July 25, 2019 6:34 PM
    Thursday, July 25, 2019 1:59 PM
  • Thanks Yitzhak. Its a already running PROD package. So, I have to use the same variables/formats the package already has.. Thanks for the understanding.

    The EMployee table has 1200 records

    This below code always brings me 500 records...same records...
    but what my requirement is, first time it should fetch 500 records and second time 501-1000 and third time 1001-1200. Please help

    Declare  @RecordThreshold int;  
    Declare  @NoOfFiles int
    Set @RecordThreshold=500; 
    Set @NoOfFiles=3;

    SELECT * FROM       
    [dbo].[EMPLOYEE]
    ORDER BY [EMPLOYEEID]
       OFFSET  (@NoOfFiles-1) * @RecordThreshold ROWS
      FETCH NEXT @RecordThreshold ROWS ONLY;


    Thursday, July 25, 2019 3:30 PM
  • Hi Revathy Menon,

    You need to increment @NoOfFiles variable from 1 to 3 in the SSIS Loop Container.

    For Loop Container in SSIS


    Thursday, July 25, 2019 5:04 PM