Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2005 Forums  Express Edition and Compact Edition (2005)  Problem with ActiveX Script Task

Author  Topic  foody Starting Member 3 Posts Posted - 2007-09-24 : 12:54:13 Hello, I have a problem with the scripting for the ActiveX Script Task, below is the diagram on my SQL Server Enterprise [imghttp://www.willhostforfood.com/files/21413/ScreenShot1.JPG.jpg[/img] In the "Delete Sheet Via.." script execute correctly, the second "Create Sheet with..." script also execute correctly by itself. The script "Drop Unorderables" and "Execute SQL Task" both correctly executes by themselves. However when I run the entire tasks I get the following error for the "Delete Sheet Via.." script I get a blank prompt error message. For the Execute SQL Task I get an error "Undefined=Tab;e 'Unorderables" already exists." Which makes no sense as it suppose to be deleted by the Drop Undorderables task. For the Create Sheet With SQL task I get this error: "The Microsoft Jet Database engine would not find the object '_VA4'. Make sure the object exists and that you spell its name and the path name correctly. For the "Create Sheet With SQL task" below is the source code: '********************************************************************** ' Visual Basic ActiveX Script '************************************************************************ Function Main() Dim rs, strConnect, sql, conn, query 'SECURITY NOTE - When possible, use Windows Authentication. strConnect = "<Censored>" set conn = CreateObject("ADODB.Connection") conn.ConnectionString = strConnect sql = "Select distinct StoreCode from StoreLocations Where StoreCode <> 'WI1' order by StoreCode desc" conn.Open Set rs = CreateObject("ADODB.Recordset") rs.LockType = 1 'adLockReadOnly rs.CursorType = 1 'adOpenKeyset rs.Open sql, conn Dim oPackage ' the Package Object Dim task Set oPackage = DTSGlobalVariables.Parent oPackage.Tasks("Drop table RecalledItems Task").Properties("SQLStatement").Value = "SELECT 1" do until rs.EOF oPackage.Tasks(2).Properties("SQLStatement").Value = GetSQL(rs ("StoreCode")) oPackage.Steps("Create Table RecalledItems Step").Execute rs.MoveNext loop rs.Close conn.Close ' Clean Up Set rs = Nothing Set strConnect = Nothing oPackage.Tasks("Create Table RecalledItems Task").Properties("SQLStatement").Value = "SELECT 1" Main = DTSTaskExecResult_Success End Function Function GetSQL(StoreCode) Dim strSQL 'strSQL = "CREATE TABLE `" + StoreCode +"` (`StoreCode` VarChar (50) , `Item` Long , `Description1` VarChar (200) , `attr` VarChar (100) , `size` VarChar (100) , `Description2` VarChar (200) , `OH` Long )" strSQL = "CREATE TABLE `_" + StoreCode +"` (`Style` VarChar (50) , `Description` VarChar (200) , `Color` varchar(70) , `Done` varchar(10), `2XS/J23/J30` varchar(20), `XS/0-3/J24/J31` varchar(20), `S/3-6/T2/Y8/0/J25/J32` varchar(20), `M/6-12/T4/Y10/1/J26` varchar(20), `L/12-18/T6/Y12/2/J27/J34` varchar(20), `XL/18-24/3/J28` varchar(20), `2XL/4/J29/J36` varchar(20), `3XL` varchar(20))" 'msgbox(strSQL) GetSQL = strSQL End Function --------- For the "Create Sheet with SQL" task below is the script: '********************************************************************** ' Visual Basic ActiveX Script '************************************************************************ Function Main() Dim rs, strConnect, sql, conn, query 'SECURITY NOTE - When possible, use Windows Authentication. strConnect = "<censored>" set conn = CreateObject("ADODB.Connection") conn.ConnectionString = strConnect sql = "Select distinct StoreCode from StoreLocations Where StoreCode <> 'WI1' order by StoreCode desc" conn.Open Set rs = CreateObject("ADODB.Recordset") rs.LockType = 1 'adLockReadOnly rs.CursorType = 1 'adOpenKeyset rs.Open sql, conn Dim oPackage ' the Package Object Dim task Set oPackage = DTSGlobalVariables.Parent oPackage.Tasks("Drop table RecalledItems Task").Properties("SQLStatement").Value = "SELECT 1" do until rs.EOF oPackage.Tasks(2).Properties("SQLStatement").Value = GetSQL(rs ("StoreCode")) oPackage.Steps("Create Table RecalledItems Step").Execute rs.MoveNext loop rs.Close conn.Close ' Clean Up Set rs = Nothing Set strConnect = Nothing oPackage.Tasks("Create Table RecalledItems Task").Properties("SQLStatement").Value = "SELECT 1" Main = DTSTaskExecResult_Success End Function Function GetSQL(StoreCode) Dim strSQL 'strSQL = "CREATE TABLE `" + StoreCode +"` (`StoreCode` VarChar (50) , `Item` Long , `Description1` VarChar (200) , `attr` VarChar (100) , `size` VarChar (100) , `Description2` VarChar (200) , `OH` Long )" strSQL = "CREATE TABLE `_" + StoreCode +"` (`Style` VarChar (50) , `Description` VarChar (200) , `Color` varchar(70) , `Done` varchar(10), `2XS/J23/J30` varchar(20), `XS/0-3/J24/J31` varchar(20), `S/3-6/T2/Y8/0/J25/J32` varchar(20), `M/6-12/T4/Y10/1/J26` varchar(20), `L/12-18/T6/Y12/2/J27/J34` varchar(20), `XL/18-24/3/J28` varchar(20), `2XL/4/J29/J36` varchar(20), `3XL` varchar(20))" 'msgbox(strSQL) GetSQL = strSQL End Function ---------------------------------- For the Drop Undorderables task below is the script: Drop table `Unorderables` For the Execute SQL Task...task below is the script: CREATE TABLE `Unorderables` (`Item` varchar(20)) The intention is to fill the excell file: FloorRestockTemplate_Fahed.xls with data, remove the worksheet "Unorderable" which comes from the task "Drop Unorderable" and remove the worksheet "_VA4" which comes from the task "Create Sheet with SQL." but I get the errors I described above. If you guys want further questions to understand my problem I am more than happy to assist in this regard. If someone can help me, it would be greatly appreciate it. Thanks in advance.