Accessing Data Table In Object Variable Row By Row
Apr 30, 2008
I have an application where the columns in a datatable stored in a variable (as a dataset with one table in it) are dynamic. This means that the number of columns and their data types will vary. Also, I would like to access the column names. For these reasons it is not very practical to map the columns to variables.
Is there a way to use a Script Task within a For Each Loop (with ADO enumeration) to read the data row by row (presumably as a datarow) getting the column names and coloumn values by numeric (column) index?
Thanks,
*** Campbell
View 1 Replies
ADVERTISEMENT
Aug 29, 2007
I am trying to access data from a database to print a report. The code I am using says I have a syntax error regarding the object person_info. Person_Info is a table value function on the SQL Server (2005). Does anybody see a problem with this line of code? Thanks for your help! Regards, Steve
"INNER JOIN (SELECT * Person_Info FROM (" & Session("current_project") & ")) ON pc.personID_fk=pe.personID_pk " & _
View 4 Replies
View Related
Jul 5, 2007
Good Day guys, sorry i'm just new in SQL Server.
My problem is like this:
I want to pass Table Object to a declared variable and make View.
Usually we make like this:
"Select * from Table1"
I want like this:
"Select * from @Table1"
I want to pass Table1 to a variable "@Table1" which the variable
reads as Table.
Please help me guys, i really appriciated everything.
Thanks,
Ero-Sennin26
View 1 Replies
View Related
Nov 17, 2006
Hi!
These problem does't look like a problem, but i am not able to solve it...
I have a stored procedure (in an Execute SQL-Task) what returns a few rows. These rows a written in an object variable. No problem.
Now i want to use these result set in a dataflow as source. How can i manage these?
1. Use a script task what returns the whole result set from the object variable? How?
2. Is there any other possibility to use the results of a stored procedure in the data flow?
All ideas are urgently welcome...
Torsten
View 6 Replies
View Related
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
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
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
May 4, 2008
When I create a tableadapter to do an insert it added
SELECT EventDate, EventID, org_id, ROMEventID FROM ROMEvents WHERE (ROMEventID = SCOPE_IDENTITY())
at the end. This looks useful to me since it implies that I can access ROMEventID which is the auto-incremented identity field.
How would I reference the variable in my .cs file?
My insert is working as ROMEventsTableAdapter ROMEventInsert = new ROMEventsTableAdapter();
ROMEventInsert.InsertIntoROMEvents(theDate, EventID, org_id);
Thanks for any help
View 2 Replies
View Related
Feb 14, 2007
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!
View 17 Replies
View Related
Apr 17, 2008
Hi
I have a sql task in my control flow, which is like
Resultset = Single Row, SQL SourceType = Direct Input, SQL Stmt = EXEC StoreProcA ?
Result Name VariableName
StartDate UserStartDate
EndDate User:EndDate
Above SQL task is working fine... My question is I have an another SQL task immediately below that one.SQL Stmt is
SELECT CreditDebitDiff
FROM (SELECT C.BalanceNbr - D.BalanceNbr As CreditDebitDiff
FROM dbo.BalanceLog C, dbo.BalanceLog D
JOIN Work.CycleControl cc
ON cc.CycleControlId = D.CycleControlId
WHERE C.BalanceItemId = '81041'
AND D.BalanceItemId = '81031'
AND cc.CycleDate = ?) AS D
Parameter0 is mapped to User:EndDate and ResultName mapped to User:CreditDebitDiff (Datatype: Double)
SQL Task fails. I know I am making a mistake but dunno how to correct it. I can do it with a store proc but don't want to.
Any help would be greatly appreciated...
View 4 Replies
View Related
Sep 4, 2007
Hi,
I have a variable in SSIS that I want to access inside the Script Task. I assigned the variable in the ReadOnlyVariables in the Script Task property. How do I access it?
cherriesh
View 1 Replies
View Related
Oct 11, 2006
Hi There,This is related to a ms access database but since I use the SqlDataSource control I thought I should post here.I have a project that I was working on with this ms access db and using sql controls, everything was working just finesince one day I started getting "Object reference not set to an instance of an object" messages when I try to designa query or retrieve a schema, nothing works at design time anymore but at runtime everything is perfect, its a lotof work for me now to create columns,schemas and everything manually, I've tried reinstalling visualstudio, ado componentsbut nothing seems to fix it, did this ever happen to any of you guys?any tip is really appreciated thanks a lot
View 2 Replies
View Related
Dec 28, 2004
Each time I press submit to insert data into the database I receive the following message. I use the same code on another page and it works fine. Here is the error:
Object reference not set to an instance of an object.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.
Source Error:
Line 125: MyCommand.Parameters("@Balance").Value = txtBalance.Text
Line 126:
Line 127: MyCommand.Connection.Open()
Line 128:
Line 129: Try
Source File: c:inetpubwwwrootCreditRepairCreditor_Default.aspx.vb Line: 127
Stack Trace:
[NullReferenceException: Object reference not set to an instance of an object.]
CreditRepair.CreditRepair.Vb.Creditor_Default.btnSaveAdd_Click(Object sender, EventArgs e) in c:inetpubwwwrootCreditRepairCreditor_Default.aspx.vb:127
System.Web.UI.WebControls.Button.OnClick(EventArgs e)
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
System.Web.UI.Page.ProcessRequestMain()
Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
If (Page.IsValid) Then
Dim DS As DataSet
Dim MyCommand As SqlCommand
Dim AddAccount As String = "insert into AccountDetails (Account_ID, Report_ID, Balance) values (@Account_ID, @Report_ID, @Balance)"
MyCommand = New SqlCommand(AddAccount, MyConnection)
MyCommand.Parameters.Add(New SqlParameter("@Account_ID", SqlDbType.Char, 50))
MyCommand.Parameters("@Account_ID").Value = txtAccount_ID.Text
MyCommand.Parameters.Add(New SqlParameter("@Report_ID", SqlDbType.Char, 50))
MyCommand.Parameters("@Report_ID").Value = txtReport_ID.Text
MyCommand.Parameters.Add(New SqlParameter("@Balance", SqlDbType.Char, 50))
MyCommand.Parameters("@Balance").Value = txtBalance.Text
MyCommand.Connection.Open()
MyCommand.ExecuteNonQuery()
Response.Redirect("Customer_Details.aspx?SS='CustHeadGrid.Columns[0].Item.lblSS.Text)")
MyCommand.Connection.Close()
End If
View 2 Replies
View Related
Apr 11, 2008
I'm doing a simple ETL that reads a database table and dumps the content to a text file. The text file will be named Employee.txt. This file name will remain the same across my environments, but I may want to vary the directory location to where I want this file dumped.
So, I defined an environment variable called "DataTargetDir" in all my environments. Now, I want to utilize this variable in the "File name:" box within the Flat File Connection Manager Editor. How do I do this? I'm thinking I can write something like "%DataTargetDir%Employee.txt" in the "File name:" box, but it's not working.
Am I approaching this the right way?
View 9 Replies
View Related
Apr 17, 2004
Im really new to this and Im really bad.
but im trying to get the userID From a table,
but the table is named "Table"+the productID
ie Table23
or Table54
has 2 columns, UnitId and UserID
heres my code
CREATE Procedure UserDetail
(
@UnitId int,
@ProductID int,
@UserID int OUTPUT
)
AS
declare @TSQL varchar(1000)
SET @TSQL= 'SELECT @UnitId , @ProductID , @UserID ' +
'FROM Table'+CAST(@ProductID AS char(20))+
' WHERE UnitId=@UnitId '
execute(@TSQL)
GO
and i get this error
Exception Details: System.Data.SqlClient.SqlException: Must declare the variable '@UnitId'
anyhelp would be aprciated,. thanks so much
View 14 Replies
View Related
Sep 20, 2007
Hallo!
I got an object variable. I used it in a €śRecordset destination€?.
How can I proof if it is empty?
Isnull(@[Benutzer::MyVar]) does not work!
View 5 Replies
View Related
Nov 20, 2006
Hi there!
I want to use a ForEachLoop. I've an object variable what i fill before going into the ForEachLoop. It contains 4 columns and in my testscenario it has two rows. In the ForEachLoop i want to set the current row values to 4 package variables (within package scope).
So i set the Enumerater as "Foreach-ADO-Enumerator", the Ado-source-variable is my objectvariable (what contains the recordset), and the enumerator-configuration i set to "rows in all tables" ("rows in the first table" works with equal result).
The variable-mapping looks like that:
Mypackvar1 - Index 0
Mypackvar2 - Index 1
Mypackvar3 - Index 2
Mypackvar4 - Index 3
Seems to be really simple, but always i get into my first parameter the value "0" - what is not in my record set (i am relatively sure).
Am i on the right way? Is it great bullshit what i am doing?
Thanks for any suggestion,
Torsten
View 3 Replies
View Related
Dec 5, 2014
Can we retrieve data from table type variable as an array in oracle key values..
E.g.
ALTER PROCEDURE prn1( @p_prn as KeyValuePair readonly)
-- proc which having parameter table type as parameter
AS
declare @v_formatted_str varchar(50)
BEGIN
set @v_formatted_str = v_formatted_str + @p_prn(1)(1)
END
View 3 Replies
View Related
Apr 25, 2008
How to insert data into a table from a string variable? Like below:
DECLARE @Data AS NVARCHAR(MAX)
SET @Data = 'bla|bla|bla
lab|lab|lab
abl|abl|abl'
BULK INSERT tablename
FROM @Data
WITH
(
DATAFILETYPE = 'char',
FIELDTERMINATOR = '|',
ROWTERMINATOR = ''
);
View 3 Replies
View Related
Mar 30, 2004
CAST a variable into a datetime object
I need to do a CAST(@variable_name as datetime)
this won't work because @variable_name has the following format
'dd/mm/yy hh:mi:ss:mmmAM'
like how do i specify a style for it.
please help..
James : (
View 4 Replies
View Related
Jan 25, 2006
Hi
I have some SSIS variables of type System.Object (they have to be this
type because they are used to hold the results of a single row result
set in an Execute SQL task which is querying an Oracle database.
Although I know the Oracle table columns are Numeric, this was the only
SSIS type that worked).
My problem is that I want to use these variables in expressions, but
can't - I get the error "The data type of variable "User::varObjectVar"
is not supported in an expression".
The only workaround I can think of is to use a script to assign
the numeric values (integers, in fact) that these variables hold to
other variables of type Int32.
Is that my only option, or am I missing something?
thanks
- Jerzy
View 6 Replies
View Related
Jan 4, 2007
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
View 2 Replies
View Related
Aug 17, 2015
I have one scenario. I am calling all columns result set to an variable and inside for each loop container using script task to get message about how many columns are coming in the loop.
At last using send mail task to send automated mails to group of people,but issue it is taking only person's mail id and coming out of loop.
how to call object type variable ?
View 4 Replies
View Related
May 26, 2004
I have a simple select quesry but with 'for XML AUTO, ELEMENTS'. I want to put in the resulting xml string into a temporary table and then alter that string as per my requirements. But I am unable to put this XML string into a table variable. Please offer your suggestions.
View 4 Replies
View Related
Jul 28, 2015
is there a way to see the data of a table variable in the SSMS debugger? For example, if I set a breakpoint in SSMS and look at a populated table variable named @MyTable in the Locals tab at the bottom of the IDE, a value of "(table)" is displayed. There does not appear to be a way to expand or drill into this variable in the debugger to see the data. Do you know if there's a way to do this through the debugger or do you use an alternate approach when using the SSMS debugger?
View 1 Replies
View Related
Aug 4, 2015
I have created procedure that creates a temptable with the users from a certain business unit.
I need to save the output from the username field to my variable @TheUsers as I will be using this variable in ssrs.
CREATE TABLE #TEMP_TABLE
(
useriduniqueidentifier,
usernamevarchar(200),
unitiduniqueidentifier,
unitnamevarchar(200)
[Code] ....
View 6 Replies
View Related
Feb 26, 2015
I am writing a query to return some production data. Basically i need to insert either 1 or 2 rows into a Table variable based on a decision as to does the production part make 1 or 2 items ( The Raw data does not allow for this it comes from a look up in my database)
I can retrieve all the source data i need easily but when i come to insert it into the table variable i need to insert 1 record if its a single part or 2 records if its a twin part. I know could use a cursor but im sure there has to be an easier way !
Below is the code i have at the moment
declare @startdate as datetime
declare @enddate as datetime
declare @Line as Integer
DECLARE @count INT
set @startdate = '2015-01-01'
set @enddate = '2015-01-31'
[Code] .....
View 1 Replies
View Related
Dec 7, 2007
Hello,
I have a 'ForEach Loop Container' that does a select from a table and I use Variable Mappings to map each row set result to Package Variables in SSIS. This works fine. However, in the Data Flow, I have a Script component in which I need to access the values of those variables that have been set in the ForEach Loop Container. How do I do this?
Perhaps I'm on the wrong track but I guess I need to access the ADO Object Source variable set in the ForEach Loop Container? Through my own experimentation, I know that accessing the VariableDispenser collection only returns whatever default values that happen to be assigned to the variables in the SSIS GUI (not the values assigned during each iteration of the ForEach Loop).
Thanks,
Clive
View 16 Replies
View Related
May 8, 2008
Hi,
I have a script component that contains a script similar to that shown below;
Public Sub Main()
Dim sqlConn As New SqlConnection("Server=myserver;Database=mydb;Trusted_Connection=True;")
Dim SQLStmt As String
SQLStmt = "select top 10 CountID from mytable"
Dim sqlComm As New SqlCommand(SQLStmt, sqlConn)
sqlConn.Open()
Dim r As SqlDataReader = sqlComm.ExecuteReader()
While r.Read()
Dim MyValue As Integer
If r("CountID") IsNot DBNull.Value Then
MyValue = CInt(r("CountID "))
' HERE I WANT TO WRITE MyValue TO A PACKAGE VARIABLE AS A ROW
End If
End While
r.Close()
sqlConn.Close()
Dts.TaskResult = Dts.Results.Success
End Sub
Once i have a value assigned to MyValue, I want to write this as a row to a variable (DATATYPE Object Value VALUE System.Object) in the package which will be an ADO Recordset Destination that i can then loop through later.
Many thanks for any help
Phil Harbour
View 1 Replies
View Related
Feb 23, 2007
Hey Guys,
Im pretty new to Scripting in SSIS, but i have worked a fair bit out already, although i am stuck with the following problem.. If anyone can help out that would be great!!
Ok, i have a package that loops through records in a table, and extracts the detailed lines in a For Each loop. and stores them in a variable called DetailRecordSet with an data type of object.
I have a built a script task so that i can send out an email confirmation for each order, and i want to list the details in a HTML Table in the body of the message ( I have read that the standard send mail task wont support this, but found an example of using .NET code to generate the email message which will support it) .
What i want to know is how do i reference the columns in the Object variable so i can extract each line and add it into my string that i am creating with HTML codes.. I
e:( str = "<TABLE> <TR>" + Cstr(Variable for 1st column in recordset) + " </TR>"
if any can help my, that would be most appriciated.. Ive tried all different things that ive found on the net, but nothing is working.
Thanks in Advance
Scotty
View 3 Replies
View Related
Feb 16, 2007
I am using a foreach loop, with the data from an ado recordset, which contains the table name that I wish to write data to an OLEDB data dest. The table names are retrieved from an execute sql task in the an object var. Within the foreach loop, for each table name, I then use a datareader to an ado.net source to pull data from that table, via an expression construct into a variable - i.e. "select * from " + @[User::table_name]. This works fine for the first table, in which mappings are setup using the SSIS design environment. The data is retrieved. I then use a variable and set the data access mode for the oledb destination to "Table name or view name variable". This also saves data fine for the first table in the loop in the oledb dest. When the next table name is retrieved from the ado provider in the foreach loop, the datareader fails, as it still thinks the metadata mappings are from the first table, which was used for the mapping in the design environment. I.E. FIN_CLASS is a column from the first table in the loop.
Error: 0xC0202005 at Data Flow Task, DataReader Source [7181]: Column "FIN_CLASS" cannot be found at the datasource.
I have set the following properties, that I thought (in my feeble mind), are supposed to avoid that behavior. For the datareader, I set ValidateExternalMetadata to false, and for the data flow task (container for the datareader), I set DelayValidation to true. These settings, according to the doc, are supposed to evaluate metadata for the datareader source at runtime (not design time), so that the column metadata is dynamic, and so that the subsequent oledb destination can use the "data access mode" for the oledb destination of "Table name or view name variable".
If I cannot get this to work, I have 2 options: Use OPENQUERY via dynamic t-sql statements, OR create 30 separate flows in SSIS - one for each table - not looking forward to that one.
View 5 Replies
View Related
May 28, 2015
I am using C# in  Visual Studio 2008 and remote database as sql server 2008 R2. I want to read remote database table's field value and i have to move that read value to string variable. how to do it.Â
And my code is :
string sql = "Select fldinput from tmessage_temp where fldTo=IDENT_CURRENT('tmessage_temp')";
SqlCommand exesql = new SqlCommand(sql, cn);
exesql.CommandType = CommandType.Text;
SqlDataReader rd1 = default(SqlDataReader);
rd1 = exesql.ExecuteReader();
View 6 Replies
View Related
Mar 1, 2007
I hope this is a simple question. I have a package-scope user variable which is populated using a Recordset Destination in a Data Flow task. I am attempting to read the variable multiple times from different Script Tasks. The first read works fine, however the second read, in the second Script Task, says that there are no rows.
Has anyone run across this before? Any thoughts would be appreciated.
Thanks
View 1 Replies
View Related
