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 outside parameter field

Can any body help me that how to get data (opening balance) of an account in report wich showing data in between specific dates. The opening balance will be the values before the parameter fields.Use Sub report to show the Opening Balance. So create a sub report without using parameters, just show the opening balance and dont link the sub report and main report.

In the sub report created a formula, "sub_op"

WhilePrintingRecords;
Shared x As Number
x = Opening Balance

and in the main report create a formula, "main_op"

WhilePrintingRecords;
Shared x as number

Use this formula in the main report to show Opening Balance. Important thing is place the subreport in the header before using the main_op formula, so that subreports gets executed first and will show the Opening Balance value in the main report.|||But if I want to get opening balance for a specific date then I have to pass a parameter for that.

So, if the I could not insert the subreport at header then what could be the solution.

Thankyou|||First I agree with khs_shankar that a subreport for the OpeningBalance is the way to go.

If you want to select date(s) for OpeningBalance independent from the the main report parameter field, then create parameter field(s) in both main & subreport, say Op_date1 (& Op-date2?). In your subreport, use these dates as a filter. Link the main & subreport by these Op_ parameter fields so you can pass the values.

If the Opening Balance are for the dates prior to your main parameter field, like a cut off date to show previous balance & details afterwards, then you can create a parameter field in your subreport, say Op-date. Link the parameter field in the main report with the Op-date field in the subreport. Use this Op_date field as a filter in the subreport.

Hope this help.|||I want to alter my question here.

The Opening balance must be of the current month and les then by the date which is selected by user.

Because I have tried "thg's" logic but when I want to link parameter main report with the Op_date of sub report, it does not show the op_date field in link combo.

Help in this regard.

Data mapping and Importing app for SQL server

Hello,

Our company often receives data from outside sources to add to our application. This data is usually provided to us in Excel, CSV, XML, etc. The files that we receive usually have different columns from the columns in our database, so we have to map these columns to our table structure to import.

I'm looking for an application that will easily allow me to load up the data file (whatever type it may be), expose the columns in the data file, allow me to map these columns in our SQL server, then import the data. I know that this can be done as DTS, however I'm looking for alternatives. Does anyone have any recommendations?

Thanks in advance.

I just dont have a solution to your post but i have the same problem. I just want to get notifications on reply event of this thread. Hopefully some one will answer you and i will benefit.