pass variable

Hello,

I placed a post regarding this issue previously but no success. So I thought I explain everything properly this time in a new post. Thanks

I have created a stored procedure which passes variables to the ssis package and then executes the package.
The two variables inside the ssis package are @.FileName and @.ConnectionPath
As you can see from the below stored procedure, xp_cmdshell is used to execute the package.
If only the first variable is used in the package and the @.connectionPath variable is hardcoded inside the package then package runs fine.
Problem is in this particular call as you see below because @.ConnectionPath is included.

The output of print is:

dtexec /f d:\sysappl\CEM\SSIS\Imports\Trades\BaseProfiles2.dtsx /set \Package.Variables[User::FileName].Properties[Value];"d:\ApplData\CEM\WorkingTemp\profiles.csv"
/set \Package.Variables[User::ConnectionPath].Properties[Value];"Data Source=servername1\instancename1, 2025;Initial Catalog=CounterpartyExposure;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;"

Error is:

Error: 2007-08-08 08:46:01.29
Code: 0xC0202009
Source: BaseProfiles2 Connection manager "CounterpartyExposure"
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for ODBC Drivers" Hresult: 0x80004005 Description: "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".
End Error

if only the output is run in the query analyser then the error is:

The identifier that starts with 'Data Source=gblond088sjy\MSQL_curves_DEV1, 2025;Initial Catalog=CounterpartyExposure;Provider=SQLNCLI.1;Integrated Security=SSPI' is too long. Maximum length is 128.

/*********************************************************************************

uspCEMTradeExecutePackage2 'd:\sysappl\CEM\SSIS\Imports\Trades\StaticMappingOverride.dtsx',
'StaticMappingOverride.csv',
'Data Source=servername1\instancename1, 2025;Initial Catalog=CounterpartyExposure;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;'
*********************************************************************************/

ALTER procedure [dbo].[uspCEMTradeExecutePackage2]

@.FullPackagePath varchar(1000),
@.FileName varchar(500),
@.ConnectionPath varchar(1000)

as

declare @.returncode int
declare @.cmd varchar(1000)

declare @.FilePath varchar(1000)
declare @.FullFilePath varchar(1000)

set @.FilePath = 'd:\ApplData\CEM\WorkingTemp\'
set @.FullFilePath = @.FilePath + @.FileName
print ' -- ' + @.FileName

set @.cmd = 'dtexec /f ' + @.FullPackagePath + ' /set \Package.Variables[User::FileName].Properties[Value];"' + @.FullFilePath + '"'
set @.cmd = 'dtexec /f ' + @.FullPackagePath +
' /set \Package.Variables[User::FileName].Properties[Value];"' + @.FullFilePath + '"
/set \Package.Variables[User::ConnectionPath].Properties[Value];"' + @.ConnectionPath + '"'

print @.cmd

set nocount on

begin try

exec @.returncode = master..xp_cmdshell @.cmd

end try

begin catch

exec @.LastGoodVersionSP

DECLARE @.msg nvarchar(200)
SET @.msg = ('Error during execute package')

EXECUTE uspErrorReporter @.msg
end catch

set nocount off

Did you get this resolved in one of your other threads, or is it still open?

Pass parameters to OLAP sub-report

Hi there
Using RS 2005
I've got a main report onto which I've placed a subreport to which I
need to pass 2 parameters.
I've done this a 100 times in the past but here I am getting this error
message:
[rsErrorExecutingSubreport] An error occurred while executing the
subreport 'subreport1': One or more parameters required to run the
report have not been specified.
(The report shows "Error: Subreport could not be shown)
The only thing different this time is that the subreport uses OLAP as
a datasource.
Is there anything else which could be producing a misleading error
message, or do I need to be aware of other things when OLAP is
involved?
Thanks
WayneCan u try to refresh the fields in the dataset of the sub-report to
make sure that the field names are being picked up correctly? I've
run into this before as well-
Matt A|||Hi Wayne,
It seems most of the questions being posted here that relate to OLAP are not
being answered. Either we're missing something obvious or not a lot of
people are doing this. Or those who are don't come to this forum!
I'm new to RS and have limited AS exposure but am slowly piecing things
together through trial-and-error - not finding much assistance in books
online or the web at large. So I can't solve your issue but maybe I'll say
something that points you in the right direction.
So far, I gather that in the case of a parameterised OLAP datasource, one
must pass in the full unique name.
Example :
territoryRegionSite = [Territory].[Region - Site].[Site].&[25]
In this example, the &[25] denotes the key value for the specified site.
Or did you know that already?
Note :
URL access requires encoding, particularly in the case of the extra
ampersand. Something like this :
territoryRegionSite =%5BTerritory%5D.%5BRegion%20-%20Site%5D.%5BSite%5D.%26%5B25%5D
Martin Dreyer
"waynest" <wayne.swaby@.tribaleducation.co.uk> wrote in message
news:1144837002.247963.133960@.i39g2000cwa.googlegroups.com...
> Hi there
> Using RS 2005
>
> I've got a main report onto which I've placed a subreport to which I
> need to pass 2 parameters.
> I've done this a 100 times in the past but here I am getting this error
> message:
> [rsErrorExecutingSubreport] An error occurred while executing the
> subreport 'subreport1': One or more parameters required to run the
> report have not been specified.
> (The report shows "Error: Subreport could not be shown)
> The only thing different this time is that the subreport uses OLAP as
> a datasource.
> Is there anything else which could be producing a misleading error
> message, or do I need to be aware of other things when OLAP is
> involved?
> Thanks
> Wayne
>|||Okay, I was missing something obvious. Well, its obvious in retrospect.
When setting, in "Jump to report" the report parameters, I needed to use the
.UniqueName property instead of the default .Value property.
Eg. Fields!Region.UniqueName instead of Fields!Region.Value
That then sends the whole value [Region-Site].[Region].&[2] , which the
receiving report is expecting. It makes sense that it needs the whole path
to uniquely identify what members you're looking for.
Martin Dreyer
"Martin Dreyer" <martindr@.avision.co.za> wrote in message
news:%23eulHH$dGHA.2068@.TK2MSFTNGP02.phx.gbl...
> Hi Wayne,
> It seems most of the questions being posted here that relate to OLAP are
> not being answered. Either we're missing something obvious or not a lot of
> people are doing this. Or those who are don't come to this forum!
> I'm new to RS and have limited AS exposure but am slowly piecing things
> together through trial-and-error - not finding much assistance in books
> online or the web at large. So I can't solve your issue but maybe I'll say
> something that points you in the right direction.
> So far, I gather that in the case of a parameterised OLAP datasource, one
> must pass in the full unique name.
> Example :
> territoryRegionSite = [Territory].[Region - Site].[Site].&[25]
> In this example, the &[25] denotes the key value for the specified site.
> Or did you know that already?
> Note :
> URL access requires encoding, particularly in the case of the extra
> ampersand. Something like this :
> territoryRegionSite => %5BTerritory%5D.%5BRegion%20-%20Site%5D.%5BSite%5D.%26%5B25%5D
> Martin Dreyer
>
> "waynest" <wayne.swaby@.tribaleducation.co.uk> wrote in message
> news:1144837002.247963.133960@.i39g2000cwa.googlegroups.com...
>> Hi there
>> Using RS 2005
>>
>> I've got a main report onto which I've placed a subreport to which I
>> need to pass 2 parameters.
>> I've done this a 100 times in the past but here I am getting this error
>> message:
>> [rsErrorExecutingSubreport] An error occurred while executing the
>> subreport 'subreport1': One or more parameters required to run the
>> report have not been specified.
>> (The report shows "Error: Subreport could not be shown)
>> The only thing different this time is that the subreport uses OLAP as
>> a datasource.
>> Is there anything else which could be producing a misleading error
>> message, or do I need to be aware of other things when OLAP is
>> involved?
>> Thanks
>> Wayne
>