Executing SQL Statement from flat file

I have been attempting to load a SQL Server table by extracting data from Oracle using a parameterized query. I need to retrieve the Oracle data from views where the key equals a specific value. The values are based on data from other Oracle tables.

I was able to create a file that contains 1 row for each key value in the syntax of "select .... from viewname where key = value". I'd like to be able to loop through the file, execute each statement, and load the resultant row(s) into a SQL Server table.

I looked at the ForEach container, but it appears to only list the files in a directory. I thought I was on the right track using the Execute SQL Task, but I could not figure out how to get the data loaded into SQL.

Any help would be greatly appreicated. Consider me an SSIS novice.

Thanks

I'm not sure if this is a good idea, but how about this?

A script task which will read the entire contents of the file and assign it to an object variable.
This object variable should be of array type, if you can iterate through the array in the for loop to execute your sql statements from the array.

Thanks|||

Another thought: If the source for your keys is a database, you can use an Execute SQL Task to get a list of keys into a recordset, and the ForEach (set to ADO Recordset instead of directory) to iterate through it.

To get the data loaded, you should use a data flow task with an OLEDB Source pointed to Oracle, and an OLEDB Destination pointed to SQL Server. The source should be set to get it's SQL from a variable (which should be populated with your view select statement).

Here's a similar example (one of many, if you search around you'll find more): http://agilebi.com/cs/blogs/jwelch/archive/2007/03/20/using-for-each-to-iterate-a-resultset.aspx

|||

I was able to get the expected data loaded into SQL Server.

thanks for the help

Executing SQL Statement from flat file

I have been attempting to load a SQL Server table by extracting data from Oracle using a parameterized query. I need to retrieve the Oracle data from views where the key equals a specific value. The values are based on data from other Oracle tables.

I was able to create a file that contains 1 row for each key value in the syntax of "select .... from viewname where key = value". I'd like to be able to loop through the file, execute each statement, and load the resultant row(s) into a SQL Server table.

I looked at the ForEach container, but it appears to only list the files in a directory. I thought I was on the right track using the Execute SQL Task, but I could not figure out how to get the data loaded into SQL.

Any help would be greatly appreicated. Consider me an SSIS novice.

Thanks

I'm not sure if this is a good idea, but how about this?

A script task which will read the entire contents of the file and assign it to an object variable.
This object variable should be of array type, if you can iterate through the array in the for loop to execute your sql statements from the array.

Thanks|||

Another thought: If the source for your keys is a database, you can use an Execute SQL Task to get a list of keys into a recordset, and the ForEach (set to ADO Recordset instead of directory) to iterate through it.

To get the data loaded, you should use a data flow task with an OLEDB Source pointed to Oracle, and an OLEDB Destination pointed to SQL Server. The source should be set to get it's SQL from a variable (which should be populated with your view select statement).

Here's a similar example (one of many, if you search around you'll find more): http://agilebi.com/cs/blogs/jwelch/archive/2007/03/20/using-for-each-to-iterate-a-resultset.aspx

|||

I was able to get the expected data loaded into SQL Server.

thanks for the help

Execute parameterized select statement from data flow

I have following requirement. From OLE-DB source I am getting IDS. Then lookup with some master data. Now I have only matching IDs. Now I need find some filed(say Frequency from some table for each above id). I already write stored procedure for same where I am passing ID as parameter.Which is working fine when I run it SQL server management studio.

Query is sort of

Select field1,fiel2... from table 1 where id = @.id

@.id is each ID from lookup

Now I want to call this stored procedure in Data flow. I tried it using OLE DB command but it did not return output of stored procudre. I am getting output same what ever I am passing input.

Is there way to do this? In short my requirement is execute parametrized select statement using data flow trasformation component.

Hi Dnyandeo,

I had a similar problem when trying to implement a parameterized sql command. Firstly i used the execute SQL task.

It appears that you can only use named parameters with an ADO.Net connection. Using the OLEDB connection you have to use Ordinal position numbering.
I found this article which has some good stuff on parameterized SQL commands and helped me a lot.

http://www.juliankuiters.id.au/index.php?topic=dts-2005-integration

I hope this helps.

Cheers,

Grant|||how is your ole db transformation configured?|||

Thanks for reply..BUT I am getting input parametr from
conditional splits in data flow so I want to execute stored

procedue in data flow .
Thanks for reply..BUT I am getting input parametr from
conditional splits in data flow so I want to execute stored

procedue in data flow .

|||

dnyandeo wrote:

Thanks for reply..BUT I am getting input parametr from
conditional splits in data flow so I want to execute stored

procedue in data flow .
Thanks for reply..BUT I am getting input parametr from
conditional splits in data flow so I want to execute stored

procedue in data flow .

again, how is your ole db transformation configured?|||

OLE DB command trasformation is configure as below.

I am calling stored prodedure by exec statement with parameters and parametrs are mapped with incoming data. Is any thing more required?

|||

dnyandeo wrote:

OLE DB command trasformation is configure as below.

I am calling stored prodedure by exec statement with parameters and parametrs are mapped with incoming data. Is any thing more required?

it would difficult to assist you without knowing your exact t-sql code, you exact exec statement, and exactly how you're mapping the parameters.|||I am executing it

exec CSP_GetJJKKChangedPricingFrequency @.BP_ID

@.BP_ID is matched with BP_IF field of resultset getting from Derived column control.

this is code is stored procedure

select VJJKK.BP_ID,

VJJKK.Security_ID,

VJJKK.CUSIP_Value,

VJJKK.State_Code,

VJJKK.Coupon_Rate,

VJJKK.Maturity_Date,

VJJKK.Pricing_Frequency,

VJJKK.Security_Desc,

VJJKK.BP_ID_Status

from view_InterfaceJJKK80OUT VJJKK

where

VJJKK.Pricing_Frequency Not in( select distinct Pricing_Frequency_Code

from

Sch_Interface.JJKennyMaster JJKKM

where JJKKM.BP_ID = @.BP_ID)

|||

dnyandeo wrote:

I am executing it

exec CSP_GetJJKKChangedPricingFrequency @.BP_ID

@.BP_ID is matched with BP_IF field of resultset getting from Derived column control.

this is code is stored procedure

select VJJKK.BP_ID,

VJJKK.Security_ID,

VJJKK.CUSIP_Value,

VJJKK.State_Code,

VJJKK.Coupon_Rate,

VJJKK.Maturity_Date,

VJJKK.Pricing_Frequency,

VJJKK.Security_Desc,

VJJKK.BP_ID_Status

from view_InterfaceJJKK80OUT VJJKK

where

VJJKK.Pricing_Frequency Not in( select distinct Pricing_Frequency_Code

from

Sch_Interface.JJKennyMaster JJKKM

where JJKKM.BP_ID = @.BP_ID)

if i'm not mistaken, your sql statement in the ole db command should be the following:

exec CSP_GetJJKKChangedPricingFrequency ?

is this what you have as your sql statement?