June 1, 2011 at 6:28 am
Hi,
I'm trying to do something completely new to me and failing miserably.
I've got a stored proc which accepts 5 parameters to create a view based on a table. The fifth of these parameters is a Name, which forms the where clause of the view.
In Management Studio I could just loop through a cursor and create the view each time with the correct data criteria, but that doesn't get me an export which is why I'm trying to do it in Visual studio.
I need to export to Excel a separate document for each dataset.
So far, I've done lots of experimenting and can get different bits to work separately, but not together. My package thus far is
1) An Execute SQL Task with the following SQL Statement.
Select Distinct KAM from tbl_PricingReview_KAMAutopriceOutput
Result Set Name of 0 and Variable Name User::KAM (which is set to object in variables)
2) A For Each Container
Collection set to ADO Enumerator and source Variable User::KAM
Within the Container I have
3) Execute SQL Task with the following SQL Statement.
[Code]="plain"]USE [Pricing]
GO
DECLARE@return_value int
EXEC@return_value = [dbo].[sp_BuildCrosstabView]
@tableName = N'Pricing.dbo.tbl_PricingReview_KAMAutopriceOutput',
@excludeCols = N'[Index], Customer_Name, sub_name, [Maj Gen], [Product Code], [Nett Price], [Disc 1], [Disc 2], CustomerProductPriceCat',
@valueColName = N'[Discount 1]',
@targetViewName = N'vw_Test',
@Where = N'''WAYNE CLARKE'''
SELECT'Return Value' = @return_value
GO[/Code]
That's as far as I've managed to get. If I run it as this, the loop appears to work fine. However, I'm not making any use of the variable. What I'd like to do is substitute WAYNE CLARKE with @User::KAM and it run through each time producing a different output.
I then need to export the results to Excel, but unfortunately the column specs of vw_test will differ given the crosstab nature of the data. If I can get the first bit working correctly it would be a great help.
I've been experiementing from google searches, but everything I've tried has failed. I can't find any reference to using a variable in this manner, except for ID fields which aren't strings and any combination of quote's hasn't worked!
Please help or I'm going to be even more prematurely bald than I am already.
June 2, 2011 at 2:16 pm
That's a pretty tall order for a first SSIS package, but I can tell you that passing in variables in SSIS is kind of tricky. You need to use a ? symbol in the SQL. Another option I can suggest, build the SQL statement using expressions in an SSIS variable and then execute the sql using the variable. I think much eaiser in this case.
The output is going to be a bit of an issue. SSIS does not do well with dynamic source of destinations in a data flow task. You can do it, but you need a third party plugin (www.cozyroc.com), and then you're getting into some fairly complex stuff.
MB
June 3, 2011 at 2:28 am
Mark,
Thanks for the reply.
I've managed to get the first portion to work although not without some difficulty. I've found a wierd bug, that the package will not run on my machine, but will run unaltered on the server and on someone else's install. Tried in both BIDS and full Visual Studio via uninstall / reinstall drama.
Now you tell me that exporting dynamic field sets is difficult in SSIS. I can't imagine that this requirement is that "off the wall" to have been ignored by Microsoft. Why would they develop a programme that doesn't do what is needed? Argh...
June 3, 2011 at 7:54 am
Well, the entire premiss of the data flow task was built around metadata of columns. So when you don't know the metadata it gets interesting. You can build it at runtime using c# and the ssis api, but I would say it's worth buying the plugin to avoid that.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply