XML
I'm in the process of building out a Data Warehouse and encountered this issue along the way.In the environment, there is a table that stores all the folders with the individual level. For example, if a document is created here:{App Path}\Level 1\Level 2\Level 3\{document}, then the DocumentFolder table would look like this:IDID_ParentFolderName1... 121Level 232Level 3To my understanding, the table was built so that:Each proposal can have multiple documents stored at various locationsDifferent ......
I start off the process with either a BizTalk Scheduler (http://biztalkscheduledtas... or a manual file drop of the XML message. The manual file drop is to allow the SQL Job to call a "File Copy" SSIS step to copy the trigger file for the next process and allows SQL Job to be linked back into BizTalk processing. The Process Trigger XML looks like the following. It is basically the configuration hub of the business process <ns0:MsgSchedulerTrigger... xmlns:ns0="urn:com:something ......
For the record, this can be just as easily done with a .NET class with a DLL call. The two major reasons for this ending up as a SSIS package are: There are a lot of SQL resources in the organization for maintenance, but not as many .NET developers. There is an existing automated process that links up SQL Jobs (more on that in the next post), and this is part of that process. To start, this is what the SSIS looks like: The first part of the control flow is just for the override scenario. In the Execute ......
The "fn_cdc_map_time_to_lsn" is especially useful DECLARE @begin_time datetime, @end_time datetime, @from_lsn binary(10), @to_lsn binary(10) SET @begin_time = '11/10/2010 10:00 AM' SET @end_time = '11/10/2010 8:00 PM' SET @from_lsn = sys.fn_cdc_map_time_to_lsn(... greater than or equal', @begin_time); SET @to_lsn = sys.fn_cdc_map_time_to_lsn(... less than or equal', @end_time); SELECT _OU.unit_natural_id AS Order_Id , CONVERT(DATETIME, LEFT(CONVERT(VARCHAR(80), _OR.return_body.query(' ......