September 21, 2008 at 10:26 pm
I'm at my wits end...what should be easy has got me completely stumpped.
I have a DTS Package that fetches a bunch of data from one database and pumps it into a SQLServer 2005 ( or 2K database). My problem exists on both platforms.
Currently the package does a quick ExecSQLTask to go find a business date and then dumps that in an output paramter that is later used as an input parameter to control the actual data pump task.
I'm executing the package from a .NET app that does a variety of other things (to include notifications, history records, data manipulation, "warehouse stuff"). You essentially end up with: Select stuff from Transactions where Entry_Date = ? and the global parameter substitutes the date fetched previously and tada...couldn't be easier.
I had an idea the other day that I'd like to put an option in the app for the user to tell me what BusinessDate they wanted to use (that way they could go back and pick up old dates we missed if they wanted to) rather than me always looking it up.
I thought this too would be easy... from my .NET app, all I have to do is change the value in dts.GlobalVariables("VarName).Value and I should be golden...right? HOWEVER... as soon as I modify the value in the global variable I get one of 2 highly descriptive messages when I attempt to execute the package. One is "General Error -2147220482" and the other is "Parameter Type Not Supported"
I even went to far as to toss a MsgBox( ... ) call inside an ActiveX Script task to show me the value in the GlobalVariable and it's "there" after I set it and execute the package.
Anyone out there fiddled with modifying globalVariables used as parameters in queries externally? What am I overlooking?
September 22, 2008 at 8:15 am
Within an hour of posting I found it... Yes, you can modify GlobalVariables via .NET code and then execute the DTS Package programmatically.
However... you need to pay attention to data types... I made 2 mistakes: In the first attempt, I simpy assigned my string date value to dts.GlobalVariables.Item("VarName").Value inside my .NET code (and the parm in the DTS package was a Date type).
On my next pass, I converted my string date value to a DateTime value and assigned that to dts.GlobalVariables.Item("VarName").Value; but this yielded the Parameter not supported error.
So, I tossed a message box in an ActiveX Script so I could "see" what the value looked like as the package was being executed and there it was... my "date" had a time value appended to the end of it and when I tried to use that in the where clause of a db2 data column query, I was toast.
So my solution ended up being:
-=VB.NET=-
Dim dtsp As New DTS.Package2
dtsp.LoadFromSQLServer( _
ServerName:=myAppSettings.strSmartServer.ToString, _
ServerUserName:=myAppSettings.strSmartUser.ToString, _
ServerPassword:=myAppSettings.strSmartPwd, _
PackageName:=strDTSPackage, _
PackagePassword:=myAppSettings.strDTSOwnerPwd)
dtsp.GlobalVariables.Item("TransactionDate").Value = Convert.ToDateTime(strParmValue)
dtsp.Execute()
Then in my DTS Package I have an ActiveX Script that makes sure the date value is in the right format to keep db2 happy:
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
If DTSGlobalVariables("_bOverrideParms").Value = 0 Then
DTSGlobalVariables("TransactionDate").Value = DTSGlobalVariables("_Current_Business_Date").Value
Else
DTSGlobalVariables("TransactionDate").Value = FormatDateTime(DTSGlobalVariables("TransactionDate").Value, 2 )
'(The last parm value "2" is the value for DateFormat.ShortDate)
End If
Main = DTSTaskExecResult_Success
End Function
Tada... (my code also sets a boolean value _bOverrideParms which is used to determine if the script should run with the date fetched from the business date lookup or from the globalVariables I'm manipulating).
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply