Help Using VBScript In An ActiveX Task In DTS Package
Jan 16, 2004
Hi,
Thanks for reading.
I am creating a DTS package to import a .txt file into sql. I have everything in place, but the text file needs to have the last record deleted before the import. I need help with this part
I would like to delete the last record from a fixed width text file before I import it into sql. The number of rows will vary from file to file.
Can any one offer suggestions on the best way to do this.
I understand that I have to use the FSO to open and read the file, but I am not sure the best way to proceed after that.
Thanks in advance,
Steve
View 1 Replies
ADVERTISEMENT
Oct 27, 2006
I need to retrieve the Global Variables set in my package configuration file within an ActiveX Script Task within an SSIS package. In DTS, I could access the Global Variables to execute a SQLXMLBulkLoad for the following statement:
==========================================
Function Main()
Response.Expires=-1
set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
objBL.ConnectionString =
"provider=SQLOLEDB.1;server=ABC123;database=MyDB;Trusted_Connection=Yes;"
objBL.KeepIdentity = False
objBL.CheckConstraints = False
objBL.Execute DTSGlobalVariables("gv_XSDSchemaFile").Value, DTSGlobalVariables("gv_XMLFullPath").Value
Main = DTSTaskExecResult_Success
set objBL=Nothing
End Function
=========================================
I have tried using the Script Task to write this in VB.NET, however the MSXML4.0 is not exposed within the limited object model of the Script Task Designer. I have written a Data Flow Object using the XML Source, however it requires quite a bit of effort to have the Data Flow Component parse the XML (with 10 hierarchical nodes), transform each and provide a SQL Server Destination. This works, however the XML Source Component requires a hardcoded reference to the XSD Schema file and does not allow for a Global Variable to used. (They do provide this functionality for the XML file source though).
My requirement is to allow for the Global Variable to be passed for the Schema file at runtime. The only way I can think of is to recreate what I was doing in DTS where I could simply pull in the XML and XSD Global Variables and execute the SQLXMLBulkLoad in VB Script.
Any ideas on how to write this in VBScript within the ActiveX Script Task in SSIS?...
Michael
View 1 Replies
View Related
Aug 23, 2007
Using ActiveX Script Task it is possible create office automation objects such as Word and Excel typically using the CreateObject() Syntax. Can you use this syntax to create a Package object using CreateObject("DTS.Package")? If not Why not? I am not sure whether it was possible in DTS.
I am aware of the fact that ActuveX Script Task is included in this Version of SSIS and will be deprecated.
View 2 Replies
View Related
Jul 20, 2005
Below is a code snippet that is throwing the following error:Error Source: Microsoft DTS PackageError Description: Error Code: 0Error Source= Microsoft VBScript runtime errorError Description: Object required: 'Server'Error on line 13In my code line 13 is the following:Set Cnxn = Server.CreateObject("ADODB.connection")I did set up a Microsoft OLE DB connection in my DTS package for thisActiveX Task. I'm new to VBScript and ActiveX. Any help would beappreciated.Thanks,-p'************************************************* *********************' Visual Basic ActiveX Script'************************************************* ***********************Function Main()Main = DTSTaskExecResult_Success' connection, command and recordset variablesDim Cnxn, strCnxn' create and open connectionSet Cnxn = Server.CreateObject("ADODB.connection")strCnxn = "data source=Pluto;initial catalog=Stats;User Id=sa;password=;"Cnxn.Open strCnxnEnd Function
View 2 Replies
View Related
Sep 19, 2007
I have a DTS package which executes an ActiveX control written in vbscript. I need to move the ActiveX code out of the DTS environment. The code is too big to execute in a Sql Server Job Step as type "ActiveX Script." Do I have any other easy options?
Thanks,
Michael
View 4 Replies
View Related
Sep 13, 2006
I have a DTS package that I brought over from SQL server 2000 in to SQL Server 2005. I have installed all of the legacy components to run the DTS packages but I need to debug an ActiveX script task. In SQL Server 2000 I could turn on Just-In-Time debugging and use the stop operator (in my vbscript) to break the running script and launch the debugger.
I don't see how to do this in SQL Server 2005 Management Studio. Is it possible to debug a script object in a DTS package running in SQL Server 2005?
Jay Abbott
View 1 Replies
View Related
Oct 1, 2007
I am getting an Error on Line 4 of sample code, any idea, where?
if isdate(mid(DTSSource("Col016"), 5, 2) & "/" &
right(DTSSource("Col016"), 2) & "/" & left(DTSSource("Col016"), 4)) then
DTSDestination("DOB") = mid(DTSSource("Col016"), 5, 2) &
"/" & right(DTSSource("Col016"), 2) & "/" & left(DTSSource("Col016"), 4)
else
DTSDestination("DOB") = NULL
end if
View 6 Replies
View Related
Jul 23, 2005
I'm looking for an example of how to execute an existing DTS* packagefrom anASP (VB)script and would appreciate any and all response. *I don'tevenknow if it's possibleThanks- Chuck GattoDan Guzman Apr 27 2000, 12:00 am show optionsNewsgroups: comp.databases.ms-sqlserverFrom: "Dan Guzman" <DGuz...@nospamplease-earthlink.net&g*t; - Findmessages by this authorDate: 2000/04/27Subject: Re: Executing a DTS Package using an ASP (VBScript) ScriptReply to Author | Forward | Print | Individual Message | Show original| Report AbuseThis VBScript example loads and executes an existing DTS pac*kage fromSQLServer.Option ExplicitConst PackageName = "PackageName"Const ServerName = "ServerName"Const UserName = "UserName"Const Password = "Password"Dim DTSPkSet DTSPk = CreateObject("dts.package")DTSPk.LoadFromSQLServer ServerName, UserName ,Password,,,,,*PackageNameDTSPk.ExecuteIf DTSPk.Steps(1).ExecutionResult = 0 ThenResponse.Write "Package execution completed"ElseResponse.Write "Package execution failed"End IfSet DTSPk = NothingYou can also create the entire package from scratch from wit*hin yourasp andexecute it.Hope this helps.Chuck Gatto <cga...@anchorsystems.com> wrote in messagenews:8eapo8$frj$1@slb7.atl.mindspring.net...- Hide quoted text -- Show quoted text -[color=blue]> I'm looking for an example of how to execute an existing D*TS[/color]package froman[color=blue]> ASP (VB)script and would appreciate any and all response.* I don't[/color]even[color=blue]> know if it's possible> Thanks> - Chuck Gatto[/color]Chuck Gatto May 1 2000, 12:00 am show optionsNewsgroups: comp.databases.ms-sqlserverFrom: "Chuck Gatto" <cga...@anchorsystems.com> - Find messages by thisauthorDate: 2000/05/01Subject: Re: Executing a DTS Package using an ASP (VBScript) ScriptReply to Author | Forward | Print | Individual Message | Show original| Report AbuseBelow code works 100% in VB but the load fails in ASP. I g*et..."Microsoft OLE DB Provider for SQL Server. Login failed for *user "".error.I think the IIS (server a) is set for NT auth. and sql7 (on *server b)aswell but I can't be sure.Any idea what I should look for.Thanks"Dan Guzman" <DGuz...@nospamplease-earthlink.net> wrote in m*essagenews:sgi3hi617qo89@corp.supernews.com...- Hide quoted text -- Show quoted text -[color=blue]> This VBScript example loads and executes an existing DTS p*ackage[/color]from SQL[color=blue]> Server.[/color][color=blue]> Option Explicit> Const PackageName = "PackageName"> Const ServerName = "ServerName"> Const UserName = "UserName"> Const Password = "Password"> Dim DTSPk> Set DTSPk = CreateObject("dts.package")> DTSPk.LoadFromSQLServer ServerName, UserName ,Password[/color],,,*,,PackageName[color=blue]> DTSPk.Execute> If DTSPk.Steps(1).ExecutionResult = 0 Then> Response.Write "Package execution completed"> Else> Response.Write "Package execution failed"> End If> Set DTSPk = Nothing[/color][color=blue]> You can also create the entire package from scratch from w*ithin[/color]your aspand[color=blue]> execute it.[/color][color=blue]> Hope this helps.[/color][color=blue]> Chuck Gatto <cga...@anchorsystems.com> wrote in message> news:8eapo8$frj$1@slb7.atl.mindspring.net...[color=green]> > I'm looking for an example of how to execute an existing* DTS[/color][/color]packagefrom[color=blue]> an[color=green]> > ASP (VB)script and would appreciate any and all respons*e. I[/color][/color]don't even[color=blue][color=green]> > know if it's possible> > Thanks> > - Chuck Gatto[/color][/color]Dan Guzman May 1 2000, 12:00 am show optionsNewsgroups: comp.databases.ms-sqlserverFrom: "Dan Guzman" <DGuz...@nospamplease-earthlink.net&g*t; - Findmessages by this authorDate: 2000/05/01Subject: Re: Executing a DTS Package using an ASP (VBScript) ScriptReply to Author | Forward | Print | Individual Message | Show original| Report AbuseYou can specify a trusted connection with flag 256 instead o*fusername andpassword. For example:DTSPk.LoadFromSQLServer ServerName, , , 256,,,,PackageNa*meAssuming this is an intranet application running under NT 4.*0 and youwantto execute the package under the invoking user's account, yo*u can dothis asfollows:Specify 'clear text' for the IIS Directory Securityauth*enticationRemove 'Everyone' from the access list on the files (req*uiresNTFS) andgrant permissions to the usersGrant logins access to the database serverWith this method, users must enter their DomainUserName and* passwordwhenprompted.NT authentication presents a challenge when multiple servers* areinvolvedbecause NT 4.0 does not support delegation. Seehttp://msdn.microsoft.com/workshop/...re/security.asp fordetails.I understand Windows 2000 provides delegation capabilities b*ut thiscan be abit tricky to implement.BTW, if your DTS package does not access SQL Server, you can* save itto afile and use the LoadFromStorageFile method instead.Hope this helps.If you need to useChuck Gatto <cga...@anchorsystems.com> wrote in messagenews:8ekrkd$pmq$1@slb7.atl.mindspring.net...- Hide quoted text -- Show quoted text -[color=blue]> Below code works 100% in VB but the load fails in ASP. I* get...> "Microsoft OLE DB Provider for SQL Server. Login failed fo*r user[/color]"".error.[color=blue]> I think the IIS (server a) is set for NT auth. and sql7 (o*n server[/color]b) as[color=blue]> well but I can't be sure.> Any idea what I should look for.> Thanks[/color]Chuck Gatto May 6 2000, 12:00 am show optionsNewsgroups: comp.databases.ms-sqlserverFrom: "Chuck Gatto" <cga...@anchorsystems.com> - Find messages by thisauthorDate: 2000/05/06Subject: Re: Executing a DTS Package using an ASP (VBScript) ScriptReply to Author | Forward | Print | Individual Message | Show original| Report AbuseHey DanThanks again. I really appreciate your input and help.I actually solved the problem by calling dtsrun...I apologize for the delay getting back w/you but thee recent* virusatacksidetracked me.Thanks again."Dan Guzman" <DGuz...@nospamplease-earthlink.net> wrote in m*essagenews:sgsfh8spoog87@corp.supernews.com...- Hide quoted text -- Show quoted text -[color=blue]> You can specify a trusted connection with flag 256 instead* of[/color]username and[color=blue]> password. For example:[/color][color=blue]> DTSPk.LoadFromSQLServer ServerName, , , 256,,,,Package*Name[/color][color=blue]> Assuming this is an intranet application running under NT *4.0 and[/color]you want[color=blue]> to execute the package under the invoking user's account, *you can[/color]do thisas[color=blue]> follows:[/color][color=blue]> Specify 'clear text' for the IIS Directory Security[/color]au*thentication[color=blue]> Remove 'Everyone' from the access list on the files (r*equires[/color]NTFS)and[color=blue]> grant permissions to the users> Grant logins access to the database server[/color][color=blue]> With this method, users must enter their DomainUserName a*nd[/color]password when[color=blue]> prompted.[/color][color=blue]> NT authentication presents a challenge when multiple serve*rs are[/color]involved[color=blue]> because NT 4.0 does not support delegation. See> http://msdn.microsoft.com/workshop/...re/security.asp for[/color]details.[color=blue]> I understand Windows 2000 provides delegation capabilities* but this[/color]can bea[color=blue]> bit tricky to implement.[/color][color=blue]> BTW, if your DTS package does not access SQL Server, you c*an save[/color]it to a[color=blue]> file and use the LoadFromStorageFile method instead.[/color][color=blue]> Hope this helps.[/color][color=blue]> If you need to use> Chuck Gatto <cga...@anchorsystems.com> wrote in message> news:8ekrkd$pmq$1@slb7.atl.mindspring.net...[color=green]> > Below code works 100% in VB but the load fails in ASP. * I get...[/color][/color][color=blue][color=green]> > "Microsoft OLE DB Provider for SQL Server. Login failed *for user[/color][/color]"".[color=blue]> error.[color=green]> > I think the IIS (server a) is set for NT auth. and sql7 *(on[/color][/color]server b) as[color=blue][color=green]> > well but I can't be sure.> > Any idea what I should look for.> > Thanks[/color][/color]chris.duni...@agwsha.nhs.uk Jan 31, 10:05 am show optionsNewsgroups: comp.databases.ms-sqlserverFrom: chris.duni...@agwsha.nhs.uk - Find messages by this authorDate: 31 Jan 2005 10:05:00 -0800Subject: Re: Executing a DTS Package using an ASP (VBScript) ScriptReply | Reply to Author | Forward | Print | Individual Message | Showoriginal | Report AbuseHi,I've tried adding this function to my ASP pages and get the *followingerror message;Microsoft VBScript runtime error '800a01ad'ActiveX component can't create object: 'DTS.Package'/asp/pages/dts.asp, line 21Does anyone have any idea what I need to fix to get the DTS *to work?I'm v. new to SQL Server and ASP so any help would be apprec*iated.Many thanks, Chris Dunigan
View 4 Replies
View Related
Feb 9, 2004
I have no idea what's going on.. but when I open the SQL DTS designer.. there's no ActiveX Script task on the left toolbar anymore. It used to be there. How do I get it back?? I remember playing around with some dts dll files on my machine... maybe I did something that made it go away (note: the DB server is not on my machine.. I just run Enterprise Manager on my machine to access it). Should I reinstall Enterprise Manager?
View 1 Replies
View Related
Apr 3, 2006
Within a SQL 2000 DTS Package I have an ActiveX Script that would go within my transform tasks and update the queries by concatenating a "Where" clause with a date from a database table. This way I could keep track of when the last time I updated the table so that I could only bring down the rows since the last run. How can this be done within SSIS? I've been looking and I'm getting confused. Any help would be greatly appreciated.
View 2 Replies
View Related
Jan 27, 2006
Hi,
I'm trying to reset the below Global varialbe in SSIS - Activex script.
Function Main()
DTSGlobalVariables("GxvFilename").value =null
Main = DTSTaskExecResult_Success
End Function
Getting the below Error when executing the package.
[ActiveX Script Task] Error: Retrieving the file name for a component failed with error code 0x060339E4
Please provide me solution for this.
Thanks,
View 2 Replies
View Related
May 12, 2006
I'm having a bear of a time with this. I have a recordset that pulls data from one, single table. I would like to iterate through the set, do some processing based on that data, then delete all of the records in that recordset.
I'm trying to do something like the following:
Set dbConn = CreateObject("ADODB.Connection")
Set dbRS = CreateObject("ADODB.Recordset")
dbConn.Open("myConnectString")
dbRS.open dbSQLCmdText, dbConn, 2, 4
While Not dbRS.EOF
Do some stuff
dbRS.Delete
Wend
dbRS.BatchUpdate
dbRS.Close
dbConn.Close
I get an error stating:
"Multiple-Step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done."
This seems to indicate the connection doesn't think it's capable of reconciling the recordset. Is there a way to do this in one batch or should I stick with executing a single delete statement for every record in my recordset?
View 2 Replies
View Related
Jun 6, 2006
I'm attempting to call a storedprocedure from within an ActiveX task ina DTS and am getting a "Command Text was not set for the commandobject" error. I have no problem if I replace the stored procedure callwith the actual SQL.Is it possible to directly call a SP via an ActiveX task? If yes, couldsomebody help me with the syntax please?This is what I have currently haveSet Conn = CreateObject("ADODB.Connection")SQL = "exec (MySP)"set RLoop=conn.Execute(SQL)do while (NOT RLoop.EOF)msgbox RLoop(1)RLoop.MoveNextLoopTIAKarthik
View 1 Replies
View Related
Jul 3, 2006
We have a SQL Server 2005 Cluster that we are trying to send email from to notify when certain jobs have completed, failed, etc. We are having a bit of a problem getting the email to work and I believe that the failure is at the SMTP server, not at the SQL level.
That being said, I'm trying to create a simple SSIS package that I can use to test connectivity to the SMTP server with and send email. I've added an ActiveX Script Task that calls a COM object that actually sends the email. I keep getting a "Function not found." error when I try to execute the package, and I have no idea why I'm getting that. It says that the errors were found during validation.
Can anyone help?
View 3 Replies
View Related
Jul 6, 2001
Hi,
I've written a DTS Package to pull information from a number of servers. I have a list of servers (Source Servers) and for each one I automatically update the Datasource property of the Source Server connection using vbscript in an ActiveX Script task and a global variable.
I have a transformation that occurs between the Source server and Target server.
As I process each Source Server in turn, the global variable gets set correctly and so does the Datasource property of the Source Server connection. However the transformation still runs against the first Source Server that was processed.
Looking at BOL it appears that a transformation stays connected even after it has finished. Therefore I assume that although I change the Datasource of the connection to a new server, it still runs against the first server that was used.
My question is how do I get the connection to run against the updated Datasource, i.e. do I need to do a refresh of the Connection, disconnect or what ?
Any ideas would be much appreciated.
thanks
View 2 Replies
View Related
Nov 2, 2000
I'm looking for a way to get the name of the server on which the DTS package lives.
I copy packages between servers. The problem is that everytime a package is copied
to different server, I have to change the reference in the connection strings to point
to the new server name. I'd like to find an automatic way to interrogate the server
name where the package currently lives and dynamically change connection strings
from within an ActiveX task. That would cut maintenance way down.
View 2 Replies
View Related
Apr 8, 2003
Hi,
I have several .xls files in a folder. I want to process all .xls file one by one. The following script will give the file name using a loop. How I can call another package by passing the name of file to it.
Dim objFSO, objFolder, colFiles, objFile
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(DTSGlobalVariables("gvFolder").value)
Set colFiles = objFolder.Files
If colFiles.Count > 0 then
For Each objFile in colFiles
If Ucase(Right(objFile,4)) = ".XLS" then
' How to call a another Package by Passing the objFile to it
End if
Next
End if
Thanks
:)
View 3 Replies
View Related
Aug 29, 2001
I have created a DTS job on one of my servers that fails when it is scheduled. If I execute the job directly from the DTS console it runs successfully. However, if I schedule the job, it returns the following error:
... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: DTSStep_DTSActiveScriptTask_1 DTSRun OnError: DTSStep_DTSActiveScriptTask_1, Error = -2147220482 (800403FE) Error string: Error Code: 4096 Error Source= Microsoft Outlook Error Description: Unable to perform the operation. The server is not available. Contact your administrator if this condition persists. Could not complete the operation because the service provider does not support it. Error on Line 23 Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 1100 Error Detail Records: Error: -2147220482 (800403FE); Provider Error: 0 (0) Error string: Error Code: 4096 Error Source= Microsoft Outlook Error Description: Unable to perform the operation. The server is not available. Contact your administrator if this condit... Process Exit Code 1. The step failed.
Does anyone know why this is occuring?
View 1 Replies
View Related
Feb 27, 2007
when i run activex Script it's shows this error
[ActiveX Script Task] Error: Retrieving the file name for a component failed with error code 0x001B6438
View 2 Replies
View Related
Jun 11, 2007
Hello All,I am trying to create a DTS package.I have two tables tbl_A and tbl_B with similar data/rows but noprimary keys.tbl_A is master.I would like this package to query tbl_A and tbl_B and find1)all rows in tbl_A that are different in tbl_B, 2)all rows in tbl_Athat are not present in tbl_B and3)all rows in tbl_B that are not present in tbl_A, and then just showthose rows.Can this be done with a simple UNION?Perhaps this could produce a temp Table that can be dropped once theDTS package exists successfully.The 2nd part after all the above rows are retrieved is that I wouldlike to add an addional Column to the retrieved data called STATUSwhich has 3 possible values(letters) at the end of each row...M (modified) means that row exists in tbl_B but has 1 or moredifferent columnsA (add) means this row exists in tbl_A but not in tbl_BD (delete) means this row exists in tbl_B but not in tbl_AI'm hopping this DTS package would output a nice comma seperated TXTfile with only...1) rows from tbl_A that are different in tbl_B (STATUS M)2) rows from tbl_A that are not present in tbl_B (STATUS A)3) rows from tbl_B that are not present in tbl_A (STATUS D)Can a DTS package in MS SQL be used to perfom all of the above tasks?I would very much appreciate any help or any advise.Thanks in advance :-)
View 7 Replies
View Related
Jul 3, 2007
I have a SQL2000 DTS package that executes vbscript to loop through a recordset which:
- runs a stored procedure and populated tables
- builds a recordset from the populated tables to write records to an Excel file
- writes status to text files with either the error or success notices
I use FSO to set up the success and error files, but the scheduled job in SQL2005 which calls the SSIS package returns the following error:
"Retrieving the file name for a component failed with error code 0x0015F74C"
I can successullly run this (vbscript) in both the SSIS package via the BI Development Studio and in MS Access (exactly the same code in both) - but not as a SSIS package called in a scheduled job in SQL2005.
I am at an impasse with this ... any and ALL assistance would be GREATLY appreciated.
TIA,
Bob
View 1 Replies
View Related
Jul 28, 2006
This error occurs when the ActiveX task tries to execute:
[ActiveX Script Task] Error: Retrieving the file name for a component failed with error code 0x001B6438.
Anybody know how to troubleshoot these errors? I can't find anything on this error code. The same script works in DTS.
View 8 Replies
View Related
Sep 21, 2006
Hi everyone,
For first time I'm testing this task and surprisingly, when I try "Edit Package" option:
1)The DTS host failed to load or save the package properly
2)The selected package cannot be opened
3)Error HRESULT E_FAIL has been returned from a call to a COM component
But after these messages you can see all the tasks but they haven't name!!
It seem as if RCW mechanism has failed between managed and unmanaged coded-partially.
I don't dare to follow doing more stuff, I don't know if that package is well-loaded or not from there. ?¿
Any guidance or idea about this?
View 5 Replies
View Related
Jul 19, 2007
Hi friends,
I have a for each loop that populates from a set of flat files into a Sql Server table, I run the Flat file Import via a dts package embedded into Execute DTS 2000 Task. I want to pass the Sourcefile Name that is fetched by the For Each Loop to assign it Global Variable in DTS. how this can be made ?
Thanks
Subhash Subramanyam
View 4 Replies
View Related
Jun 14, 2006
While Creating a script task in Control Flow, I am getting "Package Validation Error". Here is the complete message:
Error at Validate File and Load Data: The task is configured to pre-compile the script, but binary code is not found. Please visit the IDE in Script Task Editor by clicking Design Script button to cause binary code to be generated.
(Microsoft.DataTransformationServices.VsIntegration)
As mentioned in the message, I opened the script IDE and added the code I need. When I close the VSA IDE, package designer displays the same error message.
The worst part of whole story is that if I close the package designer and reopen it, I find that all the code I wrote in the script task has been deleted by the package designer. This is not at all acceptable as I saved the package the and still lost all my work. I did all the coding from scratch for that task.
Please respond if anyone faced similar problem.
Thanks in advance!
Anand
PS: If any one from Microsoft is reading this, please see what you guys are coding there. Due to the buggy software you deliver, I am loosing my credibility.<P< P>
View 5 Replies
View Related
Jun 8, 2007
I am having problems executing a child package from a parent package using the Execute Package Task. I am attempting to run the master package through a SQL Server Agent job.
The SQL Server Agent job is owned by sa. The step that runs the parent package is configured to load the package from the SSIS Package Store on the same server that the job is running.
I have the Execute Package Task configured as follows:
Location: SQL Server
ExecuteOutOfProcess: True
Connecting as a SQL Server login (let's say TestEtl)
I have added the db_dtsoperator database role to both the TestEtl login and the login that SQL Server Agent connects through. I have also configured the child package's reader role to include db_dtsoperator. Per http://msdn2.microsoft.com/en-US/library/ms141053.aspx, this should allow these logins to run the child package.
I have enabled logging of all events in both the parent and child packages. I see the following in the logs when the Execute Package Task executes (omitted portions unrelated to the execution of the child package task):
450939 OnPreExecute ChildPackage 2007-06-08 13:35:17.000 2007-06-08 13:35:17.000 450940 OnPreValidate ChildPackage 2007-06-08 13:35:17.000 2007-06-08 13:35:17.000 450941 OnPostValidate ChildPackage 2007-06-08 13:35:17.000 2007-06-08 13:35:17.000 450942 User: Diagnostic ETL 2007-06-08 13:35:17.000 2007-06-08 13:35:17.000 ExternalRequest_pre: The object is ready to make the following external request: 'IDataInitialize::GetDataSource'.450943 User: Diagnostic ETL 2007-06-08 13:35:17.000 2007-06-08 13:35:17.000 ExternalRequest_post: 'IDataInitialize::GetDataSource succeeded'. The external request has completed.450944 User: Diagnostic ETL 2007-06-08 13:35:17.000 2007-06-08 13:35:17.000 ExternalRequest_pre: The object is ready to make the following external request: 'IDBInitialize::Initialize'.450945 User: Diagnostic ETL 2007-06-08 13:35:17.000 2007-06-08 13:35:17.000 ExternalRequest_post: 'IDBInitialize::Initialize succeeded'. The external request has completed.450946 User: Diagnostic ETL 2007-06-08 13:35:17.000 2007-06-08 13:35:17.000 ExternalRequest_pre: The object is ready to make the following external request: 'IDBCreateSession::CreateSession'.450947 User: Diagnostic ETL 2007-06-08 13:35:17.000 2007-06-08 13:35:17.000 ExternalRequest_post: 'IDBCreateSession::CreateSession succeeded'. The external request has completed.450948 OnError ChildPackage 2007-06-08 13:35:17.000 2007-06-08 13:35:17.000 Error 0x80070005 while preparing to load the package. Access is denied. . 450949 OnError ParentPackage 2007-06-08 13:35:17.000 2007-06-08 13:35:17.000 Error 0x80070005 while preparing to load the package. Access is denied. . 450950 OnTaskFailed ChildPackage 2007-06-08 13:35:17.000 2007-06-08 13:35:17.000 450951 OnPostExecute ChildPackage 2007-06-08 13:35:17.000 2007-06-08 13:35:17.000 450952 OnWarning ParentPackage 2007-06-08 13:35:17.000 2007-06-08 13:35:17.000 SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. 450953 OnPostExecute ParentPackage 2007-06-08 13:35:17.000 2007-06-08 13:35:17.000 450954 PackageEnd ParentPackage 2007-06-08 13:35:17.000 2007-06-08 13:35:17.000 End of package execution.
I am sure that what I am doing is quite common, and I obviously have something misconfigured somewhere - but I'm not sure what my misconfiguration is. Can anyone enlighten me?
View 6 Replies
View Related
Aug 25, 2006
I use a execute package task to run a child package in which I run some sql task.
as the error handle I insert a script task and link a line from execute package task to script task
of course the line is red,
but I found when the child package failed, the execute package task turns red,it stopped
the script task can't be run, I don't konw why?
View 9 Replies
View Related
Jan 19, 2008
Hi,
I am calling one SSIS package from another using the Execute Package Task.
I also need to pass a parameter to the called SSIS package.
Can I do this? If yes, how? If no, then what will be the work-around for this?
Thanks in advance.
View 16 Replies
View Related
Mar 16, 2007
I am using many Execute Package Tasks in a DTS packages to call a number of other DTS packages. The problem is that when I change one of the called DTS packages the execute package task does not run the updated packages. It looks like the execute package task references a Package ID guid instead of the name. Is there a way to make it so a change to a called DTS package will reflect in all references to it
View 2 Replies
View Related
Jul 21, 2007
I am missing something simple that's killing me.
I am trying to execute a child ssis package using the execute package task. I add execute package task to the parent package and in the package task editor, under the package options I choose: filesystem for location, I choose new connection and create a connection to the package on the filesystem I want to execute, packagenamereadonly is blank. I execute the parent package and get an error that I did not specify the name of the package in the execute package task object. Now, since I can't populate the package name, as that option is readonly, I am not sure how to remove my headache. Anyone see what I'm doing wrong? None of these packages have security.
-Aaron
View 1 Replies
View Related
Feb 21, 2007
hello to every body.. well i have a problem.. well i created a package the task of which is to update thedimensions and the fact tables from the tables of source.
i created a configuration file with all the parameters that are nedded (connections managers; parameters of packages).this config file will include all the paremeters need for the execution of other packages
In that package i call an other package (execution package task). this package normally will take the parameters of the parent package isn't it??(i put the parameters of this package to the config file of the parent package).
i must also note that for the second package called there is also a package...
i must also note that i have 2 DWHs (a and b) for testing.
the problem is that when i execute the parent package to DWH b , the sacond package called do not take the parameters of the parent package. instaed it looks to its XML configuration file(in a different location of the config file of the parent package) which contains the connection parameters to DWH a and it calls strored procedures from the DWH a (instead of calling at SP from DHW b).
any ideas??
thnx a LOT0
View 3 Replies
View Related
Feb 13, 2008
Hi All,
I have a master package that has 3 child packages,so iam taking the child packages from the D drive of my QA machine.
I have used environmental variables to define the path in packages configurations,so that same environmental variables can be used to point to production server path.
The package is able to run on production server but it still fetching data from QA server.
Is the any other way to do it better.
How to use the subpackages in production server.
Please help.
Thanks,
SVGP
View 5 Replies
View Related
May 25, 2007
I would like to know if it is possible to use dtexec to run a specific task in a package? If so how?
View 4 Replies
View Related
