January 24, 2005 at 10:56 pm
Hello all,
HOpe that someone can help me with this. If there is a solution for this, my life will be much easier. And thank you everyone very much for reading my thread and trying to help me!
I have a package to load data into production. Normally, We have to run this package manually by providing some globle variables ( to feed some store procedures in the package),depending on what kind of data we are loading. Now, there is a load of data that I have to load into production daily by using that package. In order to load, I have to create a file in a correct format, so I created another package to do this task. What I want to do now is to put these two packages ( the one I created to produce the data file and the one that used to load the data in this file into production) into one package, so I can automate the process.
The problem is that I dont know how I can provide globle variables for the package that loads data into production. I can't hardcode or modify anything to that package becuase it is used by many people not just me. Is there any way that I can provide globle variables for the loading package so I can automate the whole process? Please help!
Thank you so much in advance!
January 25, 2005 at 5:46 am
I would look at the DTSRUN command in BOL. You are able to pass global variables into the package at runtime using switches within the DTSRUN.
January 25, 2005 at 5:51 am
Rather than messing about with two packages, I find it easier to execute dynamic dts packages by using vba.
Copy this code into a vba module on a word document or excel etc, and change the packagename, servername and the value of the variables. All upper case in the script so that they stand out.
'===================code========================
Public Function fnExecPackage()
On error goto err_handler
Dim oPKG As New DTS.Package
Dim oPKGname As String
Dim DidErrorOccurSomewhere as string
Dim oServerName as string
oPKGname = "YOURPACKAGENAME"
oServerName = "YOURSERVERNAME"
DidErrorOccurSomewhere = "N"
oPKG.LoadFromSQLServer oServerName, , , _
DTSSQLStgFlag_UseTrustedConnection, , , , oPKGname ' connect to the package
oPKG.GlobalVariables(1).Value = "YOURVALUE1" ' set value of first variable in the sequence
oPKG.GlobalVariables(2).Value = "YOURVALUE1" ' set value of second variable in the sequence
' Set Exec on Main Thread as VB is apartment threaded and DTS is free threaded
For Each oStep In oPKG.Steps
oStep.ExecuteInMainThread = True
Next
oPKG.Execute ' execute the package
' check for errors from step/s
For Each oStep In oPKG.Steps
If oStep.ExecutionResult = DTSStepExecResult_Failure Then
DidErrorOccurSomewhere = "Y"
End If
Next
If DidErrorOccurSomewhere = "Y" Then
msgbox "error in a step/s", vbOkOnly
End if
Exit Function
err_handler:
MsgBox "Error # " & Err & ": " & Error(Err)
End Function
'====================END=======================
Needs a bit of tidying up as I took it straight from some larger set of code I have. It will show a general error and an error in a step.
You can just call the package from another package with basically the same code and set the value of the variables there as well. It's entirely a preference thing.
January 26, 2005 at 9:23 am
Johnathan,
I liked your code and it worked for me but when I try to do this from the computer of someone who is not a sysadmin it does not work. Is a nonsysadmin able to run this? If so, what permissions need to be set to do this?
Sue
January 27, 2005 at 8:00 am
Glad to have helped.
The code above is using a trusted connection to connect sql server. You can specify a sql serer username and password in the connection string to achive this. Try this. I am not too sure about the syntax, but it should work for you. Not tested by myslef, though.
Make sure the sql server user has the necessary permissions.
oPKG.LoadFromSQLServer oServerName,"USERNAME" ,"PASSWORD" , _
DTSSQLStgFlag_Default, , , , oPKGname ' connect to the package
If you paste it into your module, you should be able to step throguh each paramater to make sure the username, password etc is correct.
January 27, 2005 at 8:13 am
If you are using SQL Server 2000 you could also create a package and use the 'Execute Package Task'. This task allows for the transfer of values between the inner (package you are trying to run) and the outer (package that contains the 'execute package task') packages.
*******************
I would expect that the login that runs the code needs to have the same rights as one that can run the package.
As for sysadmin vs non-sysadmin that all depends on what the package does. i.e. Does it access files, other servers, objects with specific permissions etc.
I forget exactly what this concept is called but if you are running the code on one server (say in a scheduled job) that kicks off a package on another server which in turn accesses a third server you can run into problems with login rights essentially not making the hops from one server to the next.
In our environment we have a Windows network login that is used to run all MSSQL and SQLServerAgent services so we avoid the problem. There are other ways around it but by the time we discovered it was a problem we would have had to make changes to all 45 of our SQL Servers and just used a different workaround.
"I met Larry Niven at ConClave 27...AND I fixed his computer. How cool is that?"
(Memoirs of a geek)
January 28, 2005 at 10:20 am
See thread: Can you set "INNER PACKAGE GLOBAL VARIABLES" through an Acti... for an example of changing Global Variables in an Execute Package Task.
-->Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply