SQL Server Performance Forum – Threads Archive

importing text files and dts

hey guys, I wrote a stored procedure that imports 2 text files using BULK INSERT and places them into 2 separate tables. These 2 tables are then used to update our production table. However, I would like to get rid of writing the "exec sp_ourscript blah,blah" in query analyer. Last night, I learned how to create a package using DTS Import wizard. I was able to create a package that imports one text file. However, I need the other text file too. I’m stucked now as to how I will combine both packages. I’m doing this so other people can execute this single package via Enterprise Manager. Some people are just afraid to type in Query Analyzer. Actually, this is what I want to happen
1. If 2 text file exists, proceed w/ next step otherwise send a Failure alert
2. Import the 2 text files into 2 separate tables
3. If import is successful, create a success workflow then update prod table using those two tables Any help would be greatly appreciated. Thanks!!!

Are you creating DTS package using Enterprise Manager? If you are, you can create connection whereby at the connection properties, you can choose to bind to the text files. Then, you can add the workflow. At the workflow properties, you can choose: On Completion, On Success, On Failure.
From there, you can link the workflow to the next step which you intend to do.
You can also use Scheduled Tasks to run the DTS package by creating a Batch file to run the DTS package. You can create a log file to keep track.

This is what I’ve come up with and it’s working. http://restricted.dyndns.org/dtspackage.gif However, I would like to insert now the sql codes that updates my prod table. How will I connect it to the new "Execute SQL Task" that I’m planning to add?

If you are to add Update statement to the Prod table, you can use notepad to do so and save it as extension .sql. Then, use connection whereby there is a properties to attach your script.

BAM! I’ve changed my package a little bit and everything is working like a charm. Just one right-click and Execute package <img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ /> This is what I came up with,<a target="_blank" href=http://restricted.dyndns.org/dtspackage2.gif>http://restricted.dyndns.org/dtspackage2.gif</a> I just need to add a failure workflow for email which is pretty easy! Thanks for the guidance!

Great that it works <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />

]]>