Dynamic Scripts

I've found that SSIS does not work well with running small dynamic scripts ...

For example, I have scripts that need be created via dynamic SQL. I want the dynamic SQL to be created using variables in SSIS.

The ways around this are to create small temporary stored procedures that I can create in the SSIS package at run time that have dynamic SQL in it - and execute it with the variables as my parameters.

Does anybody else do this, or have a better way of doing it?

I would be inclined to use:

package variables for my input values;|||

I have the same problem too; I tried 2 approaches to do it without success. Could someone please point out where I’m missing and how to do it?

Approach 1:

1, use an Execute SQL task the define an package variable

select last_modified_date as variable1

from table

2, use another Execute SQL task to define second package variable(variable2)

select * from sourceTable

where last_modified_date > ? --variable1

3, use variable2 in OLE db source in a data flow to get source data

Approach 2:

1, 1, use an Execute SQL task with dynimac sql to define an package variable

select ‘select * from SourceTable where last_modified_date > ‘’ + ‘’’ +last_modified_date + ‘’’ AS variable_SQL

from table

2, use the variable (variable_SQL) to get source data in OLE db source in a data flow

|||

Jessie,

Why is this not working for you? Do you get an error message?

-Jamie

|||I don't think he meant to say that it doesn't work- it's just not very elegant ... it's such a workaround and difficult to work with ...|||

This may work for you.

Create 2 string variables (SQL_DATE & SQL_QUERY)
Set SQL_DATE to 20050101
Change Expression property of SQL_QUERY to True
In Expression Value property, type in "Select * from mytable where datecol = " + @.[User::SQL_DATE]

Look at the resulting value. The query and the date should now be together.

You can apply this to many different scenarios. The biggest flaw, which will be fixed with SP1 is that there is no Expression designer for variables. Just use the one that is in one of the other tabs to test your expression.

cheers,

Andrew

|||I have just been creating stored procs and passing the parameters into them. This has been the easiest solution for me.sql