DTS and steps 1

[thendrickson]

Please bear with me, for what is probably a stupid question but I have not found a direct answer in BOL.

I have a DTS package that deletes all records in a temp table, then imports all records from a text file.

Test Data contains 276 records.

My question is whether this package contains 3 steps as I believe (delete,copy and dynamic) as I believe or if it contains 278 steps (delete, copy each record as 1 step, dynamic properity)

I am running the package from VB and I want to use a progress bar

 

[nigelrivett]

Should be a delete (1 step)
and an import (1 step)

If it's something that small then it should be so quick that you won't be able to show progress.
Why not do it in an SP - it will be even quicker and simpler.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[thendrickson]

Thank you.

This is a learning experience as much as anything else.

At some point the number of records being imported could be quite large.

Additionally, I want to give the user the ability to browse for the text file if it is not in the default location.

As far as I know I cannot pass the global variable (filepath and name) to the DTS package from an sp (or can I?, things would be much simpler if I could)

For this reason, I think I need to run the package frm a VB app.

 

[nigelrivett]

You can set the global variable in a dtsrun command and use it in a dynamic properties task in the package.

You can do a similar thing using the ole sp's to load the package and set properties befre the run.

Sounds a bit like you need a client app (like dts) if the user is to use it interactively so you shouldn't use a server based thing (like an SP).
I would do this by copying the file to the server then doing the load so that the database interaction is as short as possible.


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[thendrickson]

I think I let myself get off track a bit in this thread.

My question re: steps was mostly to clear up a concept.

The app in question has been customized for each customer in the past.

In the future, I would like to provide a simpler method of customizing these import file formats.

Doing DTS packages for each customer seems like the solution.

Additionally, I want pass the text file path and name to the DTS which I have just figured out how to do either from VB or a sp (Gobal variables)

The default name and Path of the text file will be stored in the registry.

My plan is to give the option to browse for the file if not found in the specified location although I may not be permitted the time to add that feature. (in the VB code)

This will be the only user input.

I have written a sp that accepts the path and file name as an argument @path, as well as the name of the DTS Package @package


Since I have several places where text files may need imported into the database, my intention is to write DTS packages as needed and name them
something like DTS_IMPWhateverCustName
DTS_ImPortNextWhateverCustName

Store these values in the registry then pass them to the sp

However

When I hardcode the name of the package in the sp, things run well but when I replace the name of the package with the variable I receive an error 'Package failed to load'


I have included the code which fails.

-DTSSQLServerStorageFlags :
--- DTSSQLStgFlag_Default = 0
--- DTSSQLStgFlag_UseTrustedConnection = 256
EXEC @hr = sp_OAMethod @oPKG,

this fails
'LoadFromSQLServer ("(local)", "", "", 256, , , ,@Package)', null

when I comment out the above and use this, it works
-- 'LoadFromSQLServer ("(local)", "", "", 256, , , , "DTS_Importorders")' ,null

 

[thendrickson]

ADDENDUM:

I have been reading on BULK Insert also but the text file is tab delimited. Would this create a problem for bul Insert?

 

[MDXer]

No. You just have soecify your column delimiters in the Bulk Insert code.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[thendrickson]

Thanks MDXer also.

I need to experiment a bit and read some more.

I just found an example of what you are saying in a book I have.

 

[nigelrivett]

See

http://www.nigelrivett.net/BCP_quoted_CSV_Format_file.html

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[cyberbiker]

nigelrivett,

Apologies for not thanking you sooner.
I gave you the star then problems arose here and I screwed up not realizing that I did not submit the post.
Your posts are always helpful to me

Terry (cyberbiker)