Truncate does not run in SSIS batch process

Question

I have an Execute SQL Task which does a very simple Truncate Table. I have declared table name as variable and in the Expressions, I have set SqlStatementSource as "Truncate Table " + @[User::TableName]

I have also declared the variable in Parameter Mapping

The execute task runs the package successfully. But the same, when I execute via the batch file, I get this error:

Description: Executing the query "" failed with the following error: "Retrieving the COM class factory for component with CLSID {32E37890-EC10-4F89-8D74-1B8CAA4C95F2} failed due to the following error: 800700c1 is not a valid Win32 application. (Exception from HRESULT: 0x800700C1).". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

What is missing here?

Answer 1

@billinkc - Actually the problem is with the SSIS and Visual Studio. It seems to be a Microsoft Bug.

Solution - I uninstalled Visual Studio 2019 and installed Visual Studio 2015 and SSDT 2015 17.4.

The package is now successful, executing from the batch file / executing as a task from Visual Studio.

Answer 2

You're mixing your options.

For Parameters, it is driver dependent. OLE/ODBC use an ordinal based placeholder ? while ADO uses a named parameter @param1 format

However, you're not going to be able to use TRUNCATE TABLE ? with parameterization (or any query that attempts to sub out the table name).

Instead, what you're looking for is an Expression. Expressions can exist on any thing in SSIS. Nearly every Task exposes them, and they even exist on SSIS Variables.

What I would do,

Step 1

Create a second variable, @[User::QueryTruncate] of type String. In the Expression column (after Value), use the following

"TRUNCATE TABLE " + @[User::TableName]

Click evaluate and confirm it renders

TRUNCATE TABLE [dbo].[ng]

Now you have an expression that changes whenever the value of TableName changes.

Step 2

Configure the Execute SQL Task to have a SQLSourceType of Variable In the SourceVariable choose @[User::QueryTruncate]

Clear any mappings you may have in the parameter mapping tab.

But there are Expressions on the Execute SQL Task

Yes, there is an option for building the expression we use to create QueryTruncate, so why not just do it in the task? It will save us a variable and we know, doing less makes things go faster!

True. Absolutely true. However, when an expression goes "wrong" it is impossible to debug what it is. What command did we send to the database? You can't inspect the SSIS package to find out. But, if you've put an Expression on a Variable, it's trivial to set a breakpoint on the offending and inspect the value immediately before the Task executes.

Build out a series of expressions that exist in all of your packages i.e. QueryTruncate, QuerySource, QueryUpdate and now you are on the road to consistent, reusable package patterns.

Addressing defects in SQL Server 2019

While I'm happy to entertain the possibility of there being a bug in the product: a package with an Execute Task, driven by a Variable, which has has an expression building out a TRUNCATE TABLE statement works just fine from the command line