programming a dts package
-
Hi!
I have a c# application where the user specified a database, and I have to export some data from that database to a csv file. Now, to do that, I would normally create a dts package. How can I do that from an application?
Thanks!
-
Not exactly sure what you are asking. Are you asking how to create a DTS package using .NET? If so, I wouldn't do that, would be better to create the package and then execute it using .NET. That can be done, it's fairly straightforward.
Are you asking how to create a CSV file from a set of data? Probably many ways to do that. One way would be to execute a DTS package from your app, as previously mentioned. Another way would be to load your data into a datareader/dataset and export it to a file using the classes in the System.IO namespace.
Do you know if the DTS runtime is going to be present where your C# app will run? If so, then maybe your best bet is to go ahead and create a DTS package, put it into a file (structured storage file) and then execute it from your app. If the DTS runtime won't be present on the machine where your app will run, then you might have to end up doing it manually (create the CSV file from code).
Anyway, need more info about what you really want to do, I think.
-
I am certainly not an expert in SQL server or C#. That said, my question is, "Can you not create the DTS job in the SQL db then execute the DTS job from a stored procedure which is then invoked from the C# app?"
Alan Monteath
-
That is most certainly possible, I've got some apps that do this very thing. You can find a lot of resources on how to do it, here's a couple (Google for "execute DTS c#" and you'll find a lot more info):
http://www.15seconds.com/issue/030909.htm
http://www.sqldev.net/dts/ExecutePackage.htm#Visual%20Csharp
Can send you some sample code if you're interested.
-
Actually, my question is this (I narrowed it down a bit): I have a DTS package created in SQL, and I defined global variables on Data source, initial catalog etc. Now, when I want to execute that package from code (LoadFromStorageFile method), and change the global variables, I get the following error during execution: "Type mismatch. Missing parameter does not have a default value."
What could this be?
-
The code is this:
try
{
Package2Class package = new Package2Class();
Object pVarPersistStgOfHost = null;
package.LoadFromStorageFile(
"someDTS.dts", null, null, null, "dtsPackage",ref pVarPersistStgOfHost);
foreach (GlobalVariable global in package.GlobalVariables)
{
switch (global.Name)
{
case "SourceDataSource":
global.Value = DataSourceServer;
break;
case "SourceInitialCatalog":
global.Value = DataSourceDatabase;
break;
case "SourceUserID":
global.Value = DataSourceUsername;
break;
case "SourcePassword":
global.Value = DataSourcePassword;
break;
case "DestDataSource":
global.Value = DataDestinationPath +
"\\CSVFile.csv";
break;
case "SourceSQLStatement":
global.Value =
"SELECT * FROM Table";
break;
}
}
package.Execute();
foreach (Step2 step in package.Steps)
{
int outError;
string outSource;
string outDescription;
string out1;
int out2;
string out3;
step.GetExecutionErrorInfo(
out outError, out outSource, out outDescription, out out1, out out2, out out3);
}
package.UnInitialize();
package =
null;
-
Had this happen too. It's apparently a bug in DTS. You have to remove all the variables and then re-add them with their values.
For example, create a NameValue collection that has your variable names and values (you can create the collection on the fly, by reading the variable from the package if you want), then do something along the lines of:
if (packageVariables.Count != 0)
{
foreach (string keyName in packageVariables.AllKeys)
{
dtsPackage.GlobalVariables.Remove(keyName);
dtsPackage.GlobalVariables.AddGlobalVariable(keyName, packageVariables[keyName]);
}
}
-
dmbaker, thanks, that worked just fine
Who would've thought that it's a bug in DTS.... Shouldn't there be a patch or service pack for that?Anyway, thanks a million!
-
Ok, now... I got into the problem a bit more... Now I'm building the dts from scratch... The goal is to build a dts to export some data (sql statement is a must) from one table to a text file. I've built a package, connections (two of them, one for the sql server, and one for the text file), steps (only one), one task with one custom task, and one transformation object (there is only one column in the select clause in sql statement). However, when I execute the package, I get the following error:
"Incomplete file format information - file cannot be opened."
DataSource property for the text file is "DTSFlatFile". I can't figure out what's wrong... help...
-
Is there any reason you need all those extra objects? Are you simply trying to export the data from the one column to a text file? To do that, all you need is the source connection and the target text file, then use the "Transform Data" task to connect the two. Then you can go into the transform (double-click the line connecting the source and target or right-click it and choose "Properties") and define your source query and how to map it to the destination. If you need to transform the data somehow, then you can do that also with custom mapping in the Transform.
-
Well, yes, but the problem is that I have to do all that from code (C#). There is no problem while creating the package in designer - that works just fine. I'm trying now to write the complete code for the dts, but when executing, I get that error message.
-
OK, now I'm a bit confused...are you still just trying to execute a package you created (executing it from your code)? Sounds like you're trying to create the package from scratch in code, if that's the case then I don't think I can help.
OTOH, if you're stull trying to just execute the package, then I'm still confused about exactly what you're trying to do. You say you want to export results of a SQL statement to a text file, is that right? Does your package look like this?
In this case, I've got a dynamic properties task that lets me set the target file name for my text file and connection properties for my SQL Server connection, based on global variables (which get set in my code before executing the package). The transform task contains my SQL statement and the column mappings to export the SQL into the text file. That's all that's needed to export data, if you're doing something else then either I don't understand what you're doing or maybe you're approaching the problem the wrong way.
Apologies if this *is* what you're doing, since I can't see what you want to do it's hard to figure out exactly what the problem is.
If this looks like what you're doing, please let me know and I'll see if I can find more information. So far, only clues have been that either you haven't mapped your destination columns, or if it's an existing text file maybe it's not writable for some reason (something else has it open or never closed it, or you don't have sufficient privs to write to it or replace it).
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply