Adding a index automatically...

Hi, still new to all this I'm afraid! I've created a DTS routine that copys some table data from an Access database into another Access database. In the second mdb, I would like the routine to add an ID field and populate all the lines. I'm sure it must be kind of easy but I can't find how!

Any help appreciated.Ah sorted - just added the ID as an Identity when the table was being created in the routine.

Adding a DTS package

Can a DTS package be added to a server via the command line using OSQL?
Thanks in advance
JohnI don't think this is possible.
Anithsql

Add unique id column - newbie

I have a table (inherited) that gets data from a DTS package. The problem i
s
that the table that the data is being read in to wasn't created with a uniqu
e
id field. Is there a way that I can add a unique id field and have it
populated?
This table has 86000 records in it already.
TIAThe following article describes how to choose and implement a primary
(unique) key
http://www.aspfaq.com/show.asp?id=2504
"ks" <ks@.discussions.microsoft.com> wrote in message
news:B4F3293A-D2C2-4C67-9772-164E5693D467@.microsoft.com...
>I have a table (inherited) that gets data from a DTS package. The problem
>is
> that the table that the data is being read in to wasn't created with a
> unique
> id field. Is there a way that I can add a unique id field and have it
> populated?
> This table has 86000 records in it already.
> TIA

Add a DateLoaded column to input data

OK, I *know* this is trivial, and please feel free to giggle, but after years of DTS 2000 my poor brain can't make all the jumps neccesary to cope with SSIS 2005.

I have a Transfer data task. Source input (non SQL Server) has x columns, SQL Destination has x+1 columns, where the extra column is a non-null smalldatetime column DateLoaded. This will be populated with the Date & time the row was added.

In DTS I do this either by adding a GetDate() equivalient to the Source SQL statement (which I can't do in this case) OR by an ActiveX script to put
DATE()+TIME() in the final column in the Transfer pane of the designer.

This must be easy in SSIS, lots of other complex things are, but I am hanged if I can find out how to do it! Scripts as source? Union All to combine? ?

Solutions gratefully received,

Richard R.Richard,
The easiest solution is to put a Derived Column component between your source and destination. You just set the desired column name and set the expression to GETDATE(). The new column will then be available in you destination mappings.
You can do quite a few things with the derived column component including complex transformations. I'm sure you'll find it to be one of your most used components.
Larry|||Another option is to use the value of the System::StartTime variable which contains the time that the package started to execute.
This will mean that all rows in the pipeline are guaranteed to have the same value for this column - which may or may not be what you want.

-Jamie|||I chose to go with the System::StartTime solution, but frequently needed it converted to a String. This can be done with the following cast:
(DT_WSTR, 20) @.[System::StartTime]

Regards,
Lars|||Another option again is to use the Audit Transformation, and the "Execution start time" audit type (column).|||Thanks to everyone who replied to my request.
Derived columns are the solution for this one, but the system time variable will be useful for some other problems that I usually solve by setting a package variable to the current time as the first step.

Thanks again,
Richard|||Richard,
The System::StartTime idea that I talked about still requires a derived column transformation. When you use the Derived column its your choice whether you add GETDATE() or System::StartTime

Using the audit transformation as mentioned by Darren is the quickest way of doing this however. And note that it does exactly teh same thing i.e. Adds the value in System::StartTime into the pipeline.

From a best practices point of view I really recommend you do one of these 2 approaches rather than use GETDATE().

-Jamie|||

Since we are on the topic, I wrote some custom logging functionality using the OnPreExecute and OnPostExecute events. What I do is log the execution time of each task and also use the System::StartTime to identify a batch run. I started out using GETDATE() for the start and stop times of the tasks, but soon realized how bad of an idea it was, since the clock was not in perfect synchronization between the server that was running SSIS and the one with the database on which GETDATE() was called. In other words, stick with one timing scheme for everything, which in my case was using those times which are available within SSIS, like System::EventHandlerStartTime for events.

Regards,
Lars

|||

lasa wrote:

Since we are on the topic, I wrote some custom logging functionality using the OnPreExecute and OnPostExecute events. What I do is log the execution time of each task and also use the System::StartTime to identify a batch run. I started out using GETDATE() for the start and stop times of the tasks, but soon realized how bad of an idea it was, since the clock was not in perfect synchronization between the server that was running SSIS and the one with the database on which GETDATE() was called. In other words, stick with one timing scheme for everything, which in my case was using those times which are available within SSIS, like System::EventHandlerStartTime for events.

Regards,
Lars

Apologies for the digression...
Lars,
Were you able to capture the execution time of each task?

I want to do this using System::ContainerStartTime but unfortunately if you reference that variable in an eventhandler then you end up referencing the instance of it that is scoped to the eventhandler container instead of the one that is scoped to the task which threw the error (which is what you want).

Hence, I can't fnd a way of determining, in an eventhandler, how long the task that threw the event has been executing for.

-Jamie|||

I was able to, but using a workaround which involves storing data in a table. Not sure if you want to take that route, but it works and you'll have the extra benefit of being able to track progress through the rows in the table. On the top level of the package I have added three event handlers, OnPreExecute, OnPostExecute and OnTaskFailure. The following pseudo-code explains what I do in the different handlers:
OnPreExecute
I store the following information in a database table:

System::StartTime AS BatchStartTime,
System::SourceID AS TaskID,
System::SourceName AS TaskName,
System::EventHandlerStartTime AS TaskStartTime,
NULL AS TaskStopTime,
'Started' AS TaskStatus

OnTaskFailure
I update the table:

SET
TaskStatus = 'Failed'
WHERE
TaskID = System::SourceID
AND
BatchStartTime = System::StartTime

OnPostExecute
I update the table:

SET
TaskStopTime = System::EventHandlerStartTime,
TaskStatus = CASE TaskStatus
WHEN 'Started' THEN 'Completed'
ELSE TaskStatus
END
WHERE
TaskID = System::SourceID
AND
BatchStartTime = System::StartTime

You could just as easily use the key TaskID, BatchStartTime to retrieve and subtract System::EventHandlerStartTime from TaskStartTime to get the length of execution in any of the handlers following the PreExecute one.

Regards,
Lars

|||

OK, I see how that could work. Although I don't think it needs to be in a table. It could maybe be done inside the package using an Object variable.

Thanks Lars. You've really made me think here. It needs a lot more thought putting into it but you've given me a starting point.

Thanks
-Jamie

ActiveX&VB.net versus transformation tasks

Hello all -

Working on DTS packages we used to have two different ways of exporting data to spreadsheet... We could do it by a single transformation task or either writing an ActiveX script, after running a procedure.

I am able to cover the first way already, while dealing with SSIS... But, I wonder if it is worth to research on how I would write a vb.net code to load data into a spreadsheet (considering we are advised to try vb.net instead of ActiveX while working on these new packages).

It is always a huge discussion to know what is the best way of doing something so my point here is to hear some of you and decide if I just keep exporting data by doing transformation tasks or if I will ever go trying to deploy a vb.net code to do that... What is the best on performance, etc...?

Thanks in advance for all your help as always.

SSIS contains an Excel Destination that you can use.|||

Hi Phil.

Thanks for your reply. I know about the excel destination which is very much the same of doing transformation tasks in our old dts packages.

My question was... If I generate the spreadsheet through a vb.net code like we used to do with ActiveX scripts for dts packages, will I win anything on performance or something else? Example...

Working on dts packages we would always choose to generate a spreadsheet through procedures combined with ActiveX scripts because the procedure would have a query plan and would have less overhead, comparing with data transformation tasks. I don't know about SSIS because I am new here If you can add any insight to this I really appreciate that.

Thanks again.

|||I think you'll see better performance from the built-in Excel Destination. Script components are generally slower than pre-built components.

ActiveX Vbscript still ok in Yukon ?

Will I still be able to use my current VBscripts (Active X) code in dts
packages in Yukon ?Hi
In SQL Server 2005, there is an optional install of the SQL Server 2000 DTS
engine. this will allow the existing scripts to work (as long as you don't
use any T-SQL that is no longer supported in SQL Server 2005).
The new DTS engine in SQL Server 2005 is amazing and you will be very
tempted to convert your existing packages ASAP to the new engine.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Rob C" <rwc1960@.bellsouth.net> wrote in message
news:I81xd.21621$rO2.720@.bignews1.bellsouth.net...
> Will I still be able to use my current VBscripts (Active X) code in dts
> packages in Yukon ?
>