Showing posts with label execute. Show all posts
Showing posts with label execute. Show all posts

Friday, March 23, 2012

How to use stored procedures as the dataset for reports?

Hi,
I want to use a stored procedure as the dataset for a report.
Even though I could execute the stored procedure in the query designer view,
the report designer did not generate the fields list (in the fields window).
Then I specified all the fields explicitly in the fields property of that
dataset. But when I tried to see the report in the preview pane, it gave me
a message that the dataset does not contain the required fields. I verified
the field names and they were exactly matching so no typo out there.
Can anyone tell me how can use a stored procedure as the dataset?
thanks,
NileshThere is a button that looks like the refresh button in IE that if you hover
over it is says refresh fields. Click on that and it will get all the field
information.
Bruce L-C
"Nilesh Oswal" <nilesh_oswal@.persistent.co.in> wrote in message
news:enGR9A3YEHA.3892@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I want to use a stored procedure as the dataset for a report.
> Even though I could execute the stored procedure in the query designer
view,
> the report designer did not generate the fields list (in the fields
window).
> Then I specified all the fields explicitly in the fields property of that
> dataset. But when I tried to see the report in the preview pane, it gave
me
> a message that the dataset does not contain the required fields. I
verified
> the field names and they were exactly matching so no typo out there.
> Can anyone tell me how can use a stored procedure as the dataset?
> thanks,
> Nilesh
>|||After executing the query from the designer, click the refresh button. Your fields should then populate.
Dave
--
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.

Monday, March 19, 2012

How to use OPENXML to retrieve detail lineitems

Hi there,
I was trying to retrieve all LineItems from the following query. However,
if I execute the following query, it only returns the First item. How could
I modify the OPENXML synatax so it will also return the Second item and Thir
d
item in one select statement like this:
VINET 1996-07-04 00:00:00.000 11 12 First item
VINET 1996-07-04 00:00:00.000 11 12 Second item
VINET 1996-07-04 00:00:00.000 11 12 Third item
VINET 1996-07-04 00:00:00.000 42 10 NULL
LILAS 1996-08-16 00:00:00.000 72 3 NULL
What I would like to do is to insert all fields on the XML doc including
these LineItem into a database table.
Thanks.
Abel Chan
-- ******************
-- ****** Query ******
-- ******************
DECLARE @.idoc int
DECLARE @.doc varchar(1000)
SET @.doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
<Order EmployeeID="5" >
<OrderID>10248</OrderID>
<CustomerID>VINET</CustomerID>
<OrderDate>1996-07-04T00:00:00</OrderDate>
<OrderDetail ProductID="11" Quantity="12">
<LineItem>First item</LineItem>
<LineItem>Second item</LineItem>
<LineItem>Third item</LineItem>
</OrderDetail>
<OrderDetail ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
<Order EmployeeID="3" >
<OrderID>10283</OrderID>
<CustomerID>LILAS</CustomerID>
<OrderDate>1996-08-16T00:00:00</OrderDate>
<OrderDetail ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>'
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
-- Execute a SELECT stmt using OPENXML rowset provider.
SELECT *
FROM OPENXML (@.idoc, '/ROOT/Customer/Order/OrderDetail')
WITH (CustomerID varchar(10) '../CustomerID',
OrderDate datetime '../OrderDate',
ProdID int '@.ProductID',
Qty int '@.Quantity',
LineItem VARCHAR(50) './LineItem' )
EXEC sp_xml_removedocument @.idocAbel
DECLARE @.idoc int
DECLARE @.doc varchar(1000)
SET @.doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
<Order EmployeeID="5" >
<OrderID>10248</OrderID>
<CustomerID>VINET</CustomerID>
<OrderDate>1996-07-04T00:00:00</OrderDate>
<OrderDetail ProductID="11" Quantity="12">
<LineItem>First item</LineItem>
</OrderDetail>
<OrderDetail ProductID="42" Quantity="10">
<LineItem>Second item</LineItem>
</OrderDetail>
<OrderDetail ProductID="42" Quantity="12">
<LineItem>Third item</LineItem>
</OrderDetail>
</Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
<Order EmployeeID="3" >
<OrderID>10283</OrderID>
<CustomerID>LILAS</CustomerID>
<OrderDate>1996-08-16T00:00:00</OrderDate>
<OrderDetail ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>'
-- <LineItem>Second item</LineItem>
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
-- Execute a SELECT stmt using OPENXML rowset provider.
SELECT *
FROM OPENXML (@.idoc, '/ROOT/Customer/Order/OrderDetail')
WITH (CustomerID varchar(10) '../CustomerID',
OrderDate datetime '../OrderDate',
ProdID int '@.ProductID',
Qty int '@.Quantity',
LineItem VARCHAR(50) './LineItem' )
EXEC sp_xml_removedocument @.idoc
"Abel Chan" <awong@.newsgroup.nospam> wrote in message
news:70D61777-6563-4323-B97C-310113BD2321@.microsoft.com...
> Hi there,
> I was trying to retrieve all LineItems from the following query. However,
> if I execute the following query, it only returns the First item. How
> could
> I modify the OPENXML synatax so it will also return the Second item and
> Third
> item in one select statement like this:
> VINET 1996-07-04 00:00:00.000 11 12 First item
> VINET 1996-07-04 00:00:00.000 11 12 Second item
> VINET 1996-07-04 00:00:00.000 11 12 Third item
> VINET 1996-07-04 00:00:00.000 42 10 NULL
> LILAS 1996-08-16 00:00:00.000 72 3 NULL
>
> What I would like to do is to insert all fields on the XML doc including
> these LineItem into a database table.
> Thanks.
> Abel Chan
> -- ******************
> -- ****** Query ******
> -- ******************
> DECLARE @.idoc int
> DECLARE @.doc varchar(1000)
> SET @.doc ='
> <ROOT>
> <Customer CustomerID="VINET" ContactName="Paul Henriot">
> <Order EmployeeID="5" >
> <OrderID>10248</OrderID>
> <CustomerID>VINET</CustomerID>
> <OrderDate>1996-07-04T00:00:00</OrderDate>
> <OrderDetail ProductID="11" Quantity="12">
> <LineItem>First item</LineItem>
> <LineItem>Second item</LineItem>
> <LineItem>Third item</LineItem>
> </OrderDetail>
> <OrderDetail ProductID="42" Quantity="10"/>
> </Order>
> </Customer>
> <Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
> <Order EmployeeID="3" >
> <OrderID>10283</OrderID>
> <CustomerID>LILAS</CustomerID>
> <OrderDate>1996-08-16T00:00:00</OrderDate>
> <OrderDetail ProductID="72" Quantity="3"/>
> </Order>
> </Customer>
> </ROOT>'
> -- Create an internal representation of the XML document.
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
> -- Execute a SELECT stmt using OPENXML rowset provider.
> SELECT *
> FROM OPENXML (@.idoc, '/ROOT/Customer/Order/OrderDetail')
> WITH (CustomerID varchar(10) '../CustomerID',
> OrderDate datetime '../OrderDate',
> ProdID int '@.ProductID',
> Qty int '@.Quantity',
> LineItem VARCHAR(50) './LineItem' )
> EXEC sp_xml_removedocument @.idoc
>
>|||Hi Uri,
Thanks to your prompt reply. However, the xml document I got has multiple
<LineItem> under each <OrderDetail>. I wish each <OrderDetail> has only one
<LineItem>.
Is there a way to extract all the <LineItem> out? Even it is in a plain xml
text. With that, I can use another OPENXML to parse it.
Thanks Uri.
Abel
"Uri Dimant" wrote:

> Abel
> DECLARE @.idoc int
> DECLARE @.doc varchar(1000)
> SET @.doc ='
> <ROOT>
> <Customer CustomerID="VINET" ContactName="Paul Henriot">
> <Order EmployeeID="5" >
> <OrderID>10248</OrderID>
> <CustomerID>VINET</CustomerID>
> <OrderDate>1996-07-04T00:00:00</OrderDate>
> <OrderDetail ProductID="11" Quantity="12">
> <LineItem>First item</LineItem>
> </OrderDetail>
> <OrderDetail ProductID="42" Quantity="10">
> <LineItem>Second item</LineItem>
> </OrderDetail>
> <OrderDetail ProductID="42" Quantity="12">
> <LineItem>Third item</LineItem>
> </OrderDetail>
> </Order>
> </Customer>
> <Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
> <Order EmployeeID="3" >
> <OrderID>10283</OrderID>
> <CustomerID>LILAS</CustomerID>
> <OrderDate>1996-08-16T00:00:00</OrderDate>
> <OrderDetail ProductID="72" Quantity="3"/>
>
> </Order>
> </Customer>
> </ROOT>'
> -- <LineItem>Second item</LineItem>
> -- Create an internal representation of the XML document.
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
> -- Execute a SELECT stmt using OPENXML rowset provider.
> SELECT *
> FROM OPENXML (@.idoc, '/ROOT/Customer/Order/OrderDetail')
> WITH (CustomerID varchar(10) '../CustomerID',
> OrderDate datetime '../OrderDate',
> ProdID int '@.ProductID',
> Qty int '@.Quantity',
> LineItem VARCHAR(50) './LineItem' )
> EXEC sp_xml_removedocument @.idoc
> "Abel Chan" <awong@.newsgroup.nospam> wrote in message
> news:70D61777-6563-4323-B97C-310113BD2321@.microsoft.com...
>
>|||Hello,
I test the following code and it works fine on my side:
DECLARE @.idoc int
DECLARE @.doc varchar(1000)
SET @.doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
<Order EmployeeID="5" >
<OrderID>10248</OrderID>
<CustomerID>VINET</CustomerID>
<OrderDate>1996-07-04T00:00:00</OrderDate>
<OrderDetail ProductID="11" Quantity="12">
<LineItem>First item</LineItem>
<LineItem>Second item</LineItem>
<LineItem>Third item</LineItem>
</OrderDetail>
<OrderDetail ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
<Order EmployeeID="3" >
<OrderID>10283</OrderID>
<CustomerID>LILAS</CustomerID>
<OrderDate>1996-08-16T00:00:00</OrderDate>
<OrderDetail ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>'
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
-- Execute a SELECT stmt using OPENXML rowset provider.
SELECT *
FROM OPENXML (@.idoc, '/ROOT/Customer/Order/OrderDetail/LineItem')
WITH (
CustomerID varchar(10) '//CustomerID',
OrderDate datetime '//OrderDate',
ProdID int '//@.ProductID',
Qty int '//@.Quantity',
LineItem VARCHAR(50) 'text()')
union
SELECT *
FROM OPENXML (@.idoc, '/ROOT/Customer/Order/OrderDetail')
WITH (CustomerID varchar(10) '../CustomerID',
OrderDate datetime '../OrderDate',
ProdID int '@.ProductID',
Qty int '@.Quantity',
LineItem VARCHAR(50) './LineItem' )
order by customerID desc
EXEC sp_xml_removedocument @.idoc
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||That is exactly want I need. Union does the trick. :> Thanks so much Soph
ie.
BTW, I came up with an alternative while I was waiting for your reply.
I setup a #temp table by inserting all the LineItems. I also insert
customerId and Orderdate so I can find the LineItem with these two
identifiers. Then I use cursor to loop through the #temp. It works but
compare to yours, your solution is way cleaner and way easier to handle any
exception errors I will replace mine with your code.
Thanks again.
Abel Chan
"Sophie Guo [MSFT]" wrote:

> Hello,
> I test the following code and it works fine on my side:
>
> DECLARE @.idoc int
> DECLARE @.doc varchar(1000)
> SET @.doc ='
> <ROOT>
> <Customer CustomerID="VINET" ContactName="Paul Henriot">
> <Order EmployeeID="5" >
> <OrderID>10248</OrderID>
> <CustomerID>VINET</CustomerID>
> <OrderDate>1996-07-04T00:00:00</OrderDate>
> <OrderDetail ProductID="11" Quantity="12">
> <LineItem>First item</LineItem>
> <LineItem>Second item</LineItem>
> <LineItem>Third item</LineItem>
> </OrderDetail>
> <OrderDetail ProductID="42" Quantity="10"/>
> </Order>
> </Customer>
> <Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
> <Order EmployeeID="3" >
> <OrderID>10283</OrderID>
> <CustomerID>LILAS</CustomerID>
> <OrderDate>1996-08-16T00:00:00</OrderDate>
> <OrderDetail ProductID="72" Quantity="3"/>
> </Order>
> </Customer>
> </ROOT>'
> -- Create an internal representation of the XML document.
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
> -- Execute a SELECT stmt using OPENXML rowset provider.
>
> SELECT *
> FROM OPENXML (@.idoc, '/ROOT/Customer/Order/OrderDetail/LineItem')
> WITH (
> CustomerID varchar(10) '//CustomerID',
> OrderDate datetime '//OrderDate',
> ProdID int '//@.ProductID',
> Qty int '//@.Quantity',
> LineItem VARCHAR(50) 'text()')
> union
> SELECT *
> FROM OPENXML (@.idoc, '/ROOT/Customer/Order/OrderDetail')
> WITH (CustomerID varchar(10) '../CustomerID',
> OrderDate datetime '../OrderDate',
> ProdID int '@.ProductID',
> Qty int '@.Quantity',
> LineItem VARCHAR(50) './LineItem' )
> order by customerID desc
>
> EXEC sp_xml_removedocument @.idoc
>
> I hope the information is helpful.
> Sophie Guo
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> ========================================
=============
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>
>|||Hello,
I am glad to hear that the informaion is helpful. Have a nice day!
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

Friday, March 9, 2012

How to use For Each loop to Read one record and insert the same into destination

Hi,

My procedure to implement a task is like this

I will be using execute SQL task to fetch the records from source,after this wanna use For each loop to access each record one at a time,perform some trnsformations and insert that record into destination.

Help me in accessing the data stored in the Variable(SQL task) in Dataflow task of foreach loop.

Why do you need to do this one record at a time - is the transformation different for each record ?

Can you explain the transformations you need to do?

|||

In the Exec SQL task Editor:

1. General Tab - Result should be of type Full Resultset.

2. ResultSet tab - Set Result Name =0 , Variable Name= your variable of type object (you can create a new variable from the Variable Name dropdown itself, or in the main Variables Pane). Variable type =Object.

http://technet.microsoft.com/en-us/library/ms141689.aspx has details.

3. Add a For Each Loop Container.

4. In the forEach Loop editor, Collection Tab, Set Enumerator=For Each ADO Enumerator

5. In the Variable Mapping, map your column variables to index 0,1,2 (assuming you have three columns in your resultset). You can create your own variables for each column. For each iteration i.e. for each row of the resultset, these variables will hold the value of the respective columns for the current row.

6. Perform your transformations

7. Use a OLEDB Command destination to insert/update your row to the table.

HTH

Kar

|||The requriment from is in that way,I have proposed for a bulk process.But m not sure if i will get the approval.

|||

The index value to be set are in tenh order of retreival from source.

How to access those variables inTansformations can u be specific using Derived column.

|||

You'll get much better performance if you use an OLE DB Source inside a data flow, rather than iterating through a resultset with a For Each. You might want to revisit your design, or post more specifics so we can help you a little more.

|||

Hi,

I will be having a SQL query to retreive data from source,incorporated in Execute SQL task.

Result will be stored in a Result Set.

After this inside a ForEach loop i have to access single record contained in the result set apply some transformations and send the data into destination.

So my requirement is to process one row of data at a time and whole data processing must take place in a loop.

My problem is,How do i process this data in Data Flow task inside For Each loop.

|||

Hi,

You can achive this by this method(i have tried it out)

Use source query and populate the Recordset destination(variable must be of type object)

For each loop(enumerte through adonet)

Based on number of columns declare those many variables and access them in a script componet(source)

and output those variables as columns,

Do the transformations and insert into destination finally.

Any queries post it here.I will help u out.

|||Sorry, but I'm still not understanding why you have to loop through the recordset rather than using an OLEDB Source. However, if it works for you, stick with it.

Sunday, February 19, 2012

How to use a Variable from the For Loop Container as a input parameter to a SP

Hi Everyone:

I have a quick but imp SSIS question. I have a For Each Loop Container, and inside that I wish to add a Execute SQL Task item, so I can call a sp to do some inserts/updates. The ForEachLoop container is looping thru a ADO Object source variable(which is the user variable defined by me, as a FullResultSet). In my Execute SQL Task, I would like to utilize one of the columns from the result set as an input parameter to my Stored procedure. Can someone please advise on how to do this? Please let me know if you have any more questions. I am waiting for a response... Thanks in advance.

MA

MA2005 wrote:

Hi Everyone:

I have a quick but imp SSIS question. I have a For Each Loop Container, and inside that I wish to add a Execute SQL Task item, so I can call a sp to do some inserts/updates. The ForEachLoop container is looping thru a ADO Object source variable(which is the user variable defined by me, as a FullResultSet). In my Execute SQL Task, I would like to utilize one of the columns from the result set as an input parameter to my Stored procedure. Can someone please advise on how to do this? Please let me know if you have any more questions. I am waiting for a response... Thanks in advance.

MA

The ForEach loop allows you to store the currently iterated values from the collection that you are iterating over in variables. You can then use those variables in any way that you would normally use variables in a parameterised Execute SQL Task.

I haven't gone into much detail here because I don't want to talk about stuff you already understand. Tell me what isn't clear about what I have said and I'll be glad to fill you in.

-Jamie