DTS question

Say I would use a ActiveX vbScript to query an LDAP server.

If i then would use that script to run in a DTS package. Witch steps do I have to take? Do i need to make a connection with the database, how do I create links.

I not looking for an out of the box solution, just maybe some good links to good sites witch explain this problem / question. My search wasn't that successfull

Greetings,
GodofredoYour DTS may contain nothing but an ActiveX script. You create connections inside the script using ADO, RDO, or whatever else you think you can use for it.|||yes but i want to fill up a table with the information retrieved by the ActiveX script|||you could just create a job that runs your activex script by querying ldap
and you could make that available to the next available step in the job. you dont really have to run dts. BUt if you want to, SQL Server has access to the ADSI oledb provider . in other words you could link a server to active directory and run a query based on your appropriate LDAP architecture.|||This is what a call a straightforward answer

Thanks,
Godofredo|||Hey, that's because he teaches this stuff...

Scott does DTS and LDAP...|||"at the same time baby!!!!"

is this thing on ?? 1.. 2.. 3 ...sqlsql

DTS package step to disable

Hi DBA's,

I have a DTS package that has steps 1 to 13. Say for a given situation I do not want the package to execute Step 11, 12 and 13 but steps 1 to 10 should be executed and the package should be treated as a sucess. Please confirm if the following approach is correct.

1. I go to Local Packages on the EM and then open up my package in designer view.
2. I go to step 11, right-click to go to WorkFlow properties. I go to Options tab and under execution I select disable this step.
3. I repeat the process for step 12 and step 13. Now 11 to 13 are disabled. I now execute the package.

Is the above correct?That will work in your description - but that is very hardwired - when would you not execute those steps ?|||Originally posted by rnealejr
That will work in your description - but that is very hardwired - when would you not execute those steps ?

Well step 11 is a stored proc which is failing now in the DTS package. So I want to disable step 11 and all the further steps. I intend to run step 11 in the QA. I tried to automate it by includusing step 11 in a DTS package. I am concerned with only step 1 to step 10. the rest of the steps 11-13 can be done manually.

DTS Package gives me a runtime error

Hi,

I am trying to create a DTS package dynamically. I have taken all these steps i.e.,

1. SN.EXE -K c:\DTS.KEY
2.tlbimp.exe "C:\program files\microsoft SQL Sever\80\Tools\Bin\dtspkg.dll" /out:c:\Microsoft.SQLServer.DTSPkg80.dll /Keyfile:c:\DTS.KEY
3.gacutil.exe -i C:\Microsoft.SQLServer.DTSPkg80.dll.

Now when i compile the code, i didnt get any compilation error. But when execute the code it gives me a runtime error which goes as given below :

An unhandled exception of type 'System.InvalidCastException' occurred in DTSFactory.exe

Additional information: QueryInterface for interface Microsoft.SQLServer.DTSPkg80.CustomTask failed.

I am getting this error near the line :oCustTask = (DTS.DataPumpTask2)oTask.CustomTask;

The following is the code.

using

System;

using

System.Drawing;

using

System.Collections;

using

System.ComponentModel;

using

System.Windows.Forms;

using

System.Data;

using

DTS = Microsoft.SQLServer.DTSPkg80;

namespace

DTSFactory

{

///<summary>/// Summary description for Form1.///This is assuming that all steps have been taken in the following document:///http://SQLDEV.NET/DTS/DotNetCookBook.htm///SN.EXE -K c:\DTS.KEY///tlbimp.exe "C:\program files\microsoft SQL Sever\80\Tools\Bin\dtspkg.dll" /out:c:\Microsoft.SQLServer.DTSPkg80.dll///Keyfile:c:\DTS.KEY///gacutil.exe -i C:\Microsoft.SQLServer.DTSPkg80.dll///These steps are needed for interop with dtspkg.dll///</summary>publicclass Form3 : System.Windows.Forms.Form

{

///<summary>/// Required designer variable.///</summary>private System.ComponentModel.Container components =null;private System.Windows.Forms.Button button1;public DTS.Package2Class pkg =new DTS.Package2Class();public Form3()

{

//// Required for Windows Form Designer support//

InitializeComponent();

//// TODO: Add any constructor code after InitializeComponent call//

}

privatevoid Form3_Load(object sender, System.EventArgs e)

{

}

///<summary>/// Clean up any resources being used.///</summary>protectedoverridevoid Dispose(bool disposing )

{

if( disposing )

{

if(components !=null)

{

components.Dispose();

}

}

base.Dispose( disposing );

}

#region

Windows Form Designer generated code///<summary>/// Required method for Designer support - do not modify/// the contents of this method with the code editor.///</summary>privatevoid InitializeComponent()

{

this.button1 =new System.Windows.Forms.Button();this.SuspendLayout();//// button1//this.button1.Location =new System.Drawing.Point(0, 0);this.button1.Name = "button1";this.button1.Size =new System.Drawing.Size(40, 56);this.button1.TabIndex = 0;this.button1.Text = "button1";this.button1.Click +=new System.EventHandler(this.button1_Click);//// Form3//this.AutoScaleBaseSize =new System.Drawing.Size(5, 13);this.ClientSize =new System.Drawing.Size(648, 429);this.Controls.Add(this.button1);this.Name = "Form3";this.Text = "Form3";this.Load +=new System.EventHandler(this.Form3_Load);this.ResumeLayout(false);

}

#endregion

privatevoid button1_Click(object sender, System.EventArgs e)

{

initpackage();

}

publicvoid initpackage()

{

CreateConnections();

CreatePackageSteps();

DefinTasks(pkg);

pkg.Name="MyCSharpDTSTest";

pkg.Description = "CShart DTS Test";

object MIA=System.Reflection.Missing.Value;

pkg.SaveToSQLServer("MyServerName", "MyUserID", "MyPassword",

DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, "","","",

ref

MIA,

false);

pkg.Execute();

pkg.UnInitialize();

pkg =

null;

}

publicvoid CreateConnections()

{

DTS.Connection txtConn,sqlConn;

txtConn = pkg.Connections.New("DTSFlatFile");

//label1.Text = label2.Text = "";

//for (int i=1;i<txtConn.ConnectionProperties.Count;i++)// label1.Text = label1.Text + "\r\n" + txtConn.ConnectionProperties.Item(i).Name;//for text connection//start

txtConn.ConnectionProperties.Item(1).Value = "C:\\FlatFiles\\1.txt";

txtConn.ConnectionProperties.Item(3).Value = 1;

txtConn.ConnectionProperties.Item(4).Value = "\r\n";

txtConn.ConnectionProperties.Item(5).Value = 1;

txtConn.ConnectionProperties.Item(7).Value = "|";

txtConn.ConnectionProperties.Item(9).Value = 1;

txtConn.ConnectionProperties.Item(10).Value = 0;

txtConn.ConnectionProperties.Item(11).Value = "\"";

txtConn.ConnectionProperties.Item(12).Value =

false;

txtConn.ConnectionProperties.Item(14).Value = 0;

txtConn.ConnectionProperties.Item(18).Value = 255;

txtConn.Name = "Text File (Source)";

txtConn.ID = 1;

txtConn.Reusable =

true;

txtConn.ConnectImmediate =

false;

txtConn.DataSource = "C:\\FlatFiles\\1.txt";

txtConn.ConnectionTimeout = 60;

txtConn.UseTrustedConnection =

false;

txtConn.UseDSL =

false;//end

pkg.Connections.Add(txtConn);

sqlConn = pkg.Connections.New("SQLOLEDB");

//for (int i=1;i<sqlConn.ConnectionProperties.Count;i++)// label2.Text = label2.Text + "\r\n" + sqlConn.ConnectionProperties.Item(i).Name;

sqlConn.ConnectionProperties.Item(3).Value =

true;

sqlConn.ConnectionProperties.Item(4).Value = "sa";

sqlConn.ConnectionProperties.Item(5).Value = "FCSUAT";

sqlConn.ConnectionProperties.Item(6).Value = "(local)";

sqlConn.ConnectionProperties.Item(19).Value = "DTS Designer";

sqlConn.Name = "Microsoft OLE DB Provider for SQL Server";

sqlConn.ID = 2;

sqlConn.Reusable =

true;

sqlConn.ConnectImmediate =

false;

sqlConn.DataSource = "(local)";

sqlConn.UserID = "sa";

sqlConn.ConnectionTimeout = 60;

sqlConn.Catalog = "FCSUAT";

sqlConn.UseTrustedConnection =

false;

sqlConn.UseDSL =

false;

pkg.Connections.Add(sqlConn);

}

publicvoid CreatePackageSteps()

{

DTS.Step2 oStep = (DTS.Step2)pkg.Steps.New();

oStep.Name = "Copying Data from myTableName";

oStep.Description = "Copying Data from myTableName";

oStep.TaskName = "Copying Data from myTableName";

oStep.CommitSuccess =

false;

oStep.RollbackFailure =

false;

oStep.ScriptLanguage = "VBScript";

oStep.AddGlobalVariables =

true;

oStep.CloseConnection =

false;

oStep.ExecuteInMainThread =

true;

oStep.IsPackageDSORowset =

false;

oStep.JoinTransactionIfPresent =

false;

oStep.DisableStep =

false;

oStep.FailPackageOnError =

true;

pkg.Steps.Add(oStep);

oStep =

null;

}

publicvoid DefinTasks(DTS.Package2Class package)

{

DTS.DataPumpTask2 oCustTask;

DTS.Task oTask;

oTask = package.Tasks.New("DTSDataPumpTask");

oCustTask = (DTS.DataPumpTask2)oTask.CustomTask;

oCustTask.Name = "Copying Data from myTableName";

oCustTask.Description = "Copying Data from myTableName to MyDestDB.myTableName";

DTS.DataPumpTask2 oDataPump = (DTS.DataPumpTask2)oTask.CustomTask;

oDataPump.SourceConnectionID = 1;

oDataPump.SourceSQLStatement = "SELECT `MyField` FROM myTableName";

oDataPump.DestinationConnectionID =2;

oDataPump.DestinationObjectName = "myTableName";

oDataPump.ProgressRowCount = 1000;

oDataPump.MaximumErrorCount = 0;

oDataPump.FetchBufferSize = 1;

oDataPump.UseFastLoad=

true;

oDataPump.InsertCommitSize = 0;

oDataPump.InsertCommitSize = 500000;

oDataPump.ExceptionFileColumnDelimiter = "|";

oDataPump.ExceptionFileRowDelimiter = "\n\r";

oDataPump.AllowIdentityInserts =

false;

oDataPump.FirstRow = 0;

oDataPump.LastRow = 0;

CreateTaskTrans(oDataPump, oCustTask);

pkg.Tasks.Add(oTask);

oCustTask =

null;

oTask =

null;

}

publicvoid CreateTaskTrans(DTS.DataPumpTask DatPump,

DTS.DataPumpTask2 CustTask)

{

DTS.Transformation2 oTransformation;

oTransformation =

(DTS.Transformation2)CustTask.Transformations.New("DTS.DataPumpTransformCopy");

oTransformation.Name = "DirectCopyXform";

oTransformation.TransformFlags = 63;

oTransformation.ForceSourceBlobsBuffered = 0;

oTransformation.ForceBlobsInMemory =

false;

oTransformation.InMemoryBlobSize = 1048576;

oTransformation.SourceColumns.AddColumn("MyField",1);

oTransformation.DestinationColumns.AddColumn("MyField",1);

DatPump.Transformations.Add(oTransformation);

}

}

}

Can anyone help me in solving this?

Thanks in advance.

Hi muser,

This seems to be an interop issue. I suggest you put the interop assembly together with your .exe file in the same directory and have another try.

Please make it as an unstrong named assembly for test.

DTS job not executing all tasks

SQL Server 2000
I created DTS package with several step.
When I run manually, all steps work OK.
When I schedule as job, only the first step runs. The next two steps
do not run.
The log simply says step ...xyz.. did not run
The owner of the job is the same as my regular XP login anme which is
also the same name I run it as manually.
Any ideas?
Thanks
jeffWhat is the login used to start the services of SQL agent.
from
Doller|||>What is the login used to start the services of SQL agent.
system account
I have more specifics about the problem.
I scheduled and ran only the package that makes up the first step of
the previously mentioned package.
This first step (package) runs a command file (DOS .bat) that ftp's
to a remote machine and does an "mget filename".
When this single package is executed manually it all works. I look in
the local machine folder and there is the file.
When run as a scheduled job the log shows the ftp mget getting the
file. However, the file does not exist on the local machine when the
job finishes.
As a matter of fact, if I manually execute the scheduled job, it also
shows that is gets file, but file does not exist on local machine.
jeff
On 19 Apr 2006 19:58:53 -0700, "doller" <sufianarif@.gmail.com> wrote:
>What is the login used to start the services of SQL agent.
>from
>Doller

DTS job not executing all tasks

SQL Server 2000
I created DTS package with several step.
When I run manually, all steps work OK.
When I schedule as job, only the first step runs. The next two steps
do not run.
The log simply says step ...xyz.. did not run
The owner of the job is the same as my regular XP login anme which is
also the same name I run it as manually.
Any ideas?
Thanks
jeffWhat is the login used to start the services of SQL agent.
from
Doller|||>What is the login used to start the services of SQL agent.
system account
I have more specifics about the problem.
I scheduled and ran only the package that makes up the first step of
the previously mentioned package.
This first step (package) runs a command file (DOS .bat) that ftp's
to a remote machine and does an "mget filename".
When this single package is executed manually it all works. I look in
the local machine folder and there is the file.
When run as a scheduled job the log shows the ftp mget getting the
file. However, the file does not exist on the local machine when the
job finishes.
As a matter of fact, if I manually execute the scheduled job, it also
shows that is gets file, but file does not exist on local machine.
jeff
On 19 Apr 2006 19:58:53 -0700, "doller" <sufianarif@.gmail.com> wrote:

>What is the login used to start the services of SQL agent.
>from
>Doller

DTS issues...

When executing a DTS Package from an ASP page, the steps of the package are executed in the wrong order, but it's the same wrong order every time. Any ideas?Cross post...

http://www.dbforums.com/t993006.html

Stay with your original thread...

DTS issues...

When executing a DTS Package from an ASP page, the steps of the package are executed in the wrong order, but it's the same wrong order every time. Any ideas?What doe sthat mean?

They could thread out, depending on how you coded it?

Where in Jersey?

http://members.tripod.com/~mugsy11/heyrube.html|||how does it run locally?|||Meaning that the steps in the package are coded correctly. I create a table, then populate the new table, then export to an excel file. Running it from Enterprise Manager works like a champ every time, but when running it from an ASP page, it always tries to populate before creating.

Now after trying to tweek a few things, my code is screwy and I'm get server errors. I'll get it sorted out and post the exact errors I was getting.|||Does it have to be excel?

Can it be csv?

How about a sproc...

Even if it needed to be a xls, you could use a com object I guess...

I've seen DTS do some weird stuff...especially if this is a versioned package...

Can you recreate a brand new package and try it?

I know this is a stupid question, but you do have workflow steps in there...right?

I'd still go with a sproc....|||Originally posted by Brett Kaiser
Does it have to be excel?

Can it be csv?

How about a sproc...

Even if it needed to be a xls, you could use a com object I guess...

I've seen DTS do some weird stuff...especially if this is a versioned package...

Can you recreate a brand new package and try it?

I know this is a stupid question, but you do have workflow steps in there...right?

I'd still go with a sproc....

Yeah, the workflow steps are in there, and you just answered my next question. I was thinking about calling a sproc to execute it, so now that you said that, I'm going to give that a try.

And I'm in the south Jersey area by the way. Erial to be exact....about 5 miles south of Deptford.|||When you said south...you ain't lion

How far to AC?

http://www.mapquest.com/maps/map.adp?country=US&countryid=250&addtohistory=&address=&city=Erial+&state=NJ&zipcode=&submit=Get+Map

Flyer fan?

Come on, you can admit it...

Go Devils...|||Oh, and let me ask...

Do you or someone you know own a confederate flag?|||Originally posted by Brett Kaiser
When you said south...you ain't lion

How far to AC?

http://www.mapquest.com/maps/map.adp?country=US&countryid=250&addtohistory=&address=&city=Erial+&state=NJ&zipcode=&submit=Get+Map

Flyer fan?

Come on, you can admit it...

Go Devils...

I'm 20 minutes or so from AC depending on how heavy my foot is that day. And I'm not a fan for ANY Philly team. I'm from the midwest originally, so that's where my loyalties are. And as far as hockey goes, I'm more of a Redwings fan than anything I guess, but the Rangers are pseudo-cool.|||A transplant TO NJ?

There's a switch...

btw, in your sproc I'd use xp_cmdshell and ftp to get the data out...

How/ why are you creating this table?

What the data source?|||Yeah. Was in the Navy for 9 years and got dropped off on the east coast and just stayed. It's a long story. And every long story involves a woman, so I'll leave it at that for now.

Every month, our affiliates require a report(xls) that contains referrals, items purchased, costs and prices, etc. It's a different query for every affiliate and until recently, the person in charge of this was running each query in QA and then saving the results to an xls file. When this duty was passed to me, I quickly decided that the was NO way that I was going to do it all manually like that. So I put together the DTS package that works just fine for me because I have all the permission and rights that a person could have, so I would just run the package once a month.

Soon after, the powers that be, who also have large plush offices and are technical idiots, decided that they wanted to be able to check this data when and where ever they want to, so I added a link to our intranet, and that's where my problem started.|||Yeah, I set that up for 1 project...

Each report has it's own sproc...

Each report is in a report table which contains report names, sproc names, if it's batch or online (batch save it to a file location, online is saved to their local through a browswer)

The browser executes a job when they select a report that runs the sproc.

That's important becuase we found if the sproc took a whil, the browser would hang...basically started another independant thread...

The report sprocs would create csv files with a header...like..

Select '"'+LTrim(RTrim(IsNull(SignOffEntity,'')))+'"'
+',"'+LTrim(RTrim(IsNull(LedgerSumAmount,'')))+'"'
+',"'+LTrim(RTrim(IsNull(LedgerNumOfRows,'')))+'"'
From
( Select 0 As SQLGroup, 'ATS Sign-Off Entity' As SignOffEntity
, 'Ledger Balance' As LedgerSumAmount, 'Ledger Count' As LedgerNumOfRows
Union All
Select 1 As SQLGroup, a.SignOffEntity
, Convert(Char(15),Sum(IsNull(b.Amount,0))) As LedgerSumAmount
, Convert(Char(15),Count(b.Entity)) As LedgerNumOfRows
From ATS_SignOff_Entity a (NoLock)
Left Join (Select Entity, IsNull(Amount,0) As Amount
From Ledger_Detail c (NoLock)
Where Account_Type In ('4', '5', '6', '7')
Union All
Select Entity, IsNull(Convert(money,BOY),0) As Amount
From tblAcct_LedgerBalance d (NoLock)) b
On a.Entity = b.Entity
Group by a.SignOffEntity
Union All
Select 2 As SQLGroup, 'TOTAL'
, Convert(Char(15),Sum(IsNull(b.Amount,0))) As LedgerSumAmount
, Convert(Char(15),Count(b.Entity)) As LedgerNumOfRows
From ATS_SignOff_Entity a (NoLock)
Left Join (Select Entity, IsNull(Amount,0) As Amount
From Ledger_Detail c (NoLock)
Where Account_Type In ('4', '5', '6', '7')
Union All
Select Entity, IsNull(Convert(money,BOY),0) As Amount
From tblAcct_LedgerBalance d ) b
On a.Entity = b.Entity
) As A

Notice that it's just 1 column, because all of the columns are concatenated...notice too the column conversions to char where needed...

Also notice the trailer or total row...they're kept in the correct order by the SQLGroup Column...

DTS Issues

Hi,

I am using Data transformation services to transform my data, from one
data model to another.

My issue is executing some of the steps take a very long time(hours), I
am doing so activex processing, but nothing too funky, I end having to
stop the server with no errors logged.

Any clues, ideas.

Thanks

JackHow much data are you transferring?
Is it a straight copy of data from one place to another?

You may wish to describe each step in your DTS process, including and SQL and ActiveX processing that is used - otherwise we can't give you much of a help!

It may also be worth telling us what you want your DTS to actually be doing, as there may be more efficient ways to do this in the first place :)

Kind regards,
George|||There is a asbout 200 mb of data 40 tables, all the data is being transformed forom one data model to another,

the tasks balk on 4 tables.
table a - 1 table 90000+ records lots of data this would be 30% of the db 3 table joing into 1
table b - 1 table 1228 records nothing much- 3 table join into 1
table c - 1 table 40000+ records nothing much as well 2 table join into 1
table d - 1 table 25000+ records nothing much this table is the same as table b - 3 table join merge into 1

although the one table with the bulk of tha data 40% transforms properly with no issues 225000+ records

I am testing for null on every field that could be null.

The data is being transformed from MSSQL to MSSQL.
All the transformation tests work correctly, and parse correctly
I have created separate packages for the remaining tables with still the same problems.

Let me know if you need other details

Jack|||What version of SQLServer?

You need to identify which steps are taking so much time, via
package logging, or logging from your ActiveX, or profiling.

Usually this is caused by SQL inefficiency, locking, and/or transactional recording. Are you using NOLOCK hints where possible?

Your db logging level can slow things down too - Recovery Model 'full' vs. 'simple' adds a lot of weight. You can use bcp to avoid this.|||I am using MSSQL 2000 sp3.

I am using the package loggin and it's shows nothing other than started at??? it never completes

I will add the activex logging. not sure waht you mean by profiling.

Can I transform the data with a BCP.

Thanks

Jack|||Right the DTS logging doesn't seem to checkpoint until completion so it's often unreliable.

So try this...and probably easier than activex logging ...if you're still trying to identify the step, then log to the database - i.e. create an Audit table, and before each step, log the current time and step # using a straight insert/update. This way you can control the logging.

It also sounds like you haven't looked at the running processes while your DTS is running - see Ent Mgr under Management -> Current Activity -> Locks/ Object and Locks/Process ID - that will probably show you some locking you didn't expect and tells you exactly your problem.

You can also use the SQL Profiler tool to watch batch-by-batch what the db is doing, when all else fails; but the previous steps should be enough.

You're into the hard part - getting it to work well! Good luck -|||I will give those a try,

the activex looging seems to be reliable, I am testing on the first step and the last step and the log file says it's progressing.
I may just be a little impatient I've been working on this for 5 straight days and getting tired, 90% the way though I appreciate the tips.

Thanks

Jack|||When you check for null, what do you do, exclude it from your export, or put a dummy value in (e.g. "n/a" )?
I bet this will be causing performance issues either way if you are implementing the check using ActiveX.

The good news is that this can be easily (and not to mention - efficiently) remedied in SQL. The bad news is that it may require to re-write some of your DTS.|||After all said and done, it was a lookup query that was bogging the package.
Put the lookup into it's own package to run after
Everything works great.

Thanks for the assistance
Jack|||Right before I am to deploy the application, the DTS is crapping out.

I have some rogue characters that I need to remove before the data gets transformed.

The problem is I have lost the field.

the field is DTSSource("l_text") and it is empty, when I execute, I am doing the activex file logging and the file is empty as well. But when I test the ransformation everything works great.

Any Assitance would be appreciated

Jack