Re: Syntax

Hi Jamie,

Let me see if I can help you save your arm some more :)

<quote>
I am still confused about why the double quotes are used at all in
SSIS when working with the SQL Server connections.
</quote>
SSIS uses a VB.Net for scripting, and a VBA-like language for
Expressions (including Derived Column expressions). This VBA-like
language uses double quotes (") to delimit strings. What this means,
is that when you're building a SQL Statement inside an Expression, you
need to enclose the string portion in double quotes, and the SQL
string portions in single quotes (') as T-SQL uses single quotes as
string delimiters. So in short - the SQL statement SELECT 'Hello' in
an expression would be: "SELECT 'Hello'". This holds true for when
you need to substitute a package variable for any portion of the
statement. Imagine you need to substitute in the table name and code
value in the following statement SELECT * FROM table1 WHERE code =
'testcode' you expression would look something like: "SELECT * FROM "
+ @[User::sTable] + " WHERE code = '" + @[User::sCode] + "'" Another
thing to remember is that the escape character for the VBA-like string
special characters is the backslash ("\") - so when you're
constructing a path, you will need to double-up on backslashes (so "C:
\Test.mdb" becomes "C:\\Test.mdb"). I don't know if that clarifies
things :-/

<quote>
in the case of Access, the connection string is actually just the
filename and path
</quote>
This is not entirely accurate. I created a package and OLEDB
connection to an Access DB to double check, and the connection string
ended up as:
"Data Source=C:\db1.mdb;Provider=Microsoft.Jet.OLEDB.4.0;" Clearly
this contains the "X=Y" format that it's asking for :)

<quote>
So, you mention to be careful regarding the "'s (single quotes) which
....
</quote>
Hopefully this has been adequately explained above. When you type in
a SQL statement directly, you use single-quotes for strings as you are
simply typing SQL code. When you use an Expression to build the code,
you quote and substitute variables the same way as you would inside
(for example) a VB application

<quote>
The connection string format is not valid.
....
</quote>
See Above

<quote>
.... wouldn't that be the "VALUE" portion of the @[User::TargetDB]
loop?
</quote>
Not if the "@[User::TargetDB]" appeared *inside* the Expression string
quotes (" ... @[User::TargetDB]..."). In this case SSIS will just
assume it is part of the string.

<quote>
.... 16 clicks required to run the wizard once for each of the 250 plus
Access databases by hand.
</quote>
Ouch!

Good luck!
J
.

Relevant Pages

  • Re: Difference between and : symbols
    ... lisp> ... You can inhibit evaluation with the special operator QUOTE. ... The specific thing that confused you is why ASDF functions ... But what ASDF really wants is a string. ...
    (comp.lang.lisp)
  • Re: DLookUp
    ... use a separate string for the SQL statement. ... quote and before your double quote. ... Set strTrainer = CurrentDb.OpenRecordset("SELECT strName FROM ...
    (microsoft.public.access.formscoding)
  • Re: fastest way to change case of string
    ... string rather than concatenating a new string is indeed much faster. ... Dim quoted As Collection ... Const QUOTE = "'" ... ChangeCaseX = StrConv ...
    (microsoft.public.vb.general.discussion)
  • MD5 Hash with single quote = grief in dao.findfirst
    ... I know when you need to create a query string and the data contains a single ... quote, you must double the quote as an escape sequence. ... Hundreds of assumption cells combined into one 16 character Hash. ... I build the criteria SQL string. ...
    (microsoft.public.access.modulesdaovba)
  • RE: VBA, and SaveAs Function
    ... The type mismatch comes about in the Str functions in your SaveAs line; ... you're passing a string to a function that expects a number. ... > I have been creating a quote module for the project managers at my lab, ... > save to a unique file, and I am up against a wall. ...
    (microsoft.public.excel.programming)
Loading