(4).Configure the second “Execute SQL Task” object
Right-Click the second “Execute SQL Task” to select “Edit…” in menu or Double-Click “Execute SQL Task”

Select the right value as Connection, SQLSourceType, FileConnection next to click “OK” button
(5).Create SSIS Variable and configure the related setting
Create Variable
In main menu, select “SSIS” –> "Variables”

Click Control Flow surface anywhere next to click “Add Variables” button

Key in “strFile” Name and select “String” Data Type
Configure Foreach Loop Container
Right-Click the “Foreach Loop Container” to select “Edit…” in menu or Double-Click “Foreach Loop Container”

Click “Collection”, select “C:\SQLFILE” Folder and assign “ *.sql ” Files

Click “Variable Mappings”, select “User::strFile” variable and click “OK” button
Configure Connection Manager
Click “SQLFILE” Connection ---> Managers, Expresssions ---> “…” button

The property Expressions Editor will show up

Select “Connection String” in Property and click “…” button

Drag and Paste “User::strFile” from Variables to “Expression” surface next to click “OK” button

So does that
“@[User::strFile]” will show on Expression now.
(6).Create file format with SQL languageIn “C:\SQLFILE” path, create “three” SQL Server Query File

In each file, the content is as follows


(7).Build and Start with debugging
When we execute “Build and Start” this SSIS package, the result is successful.

In Microsoft SQL Server Management Studio, the table “TestSQLTask” will be created and the value will be inserted now.
沒有留言:
張貼留言