September 13, 2004 at 12:01 pm
Hi guys a little question if I may.
I have a DTS package that inserts new records into a project lines table. What i need to do is write a statement that returns the most recent lines, i.e package executes, inserts 10 new lines, i then pull back those ten lines. It is being used in a datagrid so i only need to pull back the most recent.
any advice would be appreciated.
regards
Wayne
September 13, 2004 at 12:10 pm
You know WHEN the package was ran (or button pushed) and you have the DateTime items were created in the particular table(s) you are interested in so:
SELECT [Field list here]
FROM table
WHERE CreateDtTm >= [Button Pushed Date/Time]
Good Hunting!
AJ Ahrens
webmaster@kritter.net
September 13, 2004 at 12:45 pm
Aj thanks for the reply. I know that this post seems like an easy one to achieve but trust I only wish that it was.
Ok, let me explain a little further. I created a dts package that took a csv file and transferred it into a table - simply stuff. Ok then I need a way of making the csv file path dynamic so i saved the dts package as visual basic file. I then convert that code into c# code.
The data that is inserted into the table need to be linked to a project, so I need to retrieve the records that have just been inserted (using dts code) into the table so that I can display them in a datagrid on my form. The user then select which items they wish to link to there project.
Hope this helps some.
Cheers
WAA
September 13, 2004 at 4:00 pm
I think you are trying to make it harder than it is. Basically I am assuming that you have a user-initiated DTS package or a scheduled package. From there you want to retrieve the latest records added for processing.
IF the DTS package is user-initiated you capture the date/time of initiation and pass that in to the SQL code (or procedure) from the project-side and return the records with a create date/time >= to the user initiated date/time.
IF scheduled you can go after the MAX(Date/Time) the job last ran from master.dbo.sysjobhistory (or is it msdb?) and don't need to pass in any parameters from the project.
I don't think trying to have the DTS package perform the processing and retrieval will work for you.
Good Hunting!
AJ Ahrens
webmaster@kritter.net
September 14, 2004 at 7:47 am
Why don't you change the data grid to just display the loaded records that are not yet linked to a project. When you link the records to a project you must store a value somewhere. Just list the records that don't have the value stored. It has the advantage that it also lists other loaded records that haven't yet been linked to a project by the user.
Hope this helps
Peter
September 15, 2004 at 9:01 am
People thank you all for your replies. As is when you are trying to find a solution to a problem a great big stick smacks you in the head and you realise that there is another way, an easier way, infact and dam site easier.
To solve my problem I used an Oledb connection set the datasource equal to a dynamic path (which is an xls file) and then create a dataset from the command, and use the dataset as my datagrids source
Then i looped through the datagrid writting each column item into my database - well easy. Only wished i'd thought of that one two days ago.
Oh well the joys of codeing.
Speak soon
Wayne (releaved)
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply