Propery expressions: Values not updated

I see some strange behavior when running a package using the SQL Server Agent. The package I run calls a number of child packages, in which I use property expressions to set - among other things - the path to text files I read into a database.

The property expressions are in the form: @.[User::ThePath] + "\\TheFile.txt". The variable ThePath is read from an XML configuration file at runtime from the "master" package and passed on to the child packages using a parent package variable configuration.

My problem is this: When the package is executed by the SQL Server Agent, the properties for the path of the text files are not updated, and the package fails, as it cannot locate the files. When using DTExecUI to execute the same package, everything runs fine.

Could it be a permissions issue? I don't think so, as the SQL Server Agent runs under the same account as I am logged in when executing with DTExecUI.

Anyone?

It does sound a bit like permissions.
So are you referring to the Account the Agent service runs under OR the account the specific Agent Job Step is executing AS, which are 2 different things.

>>>Could it be a permissions issue? I don't think so, as the SQL Server Agent runs under the same account as I am logged in when executing with DTExecUI.

http://support.microsoft.com/?kbid=918760

|||The SQL Agent is running under a local administrator account, which is the same I use when I log in and run the package using DTExecUI. In the scheduled job, the package is "Run as" the "SQL Agent Service Account", which I think should mean that it is executing in the same security context as when I run it manually?!|||

This one has been bugging me big time!

Now, it seems that I might have found a workaround. If I store the main/parent package (the one executed in the Agent job) in MSDB and let the job execute it from here, everything seems to work fine and the property expressions are properly updated at runtime - even though those property expressions are used in the child packages. Previously I had all the packages stored in SSIS Package Store (File System), and that is also where I still keep the child packages.

Can anyone explain this behavior?

|||Can anyone give any explanation for this behavior?|||Now, it gets even more strange. When the job runs on a schedule, it fails, but when I start it manually by clicking "Start job at step..." it runs fine.

ProperCase() script to use in the dataflow

Does anyone have a nice script that can "Propercase" the values of a column in the dataflow. I was thinking of a Transform Script Task, with a Input and Output Column, which can be transformed.

With Propercase i think:

STEVEN SPIELBERG becomming Steven Spielberg

Maybe also with some exceptions soo that "DELIVERY EU" stays "Delivery EU"

you can use the following within a script to convert to proper case.

Row.Name = StrConv(Row.Name, vbProperCase)

You could setup some select case statements for exceptions...

Frank

sql

proper way to set parameters programatically?

What is the proper way to set a parameter collection's values in code? (C#
or VB.NET) should you get the parameter list using getReportParameters from
the SOAP web service then setting the items in the returned collection then
passing the collection back through setreportparameters..... or can you
just pass a list of programatically created values and names though the
setReportParaemters with out having to get the initial list (say if you
already know the parameters required) thanks!See the examples using parameters here:
http://msdn2.microsoft.com/en-us/library/aa258532(sql.80).aspx
This applies to the render method, which I'm not sure if you'll use or
not, but I think this should give you an idea of what to do.
Smokey Grindle wrote:
> What is the proper way to set a parameter collection's values in code? (C#
> or VB.NET) should you get the parameter list using getReportParameters from
> the SOAP web service then setting the items in the returned collection then
> passing the collection back through setreportparameters..... or can you
> just pass a list of programatically created values and names though the
> setReportParaemters with out having to get the initial list (say if you
> already know the parameters required) thanks!

Prooblem inserting into a table

Hi! I am currently trying to insert some values into a table. I have a very strange problem. I get the error message that i am trying to insert a null value into one of my colums, the problem is that when I write the value to a label control before I runs the insert method on my SqlDataSource control the value is not NULL. If i push the insert button on my webpage once again everything is ok.

What am I doing wrong? Anyone seen this kind of problem before?

regards,

Lumsen

Hi,

You can use the SQL Profiler to see which sql statement runs on the database after you click the button. If the error message is an sql error then you can see the statement by using the profiler.

Eralper

|||Can't be sure without more details. It could depends on what data control you're using, if any, and posting your code may help people to get better idea.|||without more info, its hard to tell. One thing you can do is add a watch to that variable and see if it is NULL at the wrong time.|||

Thanks for trying to help me out!

Here is the code from my asp page:

<%@. Page Language="VB" AutoEventWireup="false" CodeFile="newassesment.aspx.vb" Inherits="newassesment" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
Add a new assesment<br />
<br />
<asp:Label ID="Label1" runat="server" Text="assesment id"></asp:Label>
<asp:TextBox ID="text_assesment_id" runat="server"></asp:TextBox><br />
<br />
<asp:Label ID="Label2" runat="server" Text="primary language"></asp:Label>
<asp:DropDownList ID="prim_lang_DropDownList" runat="server" DataSourceID="SqlDataSource2"
DataTextField="language_id" DataValueField="language_id">
</asp:DropDownList>
<br />
<br />
<asp:Label ID="Label3" runat="server" Text="# of answer alternatives"></asp:Label>
<asp:TextBox ID="text_ans_alt" runat="server"></asp:TextBox><br />
<br />
<asp:Label ID="Label4" runat="server" Text="Dont know allowed"></asp:Label>
<asp:CheckBox ID="checkBox_dont_know_allowed" runat="server" /><br />
<br />
<asp:Label ID="Label5" runat="server" Text="Light level"></asp:Label>
<asp:CheckBox ID="checkBox_light_level" runat="server" /><br />
<br />
<asp:Label ID="Label6" runat="server" Text="Medium level"></asp:Label>
<asp:CheckBox ID="checkBox_medium_level" runat="server" /><br />
<br />
<asp:Label ID="Label7" runat="server" Text="Extensive level"></asp:Label>
<asp:CheckBox ID="checkBox_extensive_level" runat="server" /><br />
<br />
<asp:Button ID="button_submit" runat="server" Text="Submit" />
<asp:Label ID="label_error" runat="server" ForeColor="Red"></asp:Label>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:AssesmentConnectionString2 %>"
DeleteCommand="DELETE FROM [assesment_definition] WHERE [assesment_id] = @.assesment_id"
InsertCommand="INSERT INTO [assesment_definition] ([assesment_id], [primary_language_id], [number_of_answer_alternatives], [dont_know_answer_accepted], [light_level], [medium_level], [extensive_level]) VALUES (@.assesment_id, @.primary_language_id, @.number_of_answer_alternatives, @.dont_know_answer_accepted, @.light_level, @.medium_level, @.extensive_level)"
SelectCommand="SELECT * FROM [assesment_definition]" UpdateCommand="UPDATE [assesment_definition] SET [primary_language_id] = @.primary_language_id, [number_of_answer_alternatives] = @.number_of_answer_alternatives, [dont_know_answer_accepted] = @.dont_know_answer_accepted, [light_level] = @.light_level, [medium_level] = @.medium_level, [extensive_level] = @.extensive_level WHERE [assesment_id] = @.assesment_id">
<DeleteParameters>
<asp:Parameter Name="assesment_id" Type="String" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="primary_language_id" Type="String" />
<asp:Parameter Name="number_of_answer_alternatives" Type="Int32" />
<asp:Parameter Name="dont_know_answer_accepted" Type="Boolean" />
<asp:Parameter Name="light_level" Type="Boolean" />
<asp:Parameter Name="medium_level" Type="Boolean" />
<asp:Parameter Name="extensive_level" Type="Boolean" />
<asp:Parameter Name="assesment_id" Type="String" />
</UpdateParameters>
<InsertParameters>
<asp:FormParameter FormField="text_assesment_id" Name="assesment_id" Type="String" />
<asp:FormParameter FormField="prim_lang_DropDownList" Name="primary_language_id"
Type="String" />
<asp:FormParameter FormField="text_ans_alt" Name="number_of_answer_alternatives"
Type="Int32" />
<asp:FormParameter FormField="text_dontknowallowed" Name="dont_know_answer_accepted"
Type="Boolean" DefaultValue="" />
<asp:FormParameter FormField="text_lightlevel" Name="light_level" Type="Boolean" DefaultValue="false" />
<asp:FormParameter FormField="text_mediumlevel" Name="medium_level" Type="Boolean" DefaultValue="false" />
<asp:FormParameter FormField="text_extensivelevel" Name="extensive_level" Type="Boolean" DefaultValue="false" />
</InsertParameters>
</asp:SqlDataSource>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:AssesmentConnectionString2 %>"
SelectCommand="SELECT [language_id] FROM [language]"></asp:SqlDataSource>
<br />
<br />
<br />
<asp:TextBox ID="text_dontknowallowed" runat="server" CausesValidation="True"></asp:TextBox><br />
<asp:TextBox ID="text_lightlevel" runat="server"></asp:TextBox><br />
<asp:TextBox ID="text_mediumlevel" runat="server"></asp:TextBox><br />
<asp:TextBox ID="text_extensivelevel" runat="server"></asp:TextBox><br />
<br />
<br />
<asp:Label ID="testLabel" runat="server" ForeColor="Blue"></asp:Label></div>
</form>
</body>
</html>

And the code when the submit button is clicked:

Partial Class newassesment
Inherits System.Web.UI.Page

Protected Sub button_submit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles button_submit.Click

text_dontknowallowed.Text = checkBox_dont_know_allowed.Checked
text_lightlevel.Text = checkBox_light_level.Checked
text_mediumlevel.Text = checkBox_medium_level.Checked
text_extensivelevel.Text = checkBox_extensive_level.Checked

testLabel.Text = SqlDataSource1.InsertCommand()

Try

Dim recAff As String

recAff = SqlDataSource1.Insert()

label_error.Text = (recAff & " record inserted successfully!")

Catch SQLExp As System.Data.SqlClient.SqlException

label_error.Text = "An SQL Server Error Occurred: " & SQLExp.Message()

Catch except As Exception

label_error.Text = except.ToString()

End Try

End Sub

Protected Sub checkBox_dont_know_allowed_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles checkBox_dont_know_allowed.CheckedChanged

testLabel.Text = "HALLO"

End Sub
End Class

Do this help?

Thanks

Lumsen

|||

Why aren't you using one of the data controls (DetailsView or FormView) along with your SqlDataSource? They were meant to be used together. I suppose you could somehow make your code work properly, but it may prove difficult to get everything right. Also, FormView/DetailsView allow paging, edit, delete, insert to take place automatically and does data binding for you. By not using these controls, you're choosing to manually handle everything (sort of reinventing the wheel). Finally, I'm not sure if anyone will be willing to help you implement something so out of the ordinary.

So, my advice is for you to go with FormView control for Insert, Edit, Delete of your data. It'll make your project far easier.

|||You shouldn't be using formparameters to pull from ASP.NET controls to start. Either make them Parameters, and set them manually, or make them controlparameters to pull from an ASP.NET control, or change all your textboxes from ASP.NET controls to form controls "<INPUT TYPE=...>" and use formparameters.|||Thanks for guiding me! Works a lot better with the formview control!

Just one problem remains.. I want to make one of the fields in the formview a dropdownlist, where the items are selected from another table than the table associated with the formview. Is that possible?

Thanks!

Lumsen|||Using a DropDownList control within a FormView is possible. That topic has been covered many times before, so rather than describing the whole process again, try searching the forum for the keywords "formview dropdownlist".

Prompt for variable values in a SQL script

I am writing a SQL script and it needs some parameters to be entered by the user. Users will run this script from the SQL Query Analyzer. I trying to see if there is a way the script will prompt the user to enter values for the parameters. This is possible in Oracle. Is there any equivalent in SQL Server?

Thanks in advance for your timeNo, you can't prompt for variables in the SQL script.

You can create your script as a stored procedure with parameters, or you can define and set your variables at the top of the script so the user can easily modify them (good programming practice anyway!).

That said...
bad programming practice is letting users run scripts from Query Analyzer! I hope the "users" have knowledge of databases, and I hope their server and database permissions are well defined, or they could inadvertently (or even maliciously) mess things up.

Why not build a simple interface, such as an Access Data Project, that calls the procedure after prompting the User for parameters?

blindman|||I realize that it is a bad and dangerous practice to let the users access the database from the query analyzer. We are redesigning the system from the scratch but till then, we have to support the existing system. Prevoius DBA let this hole into the system and we have to live with this till we finish our re-design. I was almost positive what I was looking for is not possible but I just wanted to make sure. Thanks a lot for your time blindman.

Originally posted by blindman
No, you can't prompt for variables in the SQL script.

You can create your script as a stored procedure with parameters, or you can define and set your variables at the top of the script so the user can easily modify them (good programming practice anyway!).

That said...
bad programming practice is letting users run scripts from Query Analyzer! I hope the "users" have knowledge of databases, and I hope their server and database permissions are well defined, or they could inadvertently (or even maliciously) mess things up.

Why not build a simple interface, such as an Access Data Project, that calls the procedure after prompting the User for parameters?

blindman

Prompt

In oracle we use Prompt like in the run time it prompt to give input like
insert into test (id, name) values (&id&, &name&)
How can I fulfill this in SqlServer ?
Thanks
Noor
Hi
You can not do this in SQL Server directly. It would require a front end
application and pass the values as parameters to the query/stored procedure.
John
"Noor" <noor@.ngsol.com> wrote in message
news:eudzDz5TEHA.2448@.TK2MSFTNGP09.phx.gbl...
> In oracle we use Prompt like in the run time it prompt to give input like
> insert into test (id, name) values (&id&, &name&)
> How can I fulfill this in SqlServer ?
> Thanks
> Noor
>
>

Programmatically set position of report items

Hi All. I have a report with various items (lines, textboxes) that may or may not be visible, depending upon the values of other report items.

In one list, there is a horizontal line of textboxes and lines. If a line in the middle of it all is set to hidden=true, then all the controls to the right of it are shifted to the left. If it is set to hidden=false then they are shifted back to the right.

Is there a way to fix the position of controls (something like ReportItems!txtTextbox.left=0.444 etc.)? According to BOL, the only property in the ReportItems collection is Value.

Any help is appreciated. D. LewisTry grouping items inside of a container. A rectangle with no visible border can be very useful to keep other items from shifting on the page.

programatically get values from sqldatasource

Hi All,

I am having problems getting values out of an sqldatasource.

I have 2 on the page, i am using one to insert into a table and the other to get a couple of values from another table for the insert.

the datasource i am trying to get the values from does a select with a querystring filter i need to grab a couple of fields out of it trhen do the insert on the other datasiource with a button click.

The insert is fine, i just don't know how to get the values out of the first 'select source'

Any pointers or suggestions most appreciated.

Cheers

I have figured this out.

I am doing the whole lot in the page load event handler and assigning the values to sessions then inserting the sesion variables in the iteminserting event handler for the formview

ProtectedSub Page_Load(ByVal senderAsObject,ByVal eAs System.EventArgs)HandlesMe.Load

Dim dvAs DataView = _

CType(get_LocData.Select(DataSourceSelectArguments.Empty), DataView)

ForEach drAs DataRowIn dv.Table.Rows

Session("LocMan") = dr("LocManID").ToString()

Session("LocID") = dr("ID").ToString()

Session("Status") ="New"

Next

EndSub

ProtectedSub FormView1_ItemInserting(ByVal SenderAsObject,ByVal eAs FormViewInsertEventArgs)Handles FormView1.ItemInserting

e.Values("LocMan") = (Session("LocMan"))

e.Values("Status") ="new"

e.Values("LocationID") = (Session("LocID"))

EndSub

|||

Don't use an SqlDataSource for that.

using (SqlConnection conn = new SqlConnection("your connectionstring"))
{
SqlCommand cmd = new SqlCommand("SELECT ... FROM ... WHERE someColumn = @.someParameter", conn);
cmd.Parameters.AddWithValue("@.someParameter", Request.QueryString["yaddayadda"]);
conn.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
col1=reader["col1"]; //Here you need to cast the value to the proper type, or use e.g reader.GetString(0) if it's a string
col2=reader["col2"];
}
}
}

But if you're selecting values just to insert them, you should be able to do it without retrieving the values in the first place. Please give us more details.