How can I inside a DFT set a System variable, for example "TaskName" to an own created User Variable?
The reason is that I need to use this variable later in the Control Flow.
Regards
Riccardo
Hi,
Can we create our own system global variable using @@ in Sql server 2000?
If yes, can you please post in the sql code with it?
Alos, is the system global variables are kwywords in Sql or they can be updated?
Thanks in Advance!!!
Shobana
For MS SQL so that I can do something like...
select * from myTable where #system_row_number = 5;
Hi ,
Is it possible to use a system variable in a 'Execute sql task'
or any other query component in SSIS?
Hi,
I have a custom task to execute a package. This task sets dynamically the values of the child package at runtime before execution of the child.
Everything works fine, as long as Im not trying to use an Object Variable in the child package, trying to fill with an ArrayList. Then, during package validation at runtime, the Properties of the custom task have NULL-Values. I don't know why all Properties got NULL or 0 (int), only adding an ArrayList-Property to the Task-Code.
The ArrayList gets initialized in the constructor of the Task. Values to add in execute(). But the process never gets to the execute()-Method, due to my check in validate() for null Values. So the List is initialized but empty.
Anyone an idea, maybe I'm doing something wrong at all. Anybody experiences with Object-Variables in Packages? A HowTo?
Thanks a lot
Thorsten
All,
Is there a system variable to indicate a package run successfully or failed?
I have a task in Event Handlers to log the process into a table, but I still need extra information like execution status, more likely,
if package successfully executed, I want to get a specific values from SQL server (like in DTS, execution_status = 4 when success), Any suggestions are welcome.
Thanks in Advance
Hello everybody,
I'm executing SSIS package, like a datareader source in a report in SSRS. Everything it's ok. But, I'm using
@[System::UserName] variable like a add column to save the user that ran the package.
But it's incredible, when the user execute the report, the system don't save the login that opened the browser, neither the user that run the ssis service or user for ssrs service. This is catching the user that opened session in the host where Sql server was installed.
I need catch the user that run the report, how can i do this?
Att,
JULIAN CASTIBLANCO P
MCTS SQL SERVER 2005
BOGOTA, COLOMBIA
Hi,
I've come up against a problem in my error handling.
I have a package-level OnError handler which is a SQL Execute task. The SQL execute task constructs a SqlStatementSource expression to insert the error details into a table. This works fine and I've tested it by introducing various errors in the package.
I have another SQL Execute task, which executes a SQL script from a file connection. This is a very long script (100s of lines) and works fine. I introduced a deliberate error into it and got the following errors:
Error: The variable System::ErrorDescription contains a string that exceeds the maximum allowed length of 4000 characters.
Error: Reading the variable "System::ErrorDescription" failed with error code 0xC0047100.
Error: The expression <expression> on property "SqlStatementSource" cannot be evaluated. Modify the expression to be valid.
The problem is obviously the length of the script, since SSIS attempts to copy all of it into the ErrorDescription, but this seems to occur before I can do anything about it. I've tried putting a SUBSTRING function around the ErrorDescription variable without success. If I introduce an error into a shorter script the error handling works fine.
Is this a bug in SSIS? Is there any sort of workround that anybody knows of?
thanks
- Jerzy
I am using an Execute T-SQL Task as a part of an OnError event Handler in my SSIS Package. When occurs an error, using the Expressions-feature, my Execute T-SQL task builds an Insert Statement to insert the @System::ErrorDescription into a table.
"
INSERT INTO [ErrorDB].[dbo].[ISErrors]
([EventType]
,[PackageName]
,[TaskName]
,[DateDone]
,[Status]
,[Host]
,[ErrorCode]
,[ErrorDescription]
,[Comments])
VALUES
( 'OnError'
, '"+ @[System::PackageName] + "'
, '"+ @[System::SourceName] + "'
,getdate()
,'Failed'
,'" + @[System::MachineName] + "'
, null
, '" + @[System::ErrorDescription] + "'
,null
)
"
When I run the task ( not the package, only the task) everything is ok ( since the ErrorDescription variable is empty)
But when an error occurs in my package, then the T-SQL task fails giving the following error
[Execute SQL Task] Error: Executing the query " INSERT INTO [LogDB].[dbo].[ISFullMaintenanceErrors] ([EventType] ,[PackageName] ,[TaskName] ,[DateDone] ,[Status] ,[Host] ,[ErrorCode] ,[ErrorDescription] ,[Comments]) VALUES ( 'OnError' , 'Package' , 'TrialTempEx' ,getdate() ,'Failed' ,'SCYLLA' , null , @[System::ErrorDescription] ,null ) " failed with the following error: "Must declare the scalar variable "@".". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I realized that the problem is that the @[System::ErrorDescription] contains quotes ( " ' ) and this is the reason that the insert statement fails. I tried the replace function but there was no solution
Any help would be appreciated
I've been getting the error below inconsistently when kicking SSIS packages off from a custom windows service. This same service works fine on our development and integration servers but not on our production servers. When I run the packages manually, it works great. Can someone provide any insight as to what is going on?
Error:
The variable "System::LocaleID" is already on the read list. A variable may only be added once to either the read lock list or the write lock list.
I have tired the soultions found in http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=211081&SiteID=1 and applied the hotfix http://support.microsoft.com/kb/918091
I'm running SQL Server 2005 on Windows Server 2003 R2 SP2.
Thanks in advance
Hellois there a variable that is available to me that contains the numberof rows contained in a dataset return from a database call?
have a class that runs a stored proc and returns a dataset/resultsetlooking to simply assign an integer this value if it is possible
i'm using (learning) vb.net and sql server
thanks in advance
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.
Dan
I'm new to SSIS, but have been programming in SQL and ASP.Net for several years. In Visual Studio 2005 Team Edition I've created an SSIS that imports data from a flat file into the database. The original process worked, but did not check the creation date of the import file. I've been asked to add logic that will check that date and verify that it's more recent than a value stored in the database before the import process executes.
Here are the task steps.
[Execute SQL Task] - Run a stored procedure that checks to see if the import is running. If so, stop execution. Otherwise, proceed to the next step.
[Execute SQL Task] - Log an entry to a table indicating that the import has started.
[Script Task] - Get the create date for the current flat file via the reference provided in the file connection manager. Assign that date to a global value (FileCreateDate) and pass it to the next step. This works.
[Execute SQL Task] - Compare this file date with the last file create date in the database. This is where the process breaks. This step depends on 2 variables defined at a global level. The first is FileCreateDate, which gets set in step 3. The second is a global variable named IsNewFile. That variable needs to be set in this step based on what the stored procedure this step calls finds out on the database. Precedence constraints direct behavior to the next proper node according to the TRUE/FALSE setting of IsNewFile.
If IsNewFile is FALSE, direct the process to a step that enters a log entry to a table and conclude execution of the SSIS.
If IsNewFile is TRUE, proceed with the import. There are 5 other subsequent steps that follow this decision, but since those work they are not relevant to this post.
Here is the stored procedure that Step 4 is calling. You can see that I experimented with using and not using the OUTPUT option. I really don't care if it returns the value as an OUTPUT or as a field in a recordset. All I care about is getting that value back from the stored procedure so this node in the decision tree can point the flow in the correct direction.
CREATE PROCEDURE [dbo].[p_CheckImportFileCreateDate]
/*
The SSIS package passes the FileCreateDate parameter to this procedure, which then compares that parameter with the date saved in tbl_ImportFileCreateDate.
If the date is newer (or if there is no date), it updates the field in that table and returns a TRUE IsNewFile bit value in a recordset.
Otherwise it returns a FALSE value in the IsNewFile column.
Example:
exec p_CheckImportFileCreateDate 'GL Account Import', '2/27/2008 9:24 AM', 0
*/
@ProcessName varchar(50)
, @FileCreateDate datetime
, @IsNewFile bit OUTPUT
AS
SET NOCOUNT ON
--DECLARE @IsNewFile bit
DECLARE @CreateDateInTable datetime
SELECT @CreateDateInTable = FileCreateDate FROM tbl_ImportFileCreateDate WHERE ProcessName = @ProcessName
IF EXISTS (SELECT ProcessName FROM tbl_ImportFileCreateDate WHERE ProcessName = @ProcessName)
BEGIN
-- The process exists in tbl_ImportFileCreateDate. Compare the create dates.
IF (@FileCreateDate > @CreateDateInTable)
BEGIN
-- This is a newer file date. Update the table and set @IsNewFile to TRUE.
UPDATE tbl_ImportFileCreateDate
SET FileCreateDate = @FileCreateDate
WHERE ProcessName = @ProcessName
SET @IsNewFile = 1
END
ELSE
BEGIN
-- The file date is the same or older.
SET @IsNewFile = 0
END
END
ELSE
BEGIN
-- This is a new process for tbl_ImportFileCreateDate. Add a record to that table and set @IsNewFile to TRUE.
INSERT INTO tbl_ImportFileCreateDate (ProcessName, FileCreateDate)
VALUES (@ProcessName, @FileCreateDate)
SET @IsNewFile = 1
END
SELECT @IsNewFile
The relevant Global Variables in the package are defined as follows:
Name : Scope : Date Type : Value
FileCreateDate : (Package Name) : DateType : 1/1/2000
IsNewFile : (Package Name) : Boolean : False
Setting the properties in the "Execute SQL Task Editor" has been the difficult part of this. Here are the settings.
General
Name = Compare Last File Create Date
Description = Compares the create date of the current file with a value in tbl_ImportFileCreateDate.
TimeOut = 0
CodePage = 1252
ResultSet = None
ConnectionType = OLE DB
Connection = MyServerDataBase
SQLSourceType = Direct input
IsQueryStoredProcedure = False
BypassPrepare = True
I tried several SQL statements, suspecting it's a syntax issue. All of these failed, but with different error messages. These are the 2 most recent attempts based on posts I was able to locate.
SQLStatement = exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
SQLStatement = exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
Parameter Mapping
Variable Name = User::FileCreateDate, Direction = Input, DataType = DATE, Parameter Name = 0, Parameter Size = -1
Variable Name = User::IsNewFile, Direction = Output, DataType = BYTE, Parameter Name = 1, Parameter Size = -1
Result Set is empty.
Expressions is empty.
When I run this in debug mode with this SQL statement ...
exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
... the following error message appears.
SSIS package "MyPackage.dtsx" starting.
Information: 0x4004300A at Import data from flat file to tbl_GLImport, DTS.Pipeline: Validation phase is beginning.
Error: 0xC002F210 at Compare Last File Create Date, Execute SQL Task: Executing the query "exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output" failed with the following error: "No value given for one or more required parameters.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Compare Last File Create Date
Warning: 0x80019002 at GLImport: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "MyPackage.dtsx" finished: Failure.
When the above is run tbl_ImportFileCreateDate does not get updated, so it's failing at some point when calling the procedure.
When I run this in debug mode with this SQL statement ...
exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
... the tbl_ImportFileCreateDate table gets updated. So I know that data piece is working, but then it fails with the following message.
SSIS package "MyPackage.dtsx" starting.
Information: 0x4004300A at Import data from flat file to tbl_GLImport, DTS.Pipeline: Validation phase is beginning.
Error: 0xC001F009 at GLImport: The type of the value being assigned to variable "User::IsNewFile" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
Error: 0xC002F210 at Compare Last File Create Date, Execute SQL Task: Executing the query "exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output" failed with the following error: "The type of the value being assigned to variable "User::IsNewFile" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Compare Last File Create Date
Warning: 0x80019002 at GLImport: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "MyPackage.dtsx" finished: Failure.
The IsNewFile global variable is scoped at the package level and has a Boolean data type, and the Output parameter in the stored procedure is defined as a Bit. So what gives?
The "Possible Failure Reasons" message is so generic that it's been useless to me. And I've been unable to find any examples online that explain how to do what I'm attempting. This would seem to be a very common task. My suspicion is that one or more of the settings in that Execute SQL Task node is bad. Or that there is some cryptic, undocumented reason that this is failing.
Thanks for your help.
I am in the middle of taking course 2073B €“ Programming a Microsoft SQL Server 2000 Database. I noticed that in Module9: Implementing User-Defined Functions exercise 2, page 25; step 2 is not returning the correct answer.
Select employeeid,name,title,mgremployeeid from dbo.fn_findreports(2)
It returns manager id for both 2 and 5 and I think it should just return the results only for manager id 2. The query results for step 1 is correct but not for step 2.
Somewhere in the code I think it should compare the inemployeeid with the previous inemployeeid, and then add a counter. If the two inemployeeid are not the same then reset the counter. Then maybe add an if statement or a case statement. Can you help with the logic? Thanks!
Here is the code of the function in the book:
/*
** fn_FindReports.sql
**
** This multi-statement table-valued user-defined
** function takes an EmplyeeID number as its parameter
** and provides information about all employees who
** report to that person.
*/
USE ClassNorthwind
GO
/*
** As a multi-statement table-valued user-defined
** function it starts with the function name,
** input parameter definition and defines the output
** table.
*/
CREATE FUNCTION fn_FindReports (@InEmployeeID char(5))
RETURNS @reports TABLE
(EmployeeID char(5) PRIMARY KEY,
Name nvarchar(40) NOT NULL,
Title nvarchar(30),
MgrEmployeeID int,
processed tinyint default 0)
-- Returns a result set that lists all the employees who
-- report to a given employee directly or indirectly
AS
BEGIN
DECLARE @RowsAdded int
-- Initialize @reports with direct reports of the given employee
INSERT @reports
SELECT EmployeeID, Name = FirstName + ' ' + LastName, Title, ReportsTo, 0
FROM EMPLOYEES
WHERE ReportsTo = @InEmployeeID
SET @RowsAdded = @@rowcount
-- While new employees were added in the previous iteration
WHILE @RowsAdded > 0
BEGIN
-- Mark all employee records whose direct reports are going to be
-- found in this iteration
UPDATE @reports
SET processed = 1
WHERE processed = 0
-- Insert employees who report to employees marked 1
INSERT @reports
SELECT e.EmployeeID, Name = FirstName + ' ' + LastName , e.Title, e.ReportsTo, 0
FROM employees e, @reports r
WHERE e.ReportsTo = r.EmployeeID
AND r.processed = 1
SET @RowsAdded = @@rowcount
-- Mark all employee records whose direct reports have been
-- found in this iteration
UPDATE @reports
SET processed = 2
WHERE processed = 1
END
RETURN -- Provides the value of @reports as the result
END
GO
Hi
View 7 Replies View Related Im trying to insert some values into a table but i get the following error:
System.Data.SqlClient.SqlException: Must declare the scalar variable "@username".
the strange thing is that the variable username is declared at the beginning and acording to my debugger the variable username has a value (that it gets from a textbox when a button is pressed). Here is my code so please feel free to point out what im doing wrong. Im a beginner to using asp.net.1 protected void ButtonSubmit_Click(object sender, EventArgs e)
2 {
3 string @username = TextBoxUsername.Text;
4 string @company = TextBoxCompany.Text;
5 string @password = TextBoxPassword.Text;
6 string @mail = TextBoxMail.Text;
7 string @adr = TextBoxAdr.Text;
8 string @phone = TextBoxPhone.Text;
9 string @contact = TextBoxContact.Text;
10 string myConnectionString;
11
12 myConnectionString = @"Data Source=.SQLEXPRESS;AttachDbFilename=C:UsersalhoDocumentsIntrapointWebApp_DataIntrapoint.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";
13
14 SqlConnection myConnection = new SqlConnection(myConnectionString);
15 string myInsertQuery = "INSERT INTO company (username, password, company, mail, adr, phone, contact) Values(@username, @password, @company, @mail, @adr, @phone, @contact)";
16 SqlCommand myCommand = new SqlCommand(myInsertQuery);
17 myCommand.Connection = myConnection;
18 myConnection.Open();
19 myCommand.ExecuteNonQuery();
20 myCommand.Connection.Close();
21
22 }
I keep getting this debug error, see my code below, I have gone thru it time and time agian and do not see where the problem is. I have checked and have no NULL values that I'm trying to write back.
~~~~~~~~~~~
Error:
System.NullReferenceException was unhandled by user code Message="Object variable or With block variable not set." Source="Microsoft.VisualBasic"
~~~~~~~~~~~~
My Code
Dim DBConn As SqlConnection
Dim DBAdd As New SqlCommand
Dim strConnect As String = ConfigurationManager.ConnectionStrings("ProtoCostConnectionString").ConnectionString
DBConn = New SqlConnection(strConnect)
DBAdd.CommandText = "INSERT INTO D12_MIS (" _
& "CSJ, EST_DATE, RECORD_LOCK_FLAG, EST_CREATE_BY_NAME, EST_REVIEW_BY_NAME, m2_1, m2_2_date, m2_3_date, m2_4_date, m2_5, m3_1a, m3_1b, m3_2a, m3_2b, m3_3a, m3_3b" _
& ") values (" _
& "'" & Replace(vbCSJ.Text, "'", "''") _
& "', " _
& "'" & Replace(tmp1Date, "'", "''") _
& "', " _
& "'" & Replace(tmpRecordLock, "'", "''") _
& "', " _
& "'" & Replace(CheckedCreator, "'", "''") _
& "', " _
& "'" & Replace(CheckedReviewer, "'", "''") _
& "', " _
& "'" & Replace(vb2_1, "'", "''") _
& "', " _
& "'" & Replace(tmp2Date, "'", "''") _
& "', " _
& "'" & Replace(tmp3Date, "'", "''") _
& "', " _
& "'" & Replace(tmp4Date, "'", "''") _
& "', " _
& "'" & Replace(vb2_5, "'", "''") _
& "', " _
& "'" & Replace(vb3_1a, "'", "''") _
& "', " _
& "'" & Replace(vb3_1b, "'", "''") _
& "', " _
& "'" & Replace(vb3_2a, "'", "''") _
& "', " _
& "'" & Replace(vb3_2b, "'", "''") _
& "', " _
& "'" & Replace(vb3_3a, "'", "''") _
& "', " _
& "'" & Replace(vb3_3b, "'", "''") _
& "')"
DBAdd.Connection = DBConn
DBAdd.Connection.Open()
DBAdd.ExecuteNonQuery()
DBAdd.Connection.Close()
CREATE TABLE #T(branchnumber VARCHAR(4000))
insert into #t(branchnumber) values (005)
insert into #t(branchnumber) values (090)
insert into #t(branchnumber) values (115)
insert into #t(branchnumber) values (210)
insert into #t(branchnumber) values (216)
[code]....
I have a parameter which should take multiple values into it and pass that to the code that i use. For, this i created a parameter and temporarily for testing i am passing some values into it.Using a dynamic SQL i am converting multiple values into multiple records as rows into another variable (called @QUERY). My question is, how to insert the values from variable into a table (table variable or temp table or CTE).OR Is there any way to parse the multiple values into a table. like if we pass multiple values into a parameter. those should go into a table as rows.
Hello
Is there a way that I can create a user defined variable in my database? I have a fairly large chunk of xml that I would like to store in the database that can be updated by clients ( in rare cases ). I could use other mechinisms to allow access to it, but all other application related information is stored nicely in this one place, I hate to vary for this single piece.
Thank you for your time and thoughts.
Patrick
Can I declare variable and assign a user defined table name in Sql server 6.5. if so how can I do that ,
Thanks
Ali
I have defined a variable varFileName and when I go to the Expression Builder in my Connection Manager Properties I can't see it and even when I type the name it says the variable is not existent or in a different scope. I have set the scope to be the very same package, that's for sure. Can anybody please tell me, as I can't solve this mistery, why the Builder does not see the var???
Darek
Hi,
I have a package that uses a variable string (in date format) to execute a package.
I want to modify that variable in such a way that if no value is set for that variable then use system date else use date in varaiable.
Can someone help me out in this.. I know it has something to do with the expression builder but donn know how to do it.
Cheers
Hello,
I run the DTS to copy data from Progress to SQL Server. How can match/convert the date variables to check.
The field p-date as format 'mm-dd-year' . It has the value of 10/09/2001.
The field s-date as varchar format. It has the value 2001-10-09.
How can use the where condition ( Select ...... WHERE p-date = s-date.)
Thanks
When trying to upsize an access database to sql server using the upsize wizard, I get the following error:
"Object variable or With block variable not set."
Any assistance is greatly appreciated.
Hello!
I'm using SQL Server 2000.
I have a variable which contains the name of another variable scoped in my stored procedure. I need to get the value of that other variable, namely:
DECLARE @operation VARCHAR(3)
DECLARE @parameterValue VARCHAR(50)
SELECT @operation='DIS'
CREATE table #myTable(value VARCHAR(20))
INSERT into #myTable values('@operation')
SELECT top 1 @parameterValue = value from #myTable
-- Now @parameterValue is assigned the string '@operation'
-- Here I need some way to retrieve the value of the @operation variable I declared before (in fact
-- another process writes into the table I retrieved the value from), in this case 'DIS'
DROP TABLE #myTable
I've tried several ways, but didn't succeed yet!
Please tell me there's a way to solve my problem!!!
Thank you very much in advance!
I have an SSIS package that creates a csv file based on a execute sql task.
The sql is incredibly simple select singlecolumn from table.
In the properties of the execute sql task I specify the result set as "full result set" when I run it I get the error that: Error:
The type of the value being assigned to variable "User::CSVoutput" differs from the current variable type.
Variables may not change type during execution. Variable types are strict, except for variables of type Object.
If I change the resultset to single row then I only get the first row from the DB (same if I choose none), If I choose XML then I get the error that the result is not xml. What resultset type do I need to choose in order to get all the rows into the CSV? The variable I am populating is of type string and is User::CSVoutput
This is part of the codes of a stored procedure:
-- Create tables variables
DECLARE @_tab_PRNumList TABLE(
PRNum udt_PRNum
)
However, it cannot be compiled coz of this error:
Error 2715: Column or parameter #1: Cannot find data type udt_PRNum.
This user-defined data type is created in a user database only.
I tried to created the user-defined type into BOTH tempdb and master but same error.
How can i resolove it?
Or it is impossible to use user-defined datatype for table variable?
In the past,
i use this method:
CREATE TABLE #PRNumList (
PRNum udt_PRNum
)
and cerate udt_PRNum in tempdb only could solve it.
But now i want to use table variable.
It seems NOT work for my situation.
Any idea?
Hi everyone,
How to define a Input variable in a Execute Sql Task??
I've defined a User::Inicio variable which contains 4 as value.
In Parameter Mappins it has been defined. Then, I've gone to General->Sql Statement and allocated the following SQL Statement:
UPDATE CARGAPROCESOS SET FECHAULTIMACARGA = [Inicio]
or
UPDATE CARGAPROCESOS SET FECHAULTIMACARGA = [User::Inicio]
Anyway, I'm stuck, both did not work
Thanks in advance for your comments
Hi !
I have one Excel source with ... say 2 columns, I have an SQL Server destination with 3 columns.
I want the third column to be populated with a single value stored in a User::Variable.
Can someone help !
Jacques...
I've created a user variable but when I'm going through its variable collections for that TaskHost appears all the system users but not mine.
Why??
?th.variables.count
26
When I have 27 including the mine.
Hello,
Does anybody could help me to fixe this query, inside SQL Task to handle OnError event.
The function DT_TEXT does not work with this query and I need to convert the Error Message so that the french caracter ' can be accepted inside the Insert Query.
Here is my query :
"INSERT INTO LOG(EventType, PackageName, TaskName, EventCode, EventDescription, PackageDuration, ContainerDuration,InsertCount, Host) VALUES ('OnError', ' " + @[System::PackageName] + "', '"+ @[System::SourceName] + "', 0, '"+ (DT_TEXT)@[System::ErrorDescription] + "', 0, 0, 0, 'Toto'
)"
The error message is capted inside the Insert Query, for example,
the value of the variable ErrorDescription is : 'Erreur dans l'insertion ' with a quote inside, and therfore the Insert Query can not be executed.
Thank you for helping me to fixe this query !
how to pass the column that has a numeric(12,0) data type to user variable in SSIS? what kind of variable data type should I choose?
if i select int64, it keep giving me an error:
Error: 0xC001F009 at Row by Row process: The type of the value being assigned to variable "User:bject_Key" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
there is no numeric data type in variable..
if you click the drop down box in variable data type, you can only see the below data type:
Int32
int64
Object
Sbyte
single
string
uint32
uint64
boolean
byte
char