Package Variables Not Available at Runtime

Hello,
I have three package variables that I need to have access to at runtime. All three variables have package scope. The first two, 'StartDate' and 'EndDate' are DateTime variables. The third is called FilePath and is a String variable.

I have taken the following steps;

1. Enabled package configurations
2. Set up all three variables as Parent Package Variables, and have targeted the 'Value' property for each.
3. In the properties of the solution, I have set AllowConfigurationChanges to True.
4. After the package was built, I ran the Package Installation Wizard from the Manifest.

I have done both File System and SQL Server installations. When I go to run the package, none of the three package variables are available for modification.

What am I doing wrong?

Thank you for your help!

cdun2

Hi,

You are not doing any thing wrong.

The value you have to set into the configuration file (xxx.dtsconfig file), that has to be read by package, before executing the package.

The configuration file is like name value pair...Name will be created by SSIS, you have to fill the value

You can't modify the variable at runtime, it is updated from the configuration file at runtime.

Thanks

Dharmbir

|||Thank you for your response. Dumb question; Where do I find this file?|||cdun2,
That file is not applicable in your situation.

Phil|||Thanks. What configuration type do I need to set up for these package variables so that I can configure the package variables at run time? Should I be using an XML Configuration file?|||

cdun2 wrote:

Thanks. What configuration type do I need to set up for these package variables so that I can configure the package variables at run time? Should I be using an XML Configuration file?

Well, you can use XML config files (or SQL Server configurations) or you can use the /SET option on the command line.|||Thanks for your help!|||

I think in your case XML config file will be most usefull as it is easily readable.

when you create the config file, path will be mentioned in the wizard.

|||

Dharmbir wrote:

I think in your case XML config file will be most usefull as it is easily readable.

Why?|||

Dharmbir wrote:

I think in your case XML config file will be most usefull as it is easily readable.

when you create the config file, path will be mentioned in the wizard.

I've tried both SQL Server and XML configurations, and I must be missing something. I expected that with one or the other configurations that there would be something in the Execute Package Utility where I would see the package variables that I wanted to update, and then I could basically 'fill in the blanks'.

Now I understand that for the XML configuration files, the file has to be opened and modified directly. In the table I set up for the SQL Server configurations, do I just execute Update queries to modify the values? In the Execute Package Utility 'Set Values' property, I see where I can add the Property Path and its value. It looks like I need to type in the Property Path every time I run the package. Why would I have to do that If I have specifically set up specific package variables in the package configurations?

Thanks again.

|||If package configurations are turned on, then whatever you define in that configuration will get picked up at run time. So, for example, it is common to place the connectionstring parameter of your database connections into the configuration. Updating this parameter (either by editing the XML file, or by updating the configuration table in SQL Server) will cause that associated connection to use the new, updated connectionstring at runtime. You need not pass anything in if the configuration is correct and that you have "configured" the appropriate pieces of information (connectionstring, variable, etc...)

Does that make sense?|||Yes, it makes sense. I just expected some way to interface with the variables other than a direct edit to an XML file or an UPDATE statement against a table. I don't have anything against doing either, I just expected to see the variables expressed in the Execute Package Utility. The interface for the Connection Managers is similar to what I expected for the package variables that I set up in package configurations.|||

cdun2 wrote:

Yes, it makes sense. I just expected some way to interface with the variables other than a direct edit to an XML file or an UPDATE statement against a table. I don't have anything against doing either, I just expected to see the variables expressed in the Execute Package Utility. The interface for the Connection Managers is similar to what I expected for the package variables that I set up in package configurations.

Nope. You can do it in the Execute Package Utility though. Use the /SET command line option: http://msdn2.microsoft.com/en-us/library/ms162810.aspx|||

Phil Brammer wrote:

Nope. You can do it in the Execute Package Utility though. Use the /SET command line option: http://msdn2.microsoft.com/en-us/library/ms162810.aspx

Did you mean the dtexec utility? At least I know how I'm supposed to alter the variable values once they are configured in the package. I'll probably just write a proc to update the SQL SSIS Configurations table that I have.

Thanks again!

cdun2

Package question

Hello All,

I am new to the sql 2k5 utilities. My company had to move a couple of their access database over to sql server because they are approaching the 2G size. I have a macro in access that creating a csv file. I have to recreate that macro in sql server. So I have created a view to produce the result set for the file. I create a package that has the view as its source and the csv file as the destination. I run the package to create the file and it runs fine. I then create a job to run the package automatically but the job fails. I am not sure what I am doing wrong. All this is being done directly on the server...do I need to do something extra when I create the step?

In the step I specify step name

Type SSIS...run as 'SQL Agent Service Account'

Package source 'SQL Server"

Then I select the package from the list of SSIS packages. The error message does not give any specifics except to say package failed. The step failed.

Any ideas on what I am doing wrong?

Thanks!

Hi,

you should implement some proper error handling in your SSIS package, seems that if you are importing a csv file, either the SQL Server Agent account has no access to the files, the files are locked, or the Authentication used for loading the data in SQL Server is not setup properly (e.g. the SQL Agent Account has no access to the SQL Serverobjects)

Jens K. Suessmeyer.

http://www.sqlserver2005.de

sql

Package configuration - registry entry problems

I want to access a registry entry in a package configuration but am having problems. I am looking for some example values for the "Registry Entry" field in the Package Configuration Wizard.

The test is a simple one, fill a variable with a registry value and present it in message box. I have tried a number of different values but can't seem to get the right one. Here are somethat I tried:

Software\\MyCompany\\ImportExport\\ServerName\\Value
Software\\MyCompany\\ImportExport\\ServerName

HKEY_CURRENT_USER\\Software\\MyCompany\\ImportExport\\ServerName
HKEY_CURRENT_USER\\Software\\MyCompany\\ImportExport\\ServerName\\Value

Software\MyCompany\ImportExport\ServerName\Value
Software\MyCompany\ImportExport\ServerName

HKEY_LOCAL_MACHINE\Software\MyCompany\ImportExport\ServerName
HKEY_LOCAL_MACHINE\Software\MyCompany\ImportExport\ServerName\Value

HKEY_CURRENT_USER\Software\MyCompany\ImportExport\ServerName\Value
HKEY_CURRENT_USER\Software\MyCompany\ImportExport\ServerName

HKEY_CURRENT_USER\Software\MyCompany\ImportExport
Software\MyCompany\ImportExport

HKEY_CURRENT_USER\\Software\\MyCompany\\ImportExport
Software\\MyCompany\\ImportExport

jcl,

I just realized you have double posted this question. I have already replied in the other thread. Here is what I replied:

--

cj See if you can find the answer here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=187656&SiteID=1

|||

Sorry. I still cannot read the registry entry.

In the registry:

HKEY_CURRENT_USER\Software\MyCompany\MyPackageConfiguration

then I have a string value called:

MyParameter1

and the value data is set to:

MyTestValue

I want to see MyTestValue. What do I put in the Package Configuration Wizard text box? These did not work:

HKEY_CURRENT_USER\Software\MyCompany\MyPackageConfiguration\MyParameter1\Value

HKCU\Software\MyCompany\MyPackageConfiguration\MyParameter1\Value

\Software\MyCompany\MyPackageConfiguration\MyParameter1\Value

Software\MyCompany\MyPackageConfiguration\MyParameter1\Value

Software\MyCompany\MyPackageConfiguration\MyParameter1

\Software\MyCompany\MyPackageConfiguration\MyParameter1

HKCU\Software\MyCompany\MyPackageConfiguration\MyParameter1

HKEY_CURRENT_USER\Software\MyCompany\MyPackageConfiguration\MyParameter1

Ownership issue

One of my apps uses a login that is tied down to only use the sprocs and tables it is allowed to access. Its just given 'public' role. However, it needs to be able to add and delete fields from one table in particular. Is there a way to allow this? The login it uses has been given full rights to the table but an alter table command faults. The tbale must be a 'dbo' because other apps use it -- can I be granted DDL rights on a per table basis?Enterprise manager allow for table access specifically to tables, vies, store procedures etc... Right can be assigned per table or view. Read, Write, Update, Delete.|||Yes they can...but not for alter table it seems.|||Alter table? Dump it out and bring it back in ussing DTS utl for easy cleanup.|||Huh?

I want my app to issue an alter table command in SQL Server, it seems impossible without having dbowner in login -- which would seem massive overkill|||According to the BOL in the description of the GRANT (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqldmo/dmoref_con01_85f7.asp) command, in thefootnote 1 for ALTER TABLE it says that the object owner can issue ALTER TABLE commands for the objects that they own. The table shows that the db_owner and db_ddladmin roles can issue ALTER TABLE commands for objects owned by other users.

-PatP|||Thanks for clearing that up. Looks like I am stuffed really. Ah well...

I have got around it temporarily by raising the rights on the accout, doing the operation and then lowering them.

Owner's Access to table

SQL SERVER 2000 Personal Edition
Win 2000
Connect using SqlServer Authentification
Jim **pw**
Run the following to create a new table:-
USE SolutionsNet
GO
CREATE TABLE
Companies
(CompanyId INT PRIMARY KEY IDENTITY(1,1),
Companyname VarChar(50) NOT NULL,
IsaRestaurant BIT,
. . . . .
)
GO
Then the following:-
Use SolutionsNet
GO
SELECT * FROM Companies
REM OR select * from jim.Companies
GO
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'Companies', database 'SolutionsNet',
owner 'Jim'.
BUT
connect as sa **pw**
USE SolutionsNet
GO
SELECT * FROM Jim.Companies
GO
Displays the empty table Ok
[NB. just Companies without tyhe prefix Jim. does not display the empty
table
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'Companies'. ]
?
Why can I not access the table connected as Jim - it's owner !!!
?
Jim Bunton
Ok - got the answer on sqlserver.tools group -
I'd 'clicked' all roles 'to make sure!!!!" one of them is 'denydatareader' -

> beginners beware!!!
"Jim Bunton" <jBunton@.BlueYonder.co.uk> wrote in message
news:gpO4f.135773$RW.48588@.fe2.news.blueyonder.co. uk...
> SQL SERVER 2000 Personal Edition
> Win 2000
> Connect using SqlServer Authentification
> Jim **pw**
> Run the following to create a new table:-
> --
> USE SolutionsNet
> GO
> CREATE TABLE
> Companies
> (CompanyId INT PRIMARY KEY IDENTITY(1,1),
> Companyname VarChar(50) NOT NULL,
> IsaRestaurant BIT,
> . . . . .
> )
> GO
> --
> Then the following:-
> --
> Use SolutionsNet
> GO
> SELECT * FROM Companies
> REM OR select * from jim.Companies
> GO
> --
> Server: Msg 229, Level 14, State 5, Line 1
> SELECT permission denied on object 'Companies', database 'SolutionsNet',
> owner 'Jim'.
> BUT
> connect as sa **pw**
> --
> USE SolutionsNet
> GO
> SELECT * FROM Jim.Companies
> GO
> --
> Displays the empty table Ok
> [NB. just Companies without tyhe prefix Jim. does not display the empty
> table
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'Companies'. ]
> ?
> Why can I not access the table connected as Jim - it's owner !!!
> ?
>
> Jim Bunton
>
|||He must be in some groups which denies seeing table data. Try to
determine this via sp_helpuser 'Username'
HTH, jens Suessmeyer.

Owner's Access to table

SQL SERVER 2000 Personal Edition
Win 2000
Connect using SqlServer Authentification
Jim **pw**
Run the following to create a new table:-
--
USE SolutionsNet
GO
CREATE TABLE
Companies
(CompanyId INT PRIMARY KEY IDENTITY(1,1),
Companyname VarChar(50) NOT NULL,
IsaRestaurant BIT,
. . . . .
)
GO
--
Then the following:-
--
Use SolutionsNet
GO
SELECT * FROM Companies
REM OR select * from jim.Companies
GO
--
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'Companies', database 'SolutionsNet',
owner 'Jim'.
BUT
connect as sa **pw**
--
USE SolutionsNet
GO
SELECT * FROM Jim.Companies
GO
--
Displays the empty table Ok
[NB. just Companies without tyhe prefix Jim. does not display the empty
table
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'Companies'. ]
'
Why can I not access the table connected as Jim - it's owner !!!
?
Jim BuntonOk - got the answer on sqlserver.tools group -
I'd 'clicked' all roles 'to make sure!!!!" one of them is 'denydatareader' -

> beginners beware!!!
"Jim Bunton" <jBunton@.BlueYonder.co.uk> wrote in message
news:gpO4f.135773$RW.48588@.fe2.news.blueyonder.co.uk...
> SQL SERVER 2000 Personal Edition
> Win 2000
> Connect using SqlServer Authentification
> Jim **pw**
> Run the following to create a new table:-
> --
> USE SolutionsNet
> GO
> CREATE TABLE
> Companies
> (CompanyId INT PRIMARY KEY IDENTITY(1,1),
> Companyname VarChar(50) NOT NULL,
> IsaRestaurant BIT,
> . . . . .
> )
> GO
> --
> Then the following:-
> --
> Use SolutionsNet
> GO
> SELECT * FROM Companies
> REM OR select * from jim.Companies
> GO
> --
> Server: Msg 229, Level 14, State 5, Line 1
> SELECT permission denied on object 'Companies', database 'SolutionsNet',
> owner 'Jim'.
> BUT
> connect as sa **pw**
> --
> USE SolutionsNet
> GO
> SELECT * FROM Jim.Companies
> GO
> --
> Displays the empty table Ok
> [NB. just Companies without tyhe prefix Jim. does not display the empt
y
> table
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'Companies'. ]
> '
> Why can I not access the table connected as Jim - it's owner !!!
> ?
>
> Jim Bunton
>|||He must be in some groups which denies seeing table data. Try to
determine this via sp_helpuser 'Username'
HTH, jens Suessmeyer.

Overriding System Security

Hey folks!
First, if I am understanding correctly, even though I remove the
BUILTIN\Administrators from any roles and/or access in RS, they still have full
access to Report Manager, system settings and all. Is this correct?
If the above is true, has anyone found any way to circumvent this. I think this
a stupid concept. Just because a user is in the local Administrators group on a
server, doesn't mean he/she knows how to manage RS.
Thanks in advance.A local administrator can do just about anything on the box, including
format the hard drive. This does not mean the user should do it or knows
that he should not do it. That is why everyone is not an administrator.
Same applies to RS. Does the user need to be an administrator on the box?
If you remove the Builtin Administrators, they will not have full access to
RS. They can still do admin type work on RS, but will find they cannot view
reports and many other things.
--
| Date: Tue, 28 Jun 2005 10:01:41 -0500
| From: Darrell <Darrell.Wright.nospam@.okc.gov>
| User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317)
| X-Accept-Language: en-us, en
| MIME-Version: 1.0
| Subject: Overriding System Security
| Content-Type: text/plain; charset=ISO-8859-1; format=flowed
| Content-Transfer-Encoding: 7bit
| Message-ID: <ORrbOJ$eFHA.2128@.TK2MSFTNGP14.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: okcpxfw.okc.gov 205.162.227.132
| Lines: 1
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP14.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:46889
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Hey folks!
|
| First, if I am understanding correctly, even though I remove the
| BUILTIN\Administrators from any roles and/or access in RS, they still
have full
| access to Report Manager, system settings and all. Is this correct?
|
| If the above is true, has anyone found any way to circumvent this. I
think this
| a stupid concept. Just because a user is in the local Administrators
group on a
| server, doesn't mean he/she knows how to manage RS.
|
| Thanks in advance.
||||Brad Syputa - MS wrote:
> A local administrator can do just about anything on the box, including
> format the hard drive. This does not mean the user should do it or knows
> that he should not do it. That is why everyone is not an administrator.
>
Good point.
> Same applies to RS. Does the user need to be an administrator on the box?
>
There are a number of AD groups that these users are in that have admin rights
on the server so they can perform various maintenance tasks.
> If you remove the Builtin Administrators, they will not have full access to
> RS. They can still do admin type work on RS, but will find they cannot view
> reports and many other things.
As I said, the BUILTIN\Administrators was removed from RS altogether. Is there
any way, perhaps through IIS, that the local admins could have their access to
RS restricted?
> --
> | Date: Tue, 28 Jun 2005 10:01:41 -0500
> | From: Darrell <Darrell.Wright.nospam@.okc.gov>
> | User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317)
> | X-Accept-Language: en-us, en
> | MIME-Version: 1.0
> | Subject: Overriding System Security
> | Content-Type: text/plain; charset=ISO-8859-1; format=flowed
> | Content-Transfer-Encoding: 7bit
> | Message-ID: <ORrbOJ$eFHA.2128@.TK2MSFTNGP14.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: okcpxfw.okc.gov 205.162.227.132
> | Lines: 1
> | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP14.phx.gbl
> | Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:46889
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | Hey folks!
> |
> | First, if I am understanding correctly, even though I remove the
> | BUILTIN\Administrators from any roles and/or access in RS, they still
> have full
> | access to Report Manager, system settings and all. Is this correct?
> |
> | If the above is true, has anyone found any way to circumvent this. I
> think this
> | a stupid concept. Just because a user is in the local Administrators
> group on a
> | server, doesn't mean he/she knows how to manage RS.
> |
> | Thanks in advance.
> |
>

Override Identity Column using Datatable

I need copy a table from a remote (hosted) SQL 2000 database server to my local machine. I don't have access to backups and am unable to correctly configure my local machine to add a linked server. So I plan to retrieve the data to a datatable, copy it in code and save it to my local server. But the table contains an identity column which I will need to insert the values manually so they match the original.

Can anyone tell me how I can set the datatable's save to use my manual values instead of the autonumber value?

Thanks.

there are two options

1. First remove the constraint on identity column, then import the data, after that apply the constraint on iidentity column.

2. Add another column for it,

|||

SET IDENTITY_INSERT {YourTableName} ON

INSERT INTO {YourTableName} ....

SET IDENTITY_INSERT {YourTableName} OFF

|||

Thank you for the response Motley, but I don't want to have to create SQL statements; I want to do this directly in code using a datatable. Do you know how to do that?

|||

If you do not want to write sql query, then I suggest to go for answer1, turn off identity feature, import the data and turn it on.

|||

You would use the "SQL Statements" I gave above in a sqldataadapters's updatecommand.

|||

Hi,

Actually the code is also calling the SQL statements to do the update. So you will always need some SQL Statements for updating.

I suggest you use the way Girijesh has provided. Turn off the identity contraint off and import data.

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

Overflow Error: Visual Web Developer

I am using Visual Web Developer to design an interface to query an Access Database. Everything seems to work fine, but when I select the most intensive option I recieve the following error. As I said, everything seems to work fine, unless the user selects all the options I have made available. How do I resolve this issue without limiting the user?

Overflow

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: Overflow

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[OleDbException (0x80040e57): Overflow] System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) +177 System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) +194 System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) +56 System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) +105 System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) +91 System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +4 System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +139 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +140 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83 System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1659 System.Web.UI.WebControls.AccessDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +58 System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +13 System.Web.UI.WebControls.DataBoundControl.PerformSelect() +140 System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +68 System.Web.UI.WebControls.GridView.DataBind() +5 System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +61 System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +67 System.Web.UI.Control.EnsureChildControls() +97 System.Web.UI.Control.PreRenderRecursiveInternal() +50 System.Web.UI.Control.PreRenderRecursiveInternal() +171 System.Web.UI.Control.PreRenderRecursiveInternal() +171 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5729

I can post my code as well. It's three group bys, two sums divided by one another on a 30mb Access Database.

Thanks!

It seems like this would be better posted to an Access or ASP.NET forum.

Overflow error

Hi!

I created a VB6 program using MS Access DB and crystal reports 9. It works fine on WinXP, but I sometimes encounter an 'Overflow' error on Win98 SE.

For example, I have report A and B. When I start the program and immediately print report A, I encounter the error. But when I print report B first and then print report A, the errors gone. I think there's really no problem with the code...so what gives?

By the way, on some PCs with Win98, there's no problem too. Weird huh?

Thanks a lot!

This one's urgent...I'm too embarrassed already with the department using the program...I don't want them to think that I'm stupid or something hehe.Is there any link between those two reports?
Did you use any formula?|||Thanks for replying...

Regarding your question, the two reports are not linked...the data are also gathered from different tables, that is, I do all the necessary computations first and then load up a table for specific reports wherein data for the report will be gathered.