RE: Syntax

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
    ... "thejamie" wrote: ... An OLE DB record is available. ... place data in the target mdb database. ... 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)
  • Re: why theres no record showing?
    ... from the query. ... experimentations by adding criteria: under a field of one of the table, ... in ole formate). ... corresponding record will pull up.).. ...
    (microsoft.public.access.queries)
  • Re: How to execute a table lookup in a script component?
    ... You should be passing the variable to an OLE DB Source Adapter and using it in there. ... The query works in MS Access as a pass-through. ... Dim myAdapter As Data.OleDb.OleDbDataAdapter = New ...
    (microsoft.public.sqlserver.dts)
  • Re: SSIS is pathetic with ODBC connections ...
    ... fetch data from a Double Byte Progress database and the "DataReader ... that SSIS is a totally rewritten version of DTS. ... I have played with OdbcClient against SQL Server, ... you could try OLE DB instead. ...
    (comp.databases.ms-sqlserver)