Programmatically changing a DataReader's SQL statement

Hi,

I was wondering how I could dynamically change the SQL statement (ie the SQLCommand property) of a DataReader source.

The source is reading an IBM AS400 file, with the SQL statement:
SELECT itrloc, itrdat, itrcen, csreg#, cstrn#, csseq#, inumbr, cstime, itrqty, itrret, itrreg, itrcst, idept, isdept, iclas, isclas, csdosp, cmcust, csacct, salit, cstntp, csldgr
FROM clib.scsaltrn
WHERE itrdat between 50901 and 50931

I want to programmatically change the date figures on the end (50901 and 50931) each time the package is run.

I have tried to use variables in place of the date figures but this didn't work. I've also thought of using the Script task to programmatically modify the DataReader source, but this doesn't seem possible.

Any ideas on how I could achieve what I'm trying to do?Switch back to the control-flow, select the data-flow in which your datareader source resides and press F4. You should be able to see, in the "Misc" section of the properties pane, a property called:
[<DatareaderSourceComponentName>].SQLCommand
This refers to the thing you want to change.

You can set an expression on this property to set it to whatever you want.

If you need any help in doing that, just reply here!!

-Jamie|||

Jamie,

i set an expression on the SQL script to run it works fine on the first iteration (of a for loop), second time arround it throws an error

Error: 0xC0208380 at Run Extract, Run SQL On SPAN [147]: The output column "membernumber" (1304) does not have a matching source column in the DataReader schema.

Error: 0xC0208380 at Run Extract, Run SQL On SPAN [147]: The output column "effectivedate" (1307) does not have a matching source column in the DataReader schema.

Error: 0xC0208380 at Run Extract, Run SQL On SPAN [147]: The output column "salaryamount" (1310) does not have a matching source column in the DataReader schema.

Error: 0xC0208380 at Run Extract, Run SQL On SPAN [147]: The output column "salaryamount2" (1313) does not have a matching source column in the DataReader schema.

Error: 0xC004706B at Run Extract, DTS.Pipeline: "component "Run SQL On SPAN" (147)" failed validation and returned validation status "VS_NEEDSNEWMETADATA".

here it's refering to column names that was in the first SQL statement.

is there a way to reset the settings of the components on every execution?

|||

If you are using a component in a loop, the metadata of the component must exactly match the metadata of the external sources for each iteration. If you external metadata is different, you will need a different DataReaderSrc component.

Thanks
Mark

|||

Mark,

what i'm trying to do is, i have a table which have two fields,

1. [sql script]

2. [destination table]

have about 25 records.

basically i got the table into a result set (ADO variable), then trying to iterate through the recordset.

here each sql script is different from one another (different meta data), and each script has a corresponding destination table (I used a OLE DB destination, set it as a variable)

so how can i tackle this problem if you have different meta data in source(Note: source field names are identical to destination fields)

my source database, data provider is ODBC and i cannot use OLE DB source. (and i have another iteration to go through many databases which works fine)

any solution to this problem...?

please help!!?

cheers

AJ

|||How do I do this ?|||

Andrew wrote:

How do I do this ?

Instructions are above.