Can you call a DTS package using VBA (specifically from Excel). I would like
to have a command button to launch a DTS package that extracts data from the
spreadsheet to SQL Server. Today I have to open up Enterprise Manager to do
this.
Any code examples would be great too if this is possible.
Thanks,
Chad
I run into some issues and really need some expert help here.
Here is the problem. I have two packages (parent.dtsx and child.dtsx). Both package have its own configuration file (parent.dtsConfig and child.dtsConfig). The file Child.dtsConfig contains a variable (i.e. "X") that is to be used by Child.dtsx.
Inside parent.dtsx. there is a package-task that calls into Child.dtsx. It worked perfectly well if I run parent.dtsx using Dtexec or from inside SSIS's IDE.
Now I want to programmably call "parent.dtsx" from my C# code. I loaded package using "app.LoadPackage"... Inside C# code, I want to reconfigure Child-package's variable ("X"). I then loaded in "Child.dtsx". However when I run "parent.dtsx" and child.dtsx still loads the original value for "X". The reconfigured value for "X" is not updated.
Please help on how to get around this issue.
Thanks.
I'm new to 7.0 and to DTS, and I find it all very confusing and need some help. :)
1) I need to automatically import data from a text file on a daily basis. Can someone tell me in short, simple steps how to set this up?
2) I need to export certain data into a new Excel sheet. Can this be triggered from a stored procedure, by calling some function? If yes, is it possible to send parameters to this function?
thanks bunches.
-M
Hi,
I need to call an excell function, xIRR, from my report.
How can I do that?
Thanks,
Igor
i have an SSIS package that exports to an excel file. This works fine. the problem is that it appends the data instead of overwriting the file. Is there any way to overwrite the file like you can with a flat file? I have to email the file everyweek and don't want to have to clear it out manually. Any help would be appreciated
View 2 Replies View RelatedHi SQL Colleagues:
Is it possible for me to call a DTS package from within a stored procedure? If it is not possible to do so directly, would I at least be able to call the package through a job?
Thanks!!!
Don
Hi. Is it possible to call an executable that resides on another server from an SSIS package? If that's not possible, is it possible to call an EXE that resides on the SQL Server box itself? Thank you
View 3 Replies View RelatedI'm a rookie with MSSQL. I need to run a DTS package to export a result setto an MX Excel spread sheet. I need to call the DTS from a stored procedureand pass it three values, depending on the input parameters to the storedproc.DTS package is no problem. Pretty easy with the DTS wizard. My problem isthat I can't figure out how to instansiate the DTS package object from astored proc and pass the three values as parameters to the DTS package sothey can populate the parameters I created in it.I found an article related to it, but I'm too much of a rookie to grasp it.It showed how to do this from a stored procedure:EXEC @hr = sp_OASetProperty @oPKG, 'GlobalVariables("MyGVName").Value,'MyGVValue'IF @hr <> 0BEGINPRINT '*** GlobalVariable Assignment Failed'EXEC sp_displayoaerrorinfo @oPKG, @hrENDI have three values and tree global variables to populate. Do I need to dothe above 3 times?How do I instantiate the package object? I've read up some on sp_OACreate,but I don't get it, yet.How do I initiate the variable @oPKG?It contains the name of the sp_OACreate string, right? How do I address aDTS package in the sp_OACreate string?I would really appreciate just writing out the sp_OACreate string and how Ipass values for three existing global variables to a DTS package named"DTS_1".I'm under some real pressure to get this done.Thanks for any help I can get.Gunny
View 1 Replies View RelatedI have an SSIS package stored on the file system and I want to execute it from a Visual Studio 2005 VB project. I have searched the internet for examples and have been unable to find any examples. I gather that I will be using SQLSMO however I'm at a loss on how to do this task. Any help is appreciated! Thanks.
Karen
Just wondering if it's possible to call a package from within a script component. I'd think so, but not quite sure how to.
Thanks,
Jeff Tolman
E&M Electric
Hi,
I wanted to know if i can a DTS package using a stored procedure and if yes how should i do it.
Regards,
Karen
Have a parent package that calls many child packages (over 30) for a daily data warehouse update. On any given day, it randomly fails to call a child package with the following error:
Error 0x800706BE while preparing to load the package. The remote procedure call failed.
Its not failing the same package each day. I need a better explanation of this error message.
Environment: SQL2005 Enterprise Dec2005 RTM on Itanium64 with Windows DataCenter.
I have a SSIS package that reads the data from an Excel file using an Excel Connection Manager and imports the data to a table on a SQL Server 2005 DB instance.
When I run this package locally on the server the package being on the file system, the package executes perfectly. Now I upload the package to the msdb database and the run the package from there and the package still executes successfully.
Now I schedule the package to run as a SQL Server Agent job and the package fails and when the logging is enabled I see this in the log file;
OnError,WEB-INTSQL,NT AUTHORITYSYSTEM,Copy to CRN-ALLOCATION_COMMENTS_TEMP,{40A6BF6E-7121-448B-A49D-DED58FDC746A},{BD991566-F4BD-41BC-AEBF-264032D8D0D3},5/9/2006 1:54:52 PM,5/9/2006 1:54:52 PM,-1071611876,0x,The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009.
OnError,WEB-INTSQL,NT AUTHORITYSYSTEM,Copy to CRN-ALLOCATION_COMMENTS_TEMP,{40A6BF6E-7121-448B-A49D-DED58FDC746A},{BD991566-F4BD-41BC-AEBF-264032D8D0D3},5/9/2006 1:54:52 PM,5/9/2006 1:54:52 PM,-1073450985,0x,component "Allocation Comments" (1) failed validation and returned error code 0xC020801C.
I am wondering why the AcquireConnection method call is failing when the package is scheduled? I am running the step as a SQL Agent Service Account and it is the Local System account that starts up the SQL Server Agent and Server and is an Administrator on the box.
Any inputs will be much appreciated.
Thanks,
M.Shah
I am trying to create a DTS Package which will run a SQL query and export the results to an Excel file. I would like to the name of the excel to be "dynamic". What I would like is for the name to be ChronicDownSiteReport - mmddyy.xls. The mmddyy is the date which the package is executed.
How can I do this?
Also, I want this package to be excuted at 1am every Sunday Morning. I have attempted to schedule this to run, but when I come to work on Monday, the excel file is not present and the email, which is sent telling me that the file was created is not in my mailbox.
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
:)
I have declared a variable XYZ in Parent package
Similarly I have declared ABC in Child package and have done the configuration.
I have assigned some value to XYZ
How to get the value in Child Package.
Hello,
is there any way to execute a SSIS package at the SQL Server programatically from a remote computer? (I use .net 2)
I don't need variables or return values, I only want to start the package. I know that it is easier to do that job with a webservice etc on the SQL Server but I cannot use such.
A method to call a package from a stored procedure would also help me a lot
Kind regards, _Rodney_
I need to be able to call a SSIS package with a specific parameter for one of the variables. Basically the package needs to be triggered from an application (.NET, C#) with a specific value.
The package is stored in the SQL Server (in Stored packages).
I have seen the article on dtexec and the SET flag, however we do not want to turn on xp_cmdshell on the SQL server due to the security implications, and that seems to be the only way to call dtexec
I have seen another article that says you can have a job invoke a package then have a specific user granted permission to the job, but that doesn't address how to set the parameter/variable for the package.
There are actually some other steps that technically need to run using the same parameter, so ideally if I could have a stored procedure call the SSIS package, then the subsequent toher stored procedures using the sent in parameter that would be great. I just can't figure out how to do this securely (as xp_cmdshell is not considered secure).
Thanks for any comments.
Hi All,
I need your help to call an SSIS package using ASP.NET. I have deployed the package using SQL server deployment with an windows authentication. I need to call that package from a remote machine. Also i need to assign a value for a variable in that package while calling it from ASP.NET.
Plz let me know the steps to call that package from ASP.NET
Thanks
I am writing an ASP.NET intranet application on a Windows XP machine running IIS 5.1 which executes a MS SQL Server 2000 DTS package as part of the process. The application allows the user to select a UNC path of an input source (.txt) file to be procesed by the DTS pacakge and the UNC file path property is then set in the source object name of the DTS package tasks before executing the DTS package. Whenever I run the application using a file path that is on my development machine (the same machine that the web server is on) it works fine. When I select a source file path that is on a network share folder, the DTS package fails with the following error:Event Type: ErrorEvent Source: DataTransformationServicesEvent Category: NoneEvent ID: 81Date: 2/14/2007Time: 12:58:06 PMUser: N/AComputer: PHILSDELL2Description:The execution of the following DTS Package failed: Error Source: Microsoft Data Transformation Services (DTS) PackageError Description:Package failed because Step 'DTSStep_DTSDataPumpTask_2' failed.Error code: 80040428Error Help File:sqldts80.hlpError Help Context ID:700Package Name: ATMRecon_ImportElanACHTxtPackage Description: (null)Package ID: {3A8CC31D-A81F-40B7-BE57-AEB3AA238088}Package Version: {B01420A7-ED22-49A7-B4C7-9FA1732394E3}Package Execution Lineage: {2A9D45E8-35F8-4F0A-8339-7E5E29DA08B7}Executed On: PHILSDELL2Executed By: webaccess23Execution Started: 2/14/2007 12:58:05 PMExecution Completed: 2/14/2007 12:58:06 PMTotal Execution Time: 1.047 secondsPackage Steps execution information:Step 'DTSStep_DTSExecuteSQLTask_1' succeededStep Execution Started: 2/14/2007 12:58:05 PMStep Execution Completed: 2/14/2007 12:58:05 PMTotal Step Execution Time: 0.047 secondsProgress count in Step: 0Step 'DTSStep_DTSExecuteSQLTask_2' succeededStep Execution Started: 2/14/2007 12:58:05 PMStep Execution Completed: 2/14/2007 12:58:05 PMTotal Step Execution Time: 0.047 secondsProgress count in Step: 0Step 'DTSStep_DTSDataPumpTask_1' failedStep Error Source: Microsoft Data Transformation Services Flat File Rowset ProviderStep Error Description:Error opening datafile: Logon failure: unknown user name or bad password.Step Error code: 80004005Step Error Help File:DTSFFile.hlpStep Error Help Context ID:0Step Execution Started: 2/14/2007 12:58:05 PMStep Execution Completed: 2/14/2007 12:58:06 PMTotal Step Execution Time: 1 secondsProgress count in Step: 0Step 'DTSStep_DTSDataPumpTask_2' failedStep Error Source: Microsoft Data Transformation Services Flat File Rowset ProviderStep Error Description:Error opening datafile: Logon failure: unknown user name or bad password.Step Error code: 80004005Step Error Help File:DTSFFile.hlpStep Error Help Context ID:0Step Execution Started: 2/14/2007 12:58:05 PMStep Execution Completed: 2/14/2007 12:58:05 PMTotal Step Execution Time: 0.031 secondsProgress count in Step: 0 When I check the Security Event Log on the server on which the network share is located, I find the following set of messages repeated multiple times:Event Type: Failure AuditEvent Source: SecurityEvent Category: Account Logon Event ID: 680Date: 2/14/2007Time: 1:02:48 PMUser: NT AUTHORITYSYSTEMComputer: FS1ADescription:Logon attempt by: MICROSOFT_AUTHENTICATION_PACKAGE_V1_0 Logon account: ASPNET Source Workstation: PHILSDELL2 Error Code: 0xC000006AEvent Type: Failure AuditEvent Source: SecurityEvent Category: Logon/Logoff Event ID: 529Date: 2/14/2007Time: 1:02:48 PMUser: NT AUTHORITYSYSTEMComputer: FS1ADescription:Logon Failure: Reason: Unknown user name or bad password User Name: ASPNET Domain: PHILSDELL2 Logon Type: 3 Logon Process: NtLmSsp Authentication Package: NTLM Workstation Name: PHILSDELL2 Caller User Name: - Caller Domain: - Caller Logon ID: - Caller Process ID: - Transited Services: - Source Network Address: 10.120.4.61 Source Port: 0The problem appears to be that the DTS package is trying to access the input source file using the local ASPNET Id from PHILSDELL2 which does not have authority to the network share. The ASP.NET application is using impersonation, set up through the web.config file: <identity impersonate="True" userName="registry:HKLMSOFTWARESkylightATMReconidentityASPNET_SETREG,userName" password="registry:HKLMSOFTWARESkylightATMReconidentityASPNET_SETREG,password" />The application can sucessfully access the network share under the impersonated id (the application first verifies the existience of the source files before executing the DTS package). I have wracked my brain trying to determine where the ASPNET security context is coming from to no avail. Can someone shed some light on what security credentials the DTS package is using to access the source files and how to change them?
View 1 Replies View RelatedI am having few queries related to follow.
1. Is it possible to call SSIS package from procedure .
2. Also want to supply parameters to procedure.
3. Can multiple users execute that procedure simultaneously.
4. If yes then will it cause any issue if it is run simultaneously by 10 users.
Hello everyone,
I have implemented a package, which loads data from a flat file into an OLE DB destination. I have added the properties of the OLE DB Connection Manager to the package configuration (XML file), in order to give me enough flexibility for running the package on different databases.
Now I wanted to integrate my package into the application that would call it and I ran into these problems.
A. Call a package from a JAVA application:
I have a JAVA web application, which should provide the source file and finally trigger the load operation.
A1: How can I execute a package from within a JAVA application?
A2: How can I pass parameters to this package?
B. Parallel Package Execution:
If I was able to trigger the package execution from my application, then I can encounter a situation where the package is triggereed to be executed again with different input data before the first package has finished.
B1: Is it possible (from SSIS point of view) that the same package gets executed parallely with different input?
C. Run the package against several databases:
Assuming that I have 5 databases where I load data using this package.
I have created 5 configuration.xml files with the corresponding DB connection properties.
C1: How can I tell the package which configuration file to read from, in order to load the source data into the correct destination? Is it possible to pass the path of the configuration.xml file when executing a package?
Thanks in advance.
Regards,
Samar
Hi, Everyone:
I am getting the following error message when I try to execute a SSIS package from an asp.net page written in C# 2.0. What I am trying to do is basically just click on a button in the web page and it will execute the package. The code to execute the package is pretty simple. I pass the path of the DTS package stored in the local folder. This works fine in the machine where SQL2005 is installed locally. But it fail when I have a seperate Web Server and SQL Server. Any ideas? Do I have to install SSIS or SQL2005 on a web server as well?
Thanks
static public string Execute_SSIS_DTS(string DTS_Path)
{
Microsoft.SqlServer.Dts.Runtime.Application app;
app = new Microsoft.SqlServer.Dts.Runtime.Application();
Package package = app.LoadPackage(DTS_Path, null);
DTSExecResult result = package.Execute();
return result.ToString();
}
Retrieving the COM class factory for component with CLSID {E44847F1-FD8C-4251-B5DA-B04BB22E236E} failed due to the following error: 80040154.
Line 226: static public string Execute_SSIS_DTS(string DTS_Path)
Line 227: {
Line 228: Application app = new Application();
Line 229: Package package = app.LoadPackage(DTS_Path, null);
Line 230: DTSExecResult result = package.Execute();
[COMException (0x80040154): Retrieving the COM class factory for component with CLSID {E44847F1-FD8C-4251-B5DA-B04BB22E236E} failed due to the following error: 80040154.]
Microsoft.SqlServer.Dts.Runtime.Application..ctor() +43
[DtsPipelineException: Retrieving the COM class factory for component with CLSID {E44847F1-FD8C-4251-B5DA-B04BB22E236E} failed due to the following error: 80040154.]
Microsoft.SqlServer.Dts.Runtime.Application..ctor() +169
Utilities.Execute_SSIS_DTS(String DTS_Path) in c:InetpubwwwrootMasterTablesApp_CodeUtilities.cs:228
MasterTables_Admin_MasterTables_LOINC_External.btn_SyncLISTest_Click(Object sender, EventArgs e) in c:InetpubwwwrootMasterTablesMasterTables_CustomMasterTablesCustom_LOINC_External.aspx.cs:98
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +114
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +141
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +32
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3215
I have ssis package which is credated by VS-2010.
I want execute this SSIS package from the stored procedure (SQL server 2005).
How do we connect and get data from SAP database using SSIS 2012/2014.Currently we are extracting data using WebService, and BAPI function call for RFC, but it seems not so much reliable, due to timeout and others. ion providing the link for download of .NET Framework Data Provider for mySAP Business Suite and some sample to invoke and get the data using SSIS.
View 5 Replies View Related
Hi,
I have 3 packages.
- Staging Package
- Dimension Package
- Fact Package
I wanna embed Dimension and Fact packages into Staging Package
so I can run smoothly.
How can I do it?
thanks,
Jerkus
I am new to the SSIS.
For DTS package of sql server 2000, I can call a DTS package from stored procedure. The command is:
dtsrun /E /SMyServer /NMyDTS /Wtrue /A Parameter1:3= 'Test'
Does anyone know, how do I do the similar thing from SSIS environment.
1) How to call a SSIS package from Stored Procedure?
2) How do I pass parameter to the SSIS package?
Thanks everyone.
I have a job/step that call a Fileprocessor.dtsx package with 4 different config files. I need to make a enhancement to the job to call the same package using a new (parent) package with multiple iterations based on some business condition.
I am going to modify the job/step to call ParentFileprocessor now with same all 4 config files which in-turn have a call to run Fileprocessor.dtsx; my problem is, how do I pass all 4 config files for child call? Using which task?
I m not allowed to modify Fileproessor.dtsx (to be child pkg) SQL2012, MSDB package deployment model.
Hi,
I am producing a php report using SQL queries to show the SLA status of our calls. Each call has response, fix & completion targets. If any of these targets are breached, the whole SLA status is set as 'Breach'.
The results table should look like the one below:
CallRef.
Description
Severity
ProblemRef
Logged
Date
Call
Status
SLA Status
C0001
Approval for PO€™s not received
2
DGE0014
05-01-06 14:48
Resolved
Breach
C0002
PO€™s not published
2
DGE0014
06-01-06 10:21
Resolved
OK
C0003
Approval for PO€™s not received from Siebel.
2
n/a
05-01-06 14:48
Investigating
OK
Whereas I can pick the results for the first 6 columns from my Select query, the 'SLA Status' column requires the following calculation:
if (due_date < completed_date)
{ sla_status = 'OK';
}
else sla_status = 'Breach';
The Select statement in my query is looking like this...
Select Distinct CallRef, Description, Severity, ProblemRef, Logdate, Status, Due_date, Completed_date;
The problem is that my query is returning multiple entries for each stage of the call (see below), whereas I just want one entry for each call, with SLA status 'Breach' if any of the stages for the call were out of SLA.
CallRef.
Description
Severity
ProblemRef
Logged
Date
Call
Status
SLA Status
C0001
Approval for PO€™s not received
2
DGE0014
05-01-06 14:48
Resolved
Breach
C0001
Approval for PO€™s not received
2
DGE0014
05-01-06 14:48
Resolved
OK
C0001
Approval for PO€™s not received
2
DGE0014
05-01-06 14:48
Resolved
Breach
Any help will be much much appreciated, this issue has been bothering me for some time now!!!
Hello,
I have a bundling package that runs about 20 other packages. It has been working fine for a while but a couple of days ago it fail with the following message,
Error 0x800706BE while loading package file "D:PackagesToradSales.dtsx". The remote procedure call failed.
I´m running the SSIS packages in an 64-bit environment.
Thankful for help with this!
//Patrick
I need to get the output of a SSIS package in an Excel.
I have made a Stored Procedure for getting the output, since the output has variable number of columns hence i have made a table through the stored procedure which is dynamically created when the procedure is executed.
Now i have a table in SQL with the output data.
Any pointers, how to put it into an excel??
Can you call run an excel macro as part dts step. I would imagine it would be some active script, but am not sure.
Any Ideas ?.
I am running SQL 7.0 sp2, excel 97.
Hi,
I have a created a DTS packges which is reading data from sql server table, manipulate this data as required and then create a text file with that data. I created the text file using FileSystemObject. I was writing one field at a time to the text file.
I need to same thing but instead of creating text file, I need to create a excel file with each column from database going to separate column in excel sheet. I tried to do this with FileSystemObject, but it was wrting all the columns from database to one cell in excel sheet. How can I fix this problem?
Thanks!