Ok, now let us start moving some data around. I have looked at multiple ways of moving data everything from replication, log shipping, SSIS, and various others to writing T-SQL. However, as I am a T-SQL kind of guy and I really want the finite granular control that writing my own SQL scripts will give me I am going to do this using mainly T-SQL. Now for maintenance reasons I also have a few rules about doing this part as well.

  • I want as few SQL Jobs as possible to control everything. I really do not like looking at a SQL Server and seeing hundreds of jobs.
  • I really do not want to go to multiple servers and check the history of each job to see what is working and what is not working.
  • I want to know how long each finite step took, so that I can make changes as needed. Remember, I do not want to hurt Application performance or CBI performance just because I am moving data around.
  • I need some way of prepping the data from the Application before that data goes into the EDW.

So, where do I start?

First, as a general design I will be moving the data from the Application database into a "Stage 1 database" (for lack of a better name). A Stage 1 database is simply an intermediary database that I can pull data from an application into and then massage it in some manor before sending it on to the main EDW. No one actually has access to the database except for me. From the Stage 1 database, the data will then go to the EDW where all of the CBI happens.

Let us begin by creating a database that we can use as our Stage 1 database. Sorry, no code on this part as everybody has different locations for their database files. I will call my Stage 1 database for Dynamics GP "DYN". Hey I already told you I was not good at coming up with names.

Now, in this database let us create a Stored Procedure that I can use as a step in a SQL Job. Here is what it will look like to start with and how I will set it up as a job. But before we do that as I know I will be looking for start and end times a lot and I hate formatting the date time let us create a function to format it for us.

Create Function fncDisplayDateTime(@StartWith datetime) 
  Returns Varchar(19) as 
    return convert(varchar(10), @StartWith, 101) + ' ' + convert(varchar(10), @StartWith, 108)

 Now with all that out of the way let us get back to our Stored Procedure.

Create Procedure sjp_ImportData as
  declare @Delay char(8) 
  --This will be the length of time to wait between each sub step
  set @Delay = '00:00:02'

  declare @StartTime datetime    --When did this sub step start
  declare @EndTime datetime      --When did this sub step end
  declare @Duration varchar(10)  --How long did this sub step take

  Set @StartTime = getdate()
  Print 'Executing id_upr00100 at ' + dbo.fncDisplayDateTime(@StartTime)  

  begin try
    exec id_upr00100
  end try
  begin catch
    print '*****ERROR*****'
  end catch

  Set @EndTime = getdate()
  -- If you want to know the end time you can uncomment the following line
  --Print 'Finished with id_upr00100 at ' + dbo.fncDisplayDateTime(@EndTime)

  Set @Duration = convert(varchar(10), datediff(millisecond, @StartTime, @EndTime)) 
  Print 'Duration was: ' + @Duration + ' milliseconds'

  Print 'Let the system rest for ' + @Delay + ' seconds before starting on the next item'
  WAITFOR DELAY @Delay -- I really do not want to bog down the server! 


Note that I have not created the stored procedure "id_upr00100" that this procedure is calling; I will post the code for that on a latter post. Once I do, I will edit this post to point to that post. However, I will post how to set this up as a SQL Job before that, and explain why I have so many Print Statements in my code. Oh Yes, there is definitely a good reason for all of those Print Statements but I will make you wait for my next post to find out why.

Till later,