Início > Parameters > Parameters Don’t Always Work in your Execute SQL Task

Parameters Don’t Always Work in your Execute SQL Task

Chances are, if you’ve used SSIS in any capacity, you’ve used an Execute SQL Task.  I would say that about 95% of all the packages I write include at least one.  And if you’re a fan of making your packages dynamic, chances are you’ve also probably used the Parameter Mapping tab in the task.  Well most of the time this is fairly straight forward and easy to use…  For example, you can simply place a question mark in your SQL statement on the general tab and then select the package variable you want applied to the question mark in the Parameter Mapping tab.  However, there are a few instances where it’s not quite that simple…  Here’s an example.

If you have an Execute SQL Task that is truncating a table, and you’re using a variable to tell the task what table to truncate, you might set up your General tab to look something like this (yes, you could also just select the “Variable” option for the SQLSourceType and create a variable with the value “TRUNCATE TABLE <table>”.  However, for this example, let’s say that we want to reuse a variable used elsewhere that only has the table name):

General Tab

And your Parameter Mapping tab would look something like this:

Parameter Tab

Buuut, unfortunately if you do this you will also see an ERROR message that looks something like this:

Parameter Error

What you can do in this situation is put the variable inside another variable inside your query and execute the SQL variable.  Like this:

Parameter Fix

 

Categorias:Parameters
  1. Ainda sem comentários.
  1. No trackbacks yet.

Deixe uma Resposta

Preencha os seus detalhes abaixo ou clique num ícone para iniciar sessão:

Logótipo da WordPress.com

Está a comentar usando a sua conta WordPress.com Terminar Sessão / Alterar )

Imagem do Twitter

Está a comentar usando a sua conta Twitter Terminar Sessão / Alterar )

Facebook photo

Está a comentar usando a sua conta Facebook Terminar Sessão / Alterar )

Google+ photo

Está a comentar usando a sua conta Google+ Terminar Sessão / Alterar )

Connecting to %s

%d bloggers like this: