Variable Indirection: Choosing Variable At Runtime

Oct 18, 2007

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!

View 7 Replies


ADVERTISEMENT

Set Variable Value At Runtime

May 20, 2007

I'm trying to create a custom data flow transformation, I need to set the value of a user variable which will be used by a downstream component(for example: a data flow destination) at runtime, is this possible? If possible, in which method should I do that? Thanks.

View 4 Replies View Related

Changing Variable Value At Runtime

Oct 19, 2007



Hi,
I am relatively new to SSIS.
Please advice me on how to change the value of a variable during runtime.
i.e. user should be able to key in the value.

Regards,
Jiju

View 3 Replies View Related

Assigning Value To The Variable At Runtime.

Mar 20, 2008

Hi,

1 20031012121212 200 (recordtype, CreationDateTime(YYYYMMDDHHMISS), Rec_Count) -- Header records
2 ABCD, XYZ, 9999, 999999999, 1234 ---- Detailed Record
2 ABCD, XYZ, 9999, 999999999, 1234 ---- Detailed Record

For the above given sample data I am having two outputs at Condition Split (based on the recordtype). I want to store the 1st record datetime value into a variable and then I want to use that variable value into 2nd and 3rd row.

Basically, detailed records would be stored into the database not the header record. Is there any way I can use the variable while doing processing for 2nd and 3rd records.

Please suggest me.

Regards,

View 10 Replies View Related

Displaying Value Of A Variable During Runtime

Feb 28, 2007

Greetings all,

Apologies if this question has been asked in the past but how I display the valuw of a variable during runtime?

Thanks for your help in advance.

View 4 Replies View Related

Show Variable Values At Runtime

Nov 14, 2007

Is there a quick and easy way, other than scripting a MsgBox(), to show the value of a package variable while the package is running?

View 3 Replies View Related

Variable Value: How Do I Set It From Value In Flat File At Runtime

Jan 10, 2007

I have a number of DTS packages I am trying to convert and am totally new to Integration Services.

I have a flat file that I reference to get a date that I use in some SQL statements in the package. It seems like the best way to do this would be to create a variable to hold the date and set it at run time from the flat file. I've been searching the documentation and the forum and can't figure out how to do this. This is so basic that I feel like an idiot putting it on the forum but I need some help. This whole Integration Services thing is a big shift from DTS (which I eventually became pretty good at) Now I'm back to ground zero.

Any help would be appreciated. If you can direct me to any basic training resources that would be cool too.

View 7 Replies View Related

Changing XML Source To A Variable At Runtime

Apr 12, 2007

Hiya guys,



The question:

Can I design data flows with an XML Source pointing to file system xml files, and but run them against variables? I can see the properties at design time (Accessmode=0,1,2; XMLDataVar="User::XMLData), but the XMLSource object doesn't appear to have expressions enabled to change this at runtime, nor do these properties seem to be exposed to configurations.



The scenario:

I have a package that reads through (potentially thousands) of XML files, and having identified the embedded message "type", transforms them via XSLT to an easier XML format (XML Datasource can't cope well with multiple namespaces etc). Then send this the new file off to a type specific dataflow task to send contents to database.



For performance (and other) reasons I'd rather XSLT to a single shared variable and use this variable as the XML source in the data flows (Rather than writing out to the filesystem [via xslt] and immediately reading it back in each time [via datasource]).

But designing the data flow task using a variable as xml source is frustrating for a bunch of reasons - not the least being:

(a) The variable needs to be populated with sample xml data at design time. But this could only ever match one dataflow at a time (fine at run time, but painful at designtime - leading to validation errors popping up all around the place.)

Yes I could have a seperate variable for each xmlsource - but that just makes things more complicated up front and also leaves me with 20+ large, type specific, xml variables floating around at runtime of which only one is ever being used per import - which just doesn't feel right to me.



(b) populating the variable with sample xml at design time is painful due to it being a string - and not accepting hard returns etc that are in text files, design time changes etc)



Using a file XML source at design time is infinitely easier, when I get a design time change - I can just amend the XSLT and run the appropriate task to produce a sample XML file to design against.



Any ideas?

Ta.

Gus.

View 2 Replies View Related

Urgent: How To Get Source Data With Runtime Variable

Feb 15, 2006

All,
I need to pass a variable to get source data from Oracle on OLE DB source. The variable is defined by an Execute SQL task (previous step) on the control flow and it is a package level variable.
In the data flow, OLE DB source using €˜SQL command from variable€™. The variable sql command works when it just: Select *from table
But I need the variable SQL command like this:
Select * from table where last_modified_date > ? - - variable1
the variable1 is defined by anther Execute SQL task at runtime

Can someone give some light on this?

Many thanks

View 1 Replies View Related

SSIS Script Task Alters Package Variable, But Variable Does Not Change.

Oct 25, 2006

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?

View 1 Replies View Related

Passing A SSIS Global Variable To A Declared Variable In A Query In SQL Task

Mar 6, 2008

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

View 4 Replies View Related

SSIS: Problem Mapping Global Variables To Stored Procedure. Can't Pass One Variable To Sp And Return Another Variable From Sp.

Feb 27, 2008

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.

View 5 Replies View Related

Compare The Value Of A Variable With Previous Variable From A Function ,reset The Counter When Val Changes

Oct 15, 2007

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

View 1 Replies View Related

Scripting: Dumb Q.. How Can I Store A DTS Variable Inside A Script Variable?

Nov 1, 2005

Hi

View 7 Replies View Related

Debug Error - Object Variable Or With Block Variable Not Set -

Feb 15, 2006

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()

View 2 Replies View Related

Transact SQL :: Insert Values From Variable Into Table Variable

Nov 4, 2015

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.

View 6 Replies View Related

How To Match The Date Variable And Character Variable

Oct 9, 2001

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

View 1 Replies View Related

Object Variable Or With Block Variable Not Set (was Frank)

Mar 7, 2005

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.

View 3 Replies View Related

Integration Services :: Variable Value Does Not Fit Variable Type

May 27, 2015

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

View 8 Replies View Related

Object Variable Or With Block Variable Not Set

Aug 17, 2004

While I was processing the cubes, error "Object Variable Or With Block Variable Not Set" prompt out,
what does it mean ?

Please help !!!

View 1 Replies View Related

Foreach From Variable Using 2 Dim Array Variable

May 15, 2007



Is there any way to use a 2 dimensional array of strings as the Variable Enumerator for the "Foreach From Variable Enumerator". I am trying to copy a collection of files from folder A to folder B. In a script, I would populate, let us say, an array of (2,10), for 10 files, with one column representing the source file and other column representing the target column task. Then I would like to set this string array variable as the "Variable Enumerator" for the "Foreach From Variable Enumerator" and use file system tasks in the foreach loop to perform the tasks. The problem is that the "Foreach From Variable Enumerator" does not let me choose an index, but passes only one index 0, so, I will only be able to pass just one column. How do I let the foreach enumerator let me choose an index. The other foreach enumerators, foreach item and ADO give me the option to select index. I would like the same functionality in the foreach variable.

Note: I cannot use the "For Each File" enumerator, since the files are to be selected by a script only.
Thanks for the help.

View 3 Replies View Related

Cannot Set A Variable From A Select Statement That Contains A Variable??? Help Please

Oct 4, 2006

I am trying to set a vaiable from a select statement

DECLARE @VALUE_KEEP NVARCHAR(120),

@COLUMN_NAME NVARCHAR(120)



SET @COLUMN_NAME = (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'CONTACTS' AND COLUMN_NAME = 'FIRSTNAME')



SET @VALUE_KEEP = (SELECT @COLUMN_NAME FROM CONTACTS WHERE CONTACT_ID = 3)



PRINT @VALUE_KEEP

PRINT @COLUMN_NAME



RESULTS

-------------------------------------------------------------------------------------------

FirstName <-----------@VALUE_KEEP

FirstName <-----------@COLUMN_NAME



SELECT @COLUMN_NAME FROM CONTACTS returns: FirstName

SELECT FirstName from Contacts returns: Brent



How do I make this select statement work using the @COLUMN_NAME variable?

Any help greatly appreciated!

View 2 Replies View Related

Variable Inside A Variable From Sql TAsk

Sep 28, 2006

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.

Where am I wrong?

TIA

View 12 Replies View Related

SQL Variable And IS Variable In Execute SQL Task

Jan 3, 2007

Hi,

 

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?

 

Thanx in advance,

Pipo

View 6 Replies View Related

Set A System Variable To User Variable

May 21, 2007

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

View 10 Replies View Related

Passing Variable Value Through Environment Variable

Feb 15, 2008



Hi All!

I have a parent package that contains two children... The second child depends on the succes of the first child.

THe first child generates a variable value and stores it in an Environment variable ( Visibility - All ) ...After the first succeeds, the second will start executing and will pick up the variable value from environment variable( through package configuration setting )...

Unfortunately, this doesn't work...As the second child picks the stale value of the environment variables...Essentially it assigns variable value not after the first child is finished, but right at the beginning of parent execution...

I tried to execute coth children as Out Of Proc as well as In Proc...The same

Would anybody have an idea how to resolve this problem?

Thanks in advance for any help!

Vladimir

View 5 Replies View Related

Variable Insert To SQL Server Insert Satement Setting Values For The @variable INSIDE Sql

Apr 29, 2007

ok, I am on Day 2 of being brain dead.I have a database with a table with 2 varchar(25) columns I have a btton click event that gets the value of the userName,  and a text box.I NEED to insert a new row in a sql database, with the 2 variables.Ive used a sqldatasource object, and tried to midify the insert parameters, tried to set it at the button click event, and NOTHING is working. Anyone have a good source for sql 101/ASP.Net/Braindead where I can find this out, or better yet, give me an example.  this is what I got <%@ Page Language="C#" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><script runat="server">     protected void runit_Click(object sender, EventArgs e)    {       //SqlDataSource ID = "InsertExtraInfo".Insert();      //SqlDataSource1.Insert();    }      protected void Button1_Click1(object sender, EventArgs e)    {        SqlDataSource newsql;                newsql.InsertParameters.Add("@name", "Dan");        newsql.InsertParameters.Add("@color", "rose");        String t_c = "purple";        string tempname = Page.User.Identity.Name;        Label1.Text = tempname;        Label2.Text = t_c;        newsql.Insert();    }</script><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server">    <title>mini update</title></head><body>    <form id="form1" runat="server">        &nbsp;name<asp:TextBox ID="name" runat="server" OnTextChanged="TextBox2_TextChanged"></asp:TextBox><br />        color        <asp:TextBox ID="color" runat="server"></asp:TextBox><br />        <br />        <asp:Button ID="Button1" runat="server" OnClick="Button1_Click1" Text="Button" />        &nbsp;<br />        set lable =&gt;<asp:Label ID="Label1" runat="server" Text="Label" Width="135px" Visible="False"></asp:Label><br />        Lable 2 =&gt;        <asp:Label ID="Label2" runat="server" Text="Label"></asp:Label><br />        Usernmae=&gt;<asp:LoginName ID="LoginName1" runat="server" />        <br />        <br />        <br />        <br />        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues"            ConnectionString="<%$ ConnectionStrings:newstring %>" DeleteCommand="DELETE FROM [favcolor] WHERE [name] = @original_name AND [color] = @original_color"            InsertCommand="INSERT INTO [favcolor] ([name], [color]) VALUES (@name, @color)"            OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT [name], [color] FROM [favcolor]"            UpdateCommand="UPDATE [favcolor] SET [color] = @color WHERE [name] = @original_name AND [color] = @original_color">            <DeleteParameters>                <asp:Parameter Name="original_name" Type="String" />                <asp:Parameter Name="original_color" Type="String" />            </DeleteParameters>            <UpdateParameters>                <asp:Parameter Name="color" Type="String" />                <asp:Parameter Name="original_name" Type="String" />                <asp:Parameter Name="original_color" Type="String" />            </UpdateParameters>            <InsertParameters>        <asp:InsertParameter("@name", "Dan", Type="String" />        <asp:InsertParameter("@color", "rose") Type="String"/>                                       </InsertParameters>        </asp:SqlDataSource>        &nbsp;        <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True"            AutoGenerateColumns="False" DataKeyNames="name" DataSourceID="SqlDataSource1">            <Columns>                <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" ShowSelectButton="True" />                                <asp:BoundField DataField="color" HeaderText="color" SortExpression="color" />                <asp:BoundField DataField="name" HeaderText="name" ReadOnly="True" SortExpression="name" />            </Columns>        </asp:GridView>           </form></body></html>  

View 1 Replies View Related

If Nothing Selected Into Variable What Is The Value Of Variable?

Feb 15, 2007

DECLARE @PayTypeValue numeric (18, 5)

SET @PayTypeValue = (SELECT [LoadPayValue] FROM [CSITSS].[dbo].[LoadPayType] WHERE [CompanyDiv] = @CompDiv AND [Deleted] = 0 AND [LoadPayType] = @LoadPay)

IF THIS QUERY RETURNS NOTHING WHAT DOES IT SET @PayTypeValue to?

NULL OR 0

View 14 Replies View Related

Is It Possible To Use Twice Declared. Variable Names- KILL And After Declared. Variable

May 1, 2008

is it possible to use twice declared. Variable names-
declared. Variable and after KILL
and use the same declared. Variable
like

DECLARE

@StartDate datetime

KILL @StartDate datetime (remove from memory)
use after with the same name

i have 2 big stored PROCEDURE
i need to put one after one
and psss only 1 Variable name to the second stored PROCEDURE
like this i don't get this error


The variable name '@Start_Date' has already been declared. Variable names must be unique within a query batch or stored procedure.

Msg 134, Level 15, State 1, Line 146

The variable name '@End_Date' has already been declared. Variable names must be unique within a query batch or stored procedure.
i use like
KILL @endDate ??
KILL @StartDate ??


TNX

View 12 Replies View Related

Sql To Variable

Aug 10, 2006

Hi, i want to pass a value of a table result to a variable in a datalist, i mean, i want to check if it as a value or not so i can show or not a link to full story.I've tried to assign eval to a string with a cast but it says eval can only be used in a databound control.Thanks in advance.

View 3 Replies View Related

Use Variable???

Sep 28, 2006

through which way can i do like that?DECLARE @result intDECLARE @result2 int SELECT @result = [column1]+[column2] AS [result1], @result2=@result/[column3] AS [result2], @result3=@result2+[Column4] AS [result3]FROM [table] regards,

View 3 Replies View Related

Get Value Of MAX(ID) Into A Variable

Apr 16, 2007

Hi All, Hope someone can help a newbie!I have the following code: /* Get MAXID from tblHotels_Web to form the HotelID */
SqlCommand cmdGetMaxID = new SqlCommand("Select MAX(HotelID) from tblHotels_Web");
int intMaxID; How do I get the value of HotelID into my intMaxID variable and populate txtID.Text?Regards,Brett 

View 1 Replies View Related

Using Variable With CONTAINS

Nov 12, 2007

Hello,
I'm trying to create a simple search function on my website. A string gets the value from a textbox like this:
Dim sstr As String = tbSearch.Text
then I've tried to use the variable sstr in an SQL statement, something like:
Dim objCommand As New SqlCommand("SELECT ID, title, text FROM Processes WHERE CONTAINS(title, text, ' sstr ') ", objConn)
I've tried a bunch of things but I always get the error that it's something with the syntax near 'text'... It's possible to use a variable in SQL, isn't it? Can anyone tell me what I'm doing wrong?

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved