I am writing a SSIS package and have a newbie question.

I am in the Execute SQL Task Editor and want to create a SQL
Statement. I clicked on the Expression Builder and was creating a
insert statement. One of the values to insert will have " and ' in
it. The value will be gotten from an enviroment variable that
contains error text.

I can't figure out how to get the insert statement to not error when
the values to be inserted have " or '.

I would prefer to not replace the characters.

Can I create named parameters?
Or is there a function I can use to replace the " or '.
I tried using Replace(,,) but couldn't figure out how to specify the
replace character.

thanks

H Chunk,
Welcome to use MSDN Managed Newsgroup Support.

Would you make the statement more clear?

The Expression Builder dialog box provides a graphical user interface for
building the expressions that property expressions use. What does the
insert statement do? What is the data type of the value you want to insert?

Would you please post your insert statement? Thanks!

Please let me know the result with this so that i can provide further
assistance.

Wei Lu
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

My SQL statememt uses single quotes to delimit strings, so I need to escape
them when they are contained in the data string. The expression uses double
quotes, but nowhere do I have a luiteral double quote, so no worries.

My expression is this -

"INSERT Table VALUES('" + REPLACE(@[User:ata], "'", "''") + "')"

My variable value is this-

My silly string's have quotes, " double as well.

The evaluated result is-

INSERT Table VALUES('My silly string''s have quotes, " double as well.')


Using the parameter support of the Execute SQL Task would be safer and
possibly easier. String concatenation for SQL statements just means a risk of
SQL injection by default.

--
http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com


"Chuck P" wrote:

"Insert into tableName (ErrorDescription) Values" + " ( " +
@[System::ErrorDescription]+ " )"

Unfortunately System::ErrorDescription contains lots of ' "

For example System::ErrorDescription = " Cannot open connection to
database "database" because it's exclusively locked."


On Thu, 09 Mar 2006 04:54:43 GMT, t-weilu (AT) online (DOT) microsoft.com (Wei
Lu) wrote:

I would much prefer to use parameters but have no idea how to.
I just saw a property line for the statement to execute and figured
that SSIS is just taking the value and sticking it into
sqlcommand.text. Don't know how to use the interface to add a
parameter.

thanks,


On Thu, 9 Mar 2006 04:33:28 -0800, Darren Green
<darren.green (AT) reply-to-newsgroup-sqldts (DOT) com> wrote:

Hi Chunk,

Thanks for the update.

I have tested on my side. You can simply type the Parameter in you sql
statement and use the Parameter Mapping page of the Execute SQL Task Editor
dialog box to map variables to parameters in the SQL statement.

For example:

You type the "Insert inro tablename(ErrorDescription) Values (@Error )" in
the SQL Statement.

And then in the Parameter Mapping Page you can map the
System::ErrorDescription to the @Error parameter.


Wei Lu
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.