SQLTask ResultSet =?(Full Result), write Result TO DataFlow Task, OLEDB Destination

Question

SQLTask ResultSet =?(Full Result), write Result TO DataFlow Task, OLEDB Destination

Ismail Cassiem

SSC-Addicted

Points: 484
More actions
August 15, 2007 at 5:24 am

#153748

Hi, I've been strugling to set a parameter in Microsoft DB2 OLE DB Provider & tried setting SQLvariable but can't pass another SQL source variable as a parameter within the SQLvariable.
So i'm trying something different.
SQL Task, Expression (sqlstatement source)
Expr: "SELECT sicust from b610clivF.SIH WHERE SIINVD = " + [User:ateFrom]
It executes but can't set the ResultSet to:? (Full Result Set)
in order to create a Data Flow Task and write the Result to a OLEDB Destination.
Is this in anyway possible or any other way, can anyone please help?
Regards, Ismail

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

MikeTomkies Hall of Fame Points: 3185 More actions · Answer 1

Firstly i am supried that exp works without appending single quotes around the date.

If you do get resultset you have to store it in a variable of type object (you can then cast that variable to an ado resultset Object type to manupulate in data flow task) Create a variable of object type then on resultset section select the variable and the resultset name should be set to 0.

That should allow you to capture the results.

Ismail Cassiem SSC-Addicted Points: 484 More actions · Answer 2

Hi, Thanks a Million for the help and sorry for the late reply in S.A. Cape Town. Need some guidance! I don't really know what I'm doing.

My variable: @[User:ateFrom] is of type string a updated SQL source variable.

I started doing the following

1. Creating the New Variable: Resultvar Type: Object

2. SQL Task - General Tab: Result Set: Full Result Set (is this correct?)

3. SQL Task - Result Set Tab: Result Name:0 Variable Name:User::ResultVar

4. Data Flow Task - Create OLE DB Destination - Message: This component does not have available input columns.

5. OLE DB Destination - Connect Man: SQL Database - Access Mode: Table or View - Select Table.

Mapping Avaliable None -can't map the results,dont see any results to map

Please Assist if any of my actions are incorrect, becuase I'm still struggling.

Regards,

MikeTomkies Hall of Fame Points: 3185 More actions · Answer 3

I think you have difficulty understanding the difference between the task flow components and the data flow componenets. If all you need to do is move data from a source to a destination you do this in a data flow and this is the only task you need. So all you need is your data flow with an oledb source and an oledb destination.

1. add a Data flow task

2. in data flow add an oledb source and set its connection. You can base it on a sql query if you want.

3. add an oledb destination set it s connection connect line from source to destination. select columns and check all are mapped correctly.

this is a basic data flow you can do alot more transformations.

Ismail Cassiem SSC-Addicted Points: 484 More actions · Answer 4

Hi, The reason why I tried this method being I got the following problem.

I have a (Microsoft DB2 OLE DB Provider) connection.

I tried the below query with a paramater but when i select parameters it gives error.

Error: Parameters cannot be extracted from the SQL command. The provider might not help to parse parameter information from the command. In that case, use the "SQL command from variable" access mode, in which the entire SQL command is stored in a variable.

Provider cannot derive parameter information and SetParameterInfo has not been called. (Microsoft DB2 OLE DB Provider)

So I tried SQL command is stored in a variable, but can't call another variable as a parameter from SQL command is stored in a variable

Var Query: Select sicust from b610clivF.SIH where SIINVD=(Var DateFrom)

Variable DateFrom : 20070101 (SQL defined variable).

Tried: ?, [User:ateFrom], @DateFrom does not work.

Could not fix in order to accept parameters.

That's why I tried using the SQL task expression result set to OLE DB destination. From DB2 SQL command to SQL table destination.

Is there a way to connect to another database via SQL command then I can add it the SQL in stead of parameters.

Is there any other way, please assist!

Regards,

MikeTomkies Hall of Fame Points: 3185 More actions · Answer 5

Ok. Create a variable called sqlstatment at package level. Click on the variable and get propertiesfor it. Set the evaluate as expression to true. Click the ... on expression and create an expression to generate the sql statment something like "Select sicust from b610clivF.SIH where SIINVD= ' " + [User:ateFrom] +" ' ". Hit evaluate expression to check you get a valid sql query.

On you oledb source in the data task set the access mode to SQL query from variable and set the variable to the above created variable.

Ismail Cassiem SSC-Addicted Points: 484 More actions · Answer 6

WOW... that's excellent, it works like a bomb. Thanks a million I 've been struggling with this for weeks now. I was trying everything & about to give up. My company just started moving from 2000 to 2005, all we had is a basic training in an office - so no senior or manager to count on for help.

I BIG Thank You