SSDT Execute SQL task is not resolving parameters
-
Summary:
SSDT is not resolving parameters in a simple statement DROP TABLE [?].[zzz_?]Details:
Environment:Microsoft Visual Studio 2015 Shell (Integrated) Version 14.0.23107.0 D14REL
Microsoft .NET Framework Version 4.6.01055
Installed Version: IDE Standard
Microsoft Visual Studio Tools for Applications 2015 00322-10000-00000-AA039
Microsoft Visual Studio Tools for Applications 2015
Visual Basic 2015 00322-10000-00000-AA039
Microsoft Visual Basic 2015
Visual C# 2015 00322-10000-00000-AA039
Microsoft Visual C# 2015
SQL Server Analysis Services 14.0.608.142
Microsoft SQL Server Analysis Services Designer
Version 14.0.608.142
SQL Server Data Tools 14.0.61705.170
Microsoft SQL Server Data Tools
SQL Server Integration Services
Microsoft SQL Server Integration Services Designer
Version 14.0.600.250
SQL Server Reporting Services 14.0.608.142
Microsoft SQL Server Reporting Services Designers
Version 14.0.608.142The remote server is:
Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64) Oct 20 2015 15:36:27 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
Visual Studio is not installed, so this launches the VS2015 isolated shell.
I need to drop a table at the top of my flow. My connection to the server is OLE DB. In my Execute SQL task, if I code:
DROP TABLE [content].[FACILITY]it works fine. So, no issues connecting to the server or in the code itself.
But, my task is configured to use parameters as follows:
where User::Schema = content and User::Table = FACILITY (Note: the connection above is deleted from the screenshot, it is not blank)(My "actual" code will eventually be:
IF (EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE
TABLE_SCHEMA = '?'
AND
TABLE_NAME = 'zzz_?'
))
BEGIN
DROP TABLE [?].[zzz_?]
ENDHowever, here are the results when I execute this task:
SSIS package "C:\Users\FUBAR\Documents\Visual Studio 2015\Projects\MyProject\MyProject\FACILITY.dtsx" starting.
Error: 0xC002F210 at Drop zzz table, Execute SQL Task: Executing the query "drop table ?.zzz_?" failed with the following error: "Incorrect syntax near '@P1'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Drop zzz table
SSIS package "C:\Users\FUBAR\Documents\Visual Studio 2015\Projects\MyProject\MyProject\FACILITY.dtsx" finished: Success.I've Googled all over before posting, and I think my syntax is correct. Here are some hits:
http://www.sqlchick.com/entries/2013/1/20/resolving-an-ssis-error-due-to-parameters-not-mapped-correct.html
https://www.red-gate.com/simple-talk/sql/ssis/passing-variables-to-and-from-an-ssis-task/So why aren't my SQL parameters getting resolved?
Thanks...
-
What you're trying to do there is use a variable to declare a schema and table name. The SQL you're attempting to run would basically translate into something like:
DECLARE @Schema varchar(30);
DECLARE @Table varchar(30);SELECT @Schema = 'dbo', @Table = 'MyTable';
DROP TABLE [@Schema].[zzz_@Table];
SQL can't resolve parameters/variables into objects in a statement, you have to do it dynamically.This following should work instead (not tested):
DECLARE @sql;
SET @sql = 'DROP TABLE [ ' + ? + '].[zzz_' + ? + '];';
EXEC (@SQL);
--You can probably short hand this to:
EXEC ('DROP TABLE [ ' + ? + '].[zzz_' + ? + '];');Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
If Thom's solution does not work directly in an ExecuteSQL task, with ? parameters, there is another option.
- Create a 'calculated' variable (a variable whose definition is an Expression) which builds the entire SQL string to be executed, including the values of the params.
- Add an Expression to your ExecuteSQL task which assigns the above variable to the SQLStatementSource property.The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin - Phil Parkin - Thursday, July 20, 2017 6:16 AM
To be honest, that's actually what I normally do, but due to the wording of the question my mind was stuck in "MUST USE DIRECT SQL" mode. 😉
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Thanks for the replies. I appreciate the workarounds to the shortcomings in SSDT, and its failure to comply with its own documentation.
What you're trying to do there is use a variable to declare a schema and table name. The SQL you're attempting to run would basically translate into something like:
DECLARE @Schema varchar(30);
DECLARE @Table varchar(30);SELECT @Schema = 'dbo', @Table = 'MyTable';
DROP TABLE [@Schema].[zzz_@Table];Actually, I was expecting SSDT to replace the ? placeholders in the code in the client-side with the mapped variables BEFORE passing the resolved code to SQL Server.
I note this from the SSDT documentation: https://docs.microsoft.com/en-us/sql/integration-services/control-flow/execute-sql-task. Search on "Run parameterized SQL commands".
However, it does say in the Note: "You can use parameters in an Execute SQL task only if the data provider supports them."
In this case, I am using the OLE DB data provider, so hitting that doc: https://msdn.microsoft.com/en-us/library/ms140355(v=sql.120).aspx
I believe I have correctly followed the instructions in the above MS documentation links, but still the parameters are not resolving. I'm also using the same approach as in the two links previously posted:
http://www.sqlchick.com/entries/2013/1/20/resolving-an-ssis-error-due-to-parameters-not-mapped-correct.html
https://www.red-gate.com/simple-talk/sql/ssis/passing-variables-to-and-from-an-ssis-task/So, does SSDT work as advertised and documented, or not? Or am I doing something wrong?
I'd like an answer to the above before I consider the workarounds suggested.
P.S.: Background...I'm trying to put together a POC to convince my boss to use SSDT vs. an alternative data integration tool we already use. SSDT has some advantages over that tool, esp. the Balanced Data Distributor task. However, it's hard to make that argument if the tool doesn't function as advertised - he would rightly wonder what else might be wrong with the product, and would instead go with the comfortable option to use our current DI tool.
-
No, you're doing it incorrectly. You can't parametrise an object name. The documentation you've linked to even tells you where you can use parameters. Both SQL and SSDT are working as expected.
It's not that it isn't working as advertised, you are using it incorrectly. Both myself and Phil have given examples of how you need to supply parameterised objects.
The document does show you, however, how to do parameterised querier in the where clause, for example. That syntax is very similar:
[Code]SELECT CustomerID
FROM Customer
WHERE FirstName = ?
AND LastName = ?;[/code]
The document, at no point, replaces the name of an object with a parameter. Neither does Red Gate or SQL Chick, because it doesn't work like that.Look at the answers both myself and Phil have given. If you really want to see more, also open up a trace, and have a look at the SQL that SSDT is trying to run oin all 3 instances. You'll quickly see why your version doesn't work.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - Thom A - Friday, July 21, 2017 1:42 AM
Agreed on all of these points. Good stuff, Thom, you beat me to it.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Again, thanks for the replies, much appreciated.
Ok, I found a link that helped my understanding: https://www.mssqltips.com/sqlservertip/2981/using-parameters-for-sql-server-queries-and-stored-procedures/
@thom-2 A:
The documentation you've linked to even tells you where you can use parameters.
To help my understanding/education a bit more, can you explain how the documentation says that? Because I don't see it. Should someone new to SSDT read that documentation and know that a tablename cannot be parameterized?
I have gotten @Phil's approach to work using a "calculated" variable using an expression. However, if I write a stored procedure to drop a table if it exists, can the parm to a stored process be parameterized? Something like exec sp_DropTableIfExists ?, where ? is a mapped variable?
Finally, I still think SSDT could have been designed to resolve the placeholders on the client side before passing the resolved SQL to SQL Server. Would that have been an improved design?
- Scott In Sydney - Friday, July 21, 2017 6:39 AM
Behaviour which you are attributing to SSDT should really be directed at SQL Server. Parameterised queries in SSDT are passed to the SQL Server DB engine as parameterised queries.
Stored proc params can be parameterised in the way you describe, no problem. But you'll still need to use dynamic SQL in your stored proc to make the DROP work as you wish.
Regarding your final comment ... maybe. Though that would result in it being more difficult for the DB engine to re-use query plans, and that could easily become an issue.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
My actual question is resolved, but for completeness...
I will create the below SP:
ALTER PROCEDURE sp_DropTable (@tableName VARCHAR(255))
AS
BEGIN
DECLARE @sql VARCHAR(1000);
SET @sql='BEGIN TRY
IF EXISTS(
SELECT 1 FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N''{0}'') AND type = (N''U''))
BEGIN
DROP TABLE {0}
PRINT ''{0} dropped.''
END
ELSE
BEGIN
PRINT ''{0} does not exist.''
END
END TRY
BEGIN CATCH
PRINT ''Error dropping {0}''
END CATCH
'
SET @sql=REPLACE(@sql,'{0}',@tableName)
--PRINT @sql;
EXEC (@SQL);
ENDThen in SSDT I will code my Execute SQL Task as:
exec sp_DropTable ?and map my tableName variable to Parameter 0.
Thanks for the help.
- Scott In Sydney - Friday, July 21, 2017 8:43 AM
Glad to see that you got a working solution, how ever, I would use caution with that particular one. You could easily suffer SQL injection from that particular SP. For example, what if use used the ta let name;
Test; CREATE DATABASE [YourNewDB];
I suspect that would you'd have a nice new DB on your server.Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - Thom A - Monday, July 24, 2017 6:42 AM
Hi @thom-2 A,
The server isn't externally accessible. Only a handful of people (i.e. me) have write access/drop table access on this database. Only our DBA's have administrator (i.e. create database) access. The SP is just a helper SP for SSIS/SSDT, although it could also be useful in SSMS as well. Anyone maliciously calling it with SQL Injection parameters would likely get fired.
Having said all that, it's best to develop good coding habits, so below is V2 of my SP.
Questions:
1) I'm not sure if I should have separate schema and table parameters? That would make it more explicit. It would also allow unquoted parameters (which is really minor). What I mean by that is:
uspDropTableV2 schema, table
rather than
uspDropTableV2 'schema.table' -- the dot means I have to quote the parameter
2) I'm not sure about my approach of using placeholders (eg. {somevar}) in my SQL text, then using the REPLACE function to replace them? I Googled whether T-SQL had string formatting capabilities, found https://stackoverflow.com/questions/159554/string-format-like-functionality-in-t-sql. Any feedback on myapproach? For me, I find it less verbose and with easier quoting than:
SELECT foo = 'This ' + @and + ' That = ''' + @whatever + ''' ... etc.If there's no negative feedback I will likely use this approach in most of my SP's when appropriate.
I also found this interesting article on static vs. dynamic SQL but AFAIK it doesn't apply to this SP since a query plan isn't generated??? http://www.sommarskog.se/dyn-search.html
3) I'm still trying to wrap my head around RAISERRROR and the correct severity level to use. For the 2nd example, I'd like the SP to raise a warning and halt, but not have it be as severe as an error.
Here's my updated SP:
ALTER PROCEDURE [dbo].[uspDropTableV2]
( @TableName VARCHAR(255)
, @debug BIT = 0
)
AS
BEGIN
DECLARE @server VARCHAR(100)
DECLARE @database VARCHAR(100)
DECLARE @schema VARCHAR(100)
DECLARE @table VARCHAR(100)
DECLARE @quoted VARCHAR(255)-- Trap for SQL Injection attacks
IF @TableName LIKE '%;%' OR
@TableName LIKE '%--%' OR
@TableName LIKE '%/*%' OR
@TableName LIKE '%*/%'
BEGIN
RAISERROR(
'Illegal characters (; -- /* or */) in %s.',18,1,@TableName
--15600,-1,-1,'uspDropTableV2'
) WITH LOG
RETURN
END-- Parse the tablename
-- We only want the schema and object name
-- We do not want to drop tables in other databases or servers
SELECT @server = PARSENAME(@TableName,4)
SELECT @database = PARSENAME(@TableName,3)
SELECT @schema = PARSENAME(@TableName,2)
SELECT @table = PARSENAME(@TableName,1)-- If server or database were specified raise error and abort
IF @server IS NOT NULL OR @database IS NOT NULL
BEGIN
RAISERROR(
'Only two-level names (schema.table) are allowed. You cannot drop tables in other databases.',
12,
1
)
RETURN;
END;-- This technique will create concatenated data with separator
-- See https://stackoverflow.com/questions/19432370/concat-ws-for-sql-server
SELECT @quoted =
STUFF (
(
SELECT '.' + v
FROM (VALUES (QUOTENAME(@schema)), (QUOTENAME(@table))) AS v (v)
FOR XML PATH (''), TYPE
).value('.[1]', 'varchar(max)'),
1,1,''
)-- Build SQL text
DECLARE @sql VARCHAR(1000);
SET @sql=
'BEGIN TRY
IF EXISTS(SELECT 1 FROM sys.objects WHERE OBJECT_ID=OBJECT_ID(N''{quoted}'') AND TYPE=(N''U''))
BEGIN
DROP TABLE {quoted};
PRINT ''{quoted} dropped.'';
END
ELSE
BEGIN
PRINT ''{quoted} does not exist.'';
END
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;SELECT
@ErrorMessage = N''Error dropping {quoted}'' + CHAR(13) + CHAR(10) + ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
END CATCH
'
SET @sql=REPLACE(@sql,'{quoted}',@quoted)IF @debug = 1
PRINT @sql;IF @debug = 0
EXEC (@sql);
END
GO
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply