Multiple Reads Of User Variable (Recordset Object) Failing
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.
hi have written SSIS script and i am using script component to Row count below my code what i have written. and i am getting error below i have mention...after code see the error using System;
} the error Script component has encountered an exception in user code Object is not an ADODB.RecordSet or an ADODB.Record. Parameter name: adodb at System.Data.OleDb.OleDbDataAdapter.FillFromADODB(Object data, Object adodb, String srcTable, Boolean multipleResults) at System.Data.OleDb.OleDbDataAdapter.Fill(DataTable dataTable, Object ADODBRecordSet) at ScriptMain.CreateNewOutputRows() at UserComponent.PrimeOutput(Int32 Outputs, Int32[] OutputIDs, PipelineBuffer[] Buffers) at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.PrimeOutput(Int32 outputs, Int32[] outputIDs, PipelineBuffer[] buffers)
Hello group!I use MS Visual C++ 6.0, ADO, MS SQL Server 2000.When I attempt to open my database I meet with a following problem:when I try to get a bookmark of the current record in a Recordsetobject a following run-time error occurs: Unhandled exception intestdb.exe(KERNEL32.DLL):0xE06D7363: Microsoft C++ Exception.I created my database by 3 SQL commands:create database testdbcreate table testtable(i int)insert into testtable values(0)The error occurs in the following code snippet:#import "D:Program FilesCommon FilesSystemADOmsado15.dll" no_namespace rename("EOF", "EndOfFile")int main(){CoInitialize(NULL);bstr_t strCnn("Provider=sqloledb;Data Source=;""Initial Catalog=testdb;Trusted_Connection=YES;");const char* tablename = "testtable";_RecordsetPtr recs;recs.CreateInstance(__uuidof(Recordset) );recs -> Open(tablename, strCnn, adOpenStatic,adLockOptimistic,adCmdTable);_variant_t bm = recs -> Bookmark; // the error occurs hererecs -> Close();CoUninitialize();}During the debugging this code I met that the error depended on a typeof locking. When I set adLockBatchOptimistic or adLockOptimisticor adLockPessimistic the error occurs but when I set adLockReadOnly oradLockUnspecified it doesn't occur. By the way this error doesn'toccur whenI open Pubs database with any type of locking. What is a cause of thiserror?Thank you.
Hi everybody. I need help with the next topic. From a stored procedure in sqlserver, I need to call a com+ dll, this dll connect ot another database diferent to sqlserver y return this dll must return to stored procedure a recordser for ther processing.
I tried with sp_OACreate, sp_OAMethod, sp_OAGetProperty but I did not know how to process a recordset
I have a package that starts by loading a recordset into an object variable. The recordset is then enumerated with a ForEach loop. The loop sets some string variables. Within the loop container I have a Script task that uses a MsgBox to show the results for testing purposes. The package uses checkpoint restart (if that matters?).
The first time I run the package the 1st record is displayed in the MsgBox, then the 2nd, but then the loop is stuck on the 2nd record forever. I break the run, and when I rerun it the 1st record is displayed followed by each subsequent record correctly and the package completes successfully. Now, if I were to run again the same problem would occur on rec 2 and I would have to break the run, and then the next run everything would work fine.
Why does the script get caught in an infinite loop the first time it's run, but works fine when restarting from the checkpoint?
Here's my relevant code:
ForEach: Enumerator=ADO Enumerator, Enumeration Mode=Rows in first table
Public Sub Main() Dts.Variables("User::SQL1").Value = "SELECT src.* FROM " & Trim(Dts.Variables("User::Src1Tbl").Value.ToString) MsgBox("sql=" & Dts.Variables("User::SQL1").Value.ToString) Dts.TaskResult = Dts.Results.Success End Sub
I have the following error when I try to execute a DTS package. All the package is doing, is a bit of WScript to map a drive (need it for other packages), so:
[code]
Function Main() Dim WshNetwork Set WshNetwork = WScript.CreateObject("WScript.Network") WshNetwork.RemoveNetworkDrive "S:" WshNetwork.MapNetworkDrive "S:", "\myserverfolder1folder2folder3folder4" Main = DTSTaskExecResult_Success End Function
[/code]
If I copy it out to a .vbs file and execute it logged in as the SQL Agent profile, it works. Execute the package under a job, or just execute it, it fails with the above error. Any ideas?
I want a functionality such that I want to return a select query resultset and a varchar variable from a procedure. How can I achieve that,and moreover how can I fetch them in ASP??
Waiting for someone to shed a light of hope. Thanx a lot
I built a small package two years ago that uses Flat File Sources to copy in small text data files. Â Each source connection object has a UNC path to flat text files on another server. Â The source system changed, so I opened the package and updated the UNC path in one Connection Manager object, and clicked OK. The Flat File Source Editor that uses this source seemed to be able to see the new location when I clicked "Preview". Â Then I went back to the file source, and the connection had reverted back to the original one. Â it would not save the new UNC path. Â
I am using SQL Server 2012 SP2 with SSDT (run as admin). Â I closed the package in SSDT, edited the connection strings using XMLnotepad, and was then able to open, test, build and deploy the package.
It seems that the Source object will not let itself be changed. Â The other option is to delete it and recreate it, but I didn't want to remap the fields.
updating a recordset contained in an System.Object variable during runtime.
I am trying to execute multiple file actions (plus parsing those files into a set of staging tables) at separate locations in parallel. I know I can do this in C# but I have a business requirement to use SSIS for all ETL operations.
Any one site can have 0 to many of 1 to 3 files. I would like to run multiple sites at the same time, so when all files of all types are completed at that site then go on to the next site in the list. I know I can do a single site at a time in a foreach loop but if I can run lets say 3-5 sites concurrently then I should be able to save execution time.
My thought is to have a recordset of the sites, when any 1 of the 3 (or more) "control flows" is open, update the recordset to let it know that site being actioned, when that site is complete, update the recordset that the site is completed, and so on.Or am I running in the wrong direction?
I'm having some problems dealing with a VB script which delivers multiple results back from SQL Server. The script opens a connection to SQL 2000 and executes a procedure. The stored procedure has five select statements for its result. I receive a recordset object and try to move through each resultset. I get the first two result sets but when I execute the rs.nextrecordset on the third result, the rs.EOF is set to true and I never receive the third through fifth result set. The only difference I see from the stored procedure is that the first two result sets are "Select"s from local variables (ie @headertxt) and the third through the fifth result set are "Select"s from temporary (#tmptble) tables. I can't determine the root cause of why I can't see the third though fifth result set. I have "set nocount on" in the procedure. I have executed the stored procedure in Query Analyzer under the same ID/PW that I'm setting the connection with in the VBS. No errors are generated from the execution of the stored procedure. I'm running MDAC 2.81 on the workstation. VBS:
dim Conn, strProvider, rs, SQL, i, j
dim asoftime, errorcode, cur1, cur2, atype, asofdate, display_title
'Get the data
Set Conn = Wscript.CreateObject("ADODB.Connection")
Conn.CommandTimeout = 60
Conn.ConnectionTimeout = 60
dim datasource
datasource = "xxxxxxxxx"
strProvider = "Provider=SQLOLEDB; Data Source=" & datasource & "; Initial Catalog=s; User ID=frndis; Password=frndis1; Connect Timeout=15; Persist Security Info=True;UseProcForPrepare=0"
I'm using a stored procedure in SQL Server to generate multiple recordsets. The recordset are however generated conditionally. create procedure GetData ( @a bit @b bit )
Select * from Customers
IF(@a=1) Select * from Products
IF(@b=1) Select * from Details ------------Issue:The adapter returns the table named as Table, Table1(optional) and Table2(optional). It might be that Table1 is products or Table1 is Details.How can I map my tablename property in this scenario where the sequence of tables is not known.
Backup job for User databases is failing. I found below errors View job history
Step 0: The job failed. The job was invoked by schedule 4(DBMP_User). The last step to run was step 1(subplan) Step 1:
Message: Executed as user ServernameSystem. The package execution failed. The step failed
Appln-event log:
SQL server scheduled job DBMP_User failed. Invoked on 2007-12-24 , the job was failed.
Sql server error log
Database backed up. DBname creation,date()time€¦€¦paged dumped 8434659,first LSN: 21126:101410:48,last LSN :21128:933:1, number of dump devices:1, device information: file=1,type=disk (E:MSSQLBACKUP ) This is an informational message only. No user action is required
Error log:
Date: Log: SQL Agent (current €¦) Message: (396) An idle CPU condition has not been defined-OnIdle job schedules will have no effect.
I have loaded my c# windows app onto a PC and I can connect to the database, when I logon as an admin I can run my app and connect to the database. However when I logon as an 'ordinary' user I can run the app but not acess the database - error: Login failed for user 'Rfred' ie '<domain><user>'
Obviously I need to adjust a setting somewhere, I'm just not sure where!
I have an EXECUTE SQL Task, which gets a result-set from SQLServer using OLEDB Connection.
The result set is mapped to an object variable , say @[User::FilePath]
There are 33 row is the above resultset.
Then, I have a For-each loop, inside which, I have a Script task .
I am trying to put the above @[User::FilePath] recordset into a DataTable using DataAdapter.Fill() function. I perform some read operations to its rows.
The problem is , in the First Iteration of For-Each-Loop, the number of rows in data-table shows 33.
But from the Next Iteration, it comes out to be 0. (ZERO!!)
we have a application written in straight ASP, we recently upgraded our SQL database connecting to the application from SQL 2000 to 2005 here is the issue
currently SQL 2005 is located in our test and UAT environments, SQL 2005 is not clustered in either location. Now let me make it clear THIS PROBLEM IS NOT HAPPENING IN OUR TEST ENVIRONMENT, I REPEAT THIS PROBLEM IS NOT HAPPENING IN OUR TEST ENVIROMENT, THIS PROBLEM IS ONLY HAPPENING IN OUR UAT ENVIROMENT
Each enviroment is outfitted with the following SQL 2005 environment, Enterprise Edition SP1 with Cumlative hotfix package 2153 just the SISS fix ONLY
now the problem is this, we have a vb program located directly on the server that has SQL 2005 on it that VB program inserts a timestamp into a table inside the database, now how this happens is the program connects to the database and opens a recordset with a forwardonly cursor and inserts the timestamp
NOW THE EXACT PROBLEM IS THAT THE VB PROGRAM CANNOT OPEN MULTIPLE RECORDSETS, THE ERROR RECEIVED IS LABLED LIKE THIS
NOW here is the interesting part, AGAIN THIS IS NOT HAPPENING IN OUR TEST ENVIRONMENT, THE VB PROGRAM AGAIN IS ON THE TEST SQL SERVER AND HAS A FORWARD ONLY CURSOR AND INSERTS JUST FINE
NOW IF I MOVE THE VB PROGRAM OUT OF OUR UAT ENVIRONMENT ONTO ANOTHER SERVER IT INSERTS THE RECORD ONTO SQL SERVER JUST FINE, IT OPENS THE RECORDSET AND OPERATES NORMALLY, BOTH SERVERS HAVE EXACT SAME PERMISSIONS
Can anyone help me understand whats happening here
One note, when we changed the cursortype from forward-only to keyset it worked fine but that doesnt explain things
why does it work in test so clearly what we did with changing the cursortype isnt the answer
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?
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?
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?
Maintenance plan for bakcup is failing with "Login failed for user 'sa'. [CLIENT: <local machine>]"
I went to the Maintenance Plan and opened the Subplan. I clicked the "Manage Connections"
It has three tabs:
Name: Local Server Connection Server: prod Authentication: SQL Server Authentication
I clicked the Edit and it shows the Connection Properties:
It says: Enter information to logon to the server. "Use a specific Username and Password" is checked. Username is set as "sa" while the password is empty. I typed in the correct password and pressed Ok. When I go back again, the password still shows empty. I tried to run the plan and it again fails. Do you know why it is not showing the password as blank even if I try to save the password.
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?
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.
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).
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.