January 17, 2013 at 10:11 am
I've lost enough hair over this, so I'm hoping others can either confirm my suspicions or point me down a path to a solution.
I've got a package whose job is to archive data over N days old from a bunch (22) history/auditing tables (SQL 2008R2) out to delimited text files, and then remove the archived data from the tables. The package contains 22 sequence containers, each of which contains the following:
1) a Script task to take a configured 'KeepDays' value and calculate a 'PruneToDate' based on the current date and assign it to a local variable. (Variable is of type Varchar -- lost even more hair trying to actually use a Date type in step 3, below. More on that another time)
2) a DataFlow task that has two components -- a OLE DB source with a 'select * from MyTable where HistoryDate < ?' statement (and a parameter that uses the calculated 'PruneToDate' value), tied to a FlatFile destination component that uses a connection to a CSV file (name generated based on date) and simply writes out all columns
3) a SQL task that executes a stored procedure requiring a single parameter -- the aforementioned PruneToDate -- that deletes records from the table (with some 'throttling' code built in to avoid load/lock issues on the server).
This package has been through a lot of editing in the BIDS environment, including a couple of forays directly into the XML. However, it all loads correctly and looks fine. And, when I execute it, either in debug mode inside of BIDS or on its own, it all appears to run through all of the steps just fine (every little box turns green). HOWEVER, in about half of the 22 cases, the second step -- the 'SELECT * FROM MyTable...' and the subsequent write to the flat file -- never seems to execute. I've verified that all of the variables are set correctly, the precedence constraints are all in place, etc., but the data is never written out to the flat file, EVEN THOUGH that file is correctly created (with the appropriate date/time in the file name, etc). I have verified this by running SQL Profiler and noting that for those places where the process works, I see the 'SELECT * from MyTable...' statement shows up in the trace; however, for those where it does NOT work, that statement does NOT show up. Even though there are no errors thrown, or indications given, in the Program Execution results tab or in any logging that we're doing. I've verified over and over and over that, as far as the BIDS UI is concerned, every setting in those instances where it is NOT working matches those where it IS working (except for the table/file names, of course).
So my question is (and thanks for reading this far!), is it possible/likely that there is SOMETHING in the XML that doesn't show up in the UI that may be preventing that statement from executing, even though there is nothing showing up to indicate it (except for the non-execution of the SQL statement)? I've heard that BIDS (2008 version and prior) had bugs in the the interaction between the UI and the underlying XML.
If so, are there any suggestions for how to look for where in the XML I might look for a problem?
Thanks in advance for your help!
Rob Schripsema
Propack, Inc.
January 17, 2013 at 10:52 am
Never mind! Never mind! Found the problem. Me. A configuration error -- those where it appeared to not be working were in a database whose connection string I'd messed with -- and it was actually looking at data in a different instance of SQL.
My bad -- but I've proven again (to myself at least) that sometimes trying to explain the problem to someone else (even in a forum) gives you a fresh perspective on things and points one towards new places to look for a solution.
Rob Schripsema
Propack, Inc.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply