DTS Package - Provider Generated Code Execution Exception: EXCEPTION_ACCESS_VIOLATI
Feb 23, 2006
When running a step within my DTS package I'm receiving the following error - "Provider generated code execution exception: "EXCEPTION_ACCESS_VIOLATION".
I think it may be something to do with my global variable, but I'm not sure as I'm pretty certain I've set it all up correctly.
Below are screenprints showing my settings.
http://img153.imageshack.us/my.php?image=19tv1.jpg
http://img153.imageshack.us/my.php?image=25wz1.jpg
http://img153.imageshack.us/my.php?image=39pf.jpg
http://img164.imageshack.us/my.php?image=43nx.jpg
http://img164.imageshack.us/my.php?image=51ao.jpg
http://img164.imageshack.us/my.php?image=64lo.jpg
http://img164.imageshack.us/my.php?image=71yn.jpg
Any advice of fixing this would be greatly appreciated.
View 6 Replies
ADVERTISEMENT
Apr 17, 2007
Hi All,
This not a problem but here i wan to give u my some trial on package execution from C# code.
i just want to make sure whether this is right way or not?
I need to upload some processed text file into table using SSIS packages. I m calling these packages in runtime for different source text files passed to it.
I first created package on my machine and deployed packages on Sql server using default protection level. So when i m tryng to execute it from integration services it wont work giving some exception in AquireConnectionCall() , its coz all the sensitive information is stroed inside package is not available to that machine.
In C#
Now i m loading this package using LoadFromSqlServer().
I am creating connection manager object for each of source and destination type and then setting all sensitve information from my solution's config file.
Set the protection level of package and available connection managers to DontSaveSensitve.
by using this method m able to execute any package created on any machine with default protection level.
Can any one of tell me -ve aspects of this approach?
Thanks
View 5 Replies
View Related
Jul 26, 2007
Hello
I am trying to call the same package with different starting parameters using asynchronous method calls.
My code is
namespace ReconHost
{
public partial class HostContainer : Form
{
public delegate void InvokePackageHandler(
Microsoft.SqlServer.Dts.Runtime.Application app,
int var
);
Microsoft.SqlServer.Dts.Runtime.Application app;
public HostContainer()
{
InitializeComponent();
for (int i = 0; i < 2; i++)
{
app = new Microsoft.SqlServer.Dts.Runtime.Application();
InvokePackageHandler asyncInvokePackageHandler = InvokePackage;
asyncInvokePackageHandler.BeginInvoke(app, i, null, null);
}
}
public void InvokePackage(Microsoft.SqlServer.Dts.Runtime.Application app, int var)
{
Package pkg = app.LoadPackage(@"c:GRSGRSGRSTest_Async.dtsx", null);
pkg.Variables["intVar"].Value = var;
pkg.Execute();
pkg = null;
}
}
}
If I set the for loop to loop just once, the package is executed fine. Any more than once and only one instance of the package is executed.
Does anyone know what I am doing wrong? I know it is possible to execute the two package instances simultaneously because you can use DTexecUI.exe on two clients to do this.
Thanks in advance
Tomo
View 3 Replies
View Related
Mar 25, 2008
I need to write a front end to execute our .dtsx package due to it being encrypted and not wanting to expose the password at all.
I have successfully written code to run it but I want to show something like the Package Execution Progess window and I don't know how.
This window shows up when you just double click on the .dtsx file and Execute it using the interface provided.
Can anyone help?
Here's my code so far.
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Microsoft.SqlServer.Dts.Runtime;
namespace RunMigration
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
DTSExecResult pkgResults_Sql;
try
{
Cursor.Current = Cursors.WaitCursor;
Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
app.PackagePassword = "SomePassword";
Microsoft.SqlServer.Dts.Runtime.Package pkg = new Microsoft.SqlServer.Dts.Runtime.Package();
pkg = app.LoadPackage("T:\amber\encrypt\EncryptedPHDMigration.dtsx", null);
string mypath = this.textBox1.Text.ToString();
pkg.ImportConfigurationFile(mypath);
pkgResults_Sql = pkg.Execute();
MessageBox.Show(pkgResults_Sql.ToString());
MessageBox.Show(pkg.ExecutionStatus.ToString());
foreach (Microsoft.SqlServer.Dts.Runtime.DtsError error in pkg.Errors)
{
Console.WriteLine("{0}", error.Description);
this.richOutput.Text = error.Description;
}
}
catch (System.Exception f)
{
MessageBox.Show(f.Message);
}
finally
{
Cursor.Current = Cursors.Default;
}
}
private void btnConnect_Click(object sender, EventArgs e)
{
OpenFileDialog open = new OpenFileDialog();
open.Filter = ("SSID config files|*.dtsConfig");
if (open.ShowDialog() == DialogResult.OK)
{
this.textBox1.Text = open.FileName.ToString();
}
}
}
}
View 10 Replies
View Related
Sep 13, 2006
Hi everyone,
After a Execute method I would need to stop a package but I don't know why:
sResultDts = pkg.Execute(Nothing, Nothing, EventsSSIS, Nothing, Nothing)
I have a Events class (EventSSIS) which implements IDTSEvents and have an OnQueryCancel event but without parameters, such so:
Function OnQueryCancel() As Boolean Implements IDTSEvents.OnQueryCancel
Return False
End Function
Let me know how to pass a boolean parameter because of I can't overloaded OnQueryCancel method
TIA-
View 1 Replies
View Related
Nov 11, 2006
HI all,
if this issue has been solved, please provide me the post, or I've put this issue in a wrong category please let me know.
The following is my code:
DateTime dtBFWNow = DateTime.Today;
String dtBFW_range1 = dtBFWNow.AddDays(-7).ToShortDateString();
String dtBFW_range2 = dtBFWNow.AddDays(3).ToShortDateString();
SqlDataSource1.SelectCommand = "SELECT * FROM [MORTGAGE] WHERE ([BringForwardDate] is not null) and ([BringForwardDate]>= '" + dtBFW_range1 + "' and [BringForwardDate] <= '" + dtBFW_range2 + "')";
lblHeader.Text = "Bring Forward Reminders Due From '" + dtBFW_range1 + "' To '" + dtBFW_range2 + "'";
lblHeader.ForeColor = System.Drawing.Color.FromName("#000000");
GridView1.DataBind();
When I compiled, the following exception occurred:
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
and the exception points right at the line: GridView1.DataBind()
I use the webhost4life as my server provider. My codes run fine with the server. I mean I uploaded my program to the server, set up and run the program online, everything is fine.
But on my PC it gives me the above exception. I can't compile my code. There must be some sort of setting somewhere in my PC that can't handle the exception.
Please help.
Regards.
Joey.
View 2 Replies
View Related
Apr 23, 2007
Hello,
we've recently upgraded a database from sql 2000 to sql 2005. There is one report that creates a fatal exception and sql dump whenever it is run. It ran without error in 2000.
The report is using several sql views and one stored proc. Running these individually via sql don't cause any errors. All other reports and processes run fine in 2005.
I'm at a loss as to what to check next. Any ideas?
Please let me know if I can provide any more details to help troubleshoot the issue.
thanks,
joe
View 5 Replies
View Related
Nov 15, 2007
Hello,
Just yesterday I implemented SqlCacheDependencies on my ASP.NET 2.0 web site for SQL Server 2005. My SQL Server 2005 database is in mode 90, mirrored with a second database server. Now I am getting Error 17310 while running very simple statements, but only on a specific table.
We have a table called "tblSchools" which, due to negligence and incompetence, has a maximum size of over 8,000. I plan on fixing that eventually, by splitting all the long varchar(2048) fields off into other tables.
I wanted to use SqlCacheDependencies on this table, for every single column. So I created a DateTime column called LastChanged and created a trigger. I am using LastChanged as a DateTime type instead of a TimeStamp/RowVersion type because I thought it would be more useful.
Here are the changes made:
---------------------------
-- Adding the column...
ALTER TABLE tblSchools
ADD
LastChanged DateTime Null
GO
-- The program uses this stored procedure to watch the "LastChanged" column.
CREATE PROCEDURE [apCacheWatchSchool]
@SchoolID int
AS
select LastChanged from dbo.tblSchools where SchoolID = @SchoolID
GO
-- Any updates on the table cause the LastChanged column to be updated with getdate()
CREATE TRIGGER [UpdateLastChanged_TS]
ON [dbo].[tblSchools]
AFTER INSERT,UPDATE
AS
BEGIN
SET NOCOUNT ON;
update [dbo].[tblSchools]
set LastChanged = getdate()
where SchoolID in (select SchoolID from inserted);
END
GO
---------------------------
After adding this column and the trigger and s-proc, any update to any row in tblSchools fails with error 17310. It CANNOT set LastChanged to anything. It has to remain NULL. I was able to perform the following operation (as long as the tblSchools.LastChanged updating trigger [UpdateLastChanged_TS] was disabled): "update tblSchools set SchoolName = 'ZZZZ' + SchoolName where SchoolID = 185" -- so, it's not some kind of row length limit. I think it's a limit on # of columns that can be dealt with. I don't really know.
I don't know where the problem is occurring. I am just telling you why. I got around the problem (temporarily) by simply having the trigger update a DateTime column in another table, and having the SqlCacheDependency watch that column in the other table. No biggie.
As for moving some of the enormous varchar columns off of tblSchools... I don't really care enough. I guess that's why it's been like this for so long.
So, my problem is fixed, I just wanted to submit this bug report so you can at least fail gracefully in the next version of SQL Server.
I have the log/txt/mdmp files available, and can provide them upon request; they're too big for this form, sorry. Please contact me at plushpuffin AT wwddfd.com if you have any questions.
View 1 Replies
View Related
Sep 26, 2007
Team
I keep getting this error every so often in the SQL Server(2005.90.3054.00). 2005/SP2/Hot Fixes. Windows Server 2003 SP2. I have researched the web, every one seems to have the same question and nobody seems to have an answer.
This somehow does not show up in the Profiler. The apps work normally too.
It is a huge eye sore in the LOGs.
=================
BugCheck Dump
=================
This file is generated by Microsoft SQL Server version 9.00.3054.00 upon detection of fatal unexpected error. Please return this file, the query or program that produced the bugcheck, the database and
the error log, and any other pertinent information with a Service Request.
Computer type is AT/AT COMPATIBLE.
Bios Version is COMPAQ - 2
Current time is 15:52:36 09/27/07.
8 Intel x86 level 15, 2694 Mhz processor (s).
Windows NT 5.2 Build 3790 CSD Service Pack 2.
Memory
MemoryLoad = 93%
Total Physical = 9727 MB
Available Physical = 638 MB
Total Page File = 13613 MB
Available Page File = 4573 MB
Total Virtual = 2047 MB
Available Virtual = 246 MB
***Stack Dump being sent to E:Microsoft SQL ServerMSSQL.1MSSQLLOGSQLDump1331.txt
SqlDumpExceptionHandler: Process 91 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is
terminating this process.
* *******************************************************************************
*
* BEGIN STACK DUMP:
* 09/27/07 15:52:36 spid 91
* Exception Address = 0100E88E Module(sqlservr+0000E88E)
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 0000000C
* Input Buffer 36 bytes -
* s p S e l e c t 10 00 73 00 70 00 53 00 65 00 6c 00 65 00 63 00 74 00
* I n t e r n e t 49 00 6e 00 74 00 65 00 72 00 6e 00 65 00 74 00 00 00
* MODULE BASE END SIZE
* sqlservr 01000000 02C0AFFF 01c0b000
* ntdll 7C800000 7C8BFFFF 000c0000
* kernel32 77E40000 77F41FFF 00102000
* MSVCR80 78130000 781CAFFF 0009b000
* msvcrt 77BA0000 77BF9FFF 0005a000
* MSVCP80 7C420000 7C4A6FFF 00087000
* ADVAPI32 77F50000 77FEAFFF 0009b000
* RPCRT4 77C50000 77CEEFFF 0009f000
* Secur32 76F50000 76F62FFF 00013000
* USER32 77380000 77410FFF 00091000
* GDI32 77C00000 77C47FFF 00048000
* CRYPT32 761B0000 76242FFF 00093000
* MSASN1 76190000 761A1FFF 00012000
* MSWSOCK 71B20000 71B60FFF 00041000
* WS2_32 71C00000 71C16FFF 00017000
* WS2HELP 71BF0000 71BF7FFF 00008000
* USERENV 76920000 769E1FFF 000c2000
* opends60 333E0000 333E6FFF 00007000
* NETAPI32 71C40000 71C96FFF 00057000
* SHELL32 7C8D0000 7D0CDFFF 007fe000
* SHLWAPI 77DA0000 77DF1FFF 00052000
* comctl32 77420000 77522FFF 00103000
* psapi 76B70000 76B7AFFF 0000b000
* instapi 48060000 48069FFF 0000a000
* CLUSAPI 74DE0000 74DF1FFF 00012000
* ole32 77670000 777A8FFF 00139000
* OLEAUT32 77D00000 77D8AFFF 0008b000
* RESUTILS 74EF0000 74F02FFF 00013000
* sqlevn70 4F610000 4F7B8FFF 001a9000
* SQLOS 344D0000 344D4FFF 00005000
* rsaenh 68000000 68034FFF 00035000
* AUTHZ 76C40000 76C53FFF 00014000
* MSCOREE 79000000 79044FFF 00045000
* msv1_0 76C90000 76CB6FFF 00027000
* iphlpapi 76CF0000 76D09FFF 0001a000
* kerberos 61FF0000 62047FFF 00058000
* cryptdll 766E0000 766EBFFF 0000c000
* schannel 76750000 76776FFF 00027000
* COMRES 620B0000 62175FFF 000c6000
* XOLEHLP 62180000 62185FFF 00006000
* MSDTCPRX 62190000 62208FFF 00079000
* msvcp60 62210000 62274FFF 00065000
* MTXCLU 62280000 62298FFF 00019000
* VERSION 77B90000 77B97FFF 00008000
* WSOCK32 71BB0000 71BB8FFF 00009000
* DNSAPI 76ED0000 76EF9FFF 0002a000
* winrnr 622F0000 622F6FFF 00007000
* WLDAP32 76F10000 76F3DFFF 0002e000
* rasadhlp 62320000 62324FFF 00005000
* hnetcfg 62330000 62389FFF 0005a000
* wshtcpip 71AE0000 71AE7FFF 00008000
* security 62890000 62893FFF 00004000
* msfte 63070000 632C8FFF 00259000
* dbghelp 632E0000 633F4FFF 00115000
* WINTRUST 76BB0000 76BDAFFF 0002b000
* imagehlp 76C10000 76C37FFF 00028000
* dssenh 68100000 68126FFF 00027000
* NTMARTA 77E00000 77E20FFF 00021000
* SAMLIB 7E020000 7E02EFFF 0000f000
* ntdsapi 766F0000 76703FFF 00014000
* xpsp2res 63C00000 63EC4FFF 002c5000
* CLBCatQ 777B0000 77832FFF 00083000
* sqlncli 63ED0000 640F3FFF 00224000
* COMCTL32 77530000 775C6FFF 00097000
* comdlg32 00940000 00988FFF 00049000
* SQLNCLIR 64210000 64242FFF 00033000
* msftepxy 64AD0000 64AE4FFF 00015000
* xpsqlbot 64B30000 64B35FFF 00006000
* xpstar90 64F70000 64FB8FFF 00049000
* SQLSCM90 64FD0000 64FD8FFF 00009000
* ODBC32 64FF0000 6502CFFF 0003d000
* BatchParser90 65030000 6504EFFF 0001f000
* ATL80 7C630000 7C64AFFF 0001b000
Thanks for your help in advance.
Paresh Motiwala
EDS, Boston, USA
The other half of the error message is in the second message of the same thread below.
The file is located here:
View 1 Replies
View Related
Nov 15, 2007
I'm running SQL 2000 SP4 on a patched Windows 2003 SP2 server.
I keep getting the error:
Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17052
Date: 11/14/2007
Time: 3:51:13 AM
User: N/A
Computer: HOST20
Description:
Error: 0, Severity: 19, State: 0
SqlDumpExceptionHandler: Process 57 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
Any idea what this is or what to do to fix it?
View 7 Replies
View Related
Jan 18, 2008
Hi Guys,
How are you. i hope you all are having fun.
well i have an issue on one of the production box.
Version of the box: Microsoft SQL Server 2000 - 8.00.2187 (Intel X86) Mar 9 2006 11:38:51 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
As per SQL serve logs which i received couple of times.
step 1) Using 'dbghelp.dll' version '4.0.5'
*Stack Dump being sent to C:Program FilesMicrosoft SQL ServerMSSQLlogSQLDump0173.txt
2) SqlDumpExceptionHandler: Process 3920 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
3) Stack Signature for the dump is 0xC578316D
when i searched in google and msdn i could not found the exact solution.
if you guys have the knowledge and also got experianced about this issue.
kindly please help me in this regards and one more thing i just want to inform you all this is our production server.
I think you would understantd how criticality is this.
I really appreciate for your feedback. thank you so much,
Raghu
for the 2nd step it is haing a very huge message.
so here i have pasted some part of the message. i think this is more then enough for you all.
SqlDumpExceptionHandler: Process 3920 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
* *******************************************************************************
*
* BEGIN STACK DUMP:
* 01/17/08 15:30:57 spid 97
*
* Exception Address = 0041656B
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred writing address 00380008
* Input Buffer 124 bytes -
* EXEC MIS..law_ac_totals "G&A", 2008, 3, 100, 204, "emferrario"
*
*
* MODULE BASE END SIZE
* sqlservr 00400000 00CBEFFF 008bf000
* Invalid Address 77F80000 77FFBFFF 0007c000
* ADVAPI32 7C2D0000 7C334FFF 00065000
* KERNEL32 7C570000 7C623FFF 000b4000
* Invalid Address 77D30000 77D9EFFF 0006f000
* MSVCP71 7C3A0000 7C41AFFF 0007b000
* MSVCR71 7C340000 7C395FFF 00056000
* opends60 41060000 41065FFF 00006000
* SHELL32 7CF30000 7D175FFF 00246000
* GDI32 77F40000 77F7BFFF 0003c000
* USER32 77E10000 77E6EFFF 0005f000
* SHLWAPI 70A70000 70AD5FFF 00066000
* msvcrt 78000000 78044FFF 00045000
* COMCTL32 71710000 71793FFF 00084000
* sqlsort 42AE0000 42B6FFFF
View 2 Replies
View Related
Apr 2, 2008
When using a typed dataset in VS 2005 with SqlCe 3.1, it conitinues to make the param.dbtype for the GUID to be an 'object'. When as far as I can tell it needs to be a 'Guid' dbtype. So each time I modify the typed dataset I go in and manually change the various bugs and things work fine. My question is whether or not anyone knows a better work around for this after finding themselves in this situation.
I've attached a code sample of the erroneous designer code, and highlighted the line that should instead be set to DbType.Guid.
Code Snippet
global::System.Data.SqlServerCe.SqlCeParameter param = new global::System.Data.SqlServerCe.SqlCeParameter();
param.ParameterName = "@ProjectQuoteGUID";
param.DbType = global::System.Data.DbType.Object;
param.Size = 16;
param.IsNullable = true;
param.SourceColumn = "ProjectQuoteGUID";
this._commandCollection[1].Parameters.Add(param);
Thanks
View 2 Replies
View Related
Jan 20, 2006
{
//setup the connection and connection string
SqlConnection addTutor = new SqlConnection();
addTutor.ConnectionString = "data source=QUAKEMASTER;" +
"initial catalog=DThomas;uid=sa;password=**********;";
string InsertTutor = "INSERT INTO Tutors (firstname, lastname, tutorID, office)Values(@firstName, @lastName,@tutorID,@location)";
SqlCommand insertTutor = new SqlCommand(InsertTutor);
insertTutor.Connection = addTutor;
//populating an array of sqlParameters performs the same function as an SqlParameters.Add
SqlParameter[] param = new SqlParameter[4];
param[0] = new SqlParameter("@firstName",txtFirstName.Text);
param[1] = new SqlParameter("@lastName",txtLastName.Text);
param[2] = new SqlParameter("@tutorID",int.Parse(txtTutorID.Text));
param[3] = new SqlParameter("@location",listOffice.SelectedItem.ToString());
try
{
//open the connection
addTutor.Open();
//execute the NonSqlQuery
insertTutor.ExecuteNonQuery();
//close the connection
insertTutor.Connection.Close();
}
catch (Exception ThatFeckedUp)
{
throw ThatFeckedUp;
}
finally
{
addTutor.Close();
}
}
Sure i've missed something just not sure what.....
View 8 Replies
View Related
Nov 13, 2006
Hello,
When I run my report from within visual studio 2005 it generates just fine.
However, when I run the report from the reporting services local web site I get the following error. What do I need to do to fix this (temporarily turning off .net security uusing caspol didn't work).
An error occurred while executing OnInit: Request for the permission of type 'System.Security.Permissions.EnvironmentPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed
View 9 Replies
View Related
Feb 9, 2006
Hi
I am using Oledb Provider for Db2 from IBM. (There is another one from microsoft)
Through this provider I am constructing a oledb connection manager.
This connection manager I am Using in Oledb Source adapter.
Now when I set Data Access mode as Table or view I Am able to preview the data.
But when I use Data Access Mode as SQL Command try to preview I get this Error
TITLE: Microsoft Visual Studio
------------------------------
Error at s_NEWMAPPING10 [EMP [1]]: An OLE DB error has occurred. Error code: 0x80040E21.
------------------------------
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC0202009 (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------
BUTTONS:
OK
------------------------------
I am not able to figure out how to resolve this.
View 3 Replies
View Related
May 21, 2007
Hi,
I created a package which passes some infornmations( through parameters) to its child package.
I need to do some processing in parent package based on execution status of child package.i.e.
if child fails then some operation and if child succeeds then other operation.
To determine the status of execution of child package I am using two differnt constraint ..one constraint is having value "Success" and other having value "Failure".
My problem is that when child packge is executed successfully the constraint with value = "Success" works properly but when child fails the constraint with value "Failure" does not work.
-Prashant
View 4 Replies
View Related
Jun 14, 2007
Hi all -
I know this is prolly an old one but I would certainly appreciate some assistance =)
environment:
SERVER (IIS6, .NET2.0, SQL2005)
CLIENT (WIN2000,IE6,VBScript)
I have an aspx that is invoking ado on the clientside. I read somewhere that javascript does not support connecting to SQL clientside. I may be wrong. In any case I am using VBScript on the client.
I am using something like this to invoke the script
Code Snippet
<a href="#" onclick="doReport()">Click Here</< FONT>a>
doReport() looks something like this ...
Code Snippet
' ========================================================
sub doReport()
Dim stSQL, rs, oWord, oDoc, oRng
Dim stCurrentSection, stTemplatePath, dtNow, dtSOR, dtLastDataPoint
Dim iLastDataPoint
set oWord = CreateObject("Word.Application")
stTemplatePath = "http://crivm-ccdev/ccprocharts/supportfiles/CCWordReport.doc"
set oDoc = oWord.Documents.Open(stTemplatePath)
oWord.visible = true
stCurrentSection = "a"
loadDebug
'loadLive
openConnection()
' == THIS DB CALL GETS 2 RESULT SETS
'set rs = getrsCustomerInfoReport(mstUnits, left(mstCycles,3), mstAppName)
set rs = getReadOnlyRS("sCC_GetCustInfo_Report '" & mstUnits & "','" & left(mstCycles,3) & "','" & mstAppName & "'")
' == POPULATE VARS FROM 1st RESULT SET
dtLastDataPoint = rs(0)
iLastDataPoint = rs(1)
set rs = rs.NextRecordset ' <== THIS IS WHERE IS ERROR IS
msgbox rs(0)
end sub
here is my openconnection sub ... oConn is global
Code Snippet
<script type="text/vbscript" language="vbscript">
'== Cursor Location
CONST adUseClient = 3
CONST adUseServer = 2
' == Cursor Type
CONST adOpenStatic = 3
CONST adOpenForwardOnly = 0
CONST adOpenDynamic = 2
' == Lock Type
CONST adLockReadOnly = 1
CONST adLockOptimistic = 3
CONST adLockPessimistic = 2
' ========================================================
sub openConnection()
Dim stConn
set oConn = CreateObject("ADODB.Connection")
'stConn = "DRIVER={sql server};Server=crivm-ccdevsql2005;Database=catcheck;Integrated Security=SSPI"
stConn = "Provider=SQLOLEDB;Server=crivm-ccdevsql2005;Database=catcheck;Integrated Security=SSPI"
'msgbox oConn.state & vbcrlf & stConn1
oConn.open(stConn)
'msgbox oConn.state
ApplySecurity
end sub
' ============================================================
Sub ApplySecurity
'gbhasDBAccess is a global variable on both client and server sides.
'Server side is set in seccheck.asp, which should be at the top of every page
'Client side is set in ApplySecurity() of ConnectServer.asp
Dim appRole
appRole = "appWriters,(tsvc123)"
oConn.Execute "sp_setapprole '" & split(appRole,",")(0) & "','" & split(appRole,",")(1) & "'"
End Sub
</< FONT></script>
Before we moved to SQL2005 I always used this
Code Snippet
' ========================================================
Function getReadOnlyMultRS(strSQL)
set rs = CreateObject("ADODB.Recordset")
rs.CursorLocation = adUseClient
rs.Open strSQL, oConn, adOpenForwardOnly, adLockReadOnly
'Disconnect the Recordset
Set rs.ActiveConnection = Nothing
'Return the Recordset
Set getReadOnlyMultRS = rs
End Function
and this always allowed me to use set rs = rs.nextresultset
but now that we switched to SQL2005 it does not seem to work. I have verified in sql studio that this sp does indeed return 2 resultsets
View 4 Replies
View Related
Jun 26, 2014
I have three databases and 40 tables within each database on my server, for each of the tables I want to know which SSIS package generates that table. Is there a script that can do this?
If the SSIS package does not create or populate a table on the server I then want to check if there is a stored procedure that populates this.
View 6 Replies
View Related
Apr 20, 2007
I created a package via the import wizard in SQL Server Management Studio. When I test-ran the package I realised it needed to be modified. How do I do this without using either Visual Studio, SSIS Designer or an XML editor, none of which is installed where the package was created? Ideally I want to hear that there's a way of redisplaying the package in the import wizard.
View 1 Replies
View Related
Dec 14, 2006
In the SQL Server Technical Article Microsoft .NET Data Provider for mySAP„¢ Business Suite
is a Code example of how to invoke a BAPI in .Net Code. First line shows how to create a Connection to SAP, but there is no information in which lib the SAPConnection type is stored:
SAPConnection con = new SAPConnection("ASHOST=<SAPserver>; CLIENT=<client>;SYSNR=<sysnr>;USER=<user>;PASSWD=<password>;LANG=<logon
language>");
con.Open();Does somebody can help me?
View 3 Replies
View Related
Jul 11, 2007
Hello
The following code does not function if I use SQLOLEDB if I omit the provide and default to ODBC OLE DB it works correctly. I am assume I am coding something wrong for a SQLOLEDB provide. Any help is greatly appricated.
VB Code
Public Function SqlExecuteResult(xSQL As String, sServer As String, sDatabase As String, sUserName As String, sPassword As String, sCaller As String, Optional bLog As Boolean = False) As Object
Dim oDB As Object
Dim oRS As Object
Set oDB = CreateObject("adodb.connection")
Set oRS = CreateObject("adodb.recordset")
oDB.open "driver={SQL Server};provider=sqloledb;server=" & sServer & ";database=" & sDatabase & ";uid=" & sUserName & ";pwd=" & sPassword & ";"
oRS.CursorLocation = adUseClient
oRS.CursorType = adOpenStatic
Set oRS.ActiveConnection = oDB
oRS.open xSQL
Set oRS.ActiveConnection = Nothing
Set SqlExecuteResult = oRS
oDB.Close
Set oDB = Nothing
End Function
Private Sub Form_Load()
Dim rs As Object
Set rs = SqlExecuteResult("exec NextEntry 'SentMessages'", "surecomp-bob", "pmsureus33", "sa", "", "")
MsgBox rs.fields(0)
End Sub
SQL proceedure
CREATE PROCEDURE NextEntry @CounterName Varchar(20) AS
begin
declare @counter int
select @counter = counter from counters where countername = @counterName
select @counter = @counter + 1
update counters set counter = @counter where countername = @countername
select counter from counters where countername = @counterName
End
GO
Thanks
Bob Jenkin
View 1 Replies
View Related
Jul 20, 2005
I posted this in the MS Access group, but no luck.------------------------------------------I've got another stored procedure in the same app that returns multiplerecordsets and the code works.But now I've written another SP and the code traps out with the 3251 message.The SP is writing two recordsets.When I run the SP in Query Analyzer, both recordsets appear.But when I step through the code, when the first RS should be there, it's"Closed" and nothing I've tried will make it open.Provider=SQLOLEDB.1 (which works on the other screen...)Seems like I've been here before, but I can't remember what the problem was.--PeteCresswell
View 7 Replies
View Related
Jun 14, 2006
While Creating a script task in Control Flow, I am getting "Package Validation Error". Here is the complete message:
Error at Validate File and Load Data: The task is configured to pre-compile the script, but binary code is not found. Please visit the IDE in Script Task Editor by clicking Design Script button to cause binary code to be generated.
(Microsoft.DataTransformationServices.VsIntegration)
As mentioned in the message, I opened the script IDE and added the code I need. When I close the VSA IDE, package designer displays the same error message.
The worst part of whole story is that if I close the package designer and reopen it, I find that all the code I wrote in the script task has been deleted by the package designer. This is not at all acceptable as I saved the package the and still lost all my work. I did all the coding from scratch for that task.
Please respond if anyone faced similar problem.
Thanks in advance!
Anand
PS: If any one from Microsoft is reading this, please see what you guys are coding there. Due to the buggy software you deliver, I am loosing my credibility.<P< P>
View 5 Replies
View Related
Aug 23, 2007
I have an SSIS Package that loads data from a log file. Prior to loading the data I need to prepare the file. I run a script that cleans the file. Then I import the flat file into SQL Server.
Log File Management Task
1. Run Unix Log File Task
2. Import the new log file (flat file) into SQL Server
Error
i.Unix.dtsx
Message: The script threw an exception: Exception of type 'System.OutOfMemoryException' was thrown.
Is this because the system is running out of memory? The RAM on the server is 4gb. Below is a sample of the script. The job doesn't always fail; there are times when the job executes with success and other times when it fails.
Script Source Code
-----------------------------------------------------------
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Collections.Generic
Imports System.IO
Imports System.Text
Imports System.Diagnostics
Imports System.Globalization
Imports Microsoft.VisualBasic
Imports System.Text.RegularExpressions
Public Class ScriptMain
'********** Begin Error Log Settings **********
'Dim sSource As String = "i.SSIS.Unix.FileManager"
'Dim sLog As String = "Application"
'Dim sMachine As String = "."
'Dim ELog As New EventLog(sLog, sMachine, sSource)
'********** End Error Log Settings **********
Public Sub Main()
'variables for the unix log file
Dim newFile As String = "D:iLogunixlog.txt"
Dim copyFile As String = "\server16iLogunixlog.txt"
'variables for working log files
Dim oldFile As String = "D:i empunixlog.txt"
Dim difFile As String = "D:i empunixdiff.txt"
Dim trimdiff As String = "D:i empunixdifft.txt"
Dim formatTemp As String = "D:i empunixlog_formatted.txt"
Dim errorFile As String = "D:i empunixlog_bad.txt"
'delete unixlog.txt copy unixlog.txt
'if the file is on the local server delete it and copy the new file over
'if the file is not present copy the new file over
Try
If File.Exists(newFile) Then
File.Delete(newFile)
File.Copy(copyFile, newFile)
Else
File.Copy(copyFile, newFile)
End If
While Not File.Exists(newFile)
System.Threading.Thread.Sleep(1000)
End While
'ELog.WriteEntry("FileCopy.Success".ToString(), EventLogEntryType.SuccessAudit, 4, CType(4, Short))
Catch ex As Exception
'ELog.WriteEntry("FileCopy.Failure" & ControlChars.CrLf & ex.ToString(), EventLogEntryType.Error, 2, CType(4, Short))
End Try
'open the old file; read backwards until we reach the carriage
'return and store that "seek" position; now open the new file and
'seek to that stored position. finally, read the rest of the file
'and write that data to the difference file.
' determine position of last line in the old file
Dim lastLine As Long = GetLastLinePosition(oldFile)
' get all data in new file starting at position determined above
Dim fi As New FileInfo(newFile)
Dim buffer(fi.Length - lastLine) As Byte
Dim fs As New FileStream(newFile, FileMode.Open)
Try
fs.Seek(lastLine, SeekOrigin.Begin)
fs.Read(buffer, 0, buffer.Length)
fs.Close()
' write that new data to the difference file
fs = New FileStream(difFile, FileMode.OpenOrCreate, FileAccess.Write, FileShare.None)
fs.Write(buffer, 0, buffer.Length)
fs.Close()
'ELog.WriteEntry("FileCopy.Success".ToString(), EventLogEntryType.SuccessAudit, 4, CType(4, Short))
Catch ex As Exception
'ELog.WriteEntry("FileCopy.Failure" & ControlChars.CrLf & ex.ToString(), EventLogEntryType.Error, 2, CType(4, Short))
End Try
'remove the partial row from the difference file
Try
TrimFinal(difFile, trimdiff)
'ELog.WriteEntry("TrimFinal.Call.Success".ToString(), EventLogEntryType.SuccessAudit, 4, CType(4, Short))
Catch ex As Exception
'ELog.WriteEntry("TrimFinal.Call.Failure" & ControlChars.CrLf & ex.ToString(), EventLogEntryType.Error, 2, CType(4, Short))
End Try
'perform the file formatting
sFormatFile(trimdiff, formatTemp, errorFile)
'
Dts.TaskResult = Dts.Results.Success
End Sub
Function GetLastLinePosition(ByVal fileName As String) As Long
Dim pos As Long = -1
Dim fs As New FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)
Try
fs.Seek(-2, SeekOrigin.End) ' -2 to skip a potential vbcrlf at the end of file
While fs.Position > 0
fs.Seek(-1, SeekOrigin.Current)
If fs.ReadByte = 10 Then
pos = fs.Position
Exit While
Else
fs.Seek(-1, SeekOrigin.Current)
End If
End While
fs.Close()
'ELog.WriteEntry("GetLastLinePosition.Success".ToString(), EventLogEntryType.SuccessAudit, 4, CType(4, Short))
Catch ex As Exception
'ELog.WriteEntry("GetLastLinePosition.Failure" & ControlChars.CrLf & ex.ToString(), EventLogEntryType.Error, 2, CType(4, Short))
End Try
Return pos
End Function
Sub TrimFinal(ByVal difFile As String, ByVal trimdiff As String)
Dim fi2 As New FileStream(difFile, FileMode.OpenOrCreate, FileAccess.Read)
Dim fo2 As New FileStream(trimdiff, FileMode.OpenOrCreate, FileAccess.Write)
Dim sr2 As New StreamReader(fi2)
Dim sw2 As New StreamWriter(fo2)
Dim line2 As String
Try
Do While sr2.Peek <> -1
line2 = sr2.ReadLine()
If (sr2.Peek <> -1) Then
sw2.WriteLine(line2)
End If
Loop
sw2.Flush() : sw2.Close()
sr2.Close()
fi2.Close() : fo2.Close()
'ELog.WriteEntry("TrimFinal.Success".ToString(), EventLogEntryType.SuccessAudit, 4, CType(4, Short))
Catch ex As Exception
'ELog.WriteEntry("TrimFinal.Failure" & ControlChars.CrLf & ex.ToString(), EventLogEntryType.Error, 2, CType(4, Short))
End Try
End Sub
Sub sFormatFile(ByVal currentFile As String, ByVal tempFile As String, ByVal errorFile As String)
Dim tfp As New Microsoft.VisualBasic.FileIO.TextFieldParser(currentFile)
Dim sw As New System.IO.StreamWriter(tempFile)
Dim swErrorFile As New System.IO.StreamWriter(errorFile)
tfp.TextFieldType = FileIO.FieldType.Delimited
tfp.SetDelimiters(",")
tfp.HasFieldsEnclosedInQuotes = True
tfp.TrimWhiteSpace = True
Dim fields() As String
Try
While Not tfp.EndOfData
Try
fields = tfp.ReadFields()
If fields.Length <> 23 Then
'write bad rows to error-file
swErrorFile.WriteLine(String.Join(",", fields))
Else
If fields(3) = "" And fields(13) = "" Then
sw.WriteLine(Chr(34) & String.Join(Chr(34) & "," & Chr(34), fields) & Chr(34))
ElseIf IsDate(fields(3)) = True OrElse fields(3) = Format(CDate(fields(3)), "yyyy-MM-dd HH:mms") And fields(13) = "" Then
sw.WriteLine(Chr(34) & String.Join(Chr(34) & "," & Chr(34), fields) & Chr(34))
ElseIf IsDate(fields(13)) = True OrElse fields(13) = Format(CDate(fields(13)), "yyyy-MM-dd HH:mms") And fields(3) = "" Then
sw.WriteLine(Chr(34) & String.Join(Chr(34) & "," & Chr(34), fields) & Chr(34))
ElseIf IsDate(fields(3)) = True OrElse fields(3) = Format(CDate(fields(3)), "yyyy-MM-dd HH:mms") _
And IsDate(fields(13)) = True OrElse fields(13) = Format(CDate(fields(13)), "yyyy-MM-dd HH:mms") Then
sw.WriteLine(Chr(34) & String.Join(Chr(34) & "," & Chr(34), fields) & Chr(34))
Else
swErrorFile.WriteLine(String.Join(",", fields))
End If
End If
Catch ex As Exception
'ELog.WriteEntry("sFormatFile.TFP.Failure" & ControlChars.CrLf & ex.ToString(), EventLogEntryType.Error, 0, CType(4, Short))
End Try
End While
tfp.Close()
sw.Close()
swErrorFile.Close()
File.Delete(currentFile)
File.Move(tempFile, currentFile)
'ELog.WriteEntry("sFormatFile.Success".ToString(), EventLogEntryType.SuccessAudit, 0, CType(4, Short))
Catch ex As Exception
'ELog.WriteEntry("sFormatFile.Failure" & ControlChars.CrLf & ex.ToString(), EventLogEntryType.Error, 0, CType(4, Short))
Finally
GC.Collect()
End Try
End Sub
End Class
-------------------------
Does my script seem okay for releasing the server memory usage?
Thanks.
View 1 Replies
View Related
May 20, 2005
I have a problem.
I m working on "light weight sql server" project.
and i want to execute .sql file through vb.net code with the help of sqldmo library and sqlns namespace.
but i donot know any method to directly execute the .sql file.
i am successfully making the full script of select database of sql server.
please help me....
thanks....
View 1 Replies
View Related
Jun 1, 2007
Hi..
I'm running a package that has a XML Task in the control flow. This task tranforms a XML file with a XSLT.
The file is about 2 megs on a daily basis, but at the end of the month there is a full dump of data that makes the file to be around 400 megs. There is where my problem is.
I run this on my 2 GB memory workstation and when the memory gauge on the task manager reaches about 1.5gb the package fails with an "Out of memory exception".
I also run this package on a 8GB Ram server, and same applies.
Is there any way of making this package utilize all the available memory, I even increased the virtual memory to see if that helped my issue, but nothing.
Thanks
View 10 Replies
View Related
Feb 8, 2008
I have searched extensively and not been able to find a solution to this problem.
The problem:
We have one SSIS package will sometimes 'finish' executing (or crash from a .NET exception) when it certainly has not made its way through all of the data flow components. There are no SSIS error messages, no warnings, and it never happens at the same location in the package's pipeline. The only thing that is instantly visible is a command window that flashes on the screen and disappears too quickly to see anything,.
Sometimes the package does actually complete without any problem, but most of the time, it does not.
What we see:
If the packages is being run through the "Execute Package Utility" (by double clicking the dtsx file), after a bit, a command window flashes on the screen and instantly disappears (no text is visible), then the €œExecute Package Utility€? disappears. The event viewer of the machine then shows:
Source: .NET Runtime 2.0 Error
Category: None
Event ID: 1000
Type: Error
Description: Faulting application dtexecui.exe, version 9.0.3042.0, stamp 45cd726d, faulting module dtspipeline.dll, version 2005.90.3042.0, stamp 45cd721f, debug? 0, fault address 0x00019a66.
If the package is running within visual studio, again the command window flashes on the screen, then the "Execution has completed" prompt appears, but any "running" component remains Yellow (no red), both within the data flow and control flow (we do not have any event handlers set up). Neither our SQL Log provider, nor the "Execution Results" tab in visual studio show any type of error message... all SSIS messages just stop right in the middle of the many OnPipelineRowsSent log events (so there is no PackageEnd log event when this happens). The event viewer on the machine contains no useful messages when running within visual studio.
And other packages:
Are fine. This is only the case for this one package... we have nearly a dozen other packages, all very similar in design, that complete without issue.
We have also tried re-creating this troublesome package from scratch with no avail.
<!--[if !supportLineBreakNewLine]-->
About the package:
The Data Flow is pulling rows from 3 different external SQL data sources (400k-500k rows total), sorting and merging the rows, performing some basic lookups, then SCD'ing the results. This Data Flow is executed multiple times within 2 nested for loops (these nested loops give us particular dates, i.e. years 2000 through 2008, then months 1 through 12 for each year). There is not a single script task in the package. The problem seems to happen most as the data is being pulled from the sources and merged together, but it is not limited to this area.
<!--[if !supportLineBreakNewLine]--><!--[endif]-->
The environment:
We€™ve tried to use multiple machines with the same result. The current machine specs are as follows:
SQL Server 9.0.3042 (SP2)
Windows Server 2003 R2, Enterprise x64 Edition, SP2
3.00GHz x 16 processors, all 64 bit
63.5 GB of RAM
Over 1 terabyte of hard disk space
.NET 2.0.50727.42
The package was designed using:
Visual Studio 2005 with SP1
Microsoft SQL Server Integration Services Designer - Version 9.00.3042.00
Anyone have an idea? Thanks in advance.
View 6 Replies
View Related
Apr 16, 2008
Hi,
I have one SSIS package which is written in Visual studio business intelligence tool. For that SSIS packages i have scheduled a job from SQL server management studio 2005. I mean i have scheduled a job in SQL server agent.
This job which i have scheduled contains 6 SSIS packages and the other 5 SSIS packages executes successfully but this only fails giving sone com.Interop exception.
But it is failing giving some com.interop exception. Not sure what type of error is this?
It give following type error:
Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 11:00:00 PM Error: 2008-03-27 23:00:00.81 Code: 0x00000000 Source: Execute DTS 2000 Package Task Description: System.Runtime.InteropServices.COMException (0x80040427): Execution was canceled by user. at DTS.PackageClass.Execute() at Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.Exec80PackageTask.ExecuteThread() End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 11:00:00 PM Finished: 11:00:00 PM Elapsed: 0.579 seconds. The package execution failed. The step failed.
I get the same error when i try to execute the package from Visual studio Business Intelligence tool.
Can you please help me out as to what is this "System.Runtime.InteropServices.COMException" exception occuring when scheduling or executing the job.
Thanks,
Ashok
View 1 Replies
View Related
Jan 16, 2008
Greetings everyone, I am attempting to build my first application using Microsofts Sql databases. It is a Windows Mobile application so I am using Sql Server Compact 3.5 with Visual Studio 2008 Beta 2. When I try and insert a new row into one of my tables, the app throws the error message shown in the title of this topic.
'((System.Exception)($exception)).Message' threw an exception of type 'System.NotSupportedException'
My table has 4 columns (i have since changed my FavoriteAccount datatype from bit to Integer)
http://i85.photobucket.com/albums/k71/Scionwest/table.jpg
Account type will either be "Checking" or "Savings" when a new row is added, the user will select what they want from a combo box.
Next is a snap shot of my startup form.
http://i85.photobucket.com/albums/k71/Scionwest/form.jpg
Where it says "Favorite Account: None" in the top panel, I am using a link label. When a user clicks "None" it will go to a account creation wizard, and set the first account as it's primary/favorite. As more accounts are added the user can select which will be his/her primary/favorite. For now I am just creating a sample account when the label is clicked in an attempt to get something working. Below is the code used.
private void lnkFavoriteAccount_Click(object sender, EventArgs e)
{
FinancesDataSet.BankAccountRow account = this.financesDataSet.BankAccount.NewBankAccountRow();
account.Name = "MyBank Checking Account";
account.AccountType = "Checking";
account.Balance = Convert.ToDecimal("15.03");
account.FavoriteAccount = 1;//datatype is an integer, I have changed it since I took the screenshot.
financesDataSet.BankAccount.Rows.Add(account);
//The next three lines where added while I was trying to get this to work.
//I don't know if I really need them or not, I receive the error regardless if these are here or not.
this.bankAccountTableAdapter1.Update(financesDataSet);
this.financesDataSet.AcceptChanges();
refreshDatabase();
}
the refreshDatabase() code is here:
private void refreshDatabase()
{
this.bankAccountTableAdapter1.Fill(this.financesDataSet.BankAccount);
//Aquire a count of accounts the user has
int numAccounts = financesDataSet.BankAccount.Count;
//Loop through each account and see which one is the primary.
for (int num = 0; num != numAccounts; num++)
{
//Works ok in frmMain_Load, but when my lnkFavoriteAccount_click calls this, it throws the error.
if (this.financesDataSet.BankAccount[num].FavoriteAccount == 1)
{
//Display the primary account on our home page. User can click the link label & be taken to their account register.
this.lnkFavoriteAccount.Text = this.financesDataSet.BankAccount[num].Name.ToString();
this.lnkFavoriteFunds.Text = this.financesDataSet.BankAccount[num].Balance.ToString();
break;
}
}
}
and my form_load code
private void frmMain_Load(object sender, EventArgs e)
{
refreshDatabase();
}
So, when I click on the lnkFavoriteAccount label, and my new row gets added, the app stops at the following line in my DataSet.Designer
[global:ystem.Diagnostics.DebuggerNonUserCodeAttribute()]
public byte FavoriteAccount {
get {
try {
return ((byte)(this[this.tableBankAccount.FavoriteAccountColumn]));
}
catch (global:ystem.InvalidCastException e) {
//Stops at the following line, this error was caused by 'if (this.financesDataSet.BankAccount[num].FavoriteAccount == 1)'
throw new global:ystem.Data.StrongTypingException("The value for column 'FavoriteAccount' in table 'BankAccount' is DBNull.", e);
}
}
set {
this[this.tableBankAccount.FavoriteAccountColumn] = value;
}
}
I have no idea what I am doing wrong, all of the code I used I retreived from Microsofts help documentation included with VS2008. I have tried used my TableAdapter.Insert() method and it still failed when it got to
if (this.financesDataSet.BankAccount[num].FavoriteAccount == 1)
in my refreshDatabase() method it still failed.
When I look, the data has been added into the database, it's just when I try to retreive it now, it bails on me. Am I retreiving the information wrong?
Thanks for any help you guys can offer.
Johnathon
View 1 Replies
View Related
Nov 26, 2007
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;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Data.SqlClient;
using System.Data.OleDb;
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
IDTSConnectionManager100 connMgr;
OleDbConnection sqlConn = null;
OleDbDataReader sqlReader;
public override void AcquireConnections(object Transaction)
{
connMgr = this.Connections.MyConnection;
sqlConn = (OleDbConnection )connMgr.AcquireConnection(null);
//sqlConn = (SqlConnection)connMgr.AcquireConnection(null);
}
public override void PreExecute()
{
base.PreExecute();
/*
Add your code here for preprocessing or remove if not needed
*/
OleDbCommand cmd = new OleDbCommand("SELECT CustomerID,TerritoryID,AccountNumber,CustomerType FROM Sales.Customer", sqlConn);
sqlReader = cmd.ExecuteReader();
}
public override void PostExecute()
{
base.PostExecute();
/*
Add your code here for postprocessing or remove if not needed
You can set read/write variables here, for example:
Variables.MyIntVar = 100
*/
}
public override void CreateNewOutputRows()
{
/*
Add rows by calling the AddRow method on the member variable named "<Output Name>Buffer".
For example, call MyOutputBuffer.AddRow() if your output was named "MyOutput".
*/
System.Data.OleDb.OleDbDataAdapter oLead = new System.Data.OleDb.OleDbDataAdapter();
//SqlDataAdapter oLead = new SqlDataAdapter();
DataSet ds = new DataSet();
System.Data.DataTable dt = new System.Data.DataTable();
//DataRow row = new DataRow();
oLead.Fill(dt,this.Variables.ObjVariable);
foreach (DataRow row in dt.Rows)
{
{
Output0Buffer.AddRow();
Output0Buffer.CustomerID = (int)row["CustomerID"];
Output0Buffer.TerritoryID =(int)row["TerritoryID"];
Output0Buffer.AccountNumber = row["AccountNumber"].ToString();
Output0Buffer.CustomerType = row["CustomerType"].ToString();
}
}
}
}
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)
thanks
kedarnath
View 4 Replies
View Related
May 14, 2007
Greetings,
I am using the IBM DB2 UDB for iSeries IBMDASQL OLE DB Provider to import data via query (in a tranformation task) to a SQL2000 server within a DTS package.
Works great! However, the provider does not save my (iSeries) password although I choose this option. I really need the password to be saved so that I can schedule the DTS package... I don't want to have to login and run the package each time (even IT guys take vacations). Or at least I don't want to have to login everytime the server is restarted...
Has anyone run accross this and found a solution?
Many thanks in advance.
Billy
View 5 Replies
View Related
Feb 21, 2007
Hi,
Given that Task.Validate() exposes the package's VariableDispenser, but LogProviderBase.Validate() doesn't...
http://msdn2.microsoft.com/fr-fr/library/microsoft.sqlserver.dts.runtime.task.validate.aspx
http://msdn2.microsoft.com/fr-fr/library/microsoft.sqlserver.dts.runtime.logproviderbase.validate.aspx
...I guess that simply means that I can't access a package's variables within a custom log provider? Can anyone comment/confirm? Any other options/routes to achieving the same..?
We live in hope,
Tamim.
View 3 Replies
View Related
May 15, 2007
Greetings,
I am using the IBM DB2 UDB for iSeries IBMDASQL OLE DB Provider to import data via query (in a tranformation task) to a SQL 2000 server within a DTS package.
Works great! However, the DTS package(?) does not save my (iSeries) password although I choose this option. I really need the password to be saved so that I can schedule the DTS package... I don't want to have to login and run the package each time (even IT guys take vacations). Or at least I don't want to have to login everytime the server is restarted...
Perhaps I am ignorant, but, I can not find any configuration setting within DTS that will allow me to save the password for the IBMDASQL OLE DB provider. Nor have I been able to find a suitable solution to this by using the keywords search string of "package configuration" within the MSDN forums.
As a workaround, I can use the an ODBC connection along with a DSN file to save the username and password. However, I do not want this information sitting out there in plain text. I would like to use the OLE DB provider and store the username and password on the SQL server as the properties dialog says that I should be able to. Or I can define a UDL (still text file).[Or I can use an ODBC connection set up as a system DSN - I want to use the OLE DB provider]
I've read about adding a connection string to .Net's machine.config file - but, I do not want to use the .NET OLE DB provider and, again, I do not wish to store username and password information in plain text.
Is this a bug in as much as how SQL Server 2000 uses the IBMDASQL OLE DB provider?
I've called and spoke to an IBM tech in regard to this and was informed that the dialog box and error regarding the password are not IBM generated, but rather MS generated.
I hope that there is a solution to my problem.
Has anyone run accross this and found a solution?
Many thanks in advance.
Billy
View 5 Replies
View Related
