RE: Syntax

Does it work when you insert a valid value into the TargetDB source?

"thejamie" wrote:

By the way, the specific error is:

Error at Data Flow Task [Source - EncryptedCities [379]]: SSIS Error Code
DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available. Source: "Microsoft SQL Native Client"
Hresult: 0x80040E14 Description: "Statement(s) could not be prepared.".
An OLE DB record is available. Source: "Microsoft SQL Native Client"
Hresult: 0x80040E14 Description: "Incorrect syntax near '='.".

--
Regards,
Jamie


"Jamie" wrote:

Try using the Execute SQL task from within the loop and not the Data Flow Task.

"thejamie" wrote:

Trying in SSIS to do something which technically should be rather simple.
1) create a for each loop that contains as a variable the name of an mdb
database in a folder
2) use a stored procedure that prepares a query based on the variable name
of that target mdb database
3) place data in the target mdb database.

I am referencing a link from Jamie Thompson at
http://blogs.conchango.com/jamiethomson/archive/2005/12/09/SSIS_3A00_-Using-dynamic-SQL-in-an-OLE-DB-Source-component.aspx

In the link, there is a seven step method to exvaluate an expression using a
variable created in the SSIS package.

After many attempts over the past couple of weeks I have as yet, been unable
to determine what the proper syntax should be - worse, the details of the
error message reveal nothing about how to approach this.

The query in the variable looks like
"EXEC CREATECOUNTRYTABLE "+@[User::TagetDBNames]
or possibly "Select ABC,CNAME FROM MYTABLE WHERE
MYVAR='"+@[User::TagetDBNames]
+"'"
OR Frankly, any possible combination of the above I have been able to come
up with such as
EXEC CREATECOUNTRYTABLE ?
or
"Select ABC,CNAME FROM MYTABLE WHERE MYVAR='"+(DT_STR)@[User::TagetDBNames]
+"'"


AND ALWAYS THE SAME ERROR:

Attempt to parse the expression "EXEC CREATECOUNTRYTABLE
@[User::TagetDBNames]" failed. The expression might contain an invalid token,
an incomplete token, or an invalid element. It might not be well-formed, or
might be missing part of a required element such as a parenthesis.

Is there any hope for SSIS? Or is it not possible to pass an SSIS variable
back to a sql query?
--
Regards,
Jamie
.

Relevant Pages

  • RE: Syntax
    ... An OLE DB record is available. ... place data in the target mdb database. ... The query in the variable looks like ... Is there any hope for SSIS? ...
    (microsoft.public.sqlserver.dts)
  • RE: Syntax
    ... The connection string format is not valid. ... SSIS Error Code DTS_E_OLEDBERROR. ... "Microsoft OLE DB Provider for ODBC ... place data in the target mdb database. ...
    (microsoft.public.sqlserver.dts)