In Execute SQL Task I am having a stmt like : (Select * from Emp where EmpNm=?) also I m passing variable value through Parameter Mapping.
Problem I am facing is that if I remove brakets from both end task is executed successfull but if I but those braket as they are task execution fails. and i need those brakets as it is inside a if statement.
Problem is with parameter passing ,even if i put some value as EmpNm="ABC " with brakets, it runs successfully
2: Dataflow Task: Datareader--Script componant--OLE DB Destination (SQL Server 2005--a single table --always around 600,000 rows)
How do I set up a transaction where if there is a failure the Truncate Table command will roll back---and the OLE Destination (A single SQL Server table) will be left the same as before the load started.
Another question with that volume of data --600,000 rows will a truncate table be pratical in a transaction
Hello to all, I have a stored procedure. If i give this command exce ShortestPath 3418, '4125', 5 in a script and excute it. It takes more 30 seconds time to be excuted. but i excute it with the same parameters direct in Microsoft SQL Server Management Studio , It takes only under 1 second time I don't know why? Maybe can somebody help me? thanks in million best Regards Pinsha My Procedure Codes are here:set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date,,> -- Description: <Description,,> -- ============================================= ALTER PROCEDURE [dbo].[ShortestPath] (@IDMember int, @IDOther varchar(1000),@Level int, @Path varchar(100) = null output ) AS BEGIN
if ( @Level = 1) begin select @Path = convert(varchar(100),IDMember) from wtcomValidRelationships where wtcomValidRelationships.[IDMember]= @IDMember and PATINDEX('%'+@IDOther+'%',(select RelationshipIDs from wtcomValidRelationships where IDMember = @IDMember) ) > 0 end if (@Level = 2) begin select top 1 @Path = convert(varchar(100),A.IDMember)+'-'+convert(varchar(100),B.IDMember) from wtcomValidRelationships as A, wtcomValidRelationships as B where A.IDMember = @IDMember and charindex(convert(varchar(100),B.IDMember),A.RelationshipIDS) > 0 and PATINDEX('%'+@IDOther+'%',B.RelationshipIDs) > 0 end if (@Level = 3) begin select top 1 @Path = convert(varchar(100),A.IDMember)+ '-'+convert(varchar(100),B.IDMember)+'-'+convert(varchar(100),C.IDMember) from wtcomValidRelationships as A, wtcomValidRelationships as B, wtcomValidRelationships as C where A.IDMember = @IDMember and charindex(convert(varchar(100),B.IDMember),A.RelationshipIDS) > 0 and charindex(convert(varchar(100),C.IDMember),B.RelationshipIDs) > 0 and PATINDEX('%'+@IDOther+'%',C.RelationshipIDs) > 0 end if ( @Level = 4) begin select top 1 @Path = convert(varchar(100),A.IDMember)+ '-'+convert(varchar(100),B.IDMember)+'-'+convert(varchar(100),C.IDMember)+'-'+convert(varchar(100),D.IDMember) from wtcomValidRelationships as A, wtcomValidRelationships as B, wtcomValidRelationships as C, wtcomValidRelationships as D where A.IDMember = @IDMember and charindex(convert(varchar(100),B.IDMember),A.RelationshipIDS) > 0 and charindex(convert(varchar(100),C.IDMember),B.RelationshipIDs) > 0 and charindex(convert(varchar(100),D.IDMember), C.RelationshipIDs) > 0 and PATINDEX('%'+@IDOther+'%',D.RelationshipIDs) > 0 end if (@Level = 5) begin select top 1 @Path = convert(varchar(100),A.IDMember)+ '-'+convert(varchar(100),B.IDMember)+'-'+convert(varchar(100),C.IDMember)+'-'+convert(varchar(100),D.IDMember)+'-'+convert(varchar(100),E.IDMember) from wtcomValidRelationships as A, wtcomValidRelationships as B, wtcomValidRelationships as C, wtcomValidRelationships as D, wtcomValidRelationships as E where A.IDMember = @IDMember and charindex(convert(varchar(100),B.IDMember),A.RelationshipIDS) > 0 and charindex(convert(varchar(100),C.IDMember),B.RelationshipIDs) > 0 and charindex(convert(varchar(100),D.IDMember), C.RelationshipIDs) > 0 and charindex(convert(varchar(100),E.IDMember),D.RelationshipIDs) > 0 and PATINDEX('%'+@IDOther+'%',E.RelationshipIDs) > 0 end if (@Level = 6) begin select top 1 @Path = '' from wtcomValidRelationships end END
I'm working on an SSIS package that uses a vb.net script to grab some XML from a webservice (I'd explain why I'm not using a web service task here, but I'd just get angry), and I wish to then assign the XML string to a package variable which then gets sent along to a DataFlow Task that contains an XML Source that points at said variable. when I copy the XML string into the variable value in the script, if do a quickwatch on the variable (as in Dts.Variable("MyXML").value) it looks as though the new value has been copied to the variable, but when I step out of that task and look at the package explorer the variable is its original value.
I think the problem is that the dataflow XML source has a lock on the variable and so the script task isn't affecting it. Does anyone have any experience with this kind of problem, or know a workaround?
I have a SQL Task that updates running totals on a record inserted using a Data Flow Task. The package runs without error, but the actual row does not calculate the running totals. I suspect that the inserted record is not committed until the package completes and the SQL Task is seeing the previous record as the current. Here is the code in the SQL Task:
DECLARE @DV INT; SET @DV = (SELECT MAX(DateValue) FROM tblTG); DECLARE @PV INT; SET @PV = @DV - 1;
I've not been successful in passing a SSIS global variable to a declared parameter, but is it possible to do this:
DECLARE @DV INT; SET @DV = ?; DECLARE @PV INT; SET @PV = @DV - 1;
I have almost 50 references to these parameters in the query so a substitution would be helpful.
I've got two Sql Tasks on my dtsx. The first one loads a value into "Proyecto" user variable and the second one executes a variable named "SegundoProceso" which contains from the beginning:
"select Fecha from LogsCargaExcel where Proyecto = " + @[User::Proyecto] +"" As SqlSourceType propety I have "Variable" and inside ResultSet or Parameter Mapping nodes there is nothing.
[Execute SQL Task] Error: Executing the query ""select Fecha from LogsCargaExcel where Proyecto = " + @[User::Proyecto] +""" failed with the following error: "Cannot use empty object or column names. Use a single space if necessary.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I have an Execute SQL Task (OLE DB Connnection Manager) with a SQL script in it. In this script I use several SQL variables (@my_variable). I would like to assign an IS variable ([User::My_Variable]) to one of my SQL variables on this script. Example:
DECLARE @my_variable int
, <several_others>
SET @my_variable = ?
<do_some_stuff>
Of course, I also set up the parameter mapping.
However, it seems this is not possible. Assigning a variable using a ? only seems to work in simple T-SQL statements.
I have several reasons for wanting to do this:
- the script uses several variables, several times. Not all SQL variables are assigned via IS variables.
- For reading and mainenance purposes, I prefer to pass the variable only once. Otherwise every time the script changes u need to keep track of all questionmarks and their order.
- Passing the variable once also makes it easier to design the script outside IS using Management Studio.
- This script only does preparation for the actual ETL, so I prefer to keep it in one task instead of taking it apart to several consecutive Execute SQL Tasks.
- I prefer to use the OLE DB connection manager because it's a de facto standard here.
Could anyone help me out with the following questions:
- Is the above possible?
- If so, how?
- If not, why not?
- If not, what would be the best way around this problem?
I have a schedule task with the following command: xp_cmdshell 'bcp DBName.TBLOwner.TBLName in h:datafile.txt /fh:formatfile.fmt /Sservername /Usa /Ppassword' The error message that I recieved is "Unable to open Host file. I can run the BCP command in Dos C prompt successfully, but cannot run this in the SQL Server Scheduler. H drive is net use to the Server and it is where my data file and format file located. Does anyone has any idea? I do appreciate for your help.
I am using MS sql 2000. I think I need to use SQL agent to run a job, but not sure how to call up the procedure! The procedure runs collectly when run from SQL query.
So everything was working great. Now I have a few problems. The little play button is no longer available for me to puch and when I try a right click execute package a get a "Unexpected internal error: Invalid state for operation. (Microsoft Visual Studio)". Does anyone know how to fix this? Also when I try and copy tasks from within my package I get a "An error occurred while objects were being copied. SSIS Designer could not serialize the SSIS runtime objects. (Microsoft Visual Studio)" followed by "Could not copy object 'package name' to the clipboard. (Microsoft.DataTransformationServices.Design)" and then "Invalid access to memory location. (Exception from HRESULT: 0x800703E6) (Microsoft.SqlServer.ManagedDTS)"
i create a function("changefilepermission") to execute a procedure ("grant_file_access") to change the file permission. i click the permission checkbox to change new permission, after that i will click the submit button to update change to the database. but it does not change to the database. this is my part of code. is anybody can give me a help?
thanks in advanced!!!!!!!!!
Private Sub btnsubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnSubmit.Click
Dim FileTitle As New String("") Dim FileName As New String("")
FileName = DGPermission.Items.Item(0).Cells(0).Text 'retrieve filename from datagrid FileTitle = DatabaseCommand(userid, "fa_title", filename) ' retrieve the filetitle from table
Dim permission As Char ' set the permission value If (CkRead.Checked) Then Permission = "r" ElseIf (CkWrite.Checked) Then Permission = "w" ElseIf (CkExecute.Checked) Then Permission = "o" End If
Try ' call the store procedure function by passing 4 value ChangeFileAccess(userid, FileName, FileTitle, Permission) ' Catch ex As Exception lblErrorMsg.Text = ex.ToString End Try End Sub
' execute store procedure function Public Sub ChangeFileAccess(ByVal userid As String, ByVal DiskFilename As String, ByVal Title As String, ByVal Access As Char) Dim UpdateCommand As SqlCommand UpdateCommand = New SqlCommand
With UpdateCommand .Connection = SqlConnection .CommandType = CommandType.StoredProcedure .CommandText = "Grant_File_Access" .Parameters.Add("@vu_id", SqlDbType.VarChar, 20).Value = userid .Parameters.Add("@DiskFilename", SqlDbType.VarChar, 64).Value = DiskFilename .Parameters.Add("@Title", SqlDbType.VarChar, 50).Value = Title .Parameters.Add("@Access", SqlDbType.Char, 1).Value = Access End With
Try UpdateCommand.Connection.Open() UpdateCommand.ExecuteReader() ' call the store procedure UpdateCommand.Connection.Close() Catch ex As Exception lblErrorMsg.Text = ex.ToString End Try End Sub
Hi All Could you guys help me i have small problem in running dbcc dbreindex.I could run this command from dbo,sysadmin and Object owner logins to rebuild the index but what i want to do is i want to run this command in a stored procedure. this is also fine if i run the stored procedure any one of above logins it executes when i grant exec permission on this procedure to public it will not execute in public group since public group is not a owner of this object.. How do i excute DBCC DBreindex from public group.Could you please help me.....................
Dear all, I am using C# , asp.net and sql server 2005. Let me explain the situation. I have written procedure to insert data into the table and return last inserted value by @@identity variable. Now my question is how do I execute this process so that I can Get last inserted variable values Please help thanks
I am trying to achieve a seemingly simple task of assigning datetime value to a user variable at the point my package starts running. How can I do this without using a SQL Script Task? Should I be using a script task for this or is there a simpler way to achieving the same thing?
What I'm trying to do is get an XML query from a database and use the XML to render an excel document. Can this be done using SSIS? Can I actually read a column from a database and store it in a variable and then create an excel spreadsheet with that variable in SSIS? Would this be done in the control flow using the XML task editor?
i need to know how many rows are in a certain table and store this value in a variable in order to process this variable later in the package. of course i can build a data flow task within a row count component but as far as i understand it's necessary to read all data from a data source in order to use the row count component. now the question is if it's possible to use a sql task in the control flow and put a select count(*) statement within the task and then write the result of this select statement into a variable. shouldn't that be much more faster than using a whole dataflow for this problem?
I would like to ask how I can use the variable as the input parameter of web services task.
In microsoft 's document, http://msdn2.microsoft.com/en-us/library/ms187617.aspx, It suggests the for the value, If the Variable check-boxes are selected, select the variables in the list to provide the inputs; otherwise, type the values to use in the inputs. but I cannot find such Variable check-boxes.
It just want to know how I can check the Variable check-boxes.
I am tring to figure out how to retrieve the value of a global variable from s SQL task, the value for the Global variable is set in a Active Script Task. Any help is greatly appreciated.
I want to set the value for a user defined variable with the script task with the intention of using that value as a condition in both of two Precedence Constraints for the purpose of determine which of two different direction the package will go. The problem is I don't know how to reference the User Defined variable in the Script of a Script task, nor how to alter its value.
I have a variable SYear with the data type Integer and the value 2005.
Connection type is OLE DB, and my sql statement is:
DECLARE @MyYear Int
SET @MyYear = ?
I have set the variable User::Syear as Input with the parameter name=0, there is no result set. I set BypassPrepare both True and False. However, this simple statement does not work. I keep having an error. If I put integer value directly into @MyYear, it is fine. What is wrong in here? Somebody help me!
I need to create a Table using the SQL Task and a Variable as the Table Name
I am getting an Error: "Syntax error or access violation". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
The SQL Statement that I am using is Create Table ? (ColumnA char(5) Null)
I created a ftp task that uses a variable for the remote path because the file I download changes names every based on the date. So I build up the entire path into a container variable and set the remote path to a variable. then I get an error that the filename does not begin with '/' when the filename that the error references is my variable... I cannot even debug/build my package cause of this error. I cannot find anything online or in BOL. any help would be appriciated. If you need more info to help let me know also.
My variable is set as a string,
exact error:
Error 1 Validation error. Get DailyFiles FTP Task: Variable "DailyFiles" doesn't start with "/". DAILY.dtsx 0 0
I need help with some C# code. I have build a SSIS package with an Execute Process Task. I need to send dynamic variables in to my C# program so I thought it was a good idea to use the StandardInputVariable.
I have looked thru several similar threads with errors like this, but have not found a resolution. I have a SQL Task that runs this query:
Code Snippet select NetRevenue = cast(sum(Base_Price + AL + MI + PO) as dec(10,2)) from Lancelot.DataWhse.dbo.GrossMarginDetail where tran_date_key <= ? + ' 23:59:59' and label_group <> 'X' and not (ar_ship_key in ('S999991','S998101''S998102')) and Document <> 'Cust Bal Debit Memo'
There is a parameter mapping of "User::LastSaturday" of type date. I also have a result set with a result set name of "0" with a variable name of "User::GrossMargin," which is defined as a double in the package. The task has a resultset type of Single row, and at the moment the answer that is returned is 66228637.10.
If I change the package variable to a type of string it of course works, but then I cannot do comparisons against it. I have step by step manually copied this from an existing DTSX, so I am baffled why this isn't working, and I'm ready to throw myself under a truck!
I also tried to bring it in as a string, then convert it in a script task, but I cannot figure out how to reference the input and output variables. I can't seem to find any relevant docs on how to do that...
If anyone has any ideas, I and my sanity would greatly appreciate it.
Using the 'for each' container, I am setting a user variable (type = string) which will hold the source file name and path of files to process and then move to an archive directory.
In the move file task (which is in the for each loop container) I have set the destination to a file connection, and the source to : Issourcepathvariable=True; sourcevariable=user::sourcefilename
BUT
when I run the package, I get the following error : Error at File System Task : "Source Path" is not valid on operation type "move file".
I have tried all sorts of things relating to expressions etc. but cannot get this to work. I am obviousley doing something fundementally wrong...can anyone help me?
I am building a simple package that looks at the results of a sql query, prior to running the next step.
Basically Outline and settings
SQL task
Result Set: Full result set
SQL Source: Direct Input
SQL Statement: Select Count (*) FROM X
Parameter Mapping
Variable Name: User::C
Direction: Return Value
Data Type: Numeric
Parameter Name: NewParameterName
Result Set = NewResultName: User::C
Precedence Constraint
Evaluation Operation: Expression and Constraint
Value: Success
Expression: @C > 0 ( I originally tried User::C and received an error message)
Execute SQL Server Agent Job
If step one results greater than 1, then execute the SQL agent job (SRS Report) Using this setup I receive an error message stating "
TITLE: Package Validation Error ------------------------------ Package Validation Error ------------------------------ ADDITIONAL INFORMATION: Error at Package: The expression "@C > 0" must evaluate to True or False. Change the expression to evaluate to a Boolean value. Error at Package: There was an error in the precedence constraint between "Execute SQL Task" and "Execute SQL Server Agent Job Task". (Microsoft.DataTransformationServices.VsIntegration)
Any thought or suggestions would be greatly appreciated.
I am trying to exectue SQL task as below by passing a parameter
If I try....
@v1 datetime
set @v1 = convert(datetime, ? ,103)
it fails with below error
" failed with the following error: "Syntax error or access violation". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
however the below code works well
delete from t1 where last_update = convert(datetime, ? ,103)
I need to use a variable as column in SQL statement in Execute SQL task of integration services. I am setting Parameter Setting to map variable use it in the query like; select ? , col1name from tablename. But its not working. Anybody having any idea; would be of great help.
