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/jamiethom...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

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

"thejamie" wrote:

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

"thejamie" wrote:

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

"thejamie" wrote:

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

"thejamie" wrote:

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

"thejamie" wrote:

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

"thejamie" wrote:

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

"thejamie" wrote:

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

"thejamie" wrote:

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: