In a script task, I prompt the user for some parameters. I use the System::InteractiveMode variable to tell me if the package was launched via user interaction or from an automated process such as a file watcher. I display the prompts only if System::InteractiveMode is true. When I run from VS 2005 then I get the prompts, and when the package runs automated (dtexec) the prompts are not shown, so all is OK. However, when the package is run from dtexecui, I do not get the prompts because it seems the InteractiveMode value is false. Shouldn't the InteractiveMode value be true when the package is run from dtexecui since it is a user interface?
I am using Naive Bayes, Decision Trees, and Neural Net (SSAS 2005) to predict which of two states each record belongs to.
How can I enforce a different penalty for a false positive versus a false negative ? (I am assuming that in some sense the mining algorithms can then minimize the total penalty).
According the information provided by BIDS if it have false anulates any message such as msgbox or something like that from the ssis execution. is it true?
I have a DTEXECUI.exe related question. This util is installed on our QA server, but not our dev-server. Is this optional during installation? I can't ask our admin, he's on vacation.
Maybe important to note, is that the QA-server runs on 64 bit, where as the other servers are only 32-bit..
Is there an option(s) that will allow me to reduce the amount of output to the Package Execution Progress window? My package has a number of steps, but trying to keep track of progress is difficult at best given the deluge of messages this window displays.
I would prefer output similar to the SQL Import/Export wizard, which shows executing tasks on a single line with a status and a link to any error messages off to the side. Is this available somehow, or do I have to build it myself?
I have a very simple SSIS package that reads an environment variable, assigns it to a variable that is sent in an email, and also logs it to a text file. If I execute the package on the server via DTExecUI, it works fine as expected. However, if I schedule it as part of a job, I get the following error:
Code: 0x00000002 Source: Script Task Description: The script threw an exception: Object reference not set to an instance of an object. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 9:08:00 AM Finished: 9:08:01 AM Elapsed: 0.859 seconds. The package execution failed. The step failed.
The script is as follows:
Imports System
Imports System.Data
Imports System.IO
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
Public Sub Main()
Dim Path As String = "C:TestEnv.txt"
Dim Env As String = Environment.GetEnvironmentVariable("SSISConfig").ToString()
Dim Sw As StreamWriter
Dts.Variables("SSISConfig").Value = Env
If File.Exists(Path) = False Then
Sw = File.CreateText(Path)
Else
Sw = File.AppendText(Path)
End If
Try
Sw.WriteLine(Env)
Finally
Sw.Close()
End Try
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
I have puttered with this for a while now and can't figure out what the problem is. Any ideas?
I installed SP2 yesterday on our production server. I had some problems with that but it seemed to have installed so that the Datbase engine and the Integration Services is patch at least.
But after the update we can no longer bring up the "properties" of SSIS packages. If we connect to the integration service and choose run package the GUI just wait for a while and then nothing happens. We get an error in the event log thou ..
If we open a package that we have scheduled in the SQL Agent we get an error messages saying "No description found" .. really strange stuff is going on here ..?
Hi, I am trying to find a feature- that was there in DTS but I can't locate it in SSIS
Using DTSRUNUI, we could generate a encrypted command line for executing the pacakge. Now using DTEXECUI, I can get the command line but is there any way in which I can get it in the encryted format??
I've worked for awhile to get an SSIS package that connects to Oracle to succesfully run via DTExecUI from a 64 bit SQL Server. I completely understand that DTExecUI runs in 32 bit mode, but I need to initiate the package from a stored proc so I need to be able to utilize the 32 bit DTExec from either a xp_cmdshell call or from an sp_start_job (thanks to MatthewRouche for the suggestion). Both Ora92 and 10g were installed on the SQL Server.
Here's what I've tried unssuccessfully
1. SQL Job - Just calling the package from an SSIS step. I wouldn't expect this to run because it is 64 bit DTExec. 2. SQL Job - A job that calls the 32 bit version of dtexec from cmdexec. Here is 2 examples I have tried, the first is because I came across several postings saying that there is a problem with the "(x86)" for the Oracle driver.
\server_nameE$ProgramFilesLegacyMicrosoftSQLServerX8690DTSBinnDTExec.exe /DTS "PackageName" /SERVER "SERVER_NAME" /SET Package.Variables[PLT].Value;"8H" /CONFIGFILE "\SERVERConfigFile.dtsConfig" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW
\server_nameE$Program FilesMicrosoft SQL Server (x86)90DTSBinnDTExec.exe /DTS "PackageName" /SERVER "SERVER_NAME" /SET Package.Variables[PLT].Value;"8H" /CONFIGFILE "\SERVERConfigFile.dtsConfig" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW
Both of these give me the error below:
System.Exception: System.Data.OracleClient requires Oracle client software version 8.1.7 or greater.
I'm assuming that it is running in 32 bit mode because this was at the beginning of the job history log:
Executed as user: "XXX". ....3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved
3. I then tried to call the package from a batch file on a file server that had integration services installed on. Got the same error as above.
I'm obviously missing something if I can get it to work in DTExecUI but not DTExec 32-bit.
Any ideas? Below are some postings I have already visited several times:
Windows 64 bit and Oracle http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=358365&SiteID=1
Connecting to Oracle on 64-bit (x64) machine http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=159581&SiteID=1
One paragraph that stands out on this posting is:
Find the location of the application that is generating the error. Check the path to this location and see if it contains any parenthesis. If so, you must relocate the application to a directory without any parenthesis in the path. This didn€™t work for me, unless I was doing it wrong.
Error retrieving numeric data from Oracle 10g on 64 bit Itanium SSIS server http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1183296&SiteID=1
Connection Problems to Oracle Database http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1307460&SiteID=1
If this posting was complete, it looks like what I was looking for.
I want to input a parameter to my package. I have a variable, and I tried changing its value from DTExecUI.
I did everything as mentioned in http://blogs.conchango.com/jamiethomson/archive/2007/03/13/SSIS_3A00_-Property-Paths-syntax.aspx but I still get an error -
Could not set "Package.Variables[User::var_input_step_Name].Properties[Value]" value to "RAW".
The variable was originally read from a XML config file. I stored the package to MSDB. I then ran dtexecui, selected my msdb package, and in Set Values, I entered property path and value. I also tried removing the "", and I even deleted the XML configuration. Still the same error.
What am I doing wrong? Any Package Settings to make?
I have a table by the name of Info, in this table I have columns with the following names FirstName, LastName, PhoneNumber, CustomerID. In the columns I have the following data FirstName = Beth LastName = Riddle PhoneNumber = 864-555-1212 CustomerID = 4 The problem is with the following statement in the .vb file. The statement keeps produceing a false value, when the desired value is Beth. TextBox1.Text = SqlDataSource1.SelectCommand = "SELECT 'FirstName' From 'Info' Where(PhoneNumber = '864-555-1212')" Please help Thanks for the information
I have a stored procedure that has a boolean (bit) field passed to it (@emailcontract). If a user checks the check box on the webform I would like my where to return only the records where the email_contract column is true. If they don't check the check box I would like it to return records where email_contracts is true or false. What would my where cluse look lile for this?
when ever i drop a ole db source or destination control on data flow, upon clicking to edit it complains about -- defaultcodepage is set to false. When i check in the properties and set it to true; i get no error. What is it all about?
Hi, I need to check the existence of a row in a table. So i am using an if condition like set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go
ALTER PROCEDURE [dbo].[CheckNOAStages] @NOAID int, @StageCode nchar(20) AS BEGIN
SET NOCOUNT ON; Declare @Count int Select @Count =Count(NOAId) from NOAStages where NOAID=@NOAID and StageCode=@StageCode if (@Count>0) Begin return 1 end else begin return 0 end
END
The stored proc is executing but on the Data Access Layer I have this Boolean exists = Convert.ToBoolean (Execute.ExecuteReader(spCollection, dbSQL));
Some how I am always getting false . How can I fix this? Thanks
I am exporting an SQL Server table to a comma delimited text file. The values of Columns defined as Bit are exported as "True" or "False", but I would like that in the file appear 1 or 0 instead (with no surrounding double quotes). How can I acomplish that?
I tried using a Transformation and convert to single byte unsigned integer, but True values are exported as "255" and False values as "0". Why?
I have created a windows library control that accesses a local sql database
I tried the following strings for connecting
Dim connectionString As String = "Data Source=localhostSQLEXPRESS;Initial Catalog=TimeSheet;Trusted_Connection = true"
Dim connectionString As String = "Data Source=localhostSQLEXPRESS;Initial Catalog=TimeSheet;Integrated Security=SSPI"
I am not running the webpage in a virtual directory but in
C:Inetpubwwwrootusercontrol
and I have a simple index.html that tries to read from an sql db but throws
the error
System.Security.SecurityException: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed. at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet) at System.Security.PermissionSet.Demand() at System.Data.Common.DbConnectionOptions.DemandPermission() at System.Data.SqlClient.SqlConnection.PermissionDemand() at System.Data.SqlClient.SqlConnectionFactory.PermissionDemand(DbConnection outerConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection,
etc etc
The action that failed was: Demand The type of the first permission that failed was: System.Data.SqlClient.SqlClientPermission The Zone of the assembly that failed was: Trusted
I looked into the .net config utility but it says unrestricted and I tried adding it to the trusted internet zones in ie options security
I think that a windows form connecting to a sql database running in a webpage should be simple
Hi there, I've tried googling this (and looking at the many questions on the forum :) but I've not managed to find a decent tutorial / guide that describes a method for using checkboxs to insert true/false flags in a MS SQL db. The db field i'm setting has type set to "bit", is this correct? And secondly (its been a long day!) I just cant figure out the code to assign the bit 1 or 0 / true or false. This is what I've got so far but it's not working........Function InsertProduct(ByVal prod_code As String, ByVal prod_name As String, ByVal prod_desc As String, ByVal prod_size As String, ByVal prod_price As String, ByVal prod_category As String, ByVal aspnet As Boolean) As Integer Dim connectionString As String = "server='server'; user id='sa'; password='msde'; Database='dbLD'" Dim sqlConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(connectionString) Dim queryString As String = "INSERT INTO [tbl_LdAllProduct] ([prod_code], [prod_name], [prod_desc], [prod_size], [prod_price], [prod_category],[aspnet]) VALUES (@prod_code, @prod_name, @prod_desc, @prod_size, @prod_price, @prod_category, @aspnet)" Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection) sqlCommand.Parameters.Add("@prod_code", System.Data.SqlDbType.VarChar).Value = prod_code sqlCommand.Parameters.Add("@prod_name", System.Data.SqlDbType.VarChar).Value = prod_name sqlCommand.Parameters.Add("@prod_desc", System.Data.SqlDbType.VarChar).Value = prod_desc sqlCommand.Parameters.Add("@prod_size", System.Data.SqlDbType.VarChar).Value = prod_size sqlCommand.Parameters.Add("@prod_price", System.Data.SqlDbType.VarChar).Value = prod_price sqlCommand.Parameters.Add("@prod_category", System.Data.SqlDbType.VarChar).Value = prod_category If chkAspnet.Checked = True Then sqlCommand.Parameters.Add("@aspnet","1") Else sqlCommand.Parameters.Add("@aspnet","0") End If Dim rowsAffected As Integer = 0 sqlConnection.Open Try rowsAffected = sqlCommand.ExecuteNonQuery Finally sqlConnection.Close End Try Return rowsAffected End Function Sub SubmitBtn_Click(sender As Object, e As EventArgs) If Page.IsValid then InsertProduct(txtCode.Text, txtName.Text, txtDesc.Text, ddSize.SelectedItem.value, ddPrice.SelectedItem.value, ddCategory.SelectedItem.value, aspnet.value) Response.Redirect("ListAllProducts.aspx") End If End SubAny help would be appreciated or links to tutorials.ThanksBen
i am use this query to select the primary field colums in a table "select Column_Name as PrimaryKeycolumn from INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = 'tbl_Activity' and Constraint_Name like 'PK_%'"
but i want to select the fields which have a nullable=false for that i want know the information schema for null
I want to pass a single char to a query and use that to set two flags.
(
@ID int,
@AssessedID int,
@CompetencyID int,
@Status char,
@Creator int
)
AS
UPDATE P4_Assessment
SET P4_Cancelled_f = (@Status = ('C')),
P4_Competent_f = (@Status = ('P')),
P4_Date = getdate(),
P4_Creator = @Creator
WHERE P4_ID = @ID
I want to set the P4_Cancelled_f to true (if @Status = 'C') or false if it doesn't. This sort of syntax is fine in C#, but fails in a query. I also tried using IN (@Status IN ('C'))
Is this sort of logic possible in TSQL or should I use two parameters and set them in my code as 1 or 0?
I've been pulling my hair out for the last couple of weeks trying to make my SSIS package more robust. I'm running SQL 05 SP1 on Windows XP SP2. Right now when there's a failure, the error propagates all the way up and my package stops running. Clearly not very robust. I have a heartbeat portion that makes a connection to the database every couple of minutes. Sometimes it loses the connection in the middle of the night, probably due to some maintenance going on. I have created an event handler to log the error, sleep a couple of minutes and set Propagate=False. This works great. When the connection is down, the error is logged, once the connection comes back, the package just keeps going. I also have an FTP component that receives a file and then needs to send back a handshake file. I tried the same method here, but the event keeps propagating up. I created an event handler for the Send FTP task itself, so that in case the remote FTP server isn't responding, it will error out. The Event Handler code is called and I verified that the value in Propagate is indeed False. And yet the event keeps propagating up and kills the whole package. FailPackageonFailure is set to False, FaileParentonFailure is set to False also. Any idea what I'm doing wrong? Or is this a bug? Is there a work around? Any help would be greatly appreciated.
If I use IRowsetFastLoad::Commit(FALSE) not IRowsetFastLoad::CommitCommit(TRUE), are there any limitation of IRowsetFastLoad::InsertRow(..) before Commit(FALSE)?
Now, I'm using everlastingly InsertRow(..) and Commit(FALSE) method repeatedly. A few days after, it seems not to work.
I need a way to test if a convert function will work before I process it. if it fails, I want to intercept the error and return my own error to the front end
I have two tables I am trying to compare as I have created a new procedure to replace an old one and want to check if the new procedure produces similar results.
The problem is that when I run my compare I get false matches. Example:
CREATE TABLE #ABC (Acct VARCHAR(10), Que INT); INSERT INTO #ABC VALUES ('2310947',110), ('2310947',245);
[Code] ....
Which gives me two records when I really do not want any as the tables are identical.
Hello,I am setting up log shipping between two SQL servers. Everything in thesetup seems to work fine. The problem I am having is that the "LogShipping Alert Job - Backup" job is failing, event though the tlogbackup copy & restore process is working fine. When I look at thehistories of both the backup and copy/restore jobs, they are successfulall the way through, I can make changes to the primary db, and see themget shipped to the secondary server, so I know log shipping itself isworking. The only job that is failing is the backup alert job.I have the secondary (destination) server setup as the monitor server.I read that this issue may occur when the job can't access the logshipping tables within msdb. I have verified that the account underwhich log shipping runs has access to all those tables, and the dbabove. The account which runs log shipping and the jobs is the samedomain account under which MSSQLServer service runs.Here is the error message from the job history -Executed as user: ROTELLACAPITALsqlserver. The log shipping sourceODSQLK02.ROTELLACAPITAL.COM.dbTradePL_LS has not backed up for 1246minutes. [SQLSTATE 42000] (Error 14420). The step failed.Any ideas why this might be failing? Any ideas how to fix or work aroundit?Thanks,TGru*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
VB.NET 2003 / SQLS2KThe Stored Procedure returns records within Query Analyzer.But when the Stored Procedure is called by ADO.NET ~ it produced thefollowing error message.---------------------------Exception Message: Failed to enable constraints. One or more rowscontain values violating non-null, unique, or foreign-key constraints.------------------------------------------------------Exception Source: System.Data---------------------------If I click OK past the error messages I will get data filling thedatagrid. However not as I would like to see it.Even though it returns the proper data rows and includes all thecolumns I asked for, it also returns plenty of columns I didn't ask for(all the columns of the main table) and all those columns are filledwith "null"In addition each row header contains a red exclaimation mark whch whenhovered over reads;"Column 'cmEditedBy' does not allow DBNull.Values."An interesting thing about this column 'cmEditedBy' is that there isnoting wrong with it and all rows for that column contain data.I believe this error is a mistake! But it probably indicates some otherproblem. How should I track its cause?M O R E ...Below is the code in the data layer, the stored procedure, and the datareturned within query analyzer.\'DataAdapterFriend daView041CmptCyln As New SqlDataAdapter'SqlCommandPrivate daView041CmptCyln_CmdSel As New SqlCommand'Add the commanddaView041CmptCyln.SelectCommand = daView041CmptCyln_CmdSel'SelectWith daView041CmptCyln_CmdSel.CommandType = CommandType.StoredProcedure.CommandText = "usp_View_041Cmpt_ByJobCyln".Connection = sqlConnWith daView041CmptCyln_CmdSel.Parameters.Add(New SqlParameter("@RETURN_VALUE", SqlDbType.Int, _4, ParameterDirection.ReturnValue, False, CType(0,Byte), _CType(0, Byte), "", DataRowVersion.Current, Nothing))'Criteria.Add("@fkJob", SqlDbType.Text).Value = _"48c64a55-874d-40d0-addc-7245f5d9c118"'.Add("@fkJob", SqlDbType.Text).Value = f050View.jobIDEnd WithEnd With//\ALTER PROCEDURE usp_View_041Cmpt_ByJobCyln(@fkJob char(36))AS SET NOCOUNT ON;SELECTJobNumber,DeviceName,ComponentName,Description,Quan,Bore,Stroke,Rod,Seconds,CylPSI,PosA,PosB,PosC,PosD,PosE,HomeIsRet,RetIsRetrac,POChecks,Regulated,FlowControl,PortSize,LoadMassFROM tbl040cmptINNER JOIN tbl030Devi ON fkDevice = pkDeviceIdINNER JOIN tbl020Proc ON fkProcess = pkProcessIdINNER JOIN tbl010Job ON fkJob = pkjobIdINNER JOIN lkp202ComponentType ON fkComponenttype = pkComponentTypeIdINNER JOIN lkp201DeviceType ON fkDeviceType = pkDeviceTypeIdINNER JOIN lkp101PortSize on cmSmallint05 = pkPortSizeIdWHERE(fkJob = @fkJob)--fkJob = '48c64a55-874d-40d0-addc-7245f5d9c118'AND fkComponentType = 2GO//(note - columns are wrapped)\F1111Clip DriverCylinderClip Driver_2 - Top -Cylinder91.2502.250.8752.250NULL01101110011/8 NPTNULLF1111Punch MechCylinderPunch Mech_1 -Cylinder_222.1002.0001.0001.234NULL11000110011/8NPTNULLF1111ClipDriverCylinderBottom92.1002.0001.0001.000NULL11010110011/4NPTNULLF1111Punch MechCylinderPunch Mech_1 -Cylinder_122.1002.0001.0001.000NULL01000110011/8NPTNULLF1111DegateCylinderDegate 1 -Cylinder21.1882.500.8751.000NULL11000110011/8 NPTNULLF1111Clip DriverCylinderClip Driver 1 -Bottom11.1801.250.8751.000NULL00011110011/4 NPTNULL//
Configuration: MS SQL server 2005 SP2, and MS jdbc driver version: 1.1 The sendStringParameterAsUnicode has been set to false for performance reasons. However, when inserting unicode data, we would like to override the setting and send the data encoded in unicode, instead of defaulting the whole app to unicode=true and take a performance hit.
Any suggestions? We have tried the cast(? as nvarchar) function, but that did not help.
Sample code/output: String text = "u0143u0144"; sendStringParametersAsUnicode=false insert into unitable (_ntext) values (?) Inserting into databse: 143 144 (printed hex values) Read from database: 3f 3f (printed hex values)
I have a package that uses a complex expression for the MessageSource property in a Send Mail task. I receive the email that is composed based on the expression and it is formatted properly. However, the application also throws an exception complaining that the width of the expression may be too wide.
This is the error message (with the long expression represented by "...") from the exception:
Error Code: -2147192616
Error Description: The result string for expression " ... " may be truncated if it exceeds the maximum length of 4000 characters. The expression could have a result value that exceeds the maximum size of a DT_WSTR.
I tried wrapping the expression in SUBSTRING(expression, 1, 4000) to guarantee that the resolved expression could not cause an exception but that didn't help. Numbers lower than 4000 also do not solve the problem.
