SSIS Visual Studio 2017 - Retrieving and storing line number of each record from text file

JB2022 1 Reputation point
2022-04-25T19:57:04.703+00:00

I have an SSIS package that has a foreach loop container with a data flow task inside which loops files inside a folder to store records of text files in a SQL table. I would like to retrieve the line number of each record in the text files and store it in my SQL table as an "int" column. My connection manager is an OLE DB. How would I be able to do this?

Any help would be appreciated.

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,522 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. ZoeHui-MSFT 35,381 Reputation points
    2022-04-26T02:33:42.637+00:00

    Hi @JB2022 ,

    If you only have one file, you could use script task to meet your requirement.

    Here is a tutorial you may take a reference to.

    However, you have multiple files, it is a bit hard to do that via SSIS.

    After loading the file data to the sql database, you may use TSQL code to get Row Count for all Tables.

    SELECT  
          QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TableName]  
          , SUM(sPTN.Rows) AS [RowCount]  
    FROM   
          sys.objects AS sOBJ  
          INNER JOIN sys.partitions AS sPTN  
                ON sOBJ.object_id = sPTN.object_id  
    WHERE  
          sOBJ.type = 'U'  
          AND sOBJ.is_ms_shipped = 0x0  
          AND index_id < 2 -- 0:Heap, 1:Clustered  
    GROUP BY   
          sOBJ.schema_id  
          , sOBJ.name  
    ORDER BY [TableName]  
    GO  
    

    196416-image.png

    Regards,

    Zoe


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    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.

    0 comments No comments