SSIS loading .csv flat files using For Each Loop Container : Execute SQL Task expression to populate the audit table with Row Count

0
votes

I am loading many .csv files into a SQL Server table using SSIS (Visual Studio 2017).

I am using For Each Loop Container. I have two user variables - FileName (string) and RowCnt (int)

I have developed an audit table (dbo.FEfiles) to track each file loaded along with the number of records in each file.

Audit table has two columns:

1) FileName(varchar(20)

2) RowCount(int)

After the setting up of the Data Flow Task inside the For Each Loop Container, I have an Execute SQL Task that connects to my audit table.

In this Execute SQL Task, in the Expression tab, under the SQLStatementSource, I have the following expression to get the file name alone:

"INSERT INTO dbo.FEfiles (FileName) SELECT '" + @[User::Filename] + "' "

This parses correctly when I evaluate the expression, I get:

INSERT INTO dbo.FEfiles (FileName) SELECT 'filename.CSV'

Now, how do I add the variable for the RowCnt, which has integer data type ?

I tried many options such as the one below:

"INSERT INTO dbo.FEfiles (FileName,RowCount) SELECT '" + @[User::Filename] + "', + (DT_WSTR, 12) @[User::RowCnt] "

When I evaluate the expression, I get the following:

INSERT INTO dbo.FEfiles (FileName,RowCount) SELECT 'filename.CSV', + (DT_WSTR, 12) @[User::RowCnt]

I need to instead get this: INSERT INTO dbo.FEfiles (FileName,RowCount) SELECT 'filename.CSV', 0

Can you kindly help me in getting the right expression ?

I even watched this video, but not able to figure out:

https://www.youtube.com/watch?v=Um7tDy9jZRs

enter image description here

1
ssisrowcounttrackerforeach-loop-containeraudit-tables

1 Answers

0
votes

Give this a whirl. 

"INSERT INTO dbo.FEFiles (FileName,RowCount) VALUES ('" + @[User::Filename] + "'," +  (DT_WSTR, 12) @[User::RowCnt] + ")"

Per users comments had to put RowCount into brackets for column name.

"INSERT INTO dbo.FEFiles (FileName,[RowCount]) VALUES ('" + @[User::Filename] + "'," + (DT_WSTR, 12) @[User::RowCnt] + ")"

String parts

"INSERT INTO dbo.FEFiles (FileName,[RowCount]) VALUES ('" 
+ 
@[User::Filename] 
+ 
"'," 
+  
(DT_WSTR, 12) @[User::RowCnt] 
+ 
")"