HELP! HOW TO CALL A DTS PACKAGE FROM A STORED PROC

I'm a rookie with MSSQL. I need to run a DTS package to export a result set
to an MX Excel spread sheet. I need to call the DTS from a stored procedure
and pass it three values, depending on the input parameters to the stored
proc.

DTS package is no problem. Pretty easy with the DTS wizard. My problem is
that I can't figure out how to instansiate the DTS package object from a
stored proc and pass the three values as parameters to the DTS package so
they can populate the parameters I created in it.

I found an article related to it, but I'm too much of a rookie to grasp it.
It showed how to do this from a stored procedure:

EXEC @.hr = sp_OASetProperty @.oPKG, 'GlobalVariables("MyGVName").Value,
'MyGVValue'
IF @.hr <> 0
BEGIN
PRINT '*** GlobalVariable Assignment Failed'
EXEC sp_displayoaerrorinfo @.oPKG, @.hr
END

I have three values and tree global variables to populate. Do I need to do
the above 3 times?

How do I instantiate the package object? I've read up some on sp_OACreate,
but I don't get it, yet.

How do I initiate the variable @.oPKG?
It contains the name of the sp_OACreate string, right? How do I address a
DTS package in the sp_OACreate string?

I would really appreciate just writing out the sp_OACreate string and how I
pass values for three existing global variables to a DTS package named
"DTS_1".

I'm under some real pressure to get this done.

Thanks for any help I can get.

GunnyHi

Check out

http://www.databasejournal.com/feat...cle.php/1459181

A good resource for everything DTS are books online and
http://www.sqldts.com

John

"Cox News Server" <cfgunny@.cox.net> wrote in message
news:RJClb.97602$AH4.96936@.lakeread06...
> I'm a rookie with MSSQL. I need to run a DTS package to export a result
set
> to an MX Excel spread sheet. I need to call the DTS from a stored
procedure
> and pass it three values, depending on the input parameters to the stored
> proc.
> DTS package is no problem. Pretty easy with the DTS wizard. My problem is
> that I can't figure out how to instansiate the DTS package object from a
> stored proc and pass the three values as parameters to the DTS package so
> they can populate the parameters I created in it.
> I found an article related to it, but I'm too much of a rookie to grasp
it.
> It showed how to do this from a stored procedure:
> EXEC @.hr = sp_OASetProperty @.oPKG, 'GlobalVariables("MyGVName").Value,
> 'MyGVValue'
> IF @.hr <> 0
> BEGIN
> PRINT '*** GlobalVariable Assignment Failed'
> EXEC sp_displayoaerrorinfo @.oPKG, @.hr
> END
> I have three values and tree global variables to populate. Do I need to do
> the above 3 times?
> How do I instantiate the package object? I've read up some on sp_OACreate,
> but I don't get it, yet.
> How do I initiate the variable @.oPKG?
> It contains the name of the sp_OACreate string, right? How do I address a
> DTS package in the sp_OACreate string?
> I would really appreciate just writing out the sp_OACreate string and how
I
> pass values for three existing global variables to a DTS package named
> "DTS_1".
> I'm under some real pressure to get this done.
> Thanks for any help I can get.
>
> Gunny

HELP! Cannot pass GUID's through variables?

I have a project that uses GUID's througout and I'm completely stumped.

1) I create a "batch" GUID to batch the records I'm about to process.

2) I call a web service on a remote machine, and reserve the batch records by inserting the batch GUID into a string works fine

3)I call another web service that returns the rows that I just reserved as XML objects and insert into a string variable

4)I need to use the "batch"GUID variable which is typed as a string (DT_WSTR) as an added column so in a Data Flow Task I do the following:

a) use the XML string variable as the source of a XML Source Task -- works (now that I'm passing custom objects and not a dataset -- curious as to why I can't consume a dataset but thats a different question)

This is where things get tricky:
I've tried to add the BatchGUID as a derived column, as a datatype of DT_WSTR (unicode string) and convert it to uniqueidentifier in the Data Conversion task error, cannot convert unicode to uniqueidentifier (I know I can in C# and SQL Server so why not here).

I've tried to CAST the BatchGUID as a uniqueidentifier and pass that to the datasource -- again conversion error.

I've tried using the type Object and Casting to anything and that doesn't work either.

I've tried to pass the unicode all the way to the SQL Server Destination -- and insert into UniqueIdentifier field... again no go.

All help would be appreciated, at this point I can't see any way of using a UniqueIdentifier as a key field, and maintaining it through the package...
Is this a bug?

Oh and if you want to have some real fun, try returning the type of UniqueIdentifier as an output parameter using a ADO.NET connection.

Thanks!

Maybe a parameterized insert using the SQL Task and a property expression on the query to exchange the value of the GUID ID variable into the insert statement? Have you tried that?

Kirk Haselden
Author "SQL Server Integration Services"

|||

Could you elaborate more on how to implement the property expression in the query? I am doing exqactly as you said using a SQL task and trying to insert into custom logging tables via a sp call. I am using parameter mapping to map system variables to parameters in my sql call, however in my sp I am defining the guids as uniqueidentifiers but ssis system variable guids are strings. What is the easiest way to handle this and convert so it works?

Thanks!

|||

There's some examples here:

Using dynamic SQL in an OLE DB Source component
http://blogs.conchango.com/jamiethomson/archive/2005/12/09/2480.aspx

Setting Expressions
http://blogs.conchango.com/jamiethomson/archive/2006/03/11/3063.aspx

Dynamic modification of SSIS packages
http://blogs.conchango.com/jamiethomson/archive/2005/02/28/1085.aspx

-Jamie

Help! Call a code block based on parameter name

I have a column heading that needs to call a function based on the value
contained in the heading (which is a parameter selected by the user).
Example: they can choose system, database, network or application. I have
functions which do different translations and the functions are named
translateSystem, translateApplication, translateDatabase, etc.
I tried to do this by adding a textbox (which I named tbResType) with the
following code in it:
IIF(Parameters!ResType.value = "nw interface", "translateNetwork",
IIF(Parameters!ResType.value = "process",
"translateProcess",IIF(Parameters!ResType.value = "database",
"translateDatabase", IIF(Parameters!ResType.value = "application",
"translateApplication", "translateSystem"))))
Then I tried to use that value in the column heading to call the appropriate
code block (the function translates into multiple languages so its parm is
the language value the user chooses)
=code.(ReportItems!tbResType.Value)( Parameters!Language.Value)
anyway ... the translation for the first textbox works fine and I see
translateProcess when I entered process for the ResType parm but the column
heading gives me an error ==> The value expression for the textbox â'textbox6â'
contains an error: [BC30203] Identifier expected.
Any clues?You may have to write a wrapper around you actual calls within your code
block. This wrapper will accept the bResType parameter and the sub call the
appropriate routines from there.
Craig
"MJT" <MJT@.discussions.microsoft.com> wrote in message
news:C65EE9E3-506A-46EE-A362-02862013E6BC@.microsoft.com...
>I have a column heading that needs to call a function based on the value
> contained in the heading (which is a parameter selected by the user).
> Example: they can choose system, database, network or application. I have
> functions which do different translations and the functions are named
> translateSystem, translateApplication, translateDatabase, etc.
> I tried to do this by adding a textbox (which I named tbResType) with the
> following code in it:
> IIF(Parameters!ResType.value = "nw interface", "translateNetwork",
> IIF(Parameters!ResType.value = "process",
> "translateProcess",IIF(Parameters!ResType.value = "database",
> "translateDatabase", IIF(Parameters!ResType.value = "application",
> "translateApplication", "translateSystem"))))
> Then I tried to use that value in the column heading to call the
> appropriate
> code block (the function translates into multiple languages so its parm is
> the language value the user chooses)
> =code.(ReportItems!tbResType.Value)( Parameters!Language.Value)
> anyway ... the translation for the first textbox works fine and I see
> translateProcess when I entered process for the ResType parm but the
> column
> heading gives me an error ==> The value expression for the textbox
> 'textbox6'
> contains an error: [BC30203] Identifier expected.
> Any clues?
>|||Thanks Craig ... I am not sure how to do that. Is there an example somewhere
(simple example...) I am very new with vb .net
"Craig" wrote:
> You may have to write a wrapper around you actual calls within your code
> block. This wrapper will accept the bResType parameter and the sub call the
> appropriate routines from there.
> Craig
> "MJT" <MJT@.discussions.microsoft.com> wrote in message
> news:C65EE9E3-506A-46EE-A362-02862013E6BC@.microsoft.com...
> >I have a column heading that needs to call a function based on the value
> > contained in the heading (which is a parameter selected by the user).
> > Example: they can choose system, database, network or application. I have
> > functions which do different translations and the functions are named
> > translateSystem, translateApplication, translateDatabase, etc.
> >
> > I tried to do this by adding a textbox (which I named tbResType) with the
> > following code in it:
> > IIF(Parameters!ResType.value = "nw interface", "translateNetwork",
> > IIF(Parameters!ResType.value = "process",
> > "translateProcess",IIF(Parameters!ResType.value = "database",
> > "translateDatabase", IIF(Parameters!ResType.value = "application",
> > "translateApplication", "translateSystem"))))
> >
> > Then I tried to use that value in the column heading to call the
> > appropriate
> > code block (the function translates into multiple languages so its parm is
> > the language value the user chooses)
> >
> > =code.(ReportItems!tbResType.Value)( Parameters!Language.Value)
> >
> > anyway ... the translation for the first textbox works fine and I see
> > translateProcess when I entered process for the ResType parm but the
> > column
> > heading gives me an error ==> The value expression for the textbox
> > 'textbox6'
> > contains an error: [BC30203] Identifier expected.
> >
> > Any clues?
> >
>
>|||Put this in your code...
Public Function GetTranslation(ByVal Application As String, ByVal Language
As String) As String
Select Case Application
Case "nw Interface"
Return translateNetwork(Language)
Case "process"
Return translateProcess(Language)
Case "database"
Return translateDatabase(Language)
Case "application"
Return translateApplication(Language)
End Select
Return ""
End Function
Then in your header just put
=code.GetTranslation(Parameters!Language.Value)
Craig
"MJT" <MJT@.discussions.microsoft.com> wrote in message
news:D673084A-5BCF-4D12-95F0-B897C11290DA@.microsoft.com...
> Thanks Craig ... I am not sure how to do that. Is there an example
> somewhere
> (simple example...) I am very new with vb .net
> "Craig" wrote:
>> You may have to write a wrapper around you actual calls within your code
>> block. This wrapper will accept the bResType parameter and the sub call
>> the
>> appropriate routines from there.
>> Craig
>> "MJT" <MJT@.discussions.microsoft.com> wrote in message
>> news:C65EE9E3-506A-46EE-A362-02862013E6BC@.microsoft.com...
>> >I have a column heading that needs to call a function based on the value
>> > contained in the heading (which is a parameter selected by the user).
>> > Example: they can choose system, database, network or application. I
>> > have
>> > functions which do different translations and the functions are named
>> > translateSystem, translateApplication, translateDatabase, etc.
>> >
>> > I tried to do this by adding a textbox (which I named tbResType) with
>> > the
>> > following code in it:
>> > IIF(Parameters!ResType.value = "nw interface", "translateNetwork",
>> > IIF(Parameters!ResType.value = "process",
>> > "translateProcess",IIF(Parameters!ResType.value = "database",
>> > "translateDatabase", IIF(Parameters!ResType.value = "application",
>> > "translateApplication", "translateSystem"))))
>> >
>> > Then I tried to use that value in the column heading to call the
>> > appropriate
>> > code block (the function translates into multiple languages so its parm
>> > is
>> > the language value the user chooses)
>> >
>> > =code.(ReportItems!tbResType.Value)( Parameters!Language.Value)
>> >
>> > anyway ... the translation for the first textbox works fine and I see
>> > translateProcess when I entered process for the ResType parm but the
>> > column
>> > heading gives me an error ==> The value expression for the textbox
>> > 'textbox6'
>> > contains an error: [BC30203] Identifier expected.
>> >
>> > Any clues?
>> >
>>|||Sorry fogot one other parameter in the call...
you need...
=code.GetTranslation(Parameters!ResType.Value,Parameters!Language.Value)
Craig
"Craig" <craigm_richardson@.hotmail.com> wrote in message
news:eEjrgGaPGHA.3460@.TK2MSFTNGP15.phx.gbl...
> Put this in your code...
> Public Function GetTranslation(ByVal Application As String, ByVal Language
> As String) As String
> Select Case Application
> Case "nw Interface"
> Return translateNetwork(Language)
> Case "process"
> Return translateProcess(Language)
> Case "database"
> Return translateDatabase(Language)
> Case "application"
> Return translateApplication(Language)
> End Select
> Return ""
> End Function
> Then in your header just put
> =code.GetTranslation(Parameters!Language.Value)
>
> Craig
> "MJT" <MJT@.discussions.microsoft.com> wrote in message
> news:D673084A-5BCF-4D12-95F0-B897C11290DA@.microsoft.com...
>> Thanks Craig ... I am not sure how to do that. Is there an example
>> somewhere
>> (simple example...) I am very new with vb .net
>> "Craig" wrote:
>> You may have to write a wrapper around you actual calls within your code
>> block. This wrapper will accept the bResType parameter and the sub call
>> the
>> appropriate routines from there.
>> Craig
>> "MJT" <MJT@.discussions.microsoft.com> wrote in message
>> news:C65EE9E3-506A-46EE-A362-02862013E6BC@.microsoft.com...
>> >I have a column heading that needs to call a function based on the
>> >value
>> > contained in the heading (which is a parameter selected by the user).
>> > Example: they can choose system, database, network or application. I
>> > have
>> > functions which do different translations and the functions are named
>> > translateSystem, translateApplication, translateDatabase, etc.
>> >
>> > I tried to do this by adding a textbox (which I named tbResType) with
>> > the
>> > following code in it:
>> > IIF(Parameters!ResType.value = "nw interface", "translateNetwork",
>> > IIF(Parameters!ResType.value = "process",
>> > "translateProcess",IIF(Parameters!ResType.value = "database",
>> > "translateDatabase", IIF(Parameters!ResType.value = "application",
>> > "translateApplication", "translateSystem"))))
>> >
>> > Then I tried to use that value in the column heading to call the
>> > appropriate
>> > code block (the function translates into multiple languages so its
>> > parm is
>> > the language value the user chooses)
>> >
>> > =code.(ReportItems!tbResType.Value)( Parameters!Language.Value)
>> >
>> > anyway ... the translation for the first textbox works fine and I see
>> > translateProcess when I entered process for the ResType parm but the
>> > column
>> > heading gives me an error ==> The value expression for the textbox
>> > 'textbox6'
>> > contains an error: [BC30203] Identifier expected.
>> >
>> > Any clues?
>> >
>>
>|||I'm sorry ... I forgot to reply that it worked! Thank you very much and I
have rated it as such.
"Craig" wrote:
> Sorry fogot one other parameter in the call...
> you need...
> =code.GetTranslation(Parameters!ResType.Value,Parameters!Language.Value)
> Craig
> "Craig" <craigm_richardson@.hotmail.com> wrote in message
> news:eEjrgGaPGHA.3460@.TK2MSFTNGP15.phx.gbl...
> > Put this in your code...
> >
> > Public Function GetTranslation(ByVal Application As String, ByVal Language
> > As String) As String
> >
> > Select Case Application
> >
> > Case "nw Interface"
> >
> > Return translateNetwork(Language)
> >
> > Case "process"
> >
> > Return translateProcess(Language)
> >
> > Case "database"
> >
> > Return translateDatabase(Language)
> >
> > Case "application"
> >
> > Return translateApplication(Language)
> >
> > End Select
> >
> > Return ""
> >
> > End Function
> >
> > Then in your header just put
> >
> > =code.GetTranslation(Parameters!Language.Value)
> >
> >
> > Craig
> >
> > "MJT" <MJT@.discussions.microsoft.com> wrote in message
> > news:D673084A-5BCF-4D12-95F0-B897C11290DA@.microsoft.com...
> >> Thanks Craig ... I am not sure how to do that. Is there an example
> >> somewhere
> >> (simple example...) I am very new with vb .net
> >>
> >> "Craig" wrote:
> >>
> >> You may have to write a wrapper around you actual calls within your code
> >> block. This wrapper will accept the bResType parameter and the sub call
> >> the
> >> appropriate routines from there.
> >>
> >> Craig
> >>
> >> "MJT" <MJT@.discussions.microsoft.com> wrote in message
> >> news:C65EE9E3-506A-46EE-A362-02862013E6BC@.microsoft.com...
> >> >I have a column heading that needs to call a function based on the
> >> >value
> >> > contained in the heading (which is a parameter selected by the user).
> >> > Example: they can choose system, database, network or application. I
> >> > have
> >> > functions which do different translations and the functions are named
> >> > translateSystem, translateApplication, translateDatabase, etc.
> >> >
> >> > I tried to do this by adding a textbox (which I named tbResType) with
> >> > the
> >> > following code in it:
> >> > IIF(Parameters!ResType.value = "nw interface", "translateNetwork",
> >> > IIF(Parameters!ResType.value = "process",
> >> > "translateProcess",IIF(Parameters!ResType.value = "database",
> >> > "translateDatabase", IIF(Parameters!ResType.value = "application",
> >> > "translateApplication", "translateSystem"))))
> >> >
> >> > Then I tried to use that value in the column heading to call the
> >> > appropriate
> >> > code block (the function translates into multiple languages so its
> >> > parm is
> >> > the language value the user chooses)
> >> >
> >> > =code.(ReportItems!tbResType.Value)( Parameters!Language.Value)
> >> >
> >> > anyway ... the translation for the first textbox works fine and I see
> >> > translateProcess when I entered process for the ResType parm but the
> >> > column
> >> > heading gives me an error ==> The value expression for the textbox
> >> > 'textbox6'
> >> > contains an error: [BC30203] Identifier expected.
> >> >
> >> > Any clues?
> >> >
> >>
> >>
> >>
> >
> >
>
>

Help writing a report query

OK, I have to write a SQL query for someone, for a report. There's financial values involved. There's three tables, essentially. Call them account names, account types, and transactions. I need to return the value of each transaction with the transactions, the sum of the transactions for the account, and the sum of all account transactions across all account types for the account name. I'm not sure how best to do this, I'm thinking of creating a temporary table at the bottom level ( where the actual data is ) and joining against it to do a SUM on the higher levels. To do that, I seem to need to do group by, which then means I need to specify all my non grouped column names, is that right ?

Can you post a bit more info, particularly a small sample set of data to work with and what you want the results to be? I am having a bit of trouble envisioning what you want.

|||

OK, it works something like this:

table ShareInfo

ShareInfoId int

CurrentPrice int

table SharePurchase

SharePurchaseId int

PortfolioId int

ShareInfoId int

SharesPurchased int

PricePerShare int

table SharePortfolio

PortfolioId int

and some other columns for names, etc. So, on the bottom level, I want to list all share purchases within a portfolio, and how their value has changed in each instance to toay. This data will be collapsible on the report, and the row that shows always will show the share name, and the total value of shares purchased, and amount lost/gained. This data is also collapsible, and on the top level, I show the Portfolio name, and the total value/total amount lost or gained on the portfolio to date.

|||

Hi you can use the ROLLUP operator,

Example,
Create table #Shares
(
PortfolioId int,
ShareId int,
NoOfShares int,
TimePurchased varchar(10),
SharesPerPrice int
)

Insert Into #Shares values(1,1,10,'10:00 AM',30);
Insert Into #Shares values(1,2,100,'10:00 AM',5);
Insert Into #Shares values(1,2,50,'11:00 AM',6);
Insert Into #Shares values(1,3,112,'10:00 AM',5);
Insert Into #Shares values(1,4,112,'10:00 AM',5);

Select
PortfolioId
,ShareId
,TimePurchased
,Sum(NoOfShares)
,Sum(SharesPerPrice)
,Sum(NoOfShares * SharesPerPrice)
from
#Shares
Group By PortfolioId, ShareId, TimePurchased With Rollup

OUTPUT:

PortfolioId ShareId TimePurchased NoOfShares SharesPerPrice Totalvalue
-- -- - -- -- --
1 1 10:00 AM 10 30 300
1 1 NULL 10 30 300
1 2 10:00 AM 100 5 500
1 2 11:00 AM 50 6 300
1 2 NULL 150 11 800
1 3 10:00 AM 112 5 560
1 3 NULL 112 5 560
1 4 10:00 AM 112 5 560
1 4 NULL 112 5 560
1 NULL NULL 384 51 2220
NULL NULL NULL 384 51 2220

If TimePurchased is NOT NULL then it is Down Level Data
If TimePurchased is NULL and ShareId is not null then it is One level Collopsed from the Down Level
If TimePurchased is NULL and ShareId is NULL and PortfolioId is not NULL then it is at Portfolio Level

|||

OK - that looks good, but how would I then populate my report from this ? I expected I'd return three tables, one for each level.

Thanks for helping...

|||

Here you can change the query as follow as,

Select
PortfolioId
,ShareId
,TimePurchased
,SUM(NoOfShares) NoOfShares
,SUM(SharesPerPrice) SharesPerPrice
,Sum(NoOfShares * SharesPerPrice) TotalValue
INTO #RESULT
from
Shares
Group By PortfolioId, ShareId, TimePurchased wITH ROLLUP
Select PortfolioId,ShareId,TimePurchased,NoOfShares,SharesPerPrice,TotalValue from #RESULT where TimePurchased is not null

Select PortfolioId,ShareId,NoOfShares,SharesPerPrice,TotalValue from #RESULT where TimePurchased is Null And ShareId is NOT NULL

Select PortfolioId,NoOfShares,SharesPerPrice,TotalValue from #RESULT where TimePurchased is Null And ShareId is NULL And PortfolioId is Not Null

Help with Variance in MDX Query

I have the following MDX query. It calculates my measure (Call Count) by County on two different days. I would like my query to only return counties where the difference between day 1 and day 2 is greater than 10% of day 1. In other words I am looking for a variance of greater than 10%

select ({[Date].[Date].&[20060901], [Date].[Date].&[20060908]}, {[Measures].[Call Count]}) on columns,
[NPA NXX].[By Zip Code].[County].Members on rows
from [CallStats]

Thanks,
-Darrell

You need to use the filter function to filter your counties. Here's an example query in Adventure Works which does roughly the same thing, ie returns all products where Internet Sales Amount is 10% greater in the second of the two dates displayed:

select
[Date].[Calendar].[Calendar Semester].&[2004]&[1].children
*
[Measures].[Internet Sales Amount]
on 0,
non empty
filter(
[Product].[Product].[Product].members,
(([Measures].[Internet Sales Amount], [Date].[Calendar].[Calendar Quarter].&[2004]&[2])
-
([Measures].[Internet Sales Amount], [Date].[Calendar].[Calendar Quarter].&[2004]&[1]))
/
([Measures].[Internet Sales Amount], [Date].[Calendar].[Calendar Quarter].&[2004]&[1])
>.1)
on 1
from
[Adventure Works]

HTH,

Chris