data reader source in data flow problem

hi all,

i have a package in ssis that needs to deliver data from outside servers with odbc connection. i have desined the package with dataflow object that includes inside a datareader source. the data reader source connect via ado.net odbc connection to the ouside servers and makes a query like: select * from x where y=? and then i pass the data to my sql server. my question is like the following:

how do i config the datasource reader or the dataflow so it will recognize an input value to my above query? i.e for example:

select * from x where y=5 (5 is a global variable that i have inside the package). i did not see anywhere where can i do it.

please help,

tomer

You can set an expression on the SQLCommand property. Similar to as explained here: http://blogs.conchango.com/jamiethomson/archive/2005/12/09/2480.aspx

The difference with the datareader source is that you cannot use a variable. Instead, set the expression on the SQLCommand property via the expressions of the parent data-flow.

-Jamie

|||

hi jamie,

thank you for your advice but i still did not understand completely.

the example u sent me is with ole db data source. lets say i have created the variable

and put there the select. now how do i config the data flow to use it?

can you give me another example?

thx,

Tomer

|||

Like I said, the Datareader Source does not allow you to use a variable as input. So, instead of putting the expression that builds your SQL statement into a variable, put it in an expression that sets the SQLCommand property of your Datareader Source. The interface to setting this expression is in the properties pane of the Data Flow task in which the Datareader Source resides.

-Jamie

|||

hi again Jamie,

i have put there:

"select * from x where y=" + @.[user::max_date]

and i get an error.

can u advice?

|||

Is max_date a Datetime variable? If so you need to cast it as a string in order to be able to concatenate it.

-Jamie

|||

Jamie,

thank you very much for your help - you realy helped me here and saved me lots of time.

tomer

|||

Hi Jamie,

yesterday everyyhing worked fine and suddenly i get in the package VS_BROKEN?

why can u advice me on that please?

tomer

|||

You have made a change in one of your components that means it won't work anymore. It should tell you which component.

-Jamie

|||

it says its failed executing the SQL command at the data reader source and i get validation error VS_BROKEN/ my sql is like yesterday:

"select * from x where y = " + [user::x]

when x is a string type. i must have changed somthing small without noticing...?

|||Are you missing the @. before the variable name (should be "@.[user::x]" ) in your sql command, or is that just a typo in your post?

data loss when reinitializing subscription

we had setup merge replication on 2 db servers. For some reason, the subscription started failing a month back with the error " invalid object sysmergexxxx on the subscriber. I did a reinitialize and now all the changes on the subscriber which werent synced got deleted. I have tried all 3 log recovery tools with no luck. Is there any hope of recovering data. the last backup on the subscriber was a month ago.

I believe when you reinitialized the subscriber, you did not chose to upload the unsent changes from subscriber and hence you lost your subscriber data. Sorry to say, but there is no way to recover this data now and is overwritten by the publisher's data.

You should try to either upload the changes next time this occurs or save a backup copy of the subscriber database, then reinitialize (without upload changes) and then diff between the current subscriber and the backup and redo the DML that are different.

Hope that helps next time.

I am also moving this thread to the replication forums.

data from wcf-webservice / object parameter

i have a wcf webservice method with a "GetPropertyDetailsRequest" object as parameter. GetPropertyDetailsRequest Object: 00000000-0000-0000-0000-000000000000 04030201-0605-0807-090a-0b0c0d0e0f10 04030201-0605-0807-090a-0b0c0d0e0f10 2007-09-12T12:33:47.793157+02:00 NoDependencies 9 Subscription 90 Subscription 900 Subscription PropertyCodeFilter1 PropertyCodeFilter2 PropertyCodeFilter3 true is it possible to include this object as parameter in Reporting services i have try it so, http://tempuri.org/GetData . . . but i get an error, that the webservice method (GetDetails(GetPropertyDetailsRequest request) ) needs an object as parameter . can you help me?

could you pleas post your code and the parameters the web method take and return