Showing posts with label via. Show all posts
Showing posts with label via. Show all posts

Friday, March 23, 2012

Parent Child Integration using Configuration File

Hi All,

I though i'd been going about setting up my SSIS package to run via a SQL job in the correct way. It would appear however that is not the case.

I have 4 SSIS packages, one of which is the parent package which calls the other three in sequence. I want to run this from a SQL job so that it calls the parent package and it deals with the others. There are connection managers in the package which use a SQL account to access the relevant databases. In addition i have encrypted the package.

I have set up a configuration file which holds the password package and the BillingSystem connection manager password. This file is re used by each package.

After deployment i have set up the SQL agent job to run the parent package. The job returns an error. Looking at the SSIS logging it appears that it completes the SQL task in the parent package and then fails when trying to launch each of the sub packages with an error stating that the SQL account used in the connection manager login failed. It looks as though the sub packages are not getting the password value from the configuration file and this is causing the failure. I though that each package would automatically pick up the config file as it has been setup to do. I have specified the configuration file in the SQL job but this appears to make no difference. Running the integrations seperately i have to add the configuration file in manually to the Run Package dialog box before they will work. This is not saved for the next run.

Can anyone help me get around this problem as i think i have the wrong idea as to how it is supposed to work.

Cheers,

Grant

Are your packages stored in the filesystem or in MSDB?|||

MSDB; Does that make a difference in this case?

Grant

|||Is the SQL server connection for each of the Execute Package task part of a configuration file?|||

Initially the config file contained the connection manager password. It now has the connection string but doesn't seem to be used for the packages called from the initial package. I had to manually alter the i config xml to include the password in the connection string.

Grant

Monday, March 12, 2012

parameters via url not being seen

Iâ'm having a problem passing parameters to my report thru the url.
Iâ've read the online text, the manuals we have here, and the newsgroup
entries.
I created a "non-queried" parameter in my report: TestParam
I can test this parameter when running this report in Report Manager. I
enter a value, click View Report, and the data displays in a text box that
displays data based on the data entered into the parameter box.
When I call the report from a vb.net client application thru a url, the
report loads up in a Report Manager window, with NO errors, but no data
displays in either the parameter box or the text box. It is waiting for
parameter input, just as it normally would when bringing up the report in
report manager.
http://server/Reports/Pages/Report.aspx?ItemPath=%2fFolderPath%2fReportName&rs:Command=Render&TestParam=test
Iâ've tried this with and without the Command=Render. I've verified the
parameter name is correct and is the correct case.
Am I still missing something? Is there something more that I should do
with the report parameter before it will accept input from a url?Example from BOL
http://<Webservername>/reportserver?/<reportfolder>/employee+sales+summary&ReportYear=2004&ReportMonth=06&EmpID=24&rs:Command=Render&rs:Format=HTML4.0
so does this work?
http://server/Reports/Pages/Report.aspx?/fFolderPath/ReportName&TestParam=test&rs:Command=Render
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"David" <David@.discussions.microsoft.com> wrote in message
news:8632D6C4-002C-41E4-9E79-C1C74DC31784@.microsoft.com...
> I'm having a problem passing parameters to my report thru the url.
> I've read the online text, the manuals we have here, and the newsgroup
> entries.
> I created a "non-queried" parameter in my report: TestParam
> I can test this parameter when running this report in Report Manager. I
> enter a value, click View Report, and the data displays in a text box that
> displays data based on the data entered into the parameter box.
> When I call the report from a vb.net client application thru a url, the
> report loads up in a Report Manager window, with NO errors, but no data
> displays in either the parameter box or the text box. It is waiting for
> parameter input, just as it normally would when bringing up the report in
> report manager.
>
http://server/Reports/Pages/Report.aspx?ItemPath=%2fFolderPath%2fReportName&rs:Command=Render&TestParam=test
> I've tried this with and without the Command=Render. I've verified the
> parameter name is correct and is the correct case.
> Am I still missing something? Is there something more that I should do
> with the report parameter before it will accept input from a url?
>|||The second looks like it should work but what happens is exactly what he is
seeing, the parameters do not get filled in. You need to use reportserver
syntax as you show from BOL (the difference is RS portal (Report Manager)
versus ReportServer).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:%23Z40Ny2RFHA.3288@.TK2MSFTNGP14.phx.gbl...
> Example from BOL
>
http://<Webservername>/reportserver?/<reportfolder>/employee+sales+summary&ReportYear=2004&ReportMonth=06&EmpID=24&rs:Command=Render&rs:Format=HTML4.0
> so does this work?
>
http://server/Reports/Pages/Report.aspx?/fFolderPath/ReportName&TestParam=test&rs:Command=Render
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "David" <David@.discussions.microsoft.com> wrote in message
> news:8632D6C4-002C-41E4-9E79-C1C74DC31784@.microsoft.com...
> > I'm having a problem passing parameters to my report thru the url.
> >
> > I've read the online text, the manuals we have here, and the newsgroup
> > entries.
> >
> > I created a "non-queried" parameter in my report: TestParam
> >
> > I can test this parameter when running this report in Report Manager. I
> > enter a value, click View Report, and the data displays in a text box
that
> > displays data based on the data entered into the parameter box.
> >
> > When I call the report from a vb.net client application thru a url, the
> > report loads up in a Report Manager window, with NO errors, but no data
> > displays in either the parameter box or the text box. It is waiting for
> > parameter input, just as it normally would when bringing up the report
in
> > report manager.
> >
> >
>
http://server/Reports/Pages/Report.aspx?ItemPath=%2fFolderPath%2fReportName&rs:Command=Render&TestParam=test
> >
> > I've tried this with and without the Command=Render. I've verified the
> > parameter name is correct and is the correct case.
> >
> > Am I still missing something? Is there something more that I should do
> > with the report parameter before it will accept input from a url?
> >
> >
>

Parameters via a web service

Hy,

I have to build a report. this report has to be call by a web service. My method to call this report is :


[WebMethod]

public void Amende()

{

ReportingService rs = new localhost.ReportingService();

rs.Credentials = System.Net.CredentialCache.DefaultCredentials;

byte[] ResultStream; // bytearray for result stream

string[] StreamIdentifiers; // string array for stream idenfiers

string OptionalParam = null; // string out param for optional parameters

ParameterValue[] optionalParams = null; // parametervalue array for optional parameters

Warning[] optionalWarnings = null; // warning array for optional warnings

ResultStream = rs.Render("/SwatFillingDocuments/AMAD AYDIN 1198 2005 MD", "PDF", null,

"<DeviceInfo><StreamRoot>/RSWebServiceXS/</StreamRoot></DeviceInfo>", null, null,

null, out OptionalParam, out OptionalParam, out optionalParams,

out optionalWarnings, out StreamIdentifiers);

// Write the report to Response

HttpContext.Current.Response.BinaryWrite(ResultStream);

}


But in my report I have a parameter. And I have to give a value at this parameter via my web service. Is it possible to do that with the method Render?

oki I found how to give a parameter. So my code looks like that now:

[WebMethod]

public void Amende( string dossierId )

{

ReportingService rs = new localhost.ReportingService();

rs.Credentials = System.Net.CredentialCache.DefaultCredentials;

byte[] resultStream; // bytearray for result stream

string[] streamIdentifiers; // string array for stream idenfiers

string optionalParam = null; // string out param for optional parameters

ParameterValue[] optionalParams = null; // parametervalue array for optional parameters

// Prparation de la valeur passe en paramètre

ParameterValue[] parameters = new ParameterValue[1];

parameters[0] = new ParameterValue();

parameters[0].Name = "DossiersId";

parameters[0].Value = dossierId;

DataSourceCredentials[] credentials = null;

string showHideToggle = null;

string historyID = null;

Warning[] optionalWarnings = null; // warning array for optional warnings

resultStream = rs.Render("/SwatFillingDocuments/AMAD AYDIN 1198 2005 MD", "PDF",

historyID, @."False", parameters,credentials,showHideToggle,out optionalParam,

out optionalParam,out optionalParams, out optionalWarnings,out streamIdentifiers);

HttpContext.Current.Response.BinaryWrite(resultStream);

}

But when I put a parameter in my web service I have an error :

System.Net.WebException: The request failed with HTTP status 400: Bad Request.

at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)

at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)

at localhost.ReportingService.Render(String Report, String Format, String HistoryID, String DeviceInfo, ParameterValue[] Parameters, DataSourceCredentials[] Credentials, String ShowHideToggle, String& Encoding, String& MimeType, ParameterValue[]& ParametersUsed, Warning[]& Warnings, String[]& StreamIds) in c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\listreports\5d760f1a\6c0731c0\App_WebReferences.n1-mytpw.0.cs:line 1706

at Service.Amende(String dossierId) in c:\projects\ListReports\App_Code\Service.cs:line 36

Can you help me please? If I use this mothed without parameter it works but when i try to give a parameter it fails.Thank you|||

I do this in VB. This writes the report out to a disk after you get the byte array. This code is from inside a class that I wrap the process in so I havent't looked at it in awhile but it has been working without errors.

Public Sub RenderWriter()

Dim parameters() As MyReportService.ParameterValue

parameters = GetParameters()

Dim encoding As String

Dim mimeType As String

Dim parametersUsed() As MyReportService.ParameterValue

Dim warnings() As MyReportService.Warning

Dim streamIds() As String

'render the report

Dim data() As Byte

'data = _rs.Render(Me._ReportItem.Path, _Format.Name, Nothing, Nothing, parameters, Nothing, Nothing, encoding, mimeType, parametersUsed, warnings, streamIds)

data = _rs.Render(ReportItem.Path, Format.Name, Nothing, Nothing, parameters, Nothing, Nothing, encoding, mimeType, parametersUsed, warnings, streamIds)

'//create a file stream to write the output

'Dim fileName As String = _OutputPath & "\" & _ReportItem.Name & _Format.Extension

Write(data)

End Sub

Private Sub Write(ByVal data() As Byte)

Dim fs As New System.IO.FileStream(Me.FileName, System.IO.FileMode.OpenOrCreate)

Dim writer As New System.IO.BinaryWriter(fs)

writer.Write(Data, 0, Data.Length)

writer.Close()

fs.Close()

End Sub

Private Function GetParameters() As MyReportService.ParameterValue()

Dim i As Integer

Dim len As Integer = _ParamValues.Count - 1

Dim returnValues(len) As MyReportService.ParameterValue

For i = 0 To len

returnValues(i) = New MyReportService.ParameterValue

returnValues(i).Name = _ParamValues.Item(i).Name

returnValues(i).Value = _ParamValues.Item(i).Value

Next i

Return returnValues

End Function

Wednesday, March 7, 2012

Parameters hidden in ReportViewer

We have a report that opens via a URL. We send through the base parameters
and the report renders, but the parameter list is show.
What we really want to happen is to have the report run, but have the
parameter list minized so that it does not occupy so much screen space. The
user can then choose to make it visible if they wish.
Is this Possible? If so, how?
Thanks,
Chris E.On 18 juin, 21:02, "Chris" <cex...@.enableconsulting.com> wrote:
> We have a report that opens via a URL. We send through the base parameters
> and the report renders, but the parameter list is show.
> What we really want to happen is to have the report run, but have the
> parameter list minized so that it does not occupy so much screen space. The
> user can then choose to make it visible if they wish.
> Is this Possible? If so, how?
> Thanks,
> Chris E.
Try adding "rc:Parameters=Collapsed" in the URL|||If you're using the Report Manager to deploy to and view your reports, then the parameters can be shown or hidden via a button on the right hand side of the toolbar. Alternatively, you can hide the parameters completely from within the report definition file
From http://www.developmentnow.com/g/115_2007_6_0_0_984633/Parameters-hidden-in-ReportViewer.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.com

Parameters don't appear in drilledthrough report

Hi,
we created a "Start"-Report with a drillthrough via "Jump to Report" to an
"Linked"-Report
and both have parameters.
If we drillthrough from the Start-Report to the Linked-Report on the
SQL Server September CTP Reporting Services the parameters are not visibile
and we are
not able to make them visible. If we call the Linked-Report directly,
the parameters appear.
In the June CTP and in the preview of the September CTP everything works
fine and the parameters of both reports appear every time.
Any help will be appreciated.
RalphI'm running SQL2000 SP4 and Reporting Services SP2 but I'm having the same
problem on some reports. Others work fine. If you get an answer to this
problem please let me know. jlowther@.sta-home.com
--
John W. Lowther, Jr.
DBA / Senior Developer
Sta-home Health Agency
"bjh" wrote:
> Hi,
> we created a "Start"-Report with a drillthrough via "Jump to Report" to an
> "Linked"-Report
> and both have parameters.
> If we drillthrough from the Start-Report to the Linked-Report on the
> SQL Server September CTP Reporting Services the parameters are not visibile
> and we are
> not able to make them visible. If we call the Linked-Report directly,
> the parameters appear.
> In the June CTP and in the preview of the September CTP everything works
> fine and the parameters of both reports appear every time.
> Any help will be appreciated.
> Ralph
>
>|||John,
our problem was solved by the final release of SQL Server 2005.
It looks like is has been a bug in the September CTP.
The workaround used was to utilize "Jump to URL" instead of "Jump to Report"
Björn
"John Lowther" <johnlowther@.online.nospam> schrieb im Newsbeitrag
news:185FCD01-E94C-42DE-AD32-1335ECF7C158@.microsoft.com...
> I'm running SQL2000 SP4 and Reporting Services SP2 but I'm having the same
> problem on some reports. Others work fine. If you get an answer to this
> problem please let me know. jlowther@.sta-home.com
> --
> John W. Lowther, Jr.
> DBA / Senior Developer
> Sta-home Health Agency
>
> "bjh" wrote:
>> Hi,
>> we created a "Start"-Report with a drillthrough via "Jump to Report" to
>> an
>> "Linked"-Report
>> and both have parameters.
>> If we drillthrough from the Start-Report to the Linked-Report on the
>> SQL Server September CTP Reporting Services the parameters are not
>> visibile
>> and we are
>> not able to make them visible. If we call the Linked-Report directly,
>> the parameters appear.
>> In the June CTP and in the preview of the September CTP everything works
>> fine and the parameters of both reports appear every time.
>> Any help will be appreciated.
>> Ralph
>>

Monday, February 20, 2012

Parameterized configuratiion in SSIS

Hello,

Does anyone know if its possible to have multiple package configuations in a SSIS package. That you can control via a parameter in some way?

For example, one configuration for each country.

Thankful for any help!

//Patrick

Assuming you're kicking off the package with a SQL Agent job, you may find the following code we're using helpful. I have highlighted the section dealing with assigning a configuration file programatically. In your case you can modify the code to point to different configuration files depending on a country parameter.

Hope this helps.

ALTER PROCEDURE [dbo].[UTIL_StartPackageWithDate]

(

@.Packagename VARCHAR(255),

@.InventoryPeriodDate datetime

)

AS

BEGIN

PRINT suser_sname()

DECLARE @.ReturnCode INT

SET @.ReturnCode = 0

DECLARE @.jobId BINARY(16)

DECLARE @.JobName varchar(255)

DECLARE @.cmdline varchar(MAX)

DECLARE @.PackageNameRoot varchar(255)

-- get root of package name

DECLARE @.I int

DECLARE @.X INT

DECLARE @.Done BIT

SET @.Done = 0

SET @.I = 0

WHILE @.Done = 0

BEGIN

SET @.X = CHARINDEX('\',@.PackageName,@.I+1)

IF @.X = 0

BEGIN

SET @.Done = 1

END ELSE BEGIN

SET @.I = @.X

END

END

SET @.PackageNameRoot = SUBSTRING(@.PackageName,@.I+1,LEN(@.PackageName)-@.I)

Print @.PackageNameRoot

SET @.JobName = N'StartPackage_' + @.PackageNameRoot

SET @.cmdline = N'/SQL "' + @.PackageName + '" /SERVER "' + @.@.ServerName + '" /MAXCONCURRENT " 1 " /CHECKPOINTING OFF '

--+ ' /LOGGER "DTS.LogProviderTextFile.1";"packagelog.log" '

--+ ' /CONNECTION "PackageLog.log";"O:\Shared\Logs\' + + @.PackageNameRoot + '.log"'

+ ' /CONFIGFILE "O:\Shared\Configuration\PackageConfig.dtsConfig" '

+ ' /SET "\Package.variables[InventoryPeriodDate]";"' + CONVERT(varchar(10),@.InventoryPeriodDate,120) + '"'

PRINT @.cmdline

IF EXISTS( SELECT * FROM msdb.dbo.sysjobs_view WHERE [name] = @.JobName)

BEGIN

-- exists, so, delete the steps

SELECT @.JobID = job_id

FROM msdb.dbo.sysjobs_view

WHERE [name] = @.JobName

EXECUTE msdb.dbo.sp_update_jobstep

@.job_id = @.JobID,

@.step_id = 1,

@.subsystem=N'SSIS',

@.proxy_name=N'SSISProxy',

@.command = @.cmdLine

END

ELSE

BEGIN

-- Create the job

EXEC @.ReturnCode = msdb.dbo.sp_add_job @.job_name=@.JobName,

@.enabled=1,

@.notify_level_eventlog=0,

@.notify_level_email=0,

@.notify_level_netsend=0,

@.notify_level_page=0,

@.delete_level=0,

@.description=N'THIS JOB IS MAINTAINED AUTOMATICALLY, DO NOT MANUALLY ALTER',

@.category_name=N'[Uncategorized (Local)]',

@.owner_login_name=N'ardent',

@.job_id = @.jobId OUTPUT

-- create the new step

EXEC @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id=@.jobId, @.step_name=N'StartPackage',

@.step_id=1,

@.cmdexec_success_code=0,

@.on_success_action=1,

@.on_success_step_id=0,

@.on_fail_action=2,

@.on_fail_step_id=0,

@.retry_attempts=0,

@.retry_interval=0,

@.os_run_priority=0,

@.subsystem=N'SSIS',

@.command=@.cmdLine,

@.proxy_name=N'SSISProxy',

--@.output_file_name=N'C:\datetestlog',

@.flags=0

EXEC @.ReturnCode = msdb.dbo.sp_update_job @.job_id = @.jobId, @.start_step_id = 1

EXEC @.ReturnCode = msdb.dbo.sp_add_jobserver @.job_id = @.jobId, @.server_name = N'(local)'

END

EXEC msdb.dbo.sp_start_job @.job_id = @.JobID

END

--select * from msdb.dbo.sysjobs_view

--exec msdb.dbo.sp_help_job @.job_name = 'StartPackage_datetest'

|||

Patrick B wrote:

Hello,

Does anyone know if its possible to have multiple package configuations in a SSIS package.

Yes, that's possible.

Patrick B wrote:

That you can control via a parameter in some way?

Sorry, not quite sure what you mean by this. Can you explain your scenario?

Patrick B wrote:

For example, one configuration for each country.

Do you mean that you want to selectively choose which configuration to apply? There are ways to do that and Johan alluded to one way of accomplishing it above. Basically instead of defining the configuration files at design-time, you tell the package at execution-time which configuration file to use. I'm guessing that there is more than one way of skinning this particular cat though.

Is this what you mean?

-Jamie