July 27, 2004 at 10:22 am
Hello,
I have several DTS packages that take data from SQL Server and exports them onto an Access DB located on the network. Basically, one Execute Process Task from within my DTS package takes the Access DB and zips it while another such task copies the zipped DB and pastes it onto another location on the network. All this works fine.
This export process happens once a month so each month I have to manually add a datestamp to the end of the Access DB file that’s being exported to distinguish it from prior month's export. For example, this month's export file would have AccessDB_20040727.mdb name and the next month it would be AccessDB_20040820.mdb (date is determined based on the date the output is exported on). AccessDB.mdb is the default name of the export DB and datestamp is added at the end of the file name depending on what date the export was run. As I said, I can do this manually each month and it works fine.
I want to, however, know if there is a way to automatically supply the datestamp to the Execute Process Task's Parameters' text box? Following is what I have right now in the Parameters box:
\\ghf1-ndc8-sql\l$\production\output\lob\200406\LOB_CF_forDrilldown_All_20040727.zip \\ghf1-ndc8-sql\l$\production\output\lob\200406\LOB_CF_forDrilldown_All.mdb
I want to take above text from the parameters box and replace it with something like:
\\ghf1-ndc8-sql\l$\production\output\lob\200406\FileName_RunDate.zip \\ghf1-ndc8-sql\l$\production\output\lob\200406\LOB_CF_forDrilldown_All.mdb
Where FileName_RunDate is a variable/placeholder for the name of the output with the datestamp the output is exported on.
There are three different Execute Process Tasks that are happening within each of my DTS packages so it's a time consuming job to have to manually add a datestamp to each package every month when data is exported.
Does anyone know if what I am asking is doable? If I can use a variable in the parameters box of each Execute Process Task’s properties and supply current datestamp values to it prior to executing the package each month? If so then what are the ways? How would I do that?
Thanks much.
JN
July 27, 2004 at 11:01 am
What you can do is create a global variable, populate it, and then use that to dynamically set the string for each process task that you want to execute.
Let's say in this example you have a single process task.
First right click anywhere in the package and add a global variable gvTaskExe.
Add a SQL connection to any server (we'll use this next).
Add a Dynamic Properties Task.
Add the process task (we'll call it Run The Process).
Open the Dynamic Properties Task, click on add, drill down the global variables, click on gvTaskExe, then doubleclick on Value.
Change the source to a query, make sure that the connection points to the server we added before, then use a query to create the string that you want to run in the process task ie:
select '\\ghf1-ndc8-sql\l$\production\output\lob\200406\LOB_CF_forDrilldown_All_' + convert(varchar(12), getdate(), 112) + '.zip \\ghf1-ndc8-sql\l$\production\output\lob\200406\LOB_CF_forDrilldown_All.mdb'
Hit ok.
Now hit Add again, this time drill down to tasks, select the Run The Process task and double click on ProcessCommandLine. Change this to the global variable gvTaskExe.
Hit ok again.
Now if you execute the dynamic properties task you can then look at the command showing in the process task and it should reflect the dynamcially created information. You can then use this methodology in your packages and not have to manually change dates again.
July 27, 2004 at 12:18 pm
Thanks Nicholas! That's pretty much what I was looking for. I just never thought about the dynamic properties task!!
I wanted to know if there is a way to add more functionality to this method...
Select '\\ghf1-ndc8-sql\l$\production\output\lob\200406\LOB_CF_forDrilldown_All_' + convert (varchar(12), getdate(), 112) + '.zip \\ghf1-ndc8-sql\l$\production\output\lob\200406\LOB_CF_forDrilldown_All.mdb'
As you can see there is a folder 200406 in which the DB is published. This folder also changes each month. The folder is always one month less than the current export month. So for July export the folder is 200406 (June), for August export it will be 200407 (July) and so on. Is there a way to supply YYYYMM values to the export package based on current export month minus one month? I have some ideas, but thought of asking in case it's something simple.
Thanks much.
JN
July 27, 2004 at 12:36 pm
I made the following changes to the statement:
Select '\\ghf1-ndc8-sql\l$\production\output\lob\' + convert(varchar(6), getdate(), 112) - 1 + '\LOB_CF_forDrilldown_All_' + convert (varchar(12), getdate(), 112) + '.zip \\ghf1-ndc8-sql\l$\production\output\lob\200406\LOB_CF_forDrilldown_All.mdb'
I get this error:
Syntax error converting the varchar value \\ghf1-ndc8-sql\l$\production\output\lob\200407' to a column of data type int.
If I run the statement in Query Analyzer with just
Select convert(varchar(6), getdate(), 112) - 1
Then it runs fine and I get 200406 in the result pane, but if I make it part of the whole statement above then I get the error listed above.
Any ideas why I get that error? Something I am missing in the syntax??
Thanks.
JN
July 27, 2004 at 1:07 pm
Because you are trying to perform a calculation on the result of the convert it turns it into an int datatype, you need to encapsulate within another convert statement thus:
Select '\\ghf1-ndc8-sql\l$\production\output\lob\' + convert(varchar(6), convert(varchar(6), getdate(), 112) - 1) + '\LOB_CF_forDrilldown_All_' + convert (varchar(12), getdate(), 112) + '.zip \\ghf1-ndc8-sql\l$\production\output\lob\200406\LOB_CF_forDrilldown_All.mdb'
July 27, 2004 at 1:30 pm
Yeah, I figured it out after a few trial & errors. Thanks much for your help and suggestions.
JN
July 27, 2004 at 3:57 pm
I do have another question, for Nicholas or anyone else...
In the same DTS package, I have an Access connection with DB location where the output files are exported. This DB location and DB name also changes each month, as described in the earlier post. Basically the folder name where this DB is would be 200406 for July output export with datestamp 20040727.mdb in the output DB name. Following is what I have in my Access connection's DB path:
\\ghf1-ndc8-sql\l$\production\output\lob\200406\LOB_CF_forDrilldown_All_20040727.mdb
Is there a way to also do what we did for Execute Process Task's parameter supplying the current datestamp and year month for the folder name through a variable? I tried the same method as posted in earlier threads using query and passing values to the global variable via dynamic task, but that doesn't seem to work. I must have some values in the path of Access DB connection property, because it gives error if I erase the current path and try to populate that box using the dynamic task. If you know of a way to dynamically supply the folder name in which my export DB will be copied with the run datestamp in the file name, please let me know.
Thanks much.
JN
July 28, 2004 at 5:41 am
You do pretty much the same thing as before, create the global variable, populate it, and then assign that variable to the location of the file. I am running a similar process which actually creates the mdb file nightly based on the value of the variable using vb. In this instance creating and populating global variables call Path and TheDate
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
Dim sFilename
Dim sConnectionString
Dim oCatalog
Dim oConn
Dim oFSO
' Derive new filename - X:\Path\Export_yyyymmdd.mdb
sFilename = DTSGlobalVariables("Path").Value & "CallTracking_" & DTSGlobalVariables("TheDate").Value & ".mdb"
' Check for existing instance of file and delete
Set oFSO = CreateObject("Scripting.FileSystemObject")
If oFSO.FileExists(sFilename) Then
oFSO.DeleteFile sFilename
End If
Set oFSO = Nothing
' Create empty MDB
sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sFilename
Set oCatalog = CreateObject("ADOX.Catalog")
oCatalog.Create sConnectionString
Set oCatalog = Nothing
' Get referemce to Access Connection and set new filename
Set oConn = DTSGlobalVariables.Parent.Connections("Microsoft Access")
oConn.DataSource = sFileName
Set oConn = Nothing
Main = DTSTaskExecResult_Success
End Function
July 28, 2004 at 11:14 am
Nicholas,
I have a couple questions...
First, when I execute the dynamic task to populate the values of the variable and assign them to the Execute Process Task, both Win32 Process Task box and the Parameters box get populated with the values from global variable. This shouldn't happen. Win32 Process should always keep the values it was originally assigned, which is location of batch file to fun copy command and EXE file to run zip software to zip output files. These are static and always remain at the same location so only the output files path in the Parameter box need to be changed each run. But as I said, when I execute the Dynamic Task properties, it assigns new values to both boxes in the Execute Process Task properties, since ProcessCommandLine is set to the global variable for Zip and Copy path.
Would you know how I would modify the package so that only the Parameter box is populated with the new value upon executing Dynamic Task, but keep the Win32 Process box as it was set to originally?
Also, the last post about setting output path dynamically doesn't seem to work for me or perhaps I haven't understood your solution properly. This is what I did...
Created global variables called, OutputPath, PublishDate.
Get values by running the query.
Now where does the ActiveX code go that you provided in your last post?
Without using the VB code you gave, I have the following query to populate values in OutputPath global variable:
Select '\\ghf1-ndc8-sql\l$\production\output\lob\' + convert(varchar(6), convert(varchar(6), getdate(), 112) - 1) + '\LOB_CF_forDrilldown_All_' + convert(varchar(12), getdate(), 112) + '.mdb'
The above query runs fine in Query Analyzer, but within the DTS package it returns nothing. If I remove the 2nd getdate() statement then it returns values in DTS package, but if there are two getdate() calls in one statement like above then I get no values, no errors. Any ideas what's happening here?
Then set the DataSource properties of my Access DB connection to OutputPath global variable. But since the query returns no results, the DataSource property doesn’t get any values.
Thanks for your time and help with my questions. If you are able to through further light into this, I really appreciate it.
JN
July 28, 2004 at 11:21 am
The two getdate() thing might be a bug. Create 2 global variables, populate them with the two halves of the query and use them both in the vbscript DTSGlobalVariables("part1").Value & DTSGlobalVariables("part2").Value
For the process command line, I don't know a way of seperating it out, I would suggest creating the entire command, soup to nuts within the global variable.
July 28, 2004 at 11:46 am
That's very strange, because the following statement works fine:
Select '\\ghf1-ndc8-sql\l$\production\output\lob\' + convert(varchar(6), convert(varchar(6), getdate(), 112) - 1) + '\LOB_CF_forDrilldown_All_' + convert(varchar(12), getdate(), 112) + '.zip \\ghf1-ndc8-sql\l$\production\output\lob\' + convert(varchar(6), convert(varchar(6), getdate(), 112) - 1) + '\LOB_CF_forDrilldown_All_' + convert(varchar(12), getdate(), 112) + '.zip'
BUT the following returns no value:
SELECT '\\ghf1-ndc8-sql\l$\production\output\lob\' + convert(varchar(6), convert(varchar(6), getdate(), 112) - 1) + '\LOB_CF_forDrilldown_All_' + convert(varchar(12), getdate(), 112) + '.mdb'
Well, I will continue troubleshooting this and if I find any work-arounds, I'll post it here.
JN
July 30, 2004 at 10:55 am
Just wanted to give an update...
I was able to solve the process commandLine issue I posted in my 2nd to last post. Basically, I am supplying all the values (for the location of Zip/Batch file in W32 program box and the values for parameters box) using the same variable. When I execute the dynamic properties task, it populates the W32 box with my zip/batch file location and parameters box with the rest of the values stored in the global variable so this works out great!
I am still having the issue posted in my last mail. I contacted Microsoft regarding that, but they have not been able to resolve it as yet! One support person said she didn't get the same issue I got and the other said she got it and that she is working on it so will give me a call when she finds out what it is! I am leaning toward a bug in the SQL Server 2000 DTS program. If any of you have some time, could you run the following test to see if you get the same results as I get?
Create a TEST DTS package.
Add a SQL Server connection to it.
Create a global variable called, OutputPath or anything you want to call it.
Add Dynamic Properties Task to your package.
When the Dynamic Properties Task is open, click on Add to add dynamic property.
In the window that opens up, click on + sign next to Global Variables.
Select the Global Variable (OutputPath or whatever you called it) that you created.
On right pane under Property Name, you'll see Value. Double click Value.
From the Source dropdown box, select Query.
The Connection box might default to SQL Server connection you have in your package so keep it as is.
In the Query box paste or type this:
SELECT '\\ghf1-ndc8-sql\l$\production\output\lob\' + convert(varchar(6), convert(varchar(6), getdate(), 112) - 1) + '\LOB_CF_forDrilldown_All_' + convert(varchar(12), getdate(), 112) + '.mdb'
Then click on Parse to make sure you get 'The SQL Statement is Valid' message.
Then click on Refresh.
The grey color Preview box left of Refresh should populate values from the above query.
BUT it doesn't return anything for me - it just stays blank.
Does it return anything to you?
Now if you replace the above query with:
Select '\\ghf1-ndc8-sql\l$\production\output\lob\' + convert(varchar(6), convert(varchar(6), getdate(), 112) - 1) + '\LOB_CF_forDrilldown_All_' + convert(varchar(12), getdate(), 112) + '.zip \\ghf1-ndc8-sql\l$\production\output\lob\' + convert(varchar(6), convert(varchar(6), getdate(), 112) - 1) + '\LOB_CF_forDrilldown_All_' + convert(varchar(12), getdate(), 112) + '.zip'
Then you will see values returned in the preview pane.
This is very strange, because both above queries return values if run from within Query Analyzer and the later one (longer query) runs fine even within a DTS package, but the earlier one (shorter query) doesn't return any values nor any error message in DTS, though it runs fine in Query Analyzer!
If you would please run the above test and let me know if you are seeing the same odd behavior with the shorter query within your DTS package as I am?
Just FYI, my SQL Server:
SQL Server Enterprise Edition Version 8.00.760 with SP3.
Thanks much.
JN
July 30, 2004 at 11:00 am
I've experienced the same problem with DTS packages and the global variables in the past (2K EE, SP3a, 8.00.818). I have no idea what causes it.
August 24, 2004 at 4:31 am
I´ve got the same problem.
Is something with the size o the string returned in the select statement. If the size between 75 and 160 the problem occurs.
In my case adding spaces on the end of the string "solved" the problem.
August 24, 2004 at 10:14 am
I called Microsoft with this and was told this is a bug in DTS and that there is no fix at the time, but following is the work-around they offered:
cast('\\nat1-iac5-sql\k$\production\output\client\' as
varchar(1000)) + convert(varchar(6),
convert(varchar(6), getdate(), 112) - 1) +
'\client_output_' + convert(varchar(12), getdate(),
112) + '.mdb'
So if you use a CAST for the whole string then it will work fine.
Hope that helps.
JN
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply