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

Question

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.

Answer 1

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  

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.