| Author |
Topic |
|
igorblackbelt
Constraint Violating Yak Guru
407 Posts |
 Posted - 2005-10-11 : 16:20:52
|
| Hello All -How do I tell my DTS to create a Global variable from a table value ?---Thanks!Igor. |
|
|
mwjdavidson
Aged Yak Warrior
735 Posts |
Posted - 2005-10-12 : 05:24:33
|
Assuming you're talking about initialising a global variable with a resultset returned by selecting all rows from a table...Create an ExecuteSQL task with the command:SELECT mt.Field1, mt.Field2, ... FROM dbo.MyTable AS mt double-click the task and select 'Parameters' from the resulting dialog box. Choose the 'Output Parameters' tab and click the 'Rowset' option button. Select the target global variable from the drop down list (or click 'Create Global Variables', go through the process and then select the newly created variable).Mark |
 |
|
|
igorblackbelt
Constraint Violating Yak Guru
407 Posts |
Posted - 2005-10-13 : 15:35:48
|
| I got this part, thanks, Now I don't know how to pass this variable to the Transformation task. I have to build reports for 80 different customers (5 tabs each), I was wondering if I could have 1 transformation for for each tab that would loop thru the variable and generate a report for each client.---Thanks!Igor. |
|
|
|
mwjdavidson
Aged Yak Warrior
735 Posts |
Posted - 2005-10-14 : 05:11:17
|
| HiAre the reports all in the same format? I'm assuming you're just constraining a resultset based on the client. If this is the case, I'd suggest having a package that does the transform. The source of the transform can be a call to a stored procedure that takes a parameter specifying the client constraint. This parameter can then be a replaced with a global variable. Have a second package that does the loop by calling the above package multiple times, passing in the global variable.Mark |
|
|
|
igorblackbelt
Constraint Violating Yak Guru
407 Posts |
Posted - 2005-10-14 : 09:45:47
|
| Yep, each client will receive it's own report all of them from the same template, all that changes is the client name which is what i'm trying to make it loop and change using ActiveX and Global Variables. At least I'm getting somewhere... If you or someonelse have anything as far as code to show me, let me know.Thanks for the help!---Thanks!Igor. |
|
|
|
anuj164
Starting Member
49 Posts |
Posted - 2005-10-14 : 11:25:29
|
| You can use stored procedure using openrowset you can accomplish the same resultsselect * from openrowset('SQLOLEDB','Server';'UID';'PWD','select distinct t2.* from test t2').Refer to BOL for more details.Anuj |
|
|
|
igorblackbelt
Constraint Violating Yak Guru
407 Posts |
Posted - 2005-10-14 : 16:10:29
|
| K, I can give that a shot, I was talking to more experienced SQL developer here @ my work and he told me to have 2 packages, one feeding the variables and the other one collecting these variables and generating the report... Let's see how it comes out... Let me know if you guys have anything else to add to it...---Thanks!Igor. |
|
|
|
mwjdavidson
Aged Yak Warrior
735 Posts |
Posted - 2005-10-17 : 08:48:58
|
Hi Igor, Yes, that's the approach I was advocating. To break it down a bit, here's how I would attack it:1. Create stored proc to return the list of clients (we'll call that GetClientList)2. Create stored proc to return resultset to be used in report when passed a client id (GetResults (@ClientID INT))3. Create looping package. NB. I tend to use an Execute SQL task to get a resultset rather than creating the connection and recordset in code. a. Create Execute SQL task calling GetClientList. Output results to global variable (ClientList).b. Create ActiveX Script task that loops through the recordset and displays a message box for each row (to test). Something like this (ignore commented lines for now):'**********************************************************************' Visual Basic ActiveX Script'************************************************************************Function Main() Dim objChildPackage Dim objClientList'Create local ADO Recordset Set objClientList = DTSGlobalVariables("ClientList").Value'Create child package and load from SQL Server 'Set objChildPackage = CreateObject("DTS.Package") 'objChildPackage.LoadFromSQLServer "MyServer", "", "", 256, , "{MyPackageGUID}"'Loop through recordset Do While not objClientList.eof Msgbox objClientList.Fields("ClientID").Value'Pass global variable to child package and execute 'objChildPackage.GlobalVariables.Item("ClientID") = objClientList.Fields("ClientID").Value 'objChildPackage.Execute'Move to next record objClientList.MoveNext Loop'Destroy object 'objChildPackage.UnInitialize 'Set objChildPackage = Nothing Set objClientList = Nothing'Return Success Main = DTSTaskExecResult_SuccessEnd Functionc. Add precedence constraint so that the ExecuteSQL task runs before the ActiveX script task.4. Create the other package containing a connection to the db, a connection to your destination file, and the appropriate transformations. Add the call to the stored procedure as the source of the transformation and specify a global variable as the source of the client id parameter. Run this package with a few different client ids to test it.5. Finally, replace the remove the messagebox line from the script in the calling package and uncomment the commented out lines. Replace the "{MyPackageGUID}" with the actual Package GUID of your child package (get it from package properties).Mark |
|
|
|
igorblackbelt
Constraint Violating Yak Guru
407 Posts |
Posted - 2005-10-18 : 13:11:26
|
| I'm working on this code you provided to me, I see what you meant, makes more sense to me now... I'll keep you posted, thanks!---Thanks!Igor. |
|
|
|
igorblackbelt
Constraint Violating Yak Guru
407 Posts |
Posted - 2005-10-18 : 14:08:42
|
| Mark -On step 5, I need to create a connection to my server, we use SQL Server Authentication and that's causing an "Login Failed for user (null)...I tried :objChildPackage.LoadFromSQLServer "MyServer", "UserName", "Password", 256, , "{PKGUID}"I didn't work, Am I missing anything ? How do I create a Trusted SQL Server Connection ?Thanks!---Thanks!Igor. |
|
|
|
igorblackbelt
Constraint Violating Yak Guru
407 Posts |
Posted - 2005-10-18 : 14:57:51
|
| I usedobjChildPackage.LoadFromSQLServer "machinename", "uid", "pw", DTSSQLStgFlag_Default,"","","","packagename"and it worksAlso -Is that possible to pass 2 variables to the other ActiveX ? Or Should I do on a separate Activex ?---Thanks!Igor. |
|
|
|
igorblackbelt
Constraint Violating Yak Guru
407 Posts |
Posted - 2005-11-11 : 13:30:44
|
| I tried this, to pass 2 variables to the child package, but it's giving me an error "Expected Statement" on line 42 (End Function), do I need to reset the variables ?Thanks!'**********************************************************************' Visual Basic ActiveX Script'**********************************************************************Function Main() Dim objAllianceName Dim objReportName Dim objChildPackage'Create local ADO Recordset Set objAllianceName = DTSGlobalVariables("AllianceName").Value Set objReportName = DTSGlobalVariables("ReportName").Value'Create child package and load from SQL Server Set objChildPackage = CreateObject("DTS.Package") objChildPackage.LoadFromSQLServer "Server", "User", "Pass", DTSSQLStgFlag_Default,"","","","SQL_PK_1"'Loop through recordset Do While not objAllianceName.eof Do While not objReportName.eof' Msgbox objAllianceName.Fields("AllianceName").Value'Pass global variable to child package and execute objChildPackage.GlobalVariables.Item("AllianceName") = objAllianceName.Fields("AllianceName").Value objChildPackage.GlobalVariables.Item("ReportName") = objAllianceName.Fields("ReportName").Value objChildPackage.Execute'Move to next record objAllianceName.MoveNext objReportName.MoveNext Loop'Destroy object objChildPackage.UnInitialize Set objChildPackage = Nothing Set objAllianceName = Nothing Set objReportName= Nothing'Return Success Main = DTSTaskExecResult_SuccessEnd Function---Thanks!Igor. |
|
|
|
|
Global Variable from Table Value