Page break

Hi All,

I have created one report. When I click on view report information about different countries will be displayed.

By default, reporting services breaks pages of report on some criteria.

I want to break the pages of report on country basis.

For example: Australia: page should include only rows belonging to Australia.

Please teach me how to do this.

Regards

Abdul

Hi Abdul,

Have a read on my article below

http://www.koffeekoder.com/ArticleDetails.aspx?id=339

|||

Hi,

I hope you already have a group created in your report based on Country field. If yes you just need to select the table, right click properties, select groups tab, from the group list select the desired group in your case it will be country and click edit.

you are now on Grouping & Sorting properties dialog box. Just check Page break at end check box and that should resolve your issue.

Cheers,

...

Dont forget to click "mark as answered" on the post that helped you.

...

sql

Packages related with its jobs...

Hi,

I have diferent jobs scheduled in the system but I cant find which Package is launched by the job. I only have this information: (double click over the job, steb tab, modify button for any job step, in the command text window)

DTSRun /~Z0x9D852D31537078274085C85BE05756CCE0CA78671EC12A 4BDFFEC4E5E6017E4841EE5F41C492CCAA7F5746CA894011BB 376479B6E679EC3C6045C328D1EF1CDA7CF28B6EEFE9DFE923 7DF5662AE09BD6215C35AA4121BD2DE4433C7BABEE42EC87E7 0F47EA7C01FB44CB28

I would like to know the Package name related to.

Any help would be very appreciated.

RegardsHi,

I have diferent jobs scheduled in the system but I cant find which Package is launched by the job. I only have this information: (double click over the job, steb tab, modify button for any job step, in the command text window)

DTSRun /~Z0x9D852D31537078274085C85BE05756CCE0CA78671EC12A 4BDFFEC4E5E6017E4841EE5F41C492CCAA7F5746CA894011BB 376479B6E679EC3C6045C328D1EF1CDA7CF28B6EEFE9DFE923 7DF5662AE09BD6215C35AA4121BD2DE4433C7BABEE42EC87E7 0F47EA7C01FB44CB28

I would like to know the Package name related to.

Any help would be very appreciated.

Regards

Try:

select * from sysdtspackages where id = '[package id]'

[edit: d'oh, I should read SQL BOL first! The /~Z is an indicator that the string that follows is encrypted. I do not know SQL's encrption algorithm so I'm afraid I can't really help here.]|||thanks. I have tested the sysdtspackages table but I cant find any useful information to relate a job with the package that is running.

Meanwhile I will try SQL BOL.

Regards|||Is the dts packagae writing a log file in the logs folder of the mssql folder? If not, modify the dts package to write the log. Then, aftter the dts packagees execute, check the log. It will hold the clear text name of the package and the results of each step in the package. You should be able to correlate the run time and the associated package. And if you have to delete old logs, the dts package will recreate them on the next execution.

Package won''t run from job

I imported my packages to SQL Server 2005.

Under Stored Packages > msdb, if I right click the package name an select "run package", the package runs fine without errors.

However if I put the same package in a job and run the job, the job fails every time and won't tell me why. It just says to check the history. This is what the history says:

Executed as user: myDomain\SQLService. The package execution failed. The step failed.

Seems like some kind of permission failure.

Any ideas, please. This permissions stuff is driving me nuts.

Here are all the permission levels that I am aware of when running a job:

1.) permissions at the package level (conn mgrs, ftp mgrs, etc)

2.) rely on SQL Server storage for roles and access control - what does even mean?

3.) job owner (sa) - how does this differ from the job step owner?

4.) job step > run as [user] (SQL Server Agent Account) - why does this require a separate owner?

5.) package > server > Log on to the server [as user] - what the heck is this?

It's maddening. For example, I have no idea what #5 means - when it (the package) is logging on to the server, WHO is it logging on as if the package is running in a job? The job owner? The job step owner? Or something entirely different?

I've asked other people here if they have any clue how all these permissions interrelate, and frankly no one knows. It's a big mystery..... sort of like the big bang. Don't ask me how I passed my MCDBA. That is another great mystery of the universe.

It's no wonder I can't get this thing to run!

Thank you, error handler, for sending me task-related error messages.

So here's the problem:

The script threw an exception: Could not find a part of the path 'o:\myFolder'.

In one of my packages, I have a script that gets file names from the network drive, o:

My other packages reference the d: drive with no problem. It's the o: drive that's having problems.

So maybe whoever is running the package doesn't have permissions to the o: drive, right?

So, getting back to my first post, who needs the permission to read the o: drive? Probably the step owner?

|||

See this classic KB that explains it:

http://support.microsoft.com/default.aspx/kb/918760

The access to o: should be granted to the user who runs the package - Agent service account or Proxy account if you use it for this job. In your case this is currently myDomain\SQLService (from "Executed as user..." message). Also, if o: is network drive, switch to using UNC path (\\server\path) as drive mapping is user-specific.

|||

So it seems the problem has to do with the fact that the SQLService domain account does not have permissions to access o:, or that I need to use a UNC path.

However, I still have some questions about how all the pkg/job permissions interrelate to each other:

1.) permissions at the package level (ole db conn mgrs, ftp mgrs, etc) - the SQLService account does not have anything to do with this, right?

2.) rely on SQL Server storage for roles and access control - what does mean? does this relate to #1 above?

3.) job owner (sa) - how does this differ from the job step owner?

5.) New Job Step > General tab > SQL Server Integration Services Package > Server: Log on to the server [Windows Authentication] - will this be the same login as the job step owner, that is, the SQLServer account?

If anyone could answer any or all of these questions, and #5 especially, I would really appreciate it!

Thanks much

|||

sadie519590 wrote:

1.) permissions at the package level (ole db conn mgrs, ftp mgrs, etc) - the SQLService account does not have anything to do with this, right?

It depends - if your connection managers are using Windows Authentication, then the account SQL Agent is running under does have an impact.

sadie519590 wrote:

2.) rely on SQL Server storage for roles and access control - what does mean? does this relate to #1 above?

Without more context, I'm not sure. My best guess is that is referring to storing packages in SQL Server rather than the file system. If you do that, it can handle access to the packages.

sadie519590 wrote:

5.) New Job Step > General tab > SQL Server Integration Services Package > Server: Log on to the server [Windows Authentication] - will this be the same login as the job step owner, that is, the SQLServer account?

Yes, unless you have set up a proxy.

|||

1.) permissions at the package level (ole db conn mgrs, ftp mgrs, etc) - the SQLService account does not have anything to do with this, right?

It depends - if your connection managers are using Windows Authentication, then the account SQL Agent is running under does have an impact.

That's because the conn mgrs will be using the SQL Agent account, right? (Because the this is job step owner?)

Thanks|||Correct.|||

sadie519590 wrote:

So it seems the problem has to do with the fact that the SQLService domain account does not have permissions to access o:, or that I need to use a UNC path.

o: is a mapped drive, correct? Mapped drives are a user-session concept. That is, when you log on, you can map drives. However, if I remote into that box and log on as well, I don't get to see your mapped drives. Service accounts, such as those that run SQL Server, SQL Server Agent, etc... don't have any clue what a mapped drive is. As soon as you log off of the box, the mapped drives go away. So yes, if you are going to use network shares, you HAVE to use UNC.|||

Yes, the unc path did solve the problem.

Thanks all.

Package won't run from job

I imported my packages to SQL Server 2005.

Under Stored Packages > msdb, if I right click the package name an select "run package", the package runs fine without errors.

However if I put the same package in a job and run the job, the job fails every time and won't tell me why. It just says to check the history. This is what the history says:

Executed as user: myDomain\SQLService. The package execution failed. The step failed.

Seems like some kind of permission failure.

Any ideas, please. This permissions stuff is driving me nuts.

Here are all the permission levels that I am aware of when running a job:

1.) permissions at the package level (conn mgrs, ftp mgrs, etc)

2.) rely on SQL Server storage for roles and access control - what does even mean?

3.) job owner (sa) - how does this differ from the job step owner?

4.) job step > run as [user] (SQL Server Agent Account) - why does this require a separate owner?

5.) package > server > Log on to the server [as user] - what the heck is this?

It's maddening. For example, I have no idea what #5 means - when it (the package) is logging on to the server, WHO is it logging on as if the package is running in a job? The job owner? The job step owner? Or something entirely different?

I've asked other people here if they have any clue how all these permissions interrelate, and frankly no one knows. It's a big mystery..... sort of like the big bang. Don't ask me how I passed my MCDBA. That is another great mystery of the universe.

It's no wonder I can't get this thing to run!

Thank you, error handler, for sending me task-related error messages.

So here's the problem:

The script threw an exception: Could not find a part of the path 'o:\myFolder'.

In one of my packages, I have a script that gets file names from the network drive, o:

My other packages reference the d: drive with no problem. It's the o: drive that's having problems.

So maybe whoever is running the package doesn't have permissions to the o: drive, right?

So, getting back to my first post, who needs the permission to read the o: drive? Probably the step owner?

|||

See this classic KB that explains it:

http://support.microsoft.com/default.aspx/kb/918760

The access to o: should be granted to the user who runs the package - Agent service account or Proxy account if you use it for this job. In your case this is currently myDomain\SQLService (from "Executed as user..." message). Also, if o: is network drive, switch to using UNC path (\\server\path) as drive mapping is user-specific.

|||

So it seems the problem has to do with the fact that the SQLService domain account does not have permissions to access o:, or that I need to use a UNC path.

However, I still have some questions about how all the pkg/job permissions interrelate to each other:

1.) permissions at the package level (ole db conn mgrs, ftp mgrs, etc) - the SQLService account does not have anything to do with this, right?

2.) rely on SQL Server storage for roles and access control - what does mean? does this relate to #1 above?

3.) job owner (sa) - how does this differ from the job step owner?

5.) New Job Step > General tab > SQL Server Integration Services Package > Server: Log on to the server [Windows Authentication] - will this be the same login as the job step owner, that is, the SQLServer account?

If anyone could answer any or all of these questions, and #5 especially, I would really appreciate it!

Thanks much

|||

sadie519590 wrote:

1.) permissions at the package level (ole db conn mgrs, ftp mgrs, etc) - the SQLService account does not have anything to do with this, right?

It depends - if your connection managers are using Windows Authentication, then the account SQL Agent is running under does have an impact.

sadie519590 wrote:

2.) rely on SQL Server storage for roles and access control - what does mean? does this relate to #1 above?

Without more context, I'm not sure. My best guess is that is referring to storing packages in SQL Server rather than the file system. If you do that, it can handle access to the packages.

sadie519590 wrote:

5.) New Job Step > General tab > SQL Server Integration Services Package > Server: Log on to the server [Windows Authentication] - will this be the same login as the job step owner, that is, the SQLServer account?

Yes, unless you have set up a proxy.

|||

1.) permissions at the package level (ole db conn mgrs, ftp mgrs, etc) - the SQLService account does not have anything to do with this, right?

It depends - if your connection managers are using Windows Authentication, then the account SQL Agent is running under does have an impact.

That's because the conn mgrs will be using the SQL Agent account, right? (Because the this is job step owner?)

Thanks|||Correct.|||

sadie519590 wrote:

So it seems the problem has to do with the fact that the SQLService domain account does not have permissions to access o:, or that I need to use a UNC path.

o: is a mapped drive, correct? Mapped drives are a user-session concept. That is, when you log on, you can map drives. However, if I remote into that box and log on as well, I don't get to see your mapped drives. Service accounts, such as those that run SQL Server, SQL Server Agent, etc... don't have any clue what a mapped drive is. As soon as you log off of the box, the mapped drives go away. So yes, if you are going to use network shares, you HAVE to use UNC.|||

Yes, the unc path did solve the problem.

Thanks all.

Package Configuration Wizard Error

I am attempting to create an XML Configuration file and I am recieving the following error once I click finish in the wizard:

Could not complete wizard actions.

Additional Information:
Could not generate the configuration file. (Microsoft.Data.TransformationServices.Wizard)

Technical Details:
===================================

Could not complete wizard actions. (Microsoft Visual Studio)

===================================

Could not generate the configuration file. (Microsoft.DataTransformationServices.Wizards)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.DataTransformationServices.Wizards.ConfigurationWizardPages.ConfigurationWizardSR&EvtID=CouldNotGenerateConfigurationFile&LinkId=20476

Program Location:

at Microsoft.DataTransformationServices.Wizards.ConfigurationWizardForm.GenerateXmlConfigurationFile(DesignTimeConfiguration designTimeConfiguration)
at Microsoft.DataTransformationServices.Wizards.ConfigurationWizardForm.OnFinish(CancelEventArgs e)

===================================

Failure exporting configuration file.
(eFreedomAS)

Program Location:

at Microsoft.SqlServer.Dts.Runtime.Package.ExportConfigurationFile(String str)
at Microsoft.DataTransformationServices.Wizards.ConfigurationWizardForm.GenerateXmlConfigurationFile(DesignTimeConfiguration designTimeConfiguration)

Name:
eFreedomAS2

Type:
Configuration File

New configuration file will be created.

File name:
C:\abc.xml

Properties:
\Package.Variables[User::SourcePath].Properties[Value]
\Package.Variables[User::SourcePath].Properties[Name]
\Package.Variables[User::Packages].Properties[Value]
\Package.Variables[User::Packages].Properties[Name]
\Package.Variables[User::PackageDefNumber].Properties[Value]
\Package.Variables[User::PackageDefNumber].Properties[Name]
\Package.Variables[User::Directory].Properties[Value]
\Package.Variables[User::Directory].Properties[Name]
\Package.Variables[User::DestinationPath].Properties[Value]
\Package.Variables[User::DestinationPath].Properties[Name]
\Package.Connections[ViewHelpTopicDef].Properties[Name]
\Package.Connections[ViewHelpTopicDef].Properties[ConnectionString]
\Package.Connections[ViewDef].Properties[Name]
\Package.Connections[ViewDef].Properties[ConnectionString]
\Package.Connections[ValidationTestDef].Properties[Name]
\Package.Connections[ValidationTestDef].Properties[ConnectionString]
\Package.Connections[ValidationRulePackageDef].Properties[Name]
\Package.Connections[ValidationRulePackageDef].Properties[ConnectionString]
\Package.Connections[ValidationRuleFileDef].Properties[Name]
\Package.Connections[ValidationRuleFileDef].Properties[ConnectionString]
\Package.Connections[ValidationRuleDef].Properties[Name]
\Package.Connections[ValidationRuleDef].Properties[ConnectionString]
\Package.Connections[ValidationMessageFileDef].Properties[Name]
\Package.Connections[ValidationMessageFileDef].Properties[ConnectionString]
\Package.Connections[ValidationMessageDef].Properties[Name]
\Package.Connections[ValidationMessageDef].Properties[ConnectionString]
\Package.Connections[ValidationIdentifierDependency].Properties[Name]
\Package.Connections[ValidationIdentifierDependency].Properties[ConnectionString]
\Package.Connections[ValidationCalcGraphEdge].Properties[Name]
\Package.Connections[ValidationCalcGraphEdge].Properties[ConnectionString]
\Package.Connections[UserGroupFeatureDef].Properties[Name]
\Package.Connections[UserGroupFeatureDef].Properties[ConnectionString]
\Package.Connections[UserGroupFeature].Properties[Name]
\Package.Connections[UserGroupFeature].Properties[ConnectionString]
\Package.Connections[UserGroupDef].Properties[Name]
\Package.Connections[UserGroupDef].Properties[ConnectionString]
\Package.Connections[UserGroup].Properties[Name]
\Package.Connections[UserGroup].Properties[ConnectionString]
\Package.Connections[TableDef].Properties[Name]
\Package.Connections[TableDef].Properties[ConnectionString]
\Package.Connections[Source].Properties[UserName]
\Package.Connections[Source].Properties[ServerName]
\Package.Connections[Source].Properties[Password]
\Package.Connections[Source].Properties[Name]
\Package.Connections[Source].Properties[InitialCatalog]
\Package.Connections[Source].Properties[ConnectionString]
\Package.Connections[SelectListItemDef].Properties[Name]
\Package.Connections[SelectListItemDef].Properties[ConnectionString]
\Package.Connections[SelectListDef].Properties[Name]
\Package.Connections[SelectListDef].Properties[ConnectionString]
\Package.Connections[ScriptAssemblyDef].Properties[Name]
\Package.Connections[ScriptAssemblyDef].Properties[ConnectionString]
\Package.Connections[PrintedPageFormulaScriptDef].Properties[Name]
\Package.Connections[PrintedPageFormulaScriptDef].Properties[ConnectionString]
\Package.Connections[PageDef].Properties[Name]
\Package.Connections[PageDef].Properties[ConnectionString]
\Package.Connections[PageCalcPathSubPath].Properties[Name]
\Package.Connections[PageCalcPathSubPath].Properties[ConnectionString]
\Package.Connections[PageCalcPath].Properties[Name]
\Package.Connections[PageCalcPath].Properties[ConnectionString]
\Package.Connections[PackageLinkDef].Properties[Name]
\Package.Connections[PackageLinkDef].Properties[ConnectionString]
\Package.Connections[PackageImportTypeDef].Properties[Name]
\Package.Connections[PackageImportTypeDef].Properties[ConnectionString]
\Package.Connections[PackageIdentifierGroupDef].Properties[Name]
\Package.Connections[PackageIdentifierGroupDef].Properties[ConnectionString]
\Package.Connections[PackageIdentifierDef].Properties[Name]
\Package.Connections[PackageIdentifierDef].Properties[ConnectionString]
\Package.Connections[PackageExportTypeDef].Properties[Name]
\Package.Connections[PackageExportTypeDef].Properties[ConnectionString]
\Package.Connections[PackageDef].Properties[Name]
\Package.Connections[PackageDef].Properties[ConnectionString]
\Package.Connections[PackageDataItemTextDef].Properties[Name]
\Package.Connections[PackageDataItemTextDef].Properties[ConnectionString]
\Package.Connections[OnlinePagePrintedPageDef].Properties[Name]
\Package.Connections[OnlinePagePrintedPageDef].Properties[ConnectionString]
\Package.Connections[OnlinePageDefDependency].Properties[Name]
\Package.Connections[OnlinePageDefDependency].Properties[ConnectionString]
\Package.Connections[OnlinePageDef].Properties[Name]
\Package.Connections[OnlinePageDef].Properties[ConnectionString]
\Package.Connections[MaterialityToleranceDef].Properties[Name]
\Package.Connections[MaterialityToleranceDef].Properties[ConnectionString]
\Package.Connections[LineDef].Properties[Name]
\Package.Connections[LineDef].Properties[ConnectionString]
\Package.Connections[ImportTypeDef].Properties[Name]
\Package.Connections[ImportTypeDef].Properties[ConnectionString]
\Package.Connections[ImportDef].Properties[Name]
\Package.Connections[ImportDef].Properties[ConnectionString]
\Package.Connections[IdentifierStateDef].Properties[Name]
\Package.Connections[IdentifierStateDef].Properties[ConnectionString]
\Package.Connections[IdentifierRelatedDef].Properties[Name]
\Package.Connections[IdentifierRelatedDef].Properties[ConnectionString]
\Package.Connections[IdentifierPrintReferenceDef].Properties[Name]
\Package.Connections[IdentifierPrintReferenceDef].Properties[ConnectionString]
\Package.Connections[IdentifierGroupUserGroupFeatureDef].Properties[Name]
\Package.Connections[IdentifierGroupUserGroupFeatureDef].Properties[ConnectionString]
\Package.Connections[IdentifierGroupUserGroupFeature].Properties[Name]
\Package.Connections[IdentifierGroupUserGroupFeature].Properties[ConnectionString]
\Package.Connections[IdentifierGroupIdentifierDef].Properties[Name]
\Package.Connections[IdentifierGroupIdentifierDef].Properties[ConnectionString]
\Package.Connections[IdentifierGroupIdentifier].Properties[Name]
\Package.Connections[IdentifierGroupIdentifier].Properties[ConnectionString]
\Package.Connections[IdentifierGroupDef].Properties[Name]
\Package.Connections[IdentifierGroupDef].Properties[ConnectionString]
\Package.Connections[IdentifierGroup].Properties[Name]
\Package.Connections[IdentifierGroup].Properties[ConnectionString]
\Package.Connections[IdentifierDef].Properties[Name]
\Package.Connections[IdentifierDef].Properties[ConnectionString]
\Package.Connections[IdentifierDataItemTextDef].Properties[Name]
\Package.Connections[IdentifierDataItemTextDef].Properties[ConnectionString]
\Package.Connections[FormulaScriptDef].Properties[Name]
\Package.Connections[FormulaScriptDef].Properties[ConnectionString]
\Package.Connections[FormulaDef].Properties[Name]
\Package.Connections[FormulaDef].Properties[ConnectionString]
\Package.Connections[FeatureGroupFeatureDef].Properties[Name]
\Package.Connections[FeatureGroupFeatureDef].Properties[ConnectionString]
\Package.Connections[FeatureGroupDef].Properties[Name]
\Package.Connections[FeatureGroupDef].Properties[ConnectionString]
\Package.Connections[FeatureDef].Properties[Name]
\Package.Connections[FeatureDef].Properties[ConnectionString]
\Package.Connections[ExportTypeRelatedDef].Properties[Name]
\Package.Connections[ExportTypeRelatedDef].Properties[ConnectionString]
\Package.Connections[ExportTypeFormulaScriptDef].Properties[Name]
\Package.Connections[ExportTypeFormulaScriptDef].Properties[ConnectionString]
\Package.Connections[ExportTypeExportDef].Properties[Name]
\Package.Connections[ExportTypeExportDef].Properties[ConnectionString]
\Package.Connections[ExportTypeDef].Properties[Name]
\Package.Connections[ExportTypeDef].Properties[ConnectionString]
\Package.Connections[ExportTypeBlobTypeDef].Properties[Name]
\Package.Connections[ExportTypeBlobTypeDef].Properties[ConnectionString]
\Package.Connections[ExportFormulaScriptDef].Properties[Name]
\Package.Connections[ExportFormulaScriptDef].Properties[ConnectionString]
\Package.Connections[ExportDef].Properties[Name]
\Package.Connections[ExportDef].Properties[ConnectionString]
\Package.Connections[ColumnHeaderDef].Properties[Name]
\Package.Connections[ColumnHeaderDef].Properties[ConnectionString]
\Package.Connections[ColumnDef].Properties[Name]
\Package.Connections[ColumnDef].Properties[ConnectionString]
\Package.Connections[CellTypeFileTypeDef].Properties[Name]
\Package.Connections[CellTypeFileTypeDef].Properties[ConnectionString]
\Package.Connections[CellTypeDef].Properties[Name]
\Package.Connections[CellTypeDef].Properties[ConnectionString]
\Package.Connections[CellRangeDef].Properties[Name]
\Package.Connections[CellRangeDef].Properties[ConnectionString]
\Package.Connections[CellRangeCellDef].Properties[Name]
\Package.Connections[CellRangeCellDef].Properties[ConnectionString]
\Package.Connections[CellOverrideDef].Properties[Name]
\Package.Connections[CellOverrideDef].Properties[ConnectionString]
\Package.Connections[CellDef].Properties[Name]
\Package.Connections[CellDef].Properties[ConnectionString]
\Package.Connections[CategoryDef].Properties[Name]
\Package.Connections[CategoryDef].Properties[ConnectionString]
\Package.Connections[CalcPathSubPath].Properties[Name]
\Package.Connections[CalcPathSubPath].Properties[ConnectionString]
\Package.Connections[CalcPath].Properties[Name]
\Package.Connections[CalcPath].Properties[ConnectionString]
\Package.Connections[CalcGraphEdge].Properties[Name]
\Package.Connections[CalcGraphEdge].Properties[ConnectionString]
\Package.Connections[AreaDef].Properties[Name]
\Package.Connections[AreaDef].Properties[ConnectionString]

Found the issue myself. One of the variables I had unintentionally set for the package configuration was of type Object. Rightfully so, you are unable to save a variable of type object to be saved in an xml configuration file.