Passing The Xml Configuration File To The Package As An Input Parameter While Executing The Package
Hi,
I am planning to develop a single package that will download files from ftp server, move the files to internal file server and upload it in the database. But I want to run this package for multiple ftp file providers. For each provider the ftp server might be different and the transformation to upload the files into a database table might be different.
So can I create a single package and then multiple configuration files (xml), which will contain the details fo the ftp file providers and then pass the xml file as a parameter while executing the package. The reason being that the timings of fetching the files is different for each ftp file provider and hence cannot be combined into one.
Is this possible?
Thanks for your help.
$wapnil
View Complete Forum Thread with Replies
Related Forum Messages:
Configure A Child Package To Use The Configuration File Of A Parent Package?
Hi guys, Here's the deal. I have a child package, (say, pack01.dtsx), which uses a dtsconfig file for its connection string, which can be called from other packages, but which also can be called by itself. However I also have another package (say, pack02.dtsx) which uses the same dtsconfig file for its connection string. It calls on pack01.dtsx. When I use DTEXECUI and run pack01.dtsx, specifying the proper .dtsconfig file, it goes well. But when I try and run pack02.dtsx, an error occurs saying pack01.dtsx connection cannot be established. How do I pass the connectionstring being used by pack02 to pack01, without having to remove the configuration file setting of pack01? Can a Parent Package configuration and a configuration file try and map to the same property? Hope someone could help. Thanks.
View Replies !
How-to Execute SSIS Package With Input Parameter?
Hi all. we have SSIS package which runs under SQL Job. Now we need to modify this package in order to have input parameter which we want to setup in the "Set Value" Tab of SQL Server 2005 SQL Job |Edit Step |Option window. Could anybody give me in details how-to setup Global Variable (or something else) inside SSIS Package in order to create input parameter of the package? I also appreciate an advice how exactly we should assign value to input parameter of SSIS Package inside SQL Job Set Value Tab (example?). Thank you, Vitaliy
View Replies !
Passing Parameter To Package From Scheduler
I need to have scheduler passing the latest datetime to my Package at the time it kicks it off. Also, How would I receive the argument in my package ? I mean, in some variable or there are some other construct in SSIS. Any help in this will sincerely be appreciated. Fahad
View Replies !
Passing A Boolean Parameter To A Package
hello at everybody well i have a problem. with in a package i defined a bollean variable with the name "x" and with a default value FALSE. i created a configuaration file that contains the variable "x" as [user::x]. when i deploy the package to a server and i try to execute the package putting to the set values TRUE fror the x variable it doesnt work. what can i do?? thnxs
View Replies !
Passing Parameter From Report To Package
Hi all, I'm trying to create a report that will pass its parameter (not multi-valued) to an SSIS package which will use the parameter in one of its conditions to return data and pass it through the DataReader which in turn brings back results for the report itself. Does anyone know how to set this up?? Any help would be greatly appreciated!!
View Replies !
Passing SSIS Package Variable To Stored Procedure As Parameter
I've created a varible timeStamp that I want to feed into a stored procedure but I'm not having any luck. I'm sure its a simple SSIS 101 problem that I can't see or I may be using the wrong syntax in Execute SQL Task Editor I have conn type -- ole db connection -- some server sql source type -- direct input sql statement -- exec testStoredProc @timeStamp = ? if I put a value direclty into the statement it works just fine: exec testStoredProc '02-25-2008' This is the syntax I found to execute the procedure, I don't udnerstand few things about it. 1. why when I try to run it it changes it to exec testStoredProc @timeStamp = ? with error: EXEC construct or statement is not supported , followed by erro: no value given for one or more requreid parameters. 2. I tired using SQL commands exec testStoredProc @timeStamp and exec testStoredProc timeStamp but nothing happens. Just an error saying unable to convert varchar to datetime 3. Also from SRS I usually have to point the timeStamp to @timeStamp and I dont know how to do that here I thought it was part of the parameter mapping but I can't figure out what the parameter name and parameter size should be; size defaults to -1. Thank you, please help.
View Replies !
Package Configuration With XML File
Hi, I have about 40 packages in my job. One of the problems that I have encoutered is that I don't know how I could use same XML dtsconfig file for all of my files. Each SSIS calls different file. The XML dtsconfig file is a replacment for my *.ini file in sql 2000. The dtsconfig file will hold paths to all different files. Each SSIS will take different file name at a runtime. How can I have all the files paths in one dtsconfig file - and is it even possible to do that in SSIS? Any help greatly appreciated....Thank you, Maria
View Replies !
Error Stating Package Failure While Executing SSIS Package In Standard Edition
Hi, I have developed an SSIS package for ETL purpose. I am invoking the SSIS package through .Net console application by referencing the ManagedDTS Assembly. I am able to execute the package in Sql Server 2005 Developer Edition and it runs fine till completion. But when i try to execute the packahe in Sql Server 2005 Standard edition, by invoking the package through .Net console application the status of the package is failure. Can any one help me how to over come this problem.
View Replies !
SSIS Package Log File Configuration
Within SSIS where can I specifiy the following attributes for packagerun logs?- File name (e.g. package name with start datetime appended)- File location- Retention (e.g. 30 days)- Level of verbosityThanksM
View Replies !
Package Configuration Using Xml-file Fails
Hallo, I use SSIS Version 9.00.1399.00 and keep getting problems trying to use package configuration. I choose xml configuration file as type and browse a path on our LAN to create te configuration file . Then I select the properties of a OLEDB communication manager as Properties to Export Doing so I obtain this configuration:Name: JACBE_IF_CONFIG Type: Configuration File New configuration file will be created. File name: L:ProjectsVinciSSISDVLFMC loader ImportFMC Loader ImportFMC Loader ImportJACBE_IF_CONFIG.xml Properties: Package.Connections[JACBE_IF].Properties[UserName] Package.Connections[JACBE_IF].Properties[ServerName] Package.Connections[JACBE_IF].Properties[RetainSameConnection] Package.Connections[JACBE_IF].Properties[ProtectionLevel] Package.Connections[JACBE_IF].Properties[Password] Package.Connections[JACBE_IF].Properties[Name] Package.Connections[JACBE_IF].Properties[InitialCatalog] Package.Connections[JACBE_IF].Properties[Description] Package.Connections[JACBE_IF].Properties[ConnectionString] The system creates a XML file but when I run the package I get the following error in the output pane. Information: 0x40016041 at FMC_People: The package is attempting to configure from the XML file "L:ProjectsVinciSSISDVLFMC loader ImportFMC Loader ImportFMC Loader ImportJACBE_IF_CONFIG.xml". SSIS package "FMC_People.dtsx" starting. Information: 0x4004300A at Dataprocessing_PEOPLE, DTS.Pipeline: Validation phase is beginning. Error: 0xC0202009 at FMC_People, Connection manager "JACBE_IF": An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.". Error: 0xC020801C at Dataprocessing_PEOPLE, FMC_ARE_PRESENT_destination 1 [22338]: The AcquireConnection method call to the connection manager "JACBE_IF" failed with error code 0xC0202009. Error: 0xC0047017 at Dataprocessing_PEOPLE, DTS.Pipeline: component "FMC_ARE_PRESENT_destination 1" (22338) failed validation and returned error code 0xC020801C. Error: 0xC004700C at Dataprocessing_PEOPLE, DTS.Pipeline: One or more component failed validation. Error: 0xC0024107 at Dataprocessing_PEOPLE: There were errors during task validation. SSIS package "FMC_People.dtsx" finished: Failure. I don't get it. Where do I go wrong? I tried the same with a DtsConfig file instead of an XML but to no avail. the way of working as described in BOL and in the book professional SQL SERVER 2005 Integration service seems to me perfectly similar. Any ideas anyone? I would be most gratefull. Kind regards, Paul Baudouin
View Replies !
Package Configuration File Issue
I have created a simple package with SQL table to table load. I also created and stored connection information in xml configuration file. the package protectionlevel is "DontSaveSensitive". While i try to run the package I believe it doesn't pick up the configuration file.. and throws the below error: (but it runs fine with other protectionlevel setting). Error at Data Flow Task [OLE DB Source [1]]: The AcquireConnection method call to the connection manager "Server1.Stage.etluser" failed with error code 0xC0202009. pl ignore this post ( i don't know how to delete this post) Some how it started working once i close and restarted the ide
View Replies !
Package Not Finding Xml Configuration File
Hi, I'm having difficulty configuring a package to look for an xml configuration file that contains values for source/destination connection strings. The goal is to be able to have a package and its configuration file on one file system and to be able to copy/move the file dtsx and dtsx.config between build/staging/production servers while only having to set the xml value for the source/destination configurations. In the package, I've created an xml configuration file called zzz.config. I took the absolute path out of the location of the xml file so that I have xml location = zzz.config instead of xml location = e:ssiszzz.config I'm assuming that when it gets to the point of validating the location of that file, it starts by looking in the current directory where the package is found. All is well as long as I execute the package through the execute package utility by double clicking on the package file. The problem lies when configuring a job in the sql server agent, it doesn't seem to pick up the xml configuration values because its pointing to the old server and not the current server stored in the xml file. I'm assuming its because the current directory is not the package directory at this point. How can I get around this? Do I have to move the xml config file (zzz.config) to another directory where the agent will look for? I know I could configure this as an environment variable but this contradicts the point of having movable packages without any extra tweaks. Any help would be appreciated. Thanks, Robin
View Replies !
Passwords In XML Package Configuration File
I created a xml config file to manage servername, database name, user name and password. My password is visible to users, which I do not want. Is there a way to hide or handle password in a different way?. Please let me know. Thanks,.
View Replies !
Passing Execute DTS Package Result (success/failure) To Calling SSIS Package
I have a SSIS job, one of the last steps it performs is to execute a SQL 2000 DTS package. This has to be done as a SQL 2000 DTS package as it is performing rebuilds of SQL 2000 Analysis Services dimensions and cubes. We've found that when the DTS fails the SSIS job is happily completing showing as a success, we would prefer to know it went wrong. As far as I'm aware SSIS merely starts the DTS off and doesn't care about it's result. I've taken a look in to turning on the logging for the execute DTS package and thought that the ExecuteDTS80PackageTaskTaskResult would give me the answer I need...but is merely written to the log not available as an event-handler. It also looks like it is not safe to put a SQL task in as the next item to go look at the SQL 2000 system tables to look at the log for the DTS package as the SSIS documentation warns that the DTS package can continue to run after the execute DTS package task has ended. Ideally I want any error raised within the DTS package to cascade up to be an error in the SSIS job, I can then handle it appropriately. I cannot find a way to do this. Is there a way? If not, can anyone suggest how in the remainder of the SSIS tasks I can be sure that the DTS has completed before I start any other tasks that will check for the SQL 2000 log of its execution?
View Replies !
My Package Does Not Take The Values Configured In The Configuration File.
Hi all, I created a simple file system task to copy a file from one location to another on my local system. I used variables to specify the source and destination paths and now in my configuration file if I configure the values to something else also the package is taking the initial default values and not the values specified in the configuration file. Can anyone throw some light on this. Thanks in advance.
View Replies !
Question Regarding SSIS Package Configuration Using XML File
As I am converting my DTSs to SSIS I am replacing current *.ini file with Package configuration xml file. I already converted few DTSs and am using only one *.dtsconfig file for all of my SSIS packages. Each ssis package points to a different flat file, so my *.dtsconfig file contains all of the string connections. Something like this.... <DTSConfiguration> <DTSConfigurationHeading> <DTSConfigurationFileInfo GeneratedBy="ASAPpikul" GeneratedFromPackageName="NewTmpBackOrder" GeneratedFromPackageID="{000FA6E1-C7B7-4105-AC72-303352F27659}" GeneratedDate="9/21/2006 4:05:53 PM"/> </DTSConfigurationHeading> <Configuration ConfiguredType="Property" Path="Package.Connections[backorderdetail].Properties[ConnectionString]" ValueType="String"> <ConfiguredValue>C:Inetpubftprootackordersdetail.csv</ConfiguredValue> </Configuration> <Configuration ConfiguredType="Property" Path="Package.Connections[BackOrderCIO].Properties[ConnectionString]" ValueType="String"> <ConfiguredValue>C:Inetpubftprootackordercio.csv</ConfiguredValue> </Configuration></DTSConfiguration> And that all works fine. But when I come later and try to open any of my ssis it gives me an error and warning message that i don't have proper connection set up. When I run my SSIS regardless of the errors it seems to wok just fine. What it seems to be happening is that when I have many string connetions in one xml file (dtsconfig file) it doesn't seem to recognize it at the design time but it is able to recognize it at a run time.... Is this a known issue or is it something that I am doing wrong?? Thank you, Maria
View Replies !
SSIS Package Configuration File Malformed
Can someone identify what is wrong with the XML config file that I i'm using with a SSIS package? The XML file was created by Visual Studio tool Code Snippet <DTSConfiguration> <DTSConfigurationHeading> <DTSConfigurationFileInfo GeneratedBy="BRIDGENETjpacella" GeneratedFromPackageName="SsisEndBatch" GeneratedFromPackageID="{91A1941E-E066-47F7-8DD4-9E260F38741D}" GeneratedDate="1/21/2008 4:35:22 PM" /> </DTSConfigurationHeading> <Configuration ConfiguredType="Property" Path="Package.Connections[CHI-DEV02.BNETDB2].Properties[InitialCatalog]" ValueType="String"> <ConfiguredValue>BNETDB2IMPORTTEST</ConfiguredValue> </Configuration> <Configuration ConfiguredType="Property" Path="Package.Connections[CHI-DEV02.BNETDB2].Properties[ServerName]" ValueType="String"> <ConfiguredValue>CHI-DEV02</ConfiguredValue> </Configuration> <Configuration ConfiguredType="Property" Path="Package.Connections[CHI-PROD01.BNETDB2].Properties[InitialCatalog]" ValueType="String"> <ConfiguredValue>BNETDB2</ConfiguredValue> </Configuration> <Configuration ConfiguredType="Property" Path="Package.Connections[CHI-PROD01.BNETDB2].Properties[ServerName]" ValueType="String"> <ConfiguredValue>CHI-FILE01</ConfiguredValue> </Configuration> <Configuration ConfiguredType="Property" Path="PackageRun Report Executor.Properties[Executable]" ValueType="String"> <ConfiguredValue>C:ReportsReportProgramCHI_FILEBridgeNet.Reporting.ExecuteConsole.exe</ConfiguredValue> </Configuration> <Configuration ConfiguredType="Property" Path="PackageRun Report Executor.Properties[WorkingDirectory]" ValueType="String"> <ConfiguredValue>C:ReportsReportProgramCHI_FILE</ConfiguredValue> </Configuration> </DTSConfiguration> When I run the pacakge, this is what happens: Started: 10:48:56 AM Info: 2008-01-24 10:49:02.92 Code: 0x40016041 Source: SsisEndBatch Description: The package is attempting to configure from the XML file "C:Program Files (x86)Microsoft SQL Server90DTSPackagesBridgeNet.BI.SSIS.EndBatc ENDBATCH TEST ENV.dtsConfig". End Info Warning: 2008-01-24 10:49:03.15 Code: 0x80012011 Source: SsisEndBatch Description: Cannot load the XML configuration file. The XML configuration file may be malformed or not valid. End Warning
View Replies !
XML Package Configuration File Not Added Into Sourcesafe
Hi, I've added an XML package configuration file to my SSIS package and it appears to be working fine. The XML file is located in the same folder as the solution file. I've recently added the entire solution to Visual Sourcesafe 2005, and I achieved this by doing the following: 1. Right-clicked on the Project name in the solution explorer and selected "Add to source Control". 2. When prompted, provided my Sourcesafe login credentials and clicked "OK" 3. Accepted the default VSS project name and clicked "OK" 4. Clicked "Yes" to allow VSS to create a new project Problem: The XML configuration file hasn't be added to sourcesafe along with rest of the solution. I'm able to add the file manually myself, but this doesn't seem right to me. Am I missing something? Should the config file show as part of the Solution in Visual Studio 2005? Any answers/help would be appreciated greatly. Thanks Jon Derbyshire
View Replies !
Reading A .dtsx File's Package Configuration In VB.NET
So I've seen articles outlining how to execute a package in VB and how to pass variables along to the package, which is great. But I'm wanting write a VB.NET app that will read the xml in a .dtsx package, looking for the available variables (and datatypes) that need to be passed to the package to configure it properly. Anyone done this? Is it best to use parent package variables? Or another type of configuration? When looking at the xml of a .dtsx file, I can see a variables section that could be parsed... is there anything letting me know what is required versus optional? How do I know the datatype that I need to pass? Any help would be appreciated...
View Replies !
Absolute Path For Package Configuration File
We are facing some issues with SSIS and thought if you could be of some help. Actually, instead of Deploying our SSIS projects we copy the SSIS files to a different server from where we execute the SSIS Packages. The reason being, we have a Core Version of the application and this is deployed to different markets and subsequent customisations are performed in the market specific version of the Packages. The problem is although we have a Package Configuration Xml File for our Connection object, the first time when we copy the Packages, we have to manually open each of the Package and change the URL of the Xml File for Configuration. I read through some of the articles on the internet and came to know that the Package Configuration does not support relative path and only absolute path is possible. Can you please share some of your suggestions on the above problem.
View Replies !
Report With SSIS Package Having Indirect Package Configuration Setup
Deployed Report having SSIS package as source do not work when Indirect Package configuration is used in ETL package. It seems ETL package when called/executed from Report manager does not recognize environment variable to pick up the dtsconfig file. The Report works when Direct package configuration is used to same dtsconfig file. What could be the reason? Any solution for this? This will cause our build/deployment to QA and Prod very difficult.
View Replies !
Passing Value From A Child Package To The Parent Package That Calls It In Ssis
hi, I am interested in Passing value from a child Package variable to the Parent package that calls it in ssis. I am able to call the Child package using the execute package task and use Configurations to pass values from the parent variable to the child, but I am not able to pass the value from the child to the parent. I have a variable called datasetId in both the parent and child. it gets computed in the child and needs to be passed to the parent... Any suggestions? Thanks for any help in advance.. smathew
View Replies !
How To Use Deployment Utility Using A Single Package Configuration File
Hi, We are trying to create a deployment utility for a solution. The issue we are facing is, we are using a single package configuration file and when we try to build the solution to create the deployment utility, the build process fails saying that the package configuration file already exists. THe reason for this is while trying to build, the utility copies the configation fiel for the packages, it copies for one, but for the second onward, when it tries to copies, it fails saying the file already exists. Any idea how to overcome this, or else any suggestions how to perform the similar steps to create a deployment utility for a solution in which the packages share a single package configuration file. Any suggestion would be really appreciated. Thanks in Advance, Manish Singh
View Replies !
Single Configuration File - Mulitple Packages - Elements Not Used In Each Package
Hi, I'm fairly new to the SSIS world, and I've recently ported a bunch of dts packages over to SSIS. I'm an ASP.NET developer so I'm very familiar with the capabilities that configuration files give you, and I attempted to set up my solution as follows: All of my "Data Sources" are at the project level, and added (with the same name) to each package. I wanted to have a single config file that had all of the project-level settings (i.e. connection strings, data file paths, etc). I then have a config for each package with the package level settings - i.e. variables, etc. The problem becomes that all packages do not use all data sources. This results in an error when I try to open up a package for editing, it complains that it doesn't have a reference to data source XYZ that it is seeing in the configuration file. Is there any way that I can get around this? If I have a password to a database change, I don't want to have to look through every config file and change it in multiple places.
View Replies !
Passing Parameters Read From An Ini File To A Stored Procedure Within DTS Package
Hi I was wondering how I could pass on the following parameters from an ini file to a stored procedure within a DTS package. The parameters in the ini file look like: [DatabaseCleaner] ! -- TableToBeCleaned_N=<table name>,<months to hold on db>,<months to hold on hd> ! -- <N> must be a successive number starting from 1 ... TableToBeCleaned_1=Transactions,24,24 TableToBeCleaned_2=Payments,24,24 TableToBeCleaned_3=PresenceTickets,24,24 As I do not know how many tables that will be declared in the ini file I have to loop through until the last parameters and pass it over to the SP. How can I do that? Any idea? Thanks mipo
View Replies !
Package Configuration File Issue - Relative Path Not Recognized In VS2005
I have been reading through different posts about how to manage SSIS configuration files across multiple environments. I have seen a few that mention a solution that turns out not to really work (or maybe I€™m doing something wrong); which is what I hope to find out here. Solutions I am working with: 1: Put the XML Configuration file in the same directory as the SSIS package and only specify the config file name when setting it up in the package. I do not specify a path to the XML Configuration file, just the config file name. ISSUE: This works when I first create the package and test it without closing down visual studio. Even after deploying the SSIS package and the config file and moving it between environments it works as long as the working directory of the DTExec command is the same directory as the SSIS package and configuration files. The problem comes in after I have closed Visual Studio and opened it again and loaded up the SSIS package. It then gives a bunch of errors stating that it can€™t locate the configuration files€¦. If I could fix this issue, then this would be a solution that I could live with. (I would much rather use a relative path to a different directory but I€™ll live with what I can get). Can anyone solve the issue of Visual Studio 2005 not recognizing the location of the configuration files when only the configuration file name, and not the path is specified? -- The other odd thing is that I have been able to get this to work consistently with some of my packages but not others. I'm not sure what I did to get those packages to recognize the location of the xml config file. Thank you in advance for any input and help.
View Replies !
Passing Path As Input Parameter
Hi I have created one SSIS package which extract data from database and put that into verious text files like Emp.txt,Add.txt like that. Also I set one globle veriable (CityID) that help in extracting data as citywise. When I ran it through command prompt by passing globle veriable to it like C:setup pcaSSIS PackagesSSIS Package File Extract DataSSIS Package File Extract Data>DTExec /FILE Package.dtsx /SET Package.Variables[CityID].Value;100 it gives me data whose CityID is 100 and format it into text files. but when i want data for another CityID (101) it overwrites my all previous text files where i kept that files. Now my requirment is that i want to set another globle veriable that take PATH as input parameter and place these files in that path location.(How i set this path in command promt and also in SSIS) Please guide me Thanks
View Replies !
Passing Variables From One DTS Package To Another DTS Package
I want to loop through a recordset of email addresses, and for each value in the recordset, I want to perform a set of task which includes creating an Excel spreadsheet and emailing it through to those email addresses. I have 2 DTS package. Package 1 - This contains an ActiveXScript task. The purpose of this package is to do the looping through of the recordset, and passing the email address variable to package 2. Package 2 - Receives the email address from package 1. Package 2 contains the creating Excel spreadsheet and emailing it through to the recipients function. I am having difficulty passing the variable from package 1 to package 2. The code I have to date for the ActiveXScript task in package 1 is: '************************************************* ********************* ' Visual Basic ActiveX Script '************************************************* *********************** Function Main() Dim oPackage Dim oStep Dim oTask Dim oCustTask Dim oGlobal 'Initialise package Set oPackage = CreateObject("DTS.Package2") Set oStep = oPackage.Steps.New oStep.Name = "TestStep" Set oTask = oPackage.Tasks.New ("DTSExecutePackageTask") Set oCustTask = oTask.CustomTask oCustTask.Name = "TestCustTask" oStep.TaskName = oCustTask.Name oPackage.Steps.Add oStep oCustTask.FileName = "C:TempVeraTest1.dts" oPackage.Tasks.Add oTask set oGlobal = oPackage.GlobalVariables.New("CurrentAirlineCode") oGlobal.value = "SIA" oPackage.GlobalVariables.Add oGlobal 'Execute package oPackage.Execute Main = DTSTaskExecResult_Success End Function This does not work. Can anyone please help? Thanks very much in advance.
View Replies !
Passing Object Variable As Input Parameter To An Execute SQL Task Query
I've encountered a new problem with an SSIS Pkg where I have a seq. of Execute SQL tasks. My question are: 1) In the First Execute SQL Task, I want to store a single row result of @@identity type into a User Variable User::LoadID of What type. ( I tried using DBNull Type or Object type which works, not with any other type, it but I can't proceed to step 2 ) 2) Now I want to use this User::LoadID as input parameter of What type for the next task (I tried using Numeric, Long, DB_Numeric, Decimal, Double none of there work). Please give me solutions for the above two..
View Replies !
Package Configuration Wizard:-SQL Configurations Configuration Filter Not Working
Hi --I was wondering if this is a bug when I add new data in my table SSIS Confiurations and give wizard a new Configuration filter the package configuration wizard can not see the new values --the old values from the previous configuration are still showing---is there any known workaround or forced refresh I can do thanks in advance Dave Background: SQL Package Configurations are most important because they provide the possibility of a central configuration store for your entire enterprise!!!!!!!! and is in my mind the only way to go http://sqljunkies.com/WebLog/knight_reign/archive/2005/01/24/6843.aspx Wizard results: Name: ETL Type: SQL Server Connection name: ETLConfiguration Any existing configuration information for selected configuration filter will be overwritten with new configuration settings. Configuration table name: [dbo].[SSIS Configurations] Configuration filter: PT_CUST_ABR Target Property: Package.Variables[User::gsPreLoad].Properties[Value] Package.Variables[User::gsPostLoad].Properties[Value] Package.Variables[User::gsLoad].Properties[Value] Package.Variables[User::gsFlatFilename].Properties[Value] Package.Variables[User::gsFileName].Properties[Value] Package.Variables[User::gsCDOMailTo].Properties[Value] Package.Variables[User::gsCDOMailSubject].Properties[Value] Package.Variables[User::giRecordCount].Properties[Value] Package.Variables[User::giFileSize].Properties[Value] Package.Variables[User::giBatchID].Properties[Value] Package.Variables[User::gdFileDateCreated].Properties[Value] Package.Connections[MyDatabase].Properties[ServerName] Package.Connections[MyDatabase].Properties[InitialCatalog] USE [ETLConfiguration] GO /****** Object: Table [dbo].[SSIS Configurations] Script Date: 05/23/2006 13:34:35 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[SSIS Configurations]( [ConfigurationFilter] [nvarchar](255) COLLATE Latin1_General_CI_AS NOT NULL, [ConfiguredValue] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL, [PackagePath] [nvarchar](255) COLLATE Latin1_General_CI_AS NOT NULL, [ConfiguredValueType] [nvarchar](20) COLLATE Latin1_General_CI_AS NOT NULL ) ON [PRIMARY]
View Replies !
Error While Executing SSIS Package From Other SSIS Package
Hi, In our project we have two SSIS package. And there is a task (Execute SSIS package) in First package that calls the execution of second package. I m continuously receiving an error "Failed to decrypt protected XML node "PackagePassword" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available." As we are running first package by job, job runs successfully logging above error The protection level of second package is set to "EncryptSensitiveWithUserKey" Can anybody please suggest how to handle it?
View Replies !
Request For Data Input In A DTS Package
Hi there. This is only my second post ever- so please bear with me. First, I am using SQL Server 2000. I have built a DTS package using an Oracle ODBC and the following query; select pv.segment1 vendor_nbr, pv.vendor_name,pv.attribute4 , pv.creation_date from po.po_vendors pv , po.po_vendor_sites_all pvs where pv.vendor_id = pvs.vendor_id(+) operating unit AND trunc(pv.creation_date) = trunc(sysdate) This DTS package is not always run on the same day the records are created. So...... What I want to do is have the dts package ask the user for the date they are looking for- I know I need to probably use a parameter, but I have been unable to figure out how to do this in a DTS pkg. I have several reSQL reference books and I don't even know what to search for in Books online. Could anyone help?? Thanks Heather Vizina
View Replies !
Executing DTS Package From VB.NET
Hello, I have a web application that I kick off a DTS package. I tested the package and it works fine. However, when running the package through ASP.NET, I get the following error: Type mismatch. (mscorlib (80004005): Missing parameter does not have a default value. Parameter name: parameters) I've traced it to a dynamic task step. I have this as the first step in the workflow, but for some reason, it is executing last. Which is definitely not the place where I want it. Is there anyway I can force it, or why is it doing that? Any ideas? Brian
View Replies !
Executing Package As Job
Hi, I am having trouble executing a package as a job in SQL Server 2005. I can run the package fine manually from sql server but when I create a job for that package and run the job I get an error that says The package execution failed. The step failed. I am completely lost at this point. Any help would be greatly appreciated. Thanks Brian
View Replies !
Executing DTS Package
Hi Friends I am trying to execute the DTS package from the T-Sql Its basically Exporting the table from Sql Server to tab delimited text file DECLARE @Command varchar(1000) declare @partcode varchar(100) set @command = 'DTSRun /S "servername" /U "username" /P "password" /N "TabFormat" /G "{459784D8-0170-400C-8919-AC25423D7F34}" /W "0" ' EXEC master..xp_cmdshell @Command It Comes up with the Following Error output --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error string: Error opening datafile: The system cannot find the path specified. Error source: Microsoft Data Transformation Services Flat File Rowset Provider Help file: DTSFFile.hlp Help context: 0 Error Detail Records: Error: 3 (3); Provider Error: 3 (3) Error string: Error opening datafile: The system cannot find the path specified. Error source: Microsoft Data Transformation Services Flat File Rowset Provider Help file: DTSFFile.hlp Help context: 0 DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. NULL (22 row(s) affected) Vic
View Replies !
How To Specify Config File For Package Within A Package
I have a situation where I have Package A, which is called from Package B. Both packages have been designed to use config files for their connections, etc. The issues arises when executing Package B. I can specify a config for Package B to use, but how do I tell the embedded package to use its correct config file? By default, Package A has its config file pointed to development, but I would rather not have to save a special version of Package A that points to production. I dont see any option within the execute package task. Any ideas???
View Replies !
Executing A DTS Package Using An ASP (VBScript)
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 Replies !
Executing DTS Package From An Asp Page
hi, I have created a DTS package that export data to another database. I always click on the package itself to run it. Now I need to be able to create an asp page to execute that DTS pakcage how can I do that. Thanks for your help Al
View Replies !
Executing DTS Package From TSQL
When run in query analyzer, the procedure below returns no errors however it doesn't execute the dts package I am passing to it. The package works beautifully if you run it directly in Enterprise Manager. Can anyone help me with a possible causes or perhaps help me improve my error trapping to help me resolve this? create procedure sp_ExecuteDTSPackage @login varchar(15), @userpwd varchar(15), @svr varchar(15), @packagename varchar(255), @pkgPwd varchar(15) as declare @hr int declare @object int --create a package object EXEC @hr = sp_OACreate 'DTS.Package', @object OUTPUT if @hr <> 0 Begin Print 'Package Creation Failed' RETURN end declare @flag int select @flag = 0 -- 0 = sql svr authentication, 256 = windows authentication EXEC @hr = sp_OAMethod @object, 'LoadFromSqlServer',NULL, @ServerName=@svr, @ServerUserName=@login, @PackageName=@packagename, @Flags=@flag, @ServerPassword = @userpwd, @PackagePassword = @pkgPwd if @hr <>0 begin Print 'Package load failed' RETURN end -- execute the package EXEC @hr = sp_OAMethod @object, 'Execute' IF @hr <> 0 BEGIN print 'Package execution failed' RETURN END --return the step errors as a recordset -- EXEC @hr = sp_OAMethod @object, 'UnInitialize' IF @hr <> 0 BEGIN print 'UnInitialize failed' RETURN END -- release the package object EXEC @hr = sp_OADestroy @object if @hr <> 0 Begin RETURN end
View Replies !
Executing A DTS Package From T_SQL
Can some one point me in the right direction? I need to execute a DTS package after a set of T-SQL statements run in a stored procedure. The starting of the DTS package needs to be the last thing executed/called from the stored procedure. Thanks
View Replies !
Problem In Executing DTS Package In SP
Hi, I have created a Package to import data from MS Access to SQL Server. When I ran this package at Dos prompt by using dtsrun facily, I can able to import the required data within 2 minutes.. But when I try to run the same from Server Profiler/Query Analyzer , I couldn't import the data Command I used --------------- exec master..xp_cmdshell 'dtsrun /SDSS2 /Usa /Nhari_pack' Output: --------------------- DTSRun: Loading... Could you please give me a solution? Thanks Hari (1 row(s) affected)
View Replies !
|
TRACKER
