question

JB2022-1386 avatar image
0 Votes"
JB2022-1386 asked Zoehui-MSFT answered

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

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

Zoehui-MSFT avatar image
0 Votes"
Zoehui-MSFT answered

Hi @JB2022-1386,

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.


image.png (49.0 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.