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.
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 begin return convert(varchar(10), @StartWith, 101) + ' ' + convert(varchar(10), @StartWith, 108) end
Now with all that out of the way let us get back to our Stored Procedure.
Create Procedure sjp_ImportData asbegin 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.
<Previous Post in this Series> <Next Post in this Series>