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

Page brakes after top group item

I just started with reporting services.
I created report that has two groujping items in the table. Everything works
fine just I always get second row for the top level group on the second
page. This doesn't make any sense to me
1) I am using HTML rending and I don't need all this pages
2) the top group item has only one detailed item and second has about 8
items they all can fit on one page even if viewed in collapsed state
3) There are no any forced page breaks for any table items and for table
itself.
Any ideas how to solve the problem?
ShimonSorry
I just figure out that it looks like that only in preview but on the site it
looks fine.
Thanks,
Shimon.
"Shimon Sim" <estshim@.att.net> wrote in message
news:eeQ3XrQeEHA.2376@.tk2msftngp13.phx.gbl...
> I just started with reporting services.
> I created report that has two groujping items in the table. Everything
works
> fine just I always get second row for the top level group on the second
> page. This doesn't make any sense to me
> 1) I am using HTML rending and I don't need all this pages
> 2) the top group item has only one detailed item and second has about 8
> items they all can fit on one page even if viewed in collapsed state
> 3) There are no any forced page breaks for any table items and for table
> itself.
> Any ideas how to solve the problem?
> Shimon
>

Packaging Business Intelligence Report project for deploymnet

Hi Guys,

I created my report project, now I build it and deploy it and it works fine.

In my web application project, I created an aspx page with reportViewer control and it works fine too. I publish my website for deployment and works fine. I created a web setup project and add my web application project to it and it works fine. It installs and create the virtual directory in wwwroot (Default WebSite) and it run the script on database too and create all tables and sp.

NowHow Do I deploy mey reports on the server as part of the installaton package. Can I do that or it has to be done manually by going to report server and create a new folder and add a DataSource and upload the *.rdl files...?

1 More thing, in my myReport.aspx page where I have the reportviewer control, I have the <ServerReport ReportServerUrl="http://localhost/reportserver/Intranet" Server Path="/MyReports/ProductsReports" />

Well I think this is hard coding as it might be different on the destination server where tha pllication is going to be deployed.

So is it correct way of doing it that in code behind in page load I set these values ( reportViewer1.ServerReport.ReportServerUrl = "..."; ) and I get the value from webconfig file. I have added the url in the web.config file and all my reports use that report server url.

Thanks for your help and suggestions in advance,

Regards,
Mehdi

HI, Mehdi:

I hope this article can help:

Deploying Reports and ReportViewer Controls

http://msdn2.microsoft.com/en-us/library/ms251723(VS.80).aspx

If i misunderstand you about your question, please feel free to correct me and i will try to help you with more information.

I hope the above information will be helpful. If you have any issues or concerns, please let me know. It's my pleasure to be of assistance

|||

hi,

i to got struct. i have same problem as described above. i have a web setup of web project that consist of a form that uses report viewer whose report server url is read from web.config. i have a sql reporting services report project which consist of reports. when comes to installation on the client side i can carry web setup of web project but when come to report server project i don't want to carry sourse code to deploy. can it be possible to make websetup of report server project if yes plz reply how to make the setup else say the alternative methods so that i can install at the client side with integration issuess my email id isabdulhaseeb_1201@.yahoo.com. waiting for ur reply. the link which u have provied could not help me a lot, so dont mind plz explain it in your way

Packaging Business Intelligence Report project for deploymnet

Hi Guys,

I created my report project, now I build it and deploy it and it works fine.

In my web application project, I created an aspx page with reportViewer control and it works fine too. I publish my website for deployment and works fine. I created a web setup project and add my web application project to it and it works fine. It installs and create the virtual directory in wwwroot (Default WebSite) and it run the script on database too and create all tables and sp.

NowHow Do I deploy mey reports on the server as part of the installaton package. Can I do that or it has to be done manually by going to report server and create a new folder and add a DataSource and upload the *.rdl files...?

1 More thing, in my myReport.aspx page where I have the reportviewer control, I have the <ServerReport ReportServerUrl="http://localhost/reportserver/Intranet" Server Path="/MyReports/ProductsReports" />

Well I think this is hard coding as it might be different on the destination server where tha pllication is going to be deployed.

So is it correct way of doing it that in code behind in page load I set these values ( reportViewer1.ServerReport.ReportServerUrl = "..."; ) and I get the value from webconfig file. I have added the url in the web.config file and all my reports use that report server url.

Thanks for your help and suggestions in advance,

Regards,
Mehdi

Hi Mehdi,

We use this tool for deploying the reports to report server, its easy to use and solved our problem,

Reporting Services Scripter -http://www.sqldbatips.com/showarticle.asp?ID=62

Overview

Reporting Services Scripter is a .NET Windows Forms application that enables scripting and transfer of all Microsoft SQL Server Reporting Services catalog items to aid in transferring them from one server to another. It can also be used to easily move items on mass from one Reporting Services folder to another on the same server. Depending on the scripting options chosen, Reporting Services Scripter can also transfer all catalog item properties such as Descriptions, History options, Execution options (including report specific and shared schedules), Subscriptions (normal and data driven) and server side report parameters.

Not sure whetehr you can use the command script file with the installer or not, but having seprate deployment script for reports is better as you may have different web and database servers (as in our case).

Now regarding hardcoding the ReportServerUrl and report path you are right its not good practice to do that, web.config file should be used for storing these values so that you can update them on web server itself.

Mehdi6002:

it installs and create the virtual directory in wwwroot (Default WebSite) and it run the script on database too and create all tables and sp.

Can you tell how you run the database script through installer? :)

|||

Hello akjoshi,

Thanks for your reply. Would you just add a key to your web config file in the <appSettings> for the reports folder and reportServerUrl or there is a specific place that you have to include these, like in <httpHandlers> or < buildProviders> or ...

at the moment I just have 2 keys in <appSettings>

<add key="ReportServerUrl" value="http://..." />
<add key="ReportFolder" value="/ABC" />

is that a right way of doing it...?

To run SQL script while installing your DB, have a look at this article:

http://msdn2.microsoft.com/en-us/library/49b92ztk(VS.80).aspx

Regards,
Mehdi

|||

Yes Mehdi you are right, Using the <appSettings> section to add these keys is the right approch.

<appSettings>

<addkey="ReportServerUrl"value="http://ServerName/ReportServer"/>

<addkey="ReportPath"value="/ReportPath/"/>

</appSettings>

and acces it like this -

ReportServerUrl =newUri(ConfigurationManager.AppSettings["ReportServerUrl"]);

thanks for the link.

|||

Deploying a SQL Server Reporting Services 2005 report via an MSI -

http://blogs.msdn.com/bimusings/archive/2006/03/01/541599.aspx

Package works in BIDS, but not in SQL Server 2005

Most of my packages that I've created in BIDS will NOT run in SQL Server 2005. The simplest one that I have fails during a script task that calls external managed code. I've done all the steps outlined in "Referencing Other Assemblies...", but I'm still getting "Object reference not set to an instance of an object." Here's a sample of a script that's having a problem. The line in green is the one that seems to be cause of the error. This is extremely frustrating. This code will even run from a command line console without error. Why is it so difficult to deploy one of these projects with managed code?

Code Snippet

Public Sub Main()
Dim variable1 As String = DirectCast(Dts.Variables("packagevariable1").Value, String)
Dim variable2 As String = DirectCast(Dts.Variables("packagevariable2").Value, String)
Dim variable3 As Integer = DirectCast(Dts.Variables("packagevariable3").Value, Integer)
Dim variable4 As String = DirectCast(Dts.Variables("packagevariable4").Value, String)
Dim filePath As String = DirectCast(Dts.Variables("filePath").Value, String)
Dim variable5 As String = DirectCast(Dts.Variables("packagevariable5").Value, String)
Dim results As Boolean
Dim fileGenerator As IProviderInterface
Dim intFactory As integrationServiceFactory = New ProviderIntegrationServiceFactory()

fileGenerator = intFactory.GetProviderEnrollmentGenerator(variable2, variable5)
results = fileGenerator.GenerateFile(variable3, variable1, filePath, variable2)

If results Then
Dts.TaskResult = Dts.Results.Success
Else
Dts.TaskResult = Dts.Results.Failure
End If

End Sub

Can you be more specific what you mean by "run in SQL Server"? How are these packages being executed?

What other types of activities are failing in your packages? Is it always related to external code?

My thinking based on what you've given so far is that you're trying to load and execute packages from inside a CLR stored procedure. When you do that, you're using SQL's hosted CLR and are subject to SQL's security constraints, such as the Host Protection Attributes.

SQL CLR will deny execution of framework code that has these attributes.
ExternalProcessMgmt ExternalThreading MayLeakOnAbort SecurityInfrastructure SelfAffectingProcessMgmnt SelfAffectingThreading SharedState Synchronization UI |||Primarily, I'm trying to execute this in a SQL job. However, if I execute it from Integration Services, the result is the same. My errors seem to be related to the external code, but I have 15 packages that all use some external code. Other activities that are failing also include decrypting files, but again, these are run in scripts that use external code.
|||

Dan Wheeler wrote:

Primarily, I'm trying to execute this in a SQL job. However, if I execute it from Integration Services, the result is the same. My errors seem to be related to the external code, but I have 15 packages that all use some external code.

"if I execute it from Integration Services, the result is the same". I don't know if that means BIDS or something else, and if "the same" means success or failure. I'm mostly going off your subject that the package works in BIDS, but not from a SQL Agent job.

The difference between executing it from BIDS and from a job is usually just the security context. Under BIDS, it inherits your permissions and under SQL Agent, it is whatever account the agent runs under, which is rarely the same. Anything going on in that code that might be affected by those different permissions? Accessing remote or secured drives or files, perhaps?

There is also another affect of the different user context that I don't see any evidence of here, but gets people all the time. That is that by default, SSIS encrypts sensitive data using a user key. When the package is executed by another user (i.e. SQL Agent), this sensitive data (passwords, mostly) are not available. See threads and documentation for "ProtectionLevel" for more information if you think that might be a factor.

|||

JayH wrote:

"if I execute it from Integration Services, the result is the same". I don't know if that means BIDS or something else, and if "the same" means success or failure. I'm mostly going off your subject that the package works in BIDS, but not from a SQL Agent job."

It means that they both fail to execute the package.

The only thing remote in this situation are the databases. This package is not failing with database connectivity issues. My security context is a limited user. What permissions would SQL Agent need?

The packages are password encrypted. I provide the password when creating the SQL job. When I execute the package in Integration Services, I provide the password. Therefore, I don't think it is a protection level issue.
|||

Dan Wheeler wrote:

JayH wrote:

"if I execute it from Integration Services, the result is the same". I don't know if that means BIDS or something else, and if "the same" means success or failure. I'm mostly going off your subject that the package works in BIDS, but not from a SQL Agent job."

It means that they both fail to execute the package.

The only thing remote in this situation are the databases. This package is not failing with database connectivity issues. My security context is a limited user. What permissions would SQL Agent need?

The packages are password encrypted. I provide the password when creating the SQL job. When I execute the package in Integration Services, I provide the password. Therefore, I don't think it is a protection level issue.

I'm still confused about whether the packages run in BIDS or not. If you can call your external code from a simple test harness, then it should work from BIDS.

The error indicates that either GetProviderEnrollmentGenerator is returning Nothing, or that the error is being generated from inside GenerateFile. Is it possible that the variables you're passing into either one of those are not what you think they are? Have you put MessageBoxes in the script to display those values? Being totally agnostic about your code, I'd suggest writing those variables out to a file or something where you can get them verbatim and then calling your external code from a test harness with those values.

|||As the subject states, these packages all execute without error in BIDS. In the first post, I note that I can run this exact code from a console app. I've already created a test harness for this purpose. Since I'm trying to execute this in Integration Services, will the MsgBox's still show up?|||

Dan Wheeler wrote:

As the subject states, these packages all execute without error in BIDS.
Quote "Dan Wheeler"
This code will even run from a command line console without error.

(as you can see, I've already created a test harness for this purpose) Since I'm trying to execute this in Integration Services, will the MsgBox's still show up?

Sorry, I think I'm confused about your use of "Integration Services". I couldn't tell if you meant BIDS or not. I also don't recall seeing that about your console app in the original post.

No, MessageBoxes will not show up if you execute from dtexec (i.e. the agent job). I was thinking about BIDS at that point.
|||Anyone else have anything to offer? I've created a deployment package and still no success.
|||

What if you try running a batch file from the agent job?

My experience mirrors Jay's on this: I think the error is security related. Does the the account that SQL Server or the agent is running under have access to all the files and folders used - not just by the package, but also by the .NET component?

sql

Package Wont Open

I have just created a package to import data from excel files, and perform some simple sql statements. I saved the package, and when I try to re-open it, I get the following error:

"Error Source: Microsoft OLE DB Provider for SQL Server
Error Description: [DBNETLIB][ConnectionOpen (Connect()).] SQL Server does not exist or access denied."

And after I hit th "OK" button, I get the next error msg:

"The selected package cannot be opened. The DTS Designer has been closed"

Is my package completely lost? Can I open it elsewhere, or by changing some properties somewhere? Also, is there something I can do to avoid this in the future?! I hope all my work isn't lost ...

Any help would be greatly appreciated!

BrianClose Enterprise Manager, reopen it, and see if you can read the DTS package. You can still ping the server can't you?|||Thanks for the quick response.

I closed and re-opened enterprise manager, to no avail. I'm working with my localhost and there is no problem pinging. Everything else seems fine, just can't open this package. Any other thoughts?|||It sounds like it got corrupted somehow. I've never actually had this happen. Been doing DTS packages a long time. If you had saved it previously, right-click on the package and look at versions. See if you can look at any of the previous versions.|||I have no idea what's going on, craziness I say. I only had one version when I checked. I've gone through creating these packages all different ways. They save no problem, but I can't re-open them. Any other thoughts? Thanks!sql

Package still referencing old parameters from an old connection

I deleted and created a new OLE DB connection string then set all my connections to that string in my components in my SSIS package however below where it talks about EBN_TEMP1, that's an old database table that no longer exists after I unistalled and reinstalled SQL Server on this box. Why is it still refrencing old stuff? Is there some sort of refresh I have to do on my entire package due to the fact that I

1) Reinstalled SQL Server 2005

2) Deleted an old OLE DB Conenction my package was using (based on an old database that was in the previous SQL Server install) and createad a new OLE DB Connection in my package to point to a new database name after my reinstall of SQL Server 2005

TITLE: Package Validation Error

Package Validation Error

ADDITIONAL INFORMATION:

Error at Data Flow Task [EBN_TEMP1 [528]]: Columns "Column 0" and "AccountNumber" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [EBN_TEMP1 [528]]: Columns "Column 1" and "Screen" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [EBN_TEMP1 [528]]: Columns "Column 2" and "CaseNumber" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [EBN_TEMP1 [528]]: Columns "Column 3" and "BKYChapter" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [EBN_TEMP1 [528]]: Columns "Column 4" and "FileDate" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [EBN_TEMP1 [528]]: Columns "Column 6" and "DispositionCode" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [EBN_TEMP1 [528]]: Columns "Column 23" and "BKUDA1" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [EBN_TEMP1 [528]]: Columns "Column 24" and "RMSADDR2" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [EBN_TEMP1 [528]]: Columns "Column 25" and "RMSCMPNAME_1" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [EBN_TEMP1 [528]]: Columns "Column 26" and "RMSADDR_1" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [EBN_TEMP1 [528]]: Columns "Column 27" and "RMSCITY_1" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [EBN_TEMP1 [528]]: Columns "Column 28" and "RMSSTATECD_1" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [EBN_TEMP1 [528]]: Columns "Column 29" and "RMSZIPCODE_1" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [EBN_TEMP1 [528]]: Columns "Column 30" and "RMSWORKPHN" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [EBN_TEMP1 [528]]: Columns "Column 31" and "BKYMEETDTE" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [EBN_TEMP1 [528]]: Columns "Column 34" and "RMSCMPNAME_2" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [DTS.Pipeline]: "component "EBN_TEMP1" (528)" failed validation and returned validation status "VS_ISBROKEN".

Error at Data Flow Task [DTS.Pipeline]: One or more component failed validation.

Error at Data Flow Task: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)

BUTTONS:

OK

I don't remember the format of error message, but this string (EBN_TEMP1) is probably the name of data flow component (source, destination, etc), not the connection name. Just look around the package to find an object with such name. Or double click the error message to get editor for this component.sql

Package Script

I have created one package and now I want to generate the scipt, how can I ?
there is no option for this.
Thanks
NOOR
Hi Noor,
Do you need to transfer the packages to a new server or just script the DTS
packages.
If it is just script DTS packages, I will suggest you to backup the MSDB
database.
If you need to transfer the dts to a server then follow the below link;
http://www.sqldts.com/default.aspx?t...&i=204&p=1&a=0
Thanks
Hari
MCDBA
"Noorali Issani" <naissani@.softhome.net> wrote in message
news:uFM9exiFEHA.700@.TK2MSFTNGP09.phx.gbl...
> I have created one package and now I want to generate the scipt, how can I
?
> there is no option for this.
> Thanks
> NOOR
>

Package runs fine in Debug.. fails outside of debug

I created a package that runs fine while in debug... but when I run outside of debug (just choosing Debug.. Start without debugging) I get an error of:

Message: The task "Formulate SQL Query and Destination" cannot run on this edition of Integration Services. It requires a higher level edition.

I am on SP1 on my workstation and the only SSIS server I have. I am not doing anything fancy in my script.. I have created other packages and ran outside of debug mode without issue. I did uninstall SQL2005 and SSIS from my workstation as it was eating up too many resources.

The script I am running is (removed some of the SQL etc to shorten it):

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports System.IO

Public Class ScriptMain

Public Sub Main()

' Create the SQL

Dim SQL As String

SQL += "SELECT "

SQL += " -- Lots of stuff"

SQL += "FROM "

SQL += CStr(Dts.Variables("gvSourceTableName").Value) & " "

SQL += "WHERE "

SQL += " stuff = '" & CStr(Dts.Variables("gvCurrentSymbol").Value) & "' "

SQL += "ORDER BY date"

Dts.Variables("lvSQLQuery").Value = SQL

' MsgBox(CStr(Dts.Variables("lvSQLQuery").Value))

' Create the filename

Dim Dir As String

Dim FileName As String

Dim FullFileName As String

Dir = CStr(Dts.Variables("gvDestinationRoot").Value) & "\" & CStr(Dts.Variables("gvRunID").Value) & "\"

FileName = CStr(Dts.Variables("gvCurrentSymbol").Value) & "--.txt"

FullFileName = Dir & FileName

If Not Directory.Exists(Dir) Then

Directory.CreateDirectory(Dir)

End If

Dts.Variables("gvDestinationFile").Value = FullFileName

' MsgBox(CStr(Dts.Variables("gvDestinationFile").Value))

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

FYI.. tried reinstalling 2005 SP1 on my workstation... still receiving the error message.|||This looks familiar. Try going to the SQL Server 2005 installer and install Integration Services on your local machine. The messages I've received that were similiar to this were solved by that. I believe you have the designer installed on your machine, but not the integration services engine. Good luck.|||Thanks, when I uninstalled SQL 2005 I must have took the SSIS stuff with it.. reinstalled SSIS server components and patch and works great now.|||

Chris Honcoop wrote:

Thanks, when I uninstalled SQL 2005 I must have took the SSIS stuff with it.. reinstalled SSIS server components and patch and works great now.

You're welcome. You know, you'd think that a better error message could be raised... maybe something that actually says you don't have SSIS installed... oh well.

Package running other packages

Hi everybody,

I have to create a package that executes other packages I've already created... Every one of these packages run with the same Configuration File, but if I try to execute the main one, including the path of the file, I get errors from the other packages because they can't find it... How can I manage to pass this file and its content to the other packages?

Here a little explanation of te process:

Main Package needs configuration file X.dtsConfig, and calls: package1, which needs configuration file X.dtsConfig; package2, .......

I hope everything is clear...

I have completely replaced all these packages using config files and calling other packages.

It it is supposed to work with SP1 though. I did not try sp1 yet.

In the meantime, I have consolidated all these "sub-packages" functionality into the main packages.

Philippe

|||

teone wrote:

Hi everybody,

I have to create a package that executes other packages I've already created... Every one of these packages run with the same Configuration File, but if I try to execute the main one, including the path of the file, I get errors from the other packages because they can't find it... How can I manage to pass this file and its content to the other packages?

Here a little explanation of te process:

Main Package needs configuration file X.dtsConfig, and calls: package1, which needs configuration file X.dtsConfig; package2, .......

I hope everything is clear...

You can pass values from the parent package through to child packages using parent package configurations.

-Jamie

|||but what I need is to pass the whole configuration file, not only some variable of the package.... how can I do that?|||

Why not just reference the same config file in each package?

This is made alot easier by the use of indirect configurations: http://blogs.conchango.com/jamiethomson/archive/2005/11/02/2342.aspx

-Jamie

|||

I am stupid, and I need further explanations....

I created a new environment variable, and I set its value with the path of my configuration file... is this the right thing to do?

After this I have enabled package configuration on the package, and set the source to that variable, but it doesn't work... Why?

Thanks

|||Restart the machine so the sys variable is recognized.|||

teone wrote:

I am stupid, and I need further explanations....

I created a new environment variable, and I set its value with the path of my configuration file... is this the right thing to do?

Yes.

teone wrote:

After this I have enabled package configuration on the package, and set the source to that variable, but it doesn't work... Why?

Thanks

Without being there its a bit hard to say. What "doesn't work"? What behaviour are you expecting? What happens instead?

-Jamie

|||The people I work for decided I don't need to do this package anymore.... so I quit thinking about it.
Thanks anyway!
|||

Bonus!!

I wish my superiors would say that to me sometime! :)

Package Ownership

Ok... Now I'am panicking.... I dont know how to resolve this issue...

Package created by me, imported to sql server by dba, its executed under a different user id (all are NT logins)

Execution Command

Code Snippet

DTEXEC /SQL "\pkg1" /SERVER SERVER1 /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E

Below is the error.

Code Snippet

Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Started: 9:01:51 AM

Could not load package "\pkg1" because of error 0xC0014062.

Description: The LoadFromSQLServer method has encountered OLE DB error code 0x80040E14 (Only the owner of DTS Package 'pkg1' or a member of the sysadmin role may create new versions of it.). The SQL statement that was issued has failed.

Source:

Started: 9:01:51 AM

Finished: 9:01:51 AM

Elapsed: 0.156 seconds

Please some body help me on this.
Thanks

And what happens when you execute it with your ID?|||

It fails.

Scenarios:-
1. I Develop, I Import, I Run - every thing works fine.

2. I Develop, DBA Imports to SQL SERVER, I Run - Package fails
3. I Develop, DBA Imports to SQL SERVER, Another user runs - Package fails

4. I Develop, DBA Imports to SQL SERVER, DBA Runs - Package runs.

Finally I figuring it out that either imported user has to run or system admin has to run?

Thanks

|||It's the ProtectionLevel property of the package. Search this forum for "ProtectionLevel" for a plethora of threads on this topic. You'll likely want to use EncryptSensitiveWithPassword or DontSaveSensitive.|||

Phil Brammer wrote:

It's the ProtectionLevel property of the package. Search this forum for "ProtectionLevel" for a plethora of threads on this topic. You'll likely want to use EncryptSensitiveWithPassword or DontSaveSensitive.

One other thing... Are you importing to MSDB or the file system? I ask because if you are importing into MSDB, one other thing to try is to set the security upon import to "Rely on SQL Server Roles and Storage" or something like that.|||

Phil Brammer wrote:

It's the ProtectionLevel property of the package. Search this forum for "ProtectionLevel" for a plethora of threads on this topic. You'll likely want to use EncryptSensitiveWithPassword or DontSaveSensitive

One other thing... Are you importing to MSDB or the file system? I ask because if you are importing into MSDB, one other thing to try is to set the security upon import to "Rely on SQL Server Roles and Storage" or something like that.

We are importing to MSDB. But even file system was giving the same problem.

I already set this property to dontsavesensitive in the designer.

This property is also available when we import the package to sql server so far we left it blank, i'm going to try to set this as dontsavesensitive in this.

Thanks

|||This link provided very useful information to me: http://support.microsoft.com/kb/918760/

|||

Got this resolved by giving the executing user the role of db_dtsoperator. Now the package is fialing with different error as below.

Code Snippet

Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available

This error is coming even when I have set the protection level as Dont Save Sensitive in the dtsx file (in VS 2005). Should I also set this when I import to MSDB?

Thanks

sql

Package not picking up latest Config File changes

Hi,

I've created a solution with 5 packages in. I've scripted a config file for each package where I would be able to change the source file connection and the database servers (source and destination). On the first package, I am able to change the config attributes and the affects are picked up, but the rest of the packages do no pick up the changes.

For example, if the source file is "C:\Files\source.txt" and this is changed to "C:\Files\source123.txt" (Just to make sure it fails) it wont pick up the new changes and still uses "C:\Files\source.txt".

Also, I tried changing the name of config file itself and the package still ran as if nothing had changed. It's as if it's not recognising the config file.

Any ideas ?

Thanks, Richie.

Can you check if you file connection has any PropertyExpression that changes the ConnecftionString back to "C:\Files\source.txt"?

Thanks,
Ovidiu Burlacu

|||

Hi,

There aren't any expressions for the file connections. The way I have the package set up is like this:

STAGE_PACKAGE - Takes the data from a flat file and inserts it in to a staging table.

TRANS_PACKAGE - Takes the data from the staging table and in to the database.

PARENT_PACKAGE - First calls the STAGE_PACKAGE then the TRANS_PACKAGE. An XML config file has been created to capture the location of the file to pick up and the server name to connect to. These are then passed to the STAGE and TRANS packages via parent variables.

I've tried deleting the PARENT_PACKAGE and recreating it, but exactly the same happens.

Richie.

|||I ran into this problem with Parent Package Variables, and switched to Environment Variables instead. You might want to give this a try.|||

This is a known problem of parent package variables. Try to use other venues, like Enviroment variables or registry settings if possible.

Thanks,
Ovidiu Burlacu

|||

That is issue was already documented a while ago; so if somebody is affected by this, go there and vote:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126146

Rafael Salas

|||

Thanks for the replies.

As a work around to this, I decided to do away with the parent variables and set up a config file for the children. I thought that when the parent package is executed, it would call the first child which would then call the config file. But it didn't.

When I run the child package it picks up the config file, but when I run it from a parent package it doesn't pick it up. Is this the same issue as with the parent variables ?

Thanks,

Richie.

|||Anyone ?|||

I am not sure how your packages are set up; but If any of the configuration managers in children packages rely on the value to be received from the parent package to get configured; that simply will no work because the issue I mentioned in my previous post.

How are you running the packages? BIDS, SQL AGENT, DTEXEC?

Take a look to the execution progress to see if there is any warning about configuration no taking place.

Rafael Salas

Package not picking up latest Config File changes

Hi,

I've created a solution with 5 packages in. I've scripted a config file for each package where I would be able to change the source file connection and the database servers (source and destination). On the first package, I am able to change the config attributes and the affects are picked up, but the rest of the packages do no pick up the changes.

For example, if the source file is "C:\Files\source.txt" and this is changed to "C:\Files\source123.txt" (Just to make sure it fails) it wont pick up the new changes and still uses "C:\Files\source.txt".

Also, I tried changing the name of config file itself and the package still ran as if nothing had changed. It's as if it's not recognising the config file.

Any ideas ?

Thanks, Richie.

Can you check if you file connection has any PropertyExpression that changes the ConnecftionString back to "C:\Files\source.txt"?

Thanks,
Ovidiu Burlacu

|||

Hi,

There aren't any expressions for the file connections. The way I have the package set up is like this:

STAGE_PACKAGE - Takes the data from a flat file and inserts it in to a staging table.

TRANS_PACKAGE - Takes the data from the staging table and in to the database.

PARENT_PACKAGE - First calls the STAGE_PACKAGE then the TRANS_PACKAGE. An XML config file has been created to capture the location of the file to pick up and the server name to connect to. These are then passed to the STAGE and TRANS packages via parent variables.

I've tried deleting the PARENT_PACKAGE and recreating it, but exactly the same happens.

Richie.

|||I ran into this problem with Parent Package Variables, and switched to Environment Variables instead. You might want to give this a try.|||

This is a known problem of parent package variables. Try to use other venues, like Enviroment variables or registry settings if possible.

Thanks,
Ovidiu Burlacu

|||

That is issue was already documented a while ago; so if somebody is affected by this, go there and vote:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126146

Rafael Salas

|||

Thanks for the replies.

As a work around to this, I decided to do away with the parent variables and set up a config file for the children. I thought that when the parent package is executed, it would call the first child which would then call the config file. But it didn't.

When I run the child package it picks up the config file, but when I run it from a parent package it doesn't pick it up. Is this the same issue as with the parent variables ?

Thanks,

Richie.

|||Anyone ?|||

I am not sure how your packages are set up; but If any of the configuration managers in children packages rely on the value to be received from the parent package to get configured; that simply will no work because the issue I mentioned in my previous post.

How are you running the packages? BIDS, SQL AGENT, DTEXEC?

Take a look to the execution progress to see if there is any warning about configuration no taking place.

Rafael Salas

Package loses configuration files!

I created a project with multiple packages. I created a global config file that all packages reference. I also created a config file for each package that contains the package specific settings. I checked it all into TFS. I went to a different machine and checked it all out. Upon loading a package into the IDE I got the following 3 warnings.

Warning loading MT_LSE_PROD_StageLoad.dtsx: The configuration file "MT_LSE_PROD_StageLoad.dtsConfig" cannot be found. Check the directory and file name. e:\contentloader\sprint1a\MT_LSE_PROD_StageLoad.dtsx

Warning loading MT_LSE_PROD_StageLoad.dtsx: The configuration file "Environment.dtsConfig" cannot be found. Check the directory and file name. e:\contentloader\sprint1a\MT_LSE_PROD_StageLoad.dtsx

Warning loading MT_LSE_PROD_StageLoad.dtsx: Failed to load at least one of the configuration entries for the package. Check configurations entries and previous warnings to see descriptions of which configuration failed. e:\contentloader\sprint1a\MT_LSE_PROD_StageLoad.dtsx

Any ideas? And yes, the files *ARE* there! I tried building the project and it builds. I also tried doing a deployment build and that too built. I am so confused!!!

Are you using SQL2005/Express SP1? As I know there used to be a known issue with SP1: if you use configuration file to deploy SSIS package, the referenced file names in the configuraiton file will be change to lowercase. Since XML file is case-sensitive, the files can't be found if the name does not exactly match. Not sure whether there is any hotfix available now.|||We are using SQL 2005 Enterprise. I believe it is SP1 as well. Will see if naming everything in lower case only fixes it up. Thanks.sql

Package fails when it is scheduled

Hi
I'm using SQL Server 2000. I've created a local package which runs
fine when i execute it manually from Enterprise Manager, but
constantly fails when I schedule it. Can anybody suggest why this
might be?
Thanks
Colin
Most probably, its the permissions given to the userid that is used to
call the job.
either case, What does the history log say? (right click on the job
and select "view history")
On Mar 6, 5:52Xpm, Bobby <bob...@.blueyonder.co.uk> wrote:
> Hi
> I'm using SQL Server 2000. I've created a local package which runs
> fine when i execute it manually from Enterprise Manager, but
> constantly fails when I schedule it. Can anybody suggest why this
> might be?
> Thanks
> Colin
|||On 6 Mar, 23:56, schal <shivaramcha...@.gmail.com> wrote:
> Most probably, its the permissions given to the userid that is used to
> call the job.
> either case, What does the history log say? (right click on the job
> and select "view history")
>
The history log says the following:
Executed as user: SQLSRV01\SYSTEM. ...Start:
DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish:
DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
DTSStep_DTSDataPumpTask_1 DTSRun OnStart:
DTSStep_DTSDataPumpTask_2 DTSRun OnError:
DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error
string: The driver returned invalid (or failed to return)
SQL_DRIVER_ODBC_VER: 2.00 Error source: Microsoft OLE DB
Provider for ODBC Drivers Help file: Help context: 0
Error Detail Records: Error: -2147467259 (80004005); Provider
Error: 444 (1BC) Error string: The driver returned invalid (or
failed to return) SQL_DRIVER_ODBC_VER: 2.00 Error source:
Microsoft OLE DB Provider for ODBC Drivers Help file: Help
context: 0 Error: -2147467259 (80004005); Provider Error: 0
(0) Error string: Cannot find all files in data path Error
source: Microsoft OLE DB Provider for ODBC Drivers Help
file: ... Process Exit Code 2. The step failed.
The package has just three steps. Step 1 is an execute SQL task which
deletes from two tables. Steps 2 & 3 connect to our accounts package
(Sage) using ODBC and uses two Transform data tasks to copy data from
Sage tables to the two SQL server tables. I am connecting to SQL sever
using the Microsoft OLE DB provider. I know that Step 1 is working
because after the package fails the two SQL tables are empty.
The package works fine if I
|||On 8 Mar, 09:44, Bobby <bob...@.blueyonder.co.uk> wrote:
> On 6 Mar, 23:56, schal <shivaramcha...@.gmail.com> wrote:
>
> The history log says the following:
> Executed as user: SQLSRV01\SYSTEM. ...Start:
> DTSStep_DTSExecuteSQLTask_1 X DTSRun OnFinish:
> DTSStep_DTSExecuteSQLTask_1 X DTSRun OnStart:
> DTSStep_DTSDataPumpTask_1 X DTSRun OnStart:
> DTSStep_DTSDataPumpTask_2 X DTSRun OnError:
> DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) X X XError
> string: XThe driver returned invalid (or failed to return)
> SQL_DRIVER_ODBC_VER: 2.00 X X XError source: XMicrosoft OLE DB
> Provider for ODBC Drivers X X XHelp file: X X X XHelp context: X0
> Error Detail Records: X X XError: X-2147467259 (80004005); Provider
> Error: X444 (1BC) X X XError string: XThe driver returned invalid (or
> failed to return) SQL_DRIVER_ODBC_VER: 2.00 X X XError source:
> Microsoft OLE DB Provider for ODBC Drivers X X XHelp file: X X X XHelp
> context: X0 X X X X Error: X-2147467259 (80004005); Provider Error: X0
> (0) X X XError string: XCannot find all files in data path X XXError
> source: XMicrosoft OLE DB Provider for ODBC Drivers X X XHelp
> file: X X ... XProcess Exit Code 2. XThe step failed.
> The package has just three steps. Step 1 is an execute SQL task which
> deletes from two tables. Steps 2 & 3 connect to our accounts package
> (Sage) using ODBC and Xuses two Transform data tasks to copy data from
> Sage tables to the two SQL server tables. I am connecting to SQL sever
> using the Microsoft OLE DB provider. I know that Step 1 is working
> because after the package fails the two SQL tables are empty.
> The package works fine if I
.....right click on it and select Execute package. It only fails when
it is scheduled. Sorry, I got carried away when typing and hit the
wrong key!
Thanks for any help,
Colin
sql

Package fails when it is scheduled

Hi
I'm using SQL Server 2000. I've created a local package which runs
fine when i execute it manually from Enterprise Manager, but
constantly fails when I schedule it. Can anybody suggest why this
might be?
Thanks
ColinMost probably, its the permissions given to the userid that is used to
call the job.
either case, What does the history log say? (right click on the job
and select "view history")
On Mar 6, 5:52=A0pm, Bobby <bob...@.blueyonder.co.uk> wrote:
> Hi
> I'm using SQL Server 2000. I've created a local package which runs
> fine when i execute it manually from Enterprise Manager, but
> constantly fails when I schedule it. Can anybody suggest why this
> might be?
> Thanks
> Colin|||On 6 Mar, 23:56, schal <shivaramcha...@.gmail.com> wrote:
> Most probably, its the permissions given to the userid that is used to
> call the job.
> either case, What does the history log say? (right click on the job
> and select "view history")
>
The history log says the following:
Executed as user: SQLSRV01\SYSTEM. ...Start:
DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish:
DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
DTSStep_DTSDataPumpTask_1 DTSRun OnStart:
DTSStep_DTSDataPumpTask_2 DTSRun OnError:
DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error
string: The driver returned invalid (or failed to return)
SQL_DRIVER_ODBC_VER: 2.00 Error source: Microsoft OLE DB
Provider for ODBC Drivers Help file: Help context: 0
Error Detail Records: Error: -2147467259 (80004005); Provider
Error: 444 (1BC) Error string: The driver returned invalid (or
failed to return) SQL_DRIVER_ODBC_VER: 2.00 Error source:
Microsoft OLE DB Provider for ODBC Drivers Help file: Help
context: 0 Error: -2147467259 (80004005); Provider Error: 0
(0) Error string: Cannot find all files in data path Error
source: Microsoft OLE DB Provider for ODBC Drivers Help
file: ... Process Exit Code 2. The step failed.
The package has just three steps. Step 1 is an execute SQL task which
deletes from two tables. Steps 2 & 3 connect to our accounts package
(Sage) using ODBC and uses two Transform data tasks to copy data from
Sage tables to the two SQL server tables. I am connecting to SQL sever
using the Microsoft OLE DB provider. I know that Step 1 is working
because after the package fails the two SQL tables are empty.
The package works fine if I|||On 8 Mar, 09:44, Bobby <bob...@.blueyonder.co.uk> wrote:
> On 6 Mar, 23:56, schal <shivaramcha...@.gmail.com> wrote:
> > Most probably, its the permissions given to the userid that is used to
> > call the job.
> > either case, What does the history log say? (right click on the job
> > and select "view history")
> The history log says the following:
> Executed as user: SQLSRV01\SYSTEM. ...Start:
> DTSStep_DTSExecuteSQLTask_1 =A0 DTSRun OnFinish:
> DTSStep_DTSExecuteSQLTask_1 =A0 DTSRun OnStart:
> DTSStep_DTSDataPumpTask_1 =A0 DTSRun OnStart:
> DTSStep_DTSDataPumpTask_2 =A0 DTSRun OnError:
> DTSStep_DTSDataPumpTask_1, Error =3D -2147467259 (80004005) =A0 =A0 =A0Err=or
> string: =A0The driver returned invalid (or failed to return)
> SQL_DRIVER_ODBC_VER: 2.00 =A0 =A0 =A0Error source: =A0Microsoft OLE DB
> Provider for ODBC Drivers =A0 =A0 =A0Help file: =A0 =A0 =A0 =A0Help contex=t: =A00
> Error Detail Records: =A0 =A0 =A0Error: =A0-2147467259 (80004005); Provide=r
> Error: =A0444 (1BC) =A0 =A0 =A0Error string: =A0The driver returned invali=d (or
> failed to return) SQL_DRIVER_ODBC_VER: 2.00 =A0 =A0 =A0Error source:
> Microsoft OLE DB Provider for ODBC Drivers =A0 =A0 =A0Help file: =A0 =A0 ==A0 =A0Help
> context: =A00 =A0 =A0 =A0 =A0 Error: =A0-2147467259 (80004005); Provider E=rror: =A00
> (0) =A0 =A0 =A0Error string: =A0Cannot find all files in data path =A0 =A0= =A0Error
> source: =A0Microsoft OLE DB Provider for ODBC Drivers =A0 =A0 =A0Help
> file: =A0 =A0 ... =A0Process Exit Code 2. =A0The step failed.
> The package has just three steps. Step 1 is an execute SQL task which
> deletes from two tables. Steps 2 & 3 connect to our accounts package
> (Sage) using ODBC and =A0uses two Transform data tasks to copy data from
> Sage tables to the two SQL server tables. I am connecting to SQL sever
> using the Microsoft OLE DB provider. I know that Step 1 is working
> because after the package fails the two SQL tables are empty.
> The package works fine if I
=2E....right click on it and select Execute package. It only fails when
it is scheduled. Sorry, I got carried away when typing and hit the
wrong key!
Thanks for any help,
Colin

Package fails but single Task ends with success

Hi

I've created a simple package that contains only one task that is an execute sql task. When I run only this single task from Business Intelligence development studio it runs successfully. But when I run the whole package (also from Business intlligence studio), the package fails.

The data source I access is ODBC. I'm sure the real reason for the error is the bad ODBC driver of the data source but this can't be changed. So I need to know what is different from running only a task in a package to running the whole package. If I knew that I might be able to adjust some setting and make it work.

Any help welcome.

What is the error you are receiving?

Rafael Salas

|||I had a similar probem the other day.

all the individual steps would run ok in visual studio, but the package as a whole would fail.

the problem was a spurious executable that was not visible on the control flow design screen, but was listed as an executable in package explorer.

its was this spurious executable that was failing, and so causing my package to fail.

have a look to see what is shown as an executable in the package explorer tab?|||

Hi Rafael

Thanks for your answer.

I get the messagebox-error with "Unable to load DLL, Fatal Error!" in the messagebox title and the dll's path in the message body. The dll that is indicated there belongs to the system that should be accessed.

Christian

|||

I've checked the package in package explorer but there's only the one executable that I've just created.

Christian

sql

Package execution failed but no error (SQL2005)

I made an Integration Services Package which runs well if I start it manually
(also from SQL server Management Studio).
Now I have created a new job for the SQL server agent which need to run my
package.
After starting the job, it ends after a few seconds:
"The package execution failed" is the only message there is.
I assume it has something to do with security but for the test I am logged
in as a domain administrator which has all rights to all databases.
Can someone give some clues about how to solve this?
Thanks
Two possibilities come to my mind:
1- SQL Server run under its own account; so you should check the permission
for this account on both Windows and SQL-Server side (for example, does this
account has an explicit access to the database?)
2- You are using things like a network drive (Z:\Repertory\...) instead of
an UNC file name (\\Server\Repertory\...) which are specific to the login
account and unknown to other accounts.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Frans" <Frans@.discussions.microsoft.com> wrote in message
news:6EBD9795-92BA-435C-BCC7-CFEBDC6EAF2F@.microsoft.com...
>I made an Integration Services Package which runs well if I start it
>manually
> (also from SQL server Management Studio).
> Now I have created a new job for the SQL server agent which need to run my
> package.
> After starting the job, it ends after a few seconds:
> "The package execution failed" is the only message there is.
> I assume it has something to do with security but for the test I am logged
> in as a domain administrator which has all rights to all databases.
> Can someone give some clues about how to solve this?
> Thanks

Package execution failed but no error (SQL2005)

I made an Integration Services Package which runs well if I start it manuall
y
(also from SQL server Management Studio).
Now I have created a new job for the SQL server agent which need to run my
package.
After starting the job, it ends after a few seconds:
"The package execution failed" is the only message there is.
I assume it has something to do with security but for the test I am logged
in as a domain administrator which has all rights to all databases.
Can someone give some clues about how to solve this?
ThanksTwo possibilities come to my mind:
1- SQL Server run under its own account; so you should check the permission
for this account on both Windows and SQL-Server side (for example, does this
account has an explicit access to the database?)
2- You are using things like a network drive (Z:\Repertory\...) instead of
an UNC file name (\\Server\Repertory\...) which are specific to the login
account and unknown to other accounts.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Frans" <Frans@.discussions.microsoft.com> wrote in message
news:6EBD9795-92BA-435C-BCC7-CFEBDC6EAF2F@.microsoft.com...
>I made an Integration Services Package which runs well if I start it
>manually
> (also from SQL server Management Studio).
> Now I have created a new job for the SQL server agent which need to run my
> package.
> After starting the job, it ends after a few seconds:
> "The package execution failed" is the only message there is.
> I assume it has something to do with security but for the test I am logged
> in as a domain administrator which has all rights to all databases.
> Can someone give some clues about how to solve this?
> Thanks

Package Error.

Hi everybody,

I have created a package which contains execution of a set of SQL Stored Procedures. I scheduled the job to run every morning at 7:00 AM. I am getting the following error:

Executed as user: APD-DEV-CS517\SYSTEM. The package could not be found. The step failed.

What do you think the problem is?
Thanks again.Looks to me that "the package could not be found." Have you ensured the path is correct?|||Yeah I am sure.
Let me make sure about that and wait till tomorrow morning.

Thanks though,
Murthy here