0

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

user3812887
  • 439
  • 12
  • 33

1 Answers1

0

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] 
+ 
")"
TEEKAY
  • 1,156
  • 1
  • 10
  • 25
  • Although the expression is evaluated correctly, I get the following error while running the SSIS package: [Execute SQL Task] Error: Executing the query "INSERT INTO dbo.FEfiles (FileName,RowCount) VALUES..." failed with the following error: "Incorrect syntax near the keyword 'RowCount'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. – user3812887 Jan 03 '19 at 17:49
  • It looks like there is a problem when I mention column names with the name of the table, where insertions are to be made. INSERT INTO dbo.FEfiles (FileName,RowCount) – user3812887 Jan 03 '19 at 17:49
  • I am able to run the SSIS package successfully by removing the column names: "INSERT INTO dbo.FEfiles SELECT '" + @[User::Filename] + "' , " + (DT_WSTR, 12) @[User::RowCnt] (or) "INSERT INTO dbo.FEfiles VALUES ('" + @[User::Filename] + "'," + (DT_WSTR, 12) @[User::RowCnt] + ")" – user3812887 Jan 03 '19 at 17:49
  • But I would ideally need to mention the column names in the table, since I plan to add a few more columns into the dbo.FEfiles Audit-Tracker table. Can you help me ? – user3812887 Jan 03 '19 at 17:50
  • Also is there any rule book for this sort of parsing in SSIS? Am not able to understand when to use a single quotation(') or a double quotation("). How to use a variable, etc. I have just been taking the codes from the internet. I am spending hours for this coding ! – user3812887 Jan 03 '19 at 17:50
  • Figured out: RowCount is a keyword: so need to add it in square bracket as [RowCount]. Answer: "INSERT INTO dbo.FEFiles (FileName,[RowCount]) VALUES ('" + @[User::Filename] + "'," + (DT_WSTR, 12) @[User::RowCnt] + ")" – user3812887 Jan 03 '19 at 18:04