DB-유

SSIS Basics: Building a Simple ETL Package

· mssql, ssis, etl, ssdt

I avoided SSIS for years because the designer confused me: connection managers, control flow, data flow, variables, and a deployment model that changed in 2012. Then someone handed me a nightly CSV import and I gave in. It comes with Standard and Enterprise licences and still does that job well. Here's the smallest useful package: flat file to a table with a truncate first, deployed to the catalog and scheduled with Agent.

Before starting, get these ready. The server needs SQL Server 2016 or 2017 with Integration Services installed. Your workstation needs SSDT for Visual Studio 2017 with the Integration Services template. The target instance needs an SSIS catalog (SSISDB): in SSMS right-click Integration Services Catalogs → Create Catalog, which needs CLR enabled and asks for a password for the encryption key. And have a sample CSV with a header row, e.g. \\fileserver\imports\customers.csv.

Step 1: Create the project and connection managers

In Visual Studio: File → New → Project → Integration Services Project, name it NightlyImport. Set the project's TargetServerVersion (project properties → Configuration Properties → General) to match the server, otherwise the package will be built for a different runtime than the one that executes it.

In the Solution Explorer, right-click Connection Managers → New Connection Manager:

  1. OLEDB – provider SQL Server Native Client 11.0, server sqlprod01, database Staging, Windows authentication. Rename it Staging_OLEDB.
  2. FLATFILE – browse to the CSV, tick Column names in the first data row, set the code page (65001 for UTF-8), and on the Advanced page fix the column data types and lengths. The default is string [DT_STR] of length 50 for everything; a column with longer values will truncate and fail the package. I fix this by hand every time. Rename it Customers_CSV.

Project-level connection managers (the ones created this way) are shared by every package in the project and can be overridden per environment after deployment, which is the whole point of the project deployment model.

Step 2: Build the control flow

Open Package.dtsx. On the Control Flow tab drag in:

  • An Execute SQL Task named Truncate Staging, connection Staging_OLEDB, SQLStatement:
TRUNCATE TABLE dbo.Customers_Stage;
  • A Data Flow Task named Load Customers.

Connect them with the green precedence constraint from the SQL task to the data flow. The default constraint (Success) means the load only runs if the truncate succeeded.

Step 3: Build the data flow

Double-click the Data Flow Task. Add a Flat File Source pointing at Customers_CSV and an OLE DB Destination pointing at Staging_OLEDB, table dbo.Customers_Stage. Connect the blue data path from source to destination and open the destination's Mappings page to confirm every column is matched by name.

Two destination settings matter for performance:

  • Data access mode: Table or view - fast load. This uses a bulk insert instead of row-by-row inserts.
  • Rows per batch / Maximum insert commit size: leave the defaults unless the table is very large, then set commit size to something like 100000 to limit log growth.

If the file's date column is text like 2019-03-31, add a Data Conversion transformation between source and destination converting it to database date [DT_DBDATE], and map the new column to the destination.

Step 4: Run it in the designer

Press F5. Green ticks on both tasks and a row count on the data path mean success. Look at the Progress tab for warnings; the common ones are about unused columns, which are harmless, and about truncation, which are not.

Stop debugging with Shift+F5 before making further changes, or the designer stays read-only.

Step 5: Deploy to the catalog

Right-click the project → Deploy. The Integration Services Deployment Wizard asks for the destination server and a folder in SSISDB (create NightlyImport if it does not exist). This uploads the .ispac built from the project.

In SSMS under Integration Services Catalogs → SSISDB → NightlyImport → Projects, right-click the project → Configure to change connection strings per server without touching the package. For separate test and production values, create an Environment with variables and reference them from the project.

Step 6: Schedule with SQL Server Agent

Create a job with one step of type SQL Server Integration Services Package, package source SSIS Catalog, server sqlprod01, pick \SSISDB\NightlyImport\NightlyImport\Package.dtsx. On the Configuration → Advanced tab tick 32-bit runtime only if you rely on a 32-bit driver such as the old Excel ACE provider. Schedule it, and set a failure notification to the DBA operator.

Did the job run?

Run the job, then check both the catalog report and the table:

SELECT execution_id, folder_name, project_name, package_name, status, start_time, end_time
FROM SSISDB.catalog.executions ORDER BY execution_id DESC;

SELECT COUNT(*) FROM Staging.dbo.Customers_Stage;

status 7 means succeeded, 4 failed. For failures, the All Executions report (right-click SSISDB → Reports → Standard Reports) shows the exact error message per component.

Errors that ate my afternoons

  • "The value could not be converted because of a potential loss of data." A source column is longer or of a different type than the flat file connection manager thinks. Go back to the connection manager's Advanced page and use Suggest Types with a larger sample, or set the lengths manually.
  • "Text was truncated or one or more characters had no match in the target code page." Wrong code page on the flat file connection; for UTF-8 files set 65001 and use DT_WSTR (Unicode) columns.
  • "The package failed to load due to error 0xC0011008 ... The package is trying to load a newer version" The package was built with a TargetServerVersion higher than the server executing it. Change the project property and redeploy.
  • "Login failed for user 'CONTOSO\sqlprod01$'" or access denied on the UNC path when the Agent runs the job. The step runs as the Agent service account; grant it access to the file share and the staging database, or use a proxy account with the right credentials.

Bigger ETL solutions look like this same pattern with more tasks in the control flow.