February 28, 2008 at 6:10 pm
Hi There,
I normally dig away until I have a solution and this one currently has me stumped, so I would appreciate any insights you may have (and I'm not sure if I have this posted in the right place)
I have 2 DTS packages. The outer package is my "header" and the called package is my "base". I set it up this way as the header parses files in date order in a directory and copies each one to a standard file "input_file.txt" then it calls the base package which processes the "input_file.txt". I have done this with various tasks and they have worked.
Here's the rub with this set of packages:
I can run the import in the base package manually (execute step) and it works fine. I debugged it last night with a 990,000 row input file, and this morning with a 28 row modifier file.
When I run the header package it executes the base package (and I do know the base package is being executed as I have added emails to it for debugging) BUT the base package does not import any data from the next file (which has 159 rows of data). (It imports them if I execute the step manually.)
Transform Data Task Properties :
Options tab : changed settings :
I have turned off the fast load (as there were ODBC connection errors on the big load when it was on).
Transformations tab :
I have some columns using VB Script to manipulate integer format data into decimal format.
Michael
P.S. SQL Server 2000, SP3a ; Windows 2000 Server, SP4
February 29, 2008 at 12:40 pm
In my memory, it is one of disavantages of using DTS packages, that a package runs throught without any error but we cannot get expected results.
Two suggestions:
1. Add a log file in your package, so that you can find more detail information.
2. Check your fmt file and see whether or not any format mismatched.
March 3, 2008 at 3:44 pm
Re: [font="Times New Roman"]Two suggestions:
1. Add a log file in your package, so that you can find more detail information.
2. Check your fmt file and see whether or not any format mismatched.
[/font]
Thank you.
1. I have logging in both the Data Transformation step and for the package. Neither log any exceptions.
2. A "fmt" file? A new concept for me. I seem to have all the data type exceptions ironed out as the initial file load was 990,302 rows (and I did have to add exception handling - via VB Script - to 6 of the 80 columns), and the first update file had 28 rows (23 rows were replaced to the base data and 5 new rows were added), all done through executing each base step manually.
Could this be a permissions issue?
March 4, 2008 at 11:50 pm
We've had the problem where a package "suddenly" went weird on us in that it would work normally when executing each step one at a time but wouldn't work if the entire package was executed. Turns out one of our developers had temporarily deleted a workflow between two steps for testing purposes but when he reconnected the steps, he unknowingly connected the workflow in the opposite direction. Go through and carefully examine every workflow connector. Make sure each one points in the direction it should.
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
March 5, 2008 at 9:14 am
Thank you, Tomm,
The import data transformation was the second step in the base until I removed the first step. I put tracking emails into the base DTS and the step is executing. It is not seeing any data. *sigh*
I have taken this as an opportunity to learn a new skill: BULK INSERT.
I should have a script (which cycles through the directory and imports each input file in date order) working today and the script will not require a DTS package.
Additional thanks to SQL Oracle for getting me thinking in that direction and to Lowell for his helpful reply to the Data Mask post and others who posted about BCP, and to Steve for having SQL Server Central here.
Michael
"Go around it if you can't go through it!"
March 5, 2008 at 9:55 am
The import data transformation was the second step in the base until I removed the first step. I put tracking emails into the base DTS and the step is executing.
Of course it is. Let me go into a little more detail. The first step in our package was called "Create Temp Tables". The next to last step was called "Drop Temp Tables". The developer removed the workflow that went to "Drop Temp Tables" so he could examine the contents of the temporary tables to verify the change he had made was working correctly. When he reconnected the workflow in the wrong direction, that meant that both "Create Temp Tables" and "Drop Temp Tables" were starting points in the package execution sequence. Depending on which one actually was selected to execute first, the second step, the one that tried to use the temporary tables, would either execute normally or error out because the tables it referenced did not exist!
This seemingly random behavior was perplexing until someone noticed the wrong-way connection way down at the bottom of the package.
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
March 5, 2008 at 10:18 am
Tomm Carr (3/4/2008)
Go through and carefully examine every workflow connector. Make sure each one points in the direction it should.
Haha! I get what you are saying now. Thanks for expanding on that. I looked through the base DTS and (unfortunately?) the little arrows are all facing the direction I intended (start -> finish).
March 6, 2008 at 10:25 am
The new script with BULK INSERT is working beautifully.
The perfectionist in me still wants to know how to fix the base DTS, and the practical person in me revels in knowing a work-around: and I have enough to do without pursuing this.
Thanks!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply