无需SQL数据库连接，在SSIS中用T-SQL处理CSV并生成ISPAC

Absolutely, you can pull this off without relying on a dedicated business database connection manager in SSIS—here's a practical, step-by-step breakdown tailored to your requirements:

Yes, this is fully achievable. The key is leveraging SQL Server's built-in capabilities to read/write CSV files directly via T-SQL, paired with SSIS's parameterization and Execute SQL Task. You won't need a connection to a specific business database—only a connection to the target SQL Server's master database (which every SQL Server instance includes), and the recipient only needs a working SQL Server environment.

You'll only need a handful of SSIS objects (no custom scripts or third-party tools):

• Package-level Variables: To store parameterized values (source CSV path, output CSV path, SQL Server connection string) so the recipient can modify them without editing the package's core logic.
• Execute SQL Task: The core task that runs your T-SQL logic to read, transform, and write the CSV data.
• OLE DB Connection Manager: Configured to use your parameterized connection string (pointing to the recipient's master database).

1. Set Up Parameterized Variables #

Create 3 package-level string variables in SSIS:

• @SourceCsvPath: Full path to the input CSV (e.g., C:\Data\Source.csv)
• @OutputCsvPath: Full path to the processed output CSV (e.g., C:\Data\Processed.csv)
• @SqlConnectionString: Connection string to the recipient's SQL Server master database (e.g., Data Source=MYSQLINSTANCE;Initial Catalog=master;Integrated Security=SSPI;)

2. Configure the Execute SQL Task #

• Connection Manager: Use an OLE DB Connection Manager, then go to its properties and bind the ConnectionString property to your @SqlConnectionString variable. This lets the recipient update connection details without modifying the connection manager itself.
• SQLStatement: Use dynamic T-SQL (via sp_executesql for safety) to handle CSV operations. Below are two robust approaches:

Approach 1: Using ACE OLE DB Driver (Flexible for Dynamic Schemas) #

This method uses the Microsoft ACE OLE DB driver to read/write CSVs directly, which works even if you don't know the exact CSV schema upfront. The recipient just needs to install the free Microsoft Access Database Engine redistributable (matching their SQL Server's 32/64-bit architecture).

The T-SQL logic (embedded in the Execute SQL Task, with parameter mappings to your SSIS variables):

DECLARE @SourceDir NVARCHAR(MAX) = LEFT(?, CHARINDEX('\', REVERSE(?)) - 1)
DECLARE @SourceFile NVARCHAR(MAX) = RIGHT(?, CHARINDEX('\', REVERSE(?)) - 1)
DECLARE @OutputDir NVARCHAR(MAX) = LEFT(?, CHARINDEX('\', REVERSE(?)) - 1)
DECLARE @OutputFile NVARCHAR(MAX) = RIGHT(?, CHARINDEX('\', REVERSE(?)) - 1)

-- Dynamic query to read and transform the CSV
DECLARE @TransformQuery NVARCHAR(MAX) = N'
SELECT
    FirstName,
    LastName,
    CONCAT(FirstName, '' '', LastName) AS FullName,
    MONTH(CAST(DateString AS DATE)) AS MonthNumber,
    DATENAME(MONTH, CAST(DateString AS DATE)) AS MonthName,
    -- Include all other existing columns from your CSV here
    *
FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'',
                ''Text;HDR=YES;FMT=Delimited;Database=' + @SourceDir + ''',
                ''SELECT * FROM ' + @SourceFile + ''')'

-- Export transformed data to new CSV using bcp (no pre-existing output file needed)
DECLARE @BcpCommand NVARCHAR(MAX) = N'bcp "(' + @TransformQuery + N')" queryout "' + @OutputDir + '\' + @OutputFile + N'" -c -t, -r\n -S ' + @@SERVERNAME + N' -T'

-- Enable xp_cmdshell temporarily if needed
IF NOT EXISTS (SELECT 1 FROM sys.configurations WHERE name = 'xp_cmdshell' AND value = 1)
BEGIN
    EXEC sp_configure 'show advanced options', 1;
    RECONFIGURE;
    EXEC sp_configure 'xp_cmdshell', 1;
    RECONFIGURE;
END

EXEC xp_cmdshell @BcpCommand

-- Optional: Disable xp_cmdshell after use
-- EXEC sp_configure 'xp_cmdshell', 0; RECONFIGURE;

• Map the ? placeholders to your SSIS variables in order: @SourceCsvPath, @SourceCsvPath, @OutputCsvPath, @OutputCsvPath via the Execute SQL Task's "Parameter Mapping" tab.

Approach 2: Using BULK INSERT & Temp Tables (No External Driver Needed) #

If the recipient can't install the ACE driver, use this method which relies solely on SQL Server's built-in tools. You'll need to define the CSV schema upfront in a temp table:

DECLARE @SourcePath NVARCHAR(MAX) = ?
DECLARE @OutputPath NVARCHAR(MAX) = ?

-- Create temp table matching your CSV schema
CREATE TABLE #RawCsv (
    FirstName VARCHAR(100),
    LastName VARCHAR(100),
    DateString VARCHAR(20),
    -- Add all other CSV columns here with appropriate data types
)

-- Bulk import CSV into temp table
BULK INSERT #RawCsv
FROM @SourcePath
WITH (
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n',
    FIRSTROW = 2, -- Skip header row
    CODEPAGE = 'RAW'
)

-- Transform data into another temp table
SELECT
    FirstName,
    LastName,
    CONCAT(FirstName, ' ', LastName) AS FullName,
    MONTH(CAST(DateString AS DATE)) AS MonthNumber,
    DATENAME(MONTH, CAST(DateString AS DATE)) AS MonthName,
    -- Include all other columns from #RawCsv
    *
INTO #ProcessedCsv
FROM #RawCsv

-- Export processed data to output CSV
DECLARE @BcpCommand NVARCHAR(MAX) = N'bcp "SELECT * FROM #ProcessedCsv" queryout "' + @OutputPath + N'" -c -t, -r\n -S ' + @@SERVERNAME + N' -T'

EXEC xp_cmdshell @BcpCommand

-- Cleanup temp tables
DROP TABLE #RawCsv
DROP TABLE #ProcessedCsv

3. Deployment & Recipient Setup #

• Save your SSIS project as an .ispac file.
• The recipient only needs to:
  1. Ensure their SQL Server instance is running (any supported version).
  2. Update the @SqlConnectionString, @SourceCsvPath, and @OutputCsvPath variables to match their environment.
  3. For Approach 1: Install the Microsoft Access Database Engine redistributable.
  4. Run the package via SSIS Catalog, SQL Server Agent, or dtexec.exe.

• No Hardcoding: All critical paths and connection details are parameterized, so the recipient doesn't need to edit the package's core logic.
• Security: Use sp_executesql for dynamic queries to minimize SQL injection risks. If using xp_cmdshell, enable it only temporarily if possible.
• Compatibility: Approach 2 is more compatible across SQL Server versions since it doesn't require external drivers, but requires knowing the CSV schema upfront.

内容的提问来源于stack exchange，提问作者SQL_Kid