C#, .NET and Text file to SQL vis DTS

[abraxas]

Hi,
I hope this isn't akin to a trekkie walking into a Star Wars convention since I've gone through a year of postings in this forum and found not what I need.

Essentially, I want to add a dialog box that a user can navigate to a .CSV that will be used as a datasource for a simple DTS package (.dts file, nothing fancy ) but I am lost with Connetion2 and Package2Class classes and how the Datasource property fits in. At the moment I'm not worried about dialog boxes. Programmatically setting the datasource property is what I wish.

The C# I created runs the external .DTS file successfully but building on it has led me to a whole day of web browsing and evening with the MSDN Library. No luck.

DTS.Package2Class package = new DTS.Package2Class();

package.LoadFromStorageFile(
fileName, // file path to .dts
password,
packageID,
versionID,
name,
ref pVarPersistStgOfHost);
package.Execute(); // Works!!

I had a look at Connections.NewDataLink(UDL) but couldn't work out how to set ms data link provider properties for text file if it can at all

I found Activex scripts that alluded to Connection2 and datasource = (datasource) so I sort of gather it can be done.

Some examples would be nice. MSDN library has me going in circles.

SQL2000 and VS .net 2003 enterprise are my primary tools under xp sp2

Whooaa! Just looked at the clock ,19 hours of this, think I've got thrombosis.

Thank you in advance
Anthony

 

[mrdenny]

You can change the source of the DTS connection using the dynamic properties task.

Add the Dynamic Properties Task to your DTS Package. Edit the Dynamic Properties Task. Click Add (New, whatever). Navigate to the Connection you want to edit, select DataSource (or equilivent). Set it to what you want. You can also have it update from a Global Variable.

You can then set the global variable from the command line when you launch the DTS package from your app.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[abraxas]

Hi Denny,
God, I was tired when I wrote that question. I took your advice with the Dynamic Task but I also read through a lot of Marcin Policht's tutorials on DTS which explained everything, Not an hour after getting your message I have exactly what I wanted. Thank you very much Denny!

To anyone interested in .NET and C#
Register your dtspkg80.dll with GAC (search for cookbook and dtspkg80 for how to do this)

Add reference to Microsoft.SqlServer.DTSPkg80 (see above source)

Using DTS = Microsoft.SqlServer.DTSPkg80;
namespace myDTS {
class myClass {
static void Main() {
string dtsFilePath = @"C:\mypath";
string importFilePath = @"C:\myDTSPath";
string password = null;
string packageID = null;
string versionID = null;
object pVarPersistStgOfHost = null;
DTS.Package2Class package = new DTS.Package2Class();

package.LoadFromStorageFile(
fileName, // file path to .dts
password,
packageID,
versionID,
name,
ref pVarPersistStgOfHost);
package.GlobalVariables.Item(1).Value = importFileName; // Assign a new file path

package.Execute(); // Oh yess. It Works!!
}
}
}
Here is a link to Marcin's tutorials

http://databasejournal.com/article.php/1503191

 

[abraxas]

Errata!!
Remove this:
package.GlobalVariables.Item(1).Value = importFileName;

Didn't do what I thought it was doing. It does not change the path GlobalVar. After some further research...
// Loop through global var collection
foreach(DTS.GlobalVariable global in package.GlobalVaraibles)
{
if (global.Name == "gFilePath") // GlobalVar set in DTS package
{
package.GlobalVariables.Remove(global.Name); //Clears out the value of gFilePath
}
}
package.GlobalVariables.AddGlobalVariable("gFilePath",importFileName);
package.Execute;

The CSV file i am importing is very similar to others I was testing the filenames of. It wasn't until I deleted some records that I noticed that the default datasource path in DTS package was not being over-written.

I noticed that a lot of tutorials and helper articles all seem to derive from a common ancestor. Decendants often mutate such as the .Add method as opposed to AddGlobalVariable and perpetuate these "errors"

The crown of research is owned by the one with most caffeine.
regards
Anthony