|
I am trying to create a DTS package using C#. I have done this in VB6 without any problems. However, when I convert everything over to C# I
get the 'System.InvalidCastException'. I am running windows 2000, SQL
2000 sp3a. I have also followed the instructions in using DTS provided
by "http://sqldev.net/DTS/dotnetcookbook.htm".
The error is occurs at this line:
<br />
DTS.DataPumpTask DT = (DTS.DataPumpTask)package.Tasks.New <br />
("DTSDataPumpTask"); <br />
Does anyone know what causes this and is there a fix for it?
Below is a copy of the code.
<br />
public void createPackag() <br />
{ <br />
DTS.Connection oConnection = <br />
(DTS.Connection)package.Connections.New("Microsoft.Jet.OLEDB.4.0"); <br />
<br />
<br />
oConnection.Name ="Connection 1"; <br />
oConnection.DataSource = "C:\\MySourceDB.MDB"; <br />
oConnection.ID = 1; <br />
oConnection.Reusable = true; <br />
oConnection.ConnectImmediate = false; <br />
oConnection.ConnectionTimeout = 60; <br />
oConnection.UseTrustedConnection = false; <br />
oConnection.UseDSL = false; <br />
oConnection = null; <br />
DTS.Connection oConnection2 = <br />
(DTS.Connection)package.Connections.New("SQLOLEDB"); <br />
oConnection2.Name = "Connection 2"; <br />
oConnection2.ID = 2; <br />
oConnection2.Reusable = true; <br />
oConnection2.ConnectImmediate= false; <br />
oConnection2.DataSource= "MyServerName"; <br />
oConnection2.UserID = "MyUserID"; <br />
oConnection2.ConnectionTimeout = 60; <br />
oConnection2.Catalog = "MyDestDB"; <br />
oConnection2.UseTrustedConnection = false; <br />
oConnection2.UseDSL = false; <br />
oConnection2.Password = "MyPassword"; <br />
oConnection2 = null; <br />
DTS.Step2 oStep = <br />
(DTS.Step2)package.Steps.New(); <br />
oStep.Name = "Copying Data from MyTable"; <br />
oStep.Description = "Copying Data from <br />
MyTable"; <br />
oStep.TaskName = "Copying Data from MyTable"; <br />
oStep.CommitSuccess = false; <br />
oStep.RollbackFailure = false; <br />
oStep.ScriptLanguage = "VBScript";
about this <br />
oStep.AddGlobalVariables = true; <br />
oStep.CloseConnection = false; <br />
oStep.ExecuteInMainThread = true; <br />
oStep.IsPackageDSORowset = false; <br />
oStep.JoinTransactionIfPresent = false; <br />
oStep.DisableStep = false; <br />
oStep.FailPackageOnError = true; <br />
package.Steps.Add(oStep); <br />
oStep = null; <br />
DTS.Task oTask = <br />
(DTS.Task)package.Tasks.New("DTSDataPumpTask"); <br />
oTask.Name = "Copying Data from MyTable"; <br />
DTS.CustomTask oCustomTask = oTask.CustomTask; <br />
oCustomTask.Name = "Copying Data from MyTable"; <br />
<br />
<br />
oCustomTask.Description = "Copying Data from <br />
MyTable to <br />
MyDestDB.MyTable"; <br />
DTS.DataPumpTask DT = <br />
(DTS.DataPumpTask)package.Tasks.New("DTSDataPumpTask"); <br />
DT.SourceConnectionID = 1; <br />
DT.SourceSQLStatement = "SELECT `TestField` <br />
FROM MyTable"; <br />
DT.DestinationConnectionID =2; <br />
DT.DestinationObjectName = "MyTable"; <br />
DT.ProgressRowCount = 1000; <br />
DT.MaximumErrorCount = 0; <br />
DT.FetchBufferSize = 1; <br />
DT.UseFastLoad=true; <br />
DT.InsertCommitSize = 0; <br />
DT.InsertCommitSize = 500000; <br />
DT.ExceptionFileColumnDelimiter = "|"; <br />
DT.ExceptionFileRowDelimiter = "\r\n"; <br />
DT.AllowIdentityInserts = false; <br />
DT.FirstRow = 0; <br />
DT.LastRow = 0; <br />
DTS.Transformation Trans = <br />
(DTS.Transformation)package.Tasks.New("DataPumpTransformCopy"); <br />
Trans.Name = "DirectCopyXform"; <br />
Trans.TransformFlags = 63; <br />
Trans.ForceSourceBlobsBuffered = 0; <br />
Trans.ForceBlobsInMemory = false; <br />
Trans.InMemoryBlobSize = 1048576; <br />
Trans.SourceColumns.AddColumn("TestField",1); <br />
<br />
<br />
Trans.DestinationColumns.AddColumn("TestField",1); <br />
DT.Transformations.Add(Trans); <br />
package.Tasks.Add(oTask); <br />
oCustomTask = null; <br />
oTask = null; <br />
<br />
<br />
} <br />
<br />
|
|
|
|
|
I was able to resolve this issue and thought I would post the solution so that others may benefit from it. The following example creates, saves, and executes a DTS package in C#. This example is setup to only DTS one field from one table but it can easily be modified to do a complete database.
This particular section of code gave me the most trouble so I thought I would point it out as well. My original code looked like this and would cause an invalidcastexception.
<br />
DTS.Task oTask = (DTS.Task)package.Tasks.New("DTSDataPumpTask");<br />
DTS.CustomTask oCustomTask = oTask.CustomTask;<br />
This was how I corrected it.
<br />
DTS.DataPumpTask2 oCustTask;<br />
DTS.Task oTask;<br />
oTask = pkg.Tasks.New("DTSDataPumpTask");<br />
oCustTask = (DTS.DataPumpTask2)oTask.CustomTask;<br />
Here is the entire example:
<br />
using System;<br />
using System.Drawing;<br />
using System.Collections;<br />
using System.ComponentModel;<br />
using System.Windows.Forms;<br />
using System.Data;<br />
using DTS = Microsoft.SQLServer.DTSPkg80;<br />
<br />
namespace MyDTSTest<br />
{<br />
public class Form1 : System.Windows.Forms.Form<br />
{<br />
private System.ComponentModel.Container components = null;<br />
private System.Windows.Forms.Button button1;<br />
public DTS.Package2Class pkg = new DTS.Package2Class();<br />
<br />
private void button1_Click(object sender, System.EventArgs e)<br />
{<br />
initpackage();<br />
}<br />
<br />
public void initpackage()<br />
{<br />
CreateConnections();<br />
CreatePackageSteps();<br />
DefinTasks(pkg);<br />
pkg.Name="MyCSharpDTSTest";<br />
pkg.Description = "CShart DTS Test";<br />
object MIA=System.Reflection.Missing.Value;<br />
pkg.SaveToSQLServer("MyServerName", "MyUserID", "MyPassword", <br />
DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, "","","",ref MIA,false);<br />
pkg.Execute();<br />
pkg.UnInitialize();<br />
pkg = null;<br />
}<br />
<br />
public void CreateConnections()<br />
{<br />
<br />
DTS.Connection oConnection = (DTS.Connection)pkg.Connections.New("Microsoft.Jet.OLEDB.4.0");<br />
oConnection.Name ="Connection 1";<br />
oConnection.DataSource = "C:\\MySoureDB.MDB";<br />
oConnection.ID = 1;<br />
oConnection.Reusable = true;<br />
oConnection.ConnectImmediate = false;<br />
oConnection.ConnectionTimeout = 60;<br />
oConnection.UseTrustedConnection = false;<br />
oConnection.UseDSL = false;<br />
pkg.Connections.Add(oConnection);<br />
oConnection = null;<br />
DTS.Connection oConnection2 = (DTS.Connection)pkg.Connections.New("SQLOLEDB");<br />
oConnection2.Name = "Connection 2"; <br />
oConnection2.ID = 2;<br />
oConnection2.Reusable = true;<br />
oConnection2.ConnectImmediate= false;<br />
oConnection2.DataSource= "MyServerName";<br />
oConnection2.UserID = "MyUserID";<br />
oConnection2.ConnectionTimeout = 60;<br />
oConnection2.Catalog = "MyDestDB";<br />
oConnection2.UseTrustedConnection = false;<br />
oConnection2.UseDSL = false;<br />
oConnection2.Password = "MyPassword";<br />
pkg.Connections.Add(oConnection2);<br />
oConnection2 = null;<br />
}<br />
<br />
public void CreatePackageSteps()<br />
{<br />
DTS.Step2 oStep = (DTS.Step2)pkg.Steps.New();<br />
oStep.Name = "Copying Data from myTableName";<br />
oStep.Description = "Copying Data from myTableName";<br />
oStep.TaskName = "Copying Data from myTableName";<br />
oStep.CommitSuccess = false;<br />
oStep.RollbackFailure = false;<br />
oStep.ScriptLanguage = "VBScript";<br />
oStep.AddGlobalVariables = true;<br />
oStep.CloseConnection = false;<br />
oStep.ExecuteInMainThread = true;<br />
oStep.IsPackageDSORowset = false;<br />
oStep.JoinTransactionIfPresent = false;<br />
oStep.DisableStep = false;<br />
oStep.FailPackageOnError = true;<br />
pkg.Steps.Add(oStep);<br />
oStep = null;<br />
<br />
}<br />
<br />
public void DefinTasks(DTS.Package2Class package)<br />
{<br />
DTS.DataPumpTask2 oCustTask;<br />
DTS.Task oTask;<br />
oTask = pkg.Tasks.New("DTSDataPumpTask");<br />
oCustTask = (DTS.DataPumpTask2)oTask.CustomTask;<br />
oCustTask.Name = "Copying Data from myTableName";<br />
oCustTask.Description = "Copying Data from myTableName to MyDestDB.myTableName";<br />
DTS.DataPumpTask2 oDataPump = (DTS.DataPumpTask2)oTask.CustomTask;<br />
oDataPump.SourceConnectionID = 1;<br />
oDataPump.SourceSQLStatement = "SELECT `MyField` FROM myTableName";<br />
oDataPump.DestinationConnectionID =2;<br />
oDataPump.DestinationObjectName = "myTableName";<br />
oDataPump.ProgressRowCount = 1000;<br />
oDataPump.MaximumErrorCount = 0;<br />
oDataPump.FetchBufferSize = 1;<br />
oDataPump.UseFastLoad=true;<br />
oDataPump.InsertCommitSize = 0;<br />
oDataPump.InsertCommitSize = 500000;<br />
oDataPump.ExceptionFileColumnDelimiter = "|";<br />
oDataPump.ExceptionFileRowDelimiter = "\n\r";<br />
oDataPump.AllowIdentityInserts = false;<br />
oDataPump.FirstRow = 0;<br />
oDataPump.LastRow = 0;<br />
CreateTaskTrans(oDataPump, oCustTask);<br />
pkg.Tasks.Add(oTask);<br />
oCustTask = null;<br />
oTask = null;<br />
<br />
}<br />
public void CreateTaskTrans(DTS.DataPumpTask DatPump, DTS.DataPumpTask2 CustTask)<br />
{<br />
DTS.Transformation2 oTransformation;<br />
oTransformation = (DTS.Transformation2)CustTask.Transformations.New("DTS.DataPumpTransformCopy");<br />
oTransformation.Name = "DirectCopyXform";<br />
oTransformation.TransformFlags = 63;<br />
oTransformation.ForceSourceBlobsBuffered = 0;<br />
oTransformation.ForceBlobsInMemory = false;<br />
oTransformation.InMemoryBlobSize = 1048576;<br />
oTransformation.SourceColumns.AddColumn("MyField",1);<br />
oTransformation.DestinationColumns.AddColumn("MyField",1);<br />
DatPump.Transformations.Add(oTransformation);<br />
<br />
}<br />
}<br />
}
|
|
|
|
|
How can i read XML from a string into the XmlTextReader class?
|
|
|
|
|
|
I asked the question friday, and am gettign to it today. The original question was:
I have two controls each of which have loops (we will call loop 1 and loop 2) that call a central function that sends and gets data via serial (we will call Communication function). Only one loop is to run at a time. The problem I am having is when the user requests loop 2 while loop 1 just started. Loop 2 alerts loop 1 to stop, but if loop 1 just started, it will still be calling the communication function until it finishes.
I hope this is makeing sense...
Meanwhile, loop 2 has started calling communication function as well and I am getting overlap. I cannot simple set a bit and exit the loop at the end. (It is a big program so you will just have to trust me.)
Is there a way to cache the values needed to be sent to the communications function?
Is there a clean way to exit a loop without it finishing?
Should I be focussing on trying to make the two (in reality 10) different loops NEVER call until all other loops are inactive, or should I be trying to make the communication function able to handle multiple calls?
Peter V. mentioned Mutex which I tried and is not working. I believe it is not working because each loop is creating a new instance of the Communication function. But is this were true, wouldn't the program treat each instance seperately. Thanks again in advance.
*****************
"We need to apply 21st-century information technology to the health care field. We need to have our medical records put on the I.T." —GW
|
|
|
|
|
I guess making the Communication method static with Mutex applied to it should solve the problem.
This will ensure other threads will be blocked until the Communication function exits.
As faar as exiting the loop without it finishing, the cleaner method will be pinvoke.
Guess it helps.
Live Life King Size
Alomgir Miah
|
|
|
|
|
Sorry for the delay. I have been woring on the Mutex/ lock idea and almost have it working. I am intrigued however by the PInvoke method. Can you let me know what this is? I have found little information about it, and how it would apply to exitting a loop is escaping me.
Thanks,
Dwayne
*****************
"We need to apply 21st-century information technology to the health care field. We need to have our medical records put on the I.T." —GW
|
|
|
|
|
can anybody tell me how to insert null value into foxpro table and how to set null to some fields in foxpro table?
My code is like this:
DataRow row=ds.Tables[0].NewRow();
row[0]="xxx";
row[1]="";
ds.Tables[0].Rows.Add(row);
....
Can anybody tell me what is wrong with my coding? Are there some good way to do this?
Thanks
|
|
|
|
|
Hi everybody,
I am hosting the ActiveX webbrowser control in one of my win forms. The webbrowser navigates to the blank page, and through mshtml I add my HTML to the control, this HTML also contains an <object> which is a COM object created in .NET .
Unfortunately, the webbrowser control does not allow this kind of content because of the XP2 security settings. One way to circumvent this is to use an HTA file, in which I host my COM object.
But is there any way to alter the security settings of the webbrowser control to allow this COM object to run?
Thank you for your help.
Gidon
|
|
|
|
|
Hi there
I want to create a software package that can program the PIC16F873 device in C#. I would like to be able to compile the file that needs to be placed onto the PIC using a package like MPLAB which will then give out a hex file and then would like to use my created package to program the hex file on to the PIC device. I need to know how to go about doing this. I am in need of desparate help. Please help. Thanks
|
|
|
|
|
What PIC programmer do you have? If none, then you need to get one. This will be either a serial or parallel programmer, although these days you can buy USB ones.
For example, I have a velleman K8048 programmer (built from a kit) which will handle most of the PIC16F series, and is a serial programmer. I plug it into my serial port, and I can use the software that came with it to program or erase the PICs.
Steve S
Developer for hire!
|
|
|
|
|
hi there
thanks for the reply
I have built a programmer that i need to test. It uses the serial interface. my project includes me to build a programmer and create the software package. I am still trying to test my programmer but am confused on where to start with programming my software package. Do you have any ideas where i can start?
thanks
|
|
|
|
|
Do you know how your programmer works, that is, what you need to do to the serial interface on it to program a chip in the programmer? This might include holding a particular line (DTR etc) high while transmitting data in a particular format.
I don't know how mine works (since I have software that came with it), but I could find out if I needed to by connecting to another PC and monitoring the signals.
As a first stage, you need to investigate what you can and can't do from the CLR to talk to the serial port h/w in your PC.
Steve S
Developer for hire!
|
|
|
|
|
How can i use sqldmo to link serveral tables and save it as a view
|
|
|
|
|
I am using normal File.Copy method to copy files from remote servers to my local hard drive for processing them.
The issue is file.copy is not reliable and also system gets hanged many time while copying over the network.
so I have decided to use Robocopy (FTP cannot be used because of security reasons)
As it is a command line utility Can anybody help me in how can I use it in my C# progamme without using command line.
|
|
|
|
|
Hi,
To start a process use the function:
System.Diagnostics.Process.Start();
This function has a couple of overloads providing different kinds of options.
Gidon
|
|
|
|
|
Thanks,
Is Robocoy is the best option for copying the files from Remote server ?
Regards,
Nitin.
|
|
|
|
|
Use Powershell. Google "C# Powershell". If you are a big command line fan and use C# then Powershell is your best friend.
|
|
|
|
|
Hi all!
I know this sounds lazy but can someone please explain to me what REGEX actually does? I mean I've read some tutorials but REGEX was vaguely explained and besides I'd rather learn its actual uses coming from experienced programers rather than text book explanations
Thanks for all your posts! Its greatly appreciated!
Kampai!!!
|
|
|
|
|
simply put, regular expressions are used to parse text data or determine if a particular sequence of characters are contained in a string.
It is a very powerful sub-language used for many many tasks.
|
|
|
|
|
Regex or Regular Expression is Designed to Solve Some Problem Like
1-Search and Replace text
2-Validate Text By Pattern
3-Parse Text
So Regular Expression is String Pattern That Used To Manipulate text
So How its Work?
Regx have special characters that you can use them to Create pattern that will be proccessed by regx and get the result
The charachter represnt many type you need to search for Like char,num,white spaceand the repitition of all of this
Some of this Chars are
\d match Any digit
\D match Any nondigit
\s match Any whitespace character
\SW match Any nonwhitespace
. match Any character except newline
* match 0 or more characters
+ match 1 or more characters
? match 0 or 1 characters
{n}match n characters
For examle if you have string like this "Codeproject is great site"
if you want to match codeproject but you forget its name is it codeproject or codesource or any thing like that you
may write pattern like the following
Code.
this will match codeproject
this a brief info about regx its a large topic you will find here at codeproject articles and tools that will help you Yo understand it
Some Links
regular-expressions[^]
Expresso[^]
The 30 Minute Regex Tutorial
[^]
MCAD
|
|
|
|
|
Regular expressions are used to match text patterns. Like * and ? in DOS, or like % and _ in SQL LIKE, only much more powerful.
---
b { font-weight: normal; }
|
|
|
|
|
Thanx all!! That was a very big help for me! I appreciate the effort! Thanx again!
Kampai!!!
|
|
|
|
|
I have an UserControl in the main form. This UserControl contains Panel and this panel contains PictureBox.
I've tried to use this event handler: PictureBoxOnMouseEnter. This handler changes mouse cursor into cross.
It's simple, but when cursor enters the area, it changes for one nanosecond, and then turns back into arrow. Why? Panel, UserControl and even the main forms don't contain any mouse event handlers. There's no code for changing mouse cursor into arrow, but it changes spontaneously. 
Now I discovered that I'm not able to handle MouseWheel events of that PictureBox. And - of course - Panel, UserControl and Form don't work with mouse wheel at all.
|
|
|
|
|
Could someone please give me an example in c# on capturing the right click and paste mouse events from any forms/applications. I can capture/monitor this using the keyboard but cannot do so when using mouse cut/copy and paste. Need help please.
I looked at the Globalhooksdemo (project in code project) in c# but only helped me on keyboard cut/copy and paste.
Thanks in advance
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
