Run package for each of the last 365 days

[johnpayback]

I need to have this package pull the count for each day for the past 365 days. Currently it will pull the count every night for the previous day but I need it to pull the count for each of the past 365 days and insert them separately based on each day. I do not need to get the total count for the past 365 days...that is easy by changing the -1 to -365. Can I do this with a package or do I need to write a script to do this? Below is the SQL statement for the current package.

Select count(*) as CallCount, 
dateadd(d,-1,getdate()) as CallDate, 
'CON' as Site  
From dbo.master_index where FileDateTime >=  convert(char(10),dateadd(d,-1,getdate()),110) AND FileDateTime <convert(char(10),getdate(),110)

thks JP

 

[AlexCuse]

You could set up the package to loop, with a parameter for the number of days back to go. Set this parameter to 365, and subtract one every time you run. Have the loop end when you get to 0.

This link will get you started:

http://www.sqldts.com/default.aspx?246

Sorry I don't have more right now, possibly after the weekend I can help further.

Good Luck,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[johnpayback]

Thanks...I actually have a script but I am unsure how to run it in the DTS as I've tried and it doesn't recognize the VBScript commands. Any ideas? The link helps for my script but not on how to run it.

JP

 

[AlexCuse]

What is the script you are using? You actually will need a few scripts, what you are doing is building a 'container' around your package. The container doesn't do anything but loop through the dates, and pass the current date to your package.

Which of the scripts have you got working, and what is the problem with running it?

Good Luck,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[johnpayback]

Here is what I have so far. I just don't know how to get this to execute on the "SFLAB" and put the data it returns on the "LUBLAB". Somehow I need to get the data from the "SFLAB" on the "LUBLAB" but I'm not sure how to do it. If you have an idea I would be in your debt.

I've added some code for the "LUBLAB" server and db but I don't know where to go from here. Two connections and two recordsets but how do I get the data from the first one on the second one? I appreciate it.

Before you ask....the userID and password for the databases are not set to "sa" and "passwd". I've changed them in order to post here.

Const OpenFileForReading = 1
Const OpenFileForWriting = 2
Const OpenFileForAppending = 8
Const adopenstatic = 3
Const adlockoptimistic = 3
Const aduseclient = 3

Dim objFSO

Set objFSO = CreateObject("Scripting.FileSystemObject")

strServer = "SFLAB"
'strServer2 = "LUBLAB"

Set objconnection = CreateObject("ADODB.Connection")
Set objconnection2 = CreateObject("ADODB.Connection")
Set objrecordset = CreateObject("ADODB.Recordset")
Set objrecordset2 = CreateObject("ADODB.Recordset")
objconnection.open "driver=SQL Server;server="&strserver&";uid=sa;pwd=passwd;database=TestDB;"
'objconnection2.open "driver=SQL Server;server="&strserver2&";uid=sa;pwd=passwd;database=RecorderDB;"

StartDate = Date()

For i = -397 To -1
    Date1 = DateAdd("d", i, StartDate)
    sqlQuery = "Select count(*) as CallCount," & _
        Date1 & " as CallDate," & _
        "'LUB' as Site " & _
        "From dbo.master_index where FileDateTime >=  convert(char(10)," & Date1 & ",110) AND FileDateTime <convert(char(10)," & DateAdd("d", 1, Date1) & ",110)"
'    WScript.Echo sqlQuery
objrecordset.open sqlQuery , objconnection, adopenstatic, adlockoptimistic
Next 

objRecordSet.Close
objConnection.Close
WScript.Quit

JP

 

[johnpayback]

Here is what I am trying now. I am actually going to post in the VBScript forum as I think that I've went outside the realm of this forum. I do use a DTS package on a daily basis which works well. I did not know how to do a DTS package to accomplish what I am trying to do in this script for a full year so I will try the other forum. Thank you for the help.

Option Explicit
'On Error Resume Next

Dim strServer, strServer2, objconnection, objconnection2
Dim objrecordset, objrecordset2, StartDate, i, sqlQuery
Dim date1, date2

Const OpenFileForReading = 1
Const OpenFileForWriting = 2
Const OpenFileForAppending = 8
Const adopenstatic = 3
Const adlockoptimistic = 3
Const aduseclient = 3

strServer = "SFLAB"
strServer2 = "LUBLAB"
StartDate = Date()

Set objconnection = CreateObject("ADODB.Connection")
Set objconnection2 = CreateObject("ADODB.Connection")
Set objrecordset = CreateObject("ADODB.Recordset")
Set objrecordset2 = CreateObject("ADODB.Recordset")
objconnection.open "driver=SQL Server;server="&strserver&";uid=sa;pwd=passwd;database=TestDB;"
objconnection2.open "driver=SQL Server;server="&strserver2&";uid=sa;pwd=passwd;database=RecDB;"

For i = -365 To -1
    Date1 = DateAdd("d", i, StartDate)
	Date2 = DateAdd("d", 1, Date1)
    sqlQuery = "Select count(*) as CallCount," & _
        "convert(char(10)," & date1 & ",105) as CallDate," & _
        "'LAB' as Site " & _
        "From dbo.master_index where FileDateTime >=  convert(char(10)," & Date1 & ",110) AND FileDateTime <convert(char(10)," & Date2 & ",110)"

    objrecordset2.open sqlQuery, objconnection2, adopenstatic, adlockoptimistic
	objrecordset2.Close
	objrecordset.Open "INSERT INTO LoggerRecStats (NumOfRecs, RecDate, Site) VALUES (CallCount, CallDate, Site)", objconnection, adopenstatic, adlockoptimistic
    objrecordset.Close
Next

objrecordset2.Close
objconnection2.Close
objConnection.Close

JP