RE: Syntax

Execute SQL is what the Variable is supposed to do. I have the variable set
to examine two letters in the mdb connection string (The mdb connection
string is the file in the folder that is enumerated by a variable called
TargetDB in the ForEach Loop) I name the variable to be SourceSQL and it is
within the scope of the dataflow task. The task is supposed to filter all
records from the source database filtered by the two letter string above.
In the expression builder where the variable is defined
"select left(abc,110)abc,left(cityname,90)cityname from
fips.dbo.encryptedcities where cc1="+REPLACE( UPPER(RIGHT(
@[User::TargetDB],6)),".MDB","")
the evaluate expression evaluates to a blank. I am presuming this is
because the targetdb variable does not load until the for each loop is
initialized. For this reason I have set ValidateExternalMetadata on the
source to be false.

The SSIS will run if the targetdb is explicitly defined as one of the file
databases in the folder. As soon as it is replaced with the enumerator from
the for each loop, it no longer allows me to save the condition in the source
as a SQLCommandFromVariable.

I know the command works - is there any way to save it to avoid the syntax
error that occurs because the enumerator is not yet loaded?
--
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: Opening Excel from Access
    ... Tried changing it to an MDB but no luck. ... Dim MDBName As String, DefaultDirectory As String, SQLStg As String ... If MsgBox("Do you want to use this database in future?", ... comes when I click the button on Access form to show the Excel file. ...
    (comp.databases.ms-access)
  • Re: Access crashes when calling DLL function.
    ... I have started bypassing code in the form to find the problem. ... are local String variables. ... I then built a simple input form (in the same mdb) that calls the crashing ... functions from a DLL they provide. ...
    (microsoft.public.access.modulesdaovba)
  • Re: How do I open an Access Database using VB6.0
    ... Use something like a ShellExecute() API to start up the Access ... ByVal lpFile As String, ByVal lpParameters As String, _ ... close the MDB when your application is closed from the System Tray. ... it opens it then closes it when ...
    (microsoft.public.vb.database.dao)
  • Re: Filtering on a VB function return value
    ... I tried returning a string, but I have the same problem. ... Dim typeName As String ... In a query I pass certain values from a table to the VBA function. ... in Access 2000/2002 mdb, ...
    (microsoft.public.access.queries)
  • Re: Visual Basic 6 - Run a shortcut
    ... opened is only the front-end, and when it is opened with ShellExecute, I do ... I would not delete the local copy of the mdb before you've copied it ... > dependencies have dependencies themselves that you don't see but the setup ... lpstrFolderName As String, ByVal lpstrLinkName As String, ByVal ...
    (microsoft.public.vb.general.discussion)
Loading