Execute DTS 2000 Package Task Editor (Inner Variables Vs Outer Variables)
Sep 4, 2006
Hi,
I am not comfortable with DTS 2000 but I need to execute a encapsulated DTS 2000 package from a SSIS package. The real problem is when I need to pass SSIS variables to DTS 2000 package. The DTS 2000 package have 3 global variables that I can identify on " Execute DTS 2000 Package Task Editor - Inner Variables ". I believe the SSIS variables must be mapped on " Execute DTS 2000 Package Task Editor - OuterVariables ". How can I associate the SSIS variables(OuterVariables ) to "Inner Variables"? How can I do it? Much Thanks.
João
View 8 Replies
ADVERTISEMENT
Jun 11, 2007
Hi,
Im trying to do an Insert:
INSERT INTO myTable(column1)
VALUES(..)
How can I use my parameter mapping value inside the SQL statement ablove?
Thank you.
View 5 Replies
View Related
Feb 23, 2006
Hi,
I need to run an SQL command, in which one of the fields is based on the current time, represented as a string in a specific format.
I used a script task to create the string from the current time, and store it in a package variable named "newDate". Later, in the execute sql task, I have the following update statement: "update table_1 set field_1 = ?" (OLE DB connection is used)
and in the parameters mapping I set parameter 0 to the string package variable "newDate".
The problem is that on runtime, the sql executes with the default value of the variable - i.e. it doesn't take the new value.
I checked it and placed another script task after the first one, that simply tries to read the variable "newDate" and print it to screen, but even here the default value is used.
how can I use the new value of the variable?
thanks.
View 7 Replies
View Related
Aug 14, 2007
Hi,
I am having trouble getting the Execute SQL Task to recognize the package-level variables in an SSIS package. The tasks fail execution. The package contains three Execute SQL tasks and none of them receive the variables. However, when I replace the '?' in the code with the variable values they execute fine. These are the steps I have taken thus far:
Made sure the three variables are package level.
Verified I'm using an OLE DB valid connection.
Verified the variables are properly mapped in the parameters mapping section of the task properties.
Tried all 4 types of variable to parameter mapping (even though '?' in the query and '0,1,2..' in the name should work).
Changed the Bypassprepare property to 'True'.
Breakpoints and watch windows to verify the variable values.
I actually tried the simple tutorials on a one-liner SQL statement that uses variables, but had the same problem. I know there must be something simple that I'm missing here, but any info would be greatly appreciated!
Thanks!
View 5 Replies
View Related
Apr 28, 2004
I have a package (Package1) that is run from another package (Package2) via a Execute Package Task. I set a Global Variable called sErrorMessage in the in Package1 and would like to access that Global Variable in an ActiveX Script Task in Package2. How can I do this?
View 6 Replies
View Related
Feb 13, 2007
Hi,
Let's say I have a package taking as parameter "InvoiceID". I want to execute this package as a child in another package. The parent package gets the list of invoices to produce and calls the child package for each entry of the list.
How do I pass the InvoiceID to the child? I know I can use the parent's variables from the child but I don't want the child package to be dependant on the parent package. For example, I might want to execute the "child" package as a stand-alone in development (providing it with a predefined InvoiceID). I might also want to call the same child package from another parent package with other variable names.
What I would like to do is "push" the value instead of "pulling" it. I know it's possible using the command line and the /SET option (ex.: /SET Package.Variables[InvoiceID].Value;' 184084)... Is it possible using the Execute Package Task?
Thanks
View 4 Replies
View Related
Mar 4, 2014
I've got a package in SSIS 2012 that has an Execute SQL task in the control flow level.
The SQL in question does an Upsert via the SQL merge statement. What I want to do, is return the count of records inserted and records updated (No deletes going on here to worry about). I'm using the output option to output the changed recs to a table variable.
I've tried returning the values as:
Select Count(*) as UpdateCount from @mergeOutput where Action = 'Update'
and
Select Count(*) as InsertCount from @mergeOutput where Action = 'Insert'
I've tried setting the resultset to both Single rowset and Full rowset, but i'm not seeing anything returned to the package variables I've set for them (intInsertcount and intUpdatecount).
View 2 Replies
View Related
Aug 24, 2006
I define a package variables "varOutTable" and "varFromTable".
and I insert a SQL Execute SQL Task into Control Flow
my sql command is
"Select * into @[User::varOutTable] from @[User::varFromTable]"
but the task failed,
it seems that sql task can't get the varOutTable and varFromTable
How can I use the Variables in SQL Execute SQL task?
thanks!!
View 14 Replies
View Related
Aug 28, 2007
how can you pass variables from one 'Execute SQL Task' to another?
View 9 Replies
View Related
Jun 30, 2006
Hi there
I'm attempting to update a variable using the Execute SQL task, I've read a lot of posts on this and seems reasonably simple but obviously not. The first time I ran it the variable was updated correctly, I then manually changed the variable value and since then it doesn't work.
I have a task with the following properties;
Resultset: SingleRow
SQLStatement: SELECT MAX(Player_Daily_Data_Pull_Date) as 'PlayerDaily' From Job_Control
On the resultset tab I have the resultsetname = PlayerDailyand the variable I want to update.
The variable has a type of datatime and it's scope is the container that I'm running the sql task within.
Any help would be appreciated.
Derek
View 5 Replies
View Related
May 2, 2007
I am trying to run a simple update (just to establish it works) passing in a variable in the execute SQL task. It is within a Foreach loop and the incoming data is from a RecordSet destination. One of the fields in the recordset is UniqueID. I have a variable called User::UniqueID which holds the uniqueID value. I then have a second variable which is the insert statement of
"INSERT rptlifespan_transactionimagecopy SELECT " + (DT_WSTR, 20)@[User::UniqueID] + ", null, null,null,null,null,null,null"
with delayvalidation set to True.
when I run the package i get the following error:
Error: 0xC0014054 at Execute SQL Task: Failed to lock variable "INSERT rptlifespan_transactionimagecopy SELECT 543, null, null,null,null,null,null,null" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".
543 is the value of my first UniqueID so that bit is working ok. What I do not know is why the insert statement is failing.
Please help i have very little hair left!!
View 16 Replies
View Related
Jan 15, 2008
I have a package with variables which are set to default values, in this instance table names.
I then use a script task to change these variable values, these change fine and I have looked in the watch window whilst debugging to ensure that the values have changed correctly, and they have.
My Script Task then pipes to a Execute SQL Task, where I am using Expressions to set the SqlStatementSource. The Expressions statement uses the variables I change in my script task to form a SQL query.
However, at run-time the Expression appears to be fixed with the default variable values, it does not change even though the variable values have changed. Below is an extract from my expression for the SqlStatementSource.
i.e
Code Block
"USE [DBNAme]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].["+ @[User::StagingEventLossTable] +"](
[PolicyId] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
<snip>
...
Is an Expression value fixed from the moment the package is run and is therefore not dynamic during run-time?
If this is the case, how can I get around this? As I want to be able to change this SQL Statement on the fly at run-time.
Thanks
View 8 Replies
View Related
Apr 15, 2008
Somebody provided me with this link http://www.sqlis.com/58.aspx that helped me understand Execute SQL Task and how to assign values to variables from a resulting SQL query.
But I'm not sure if this tutorial addresses my particular example.
Here is what I'm trying to do.
I have a a simple table called VarTable with 2 columns. Col1 is called VarName, and Col2 is called VarVal. As you may have guessed from the column names, I want to hold variable and value pairs in this table.
Right now, I have 2 entries as follows.
Var1, Hello
Var2, Bye
I want to assign Hello and Bye into two variables in my package.
In using the above tutorial, I figured out how to assign one variable by doing the following SQL and using "Single row" Result Set property.
Select VarVal from VarTable
Where VarName = 'Var1'
Then in Result Set section of the Execute Task Editor I map VarVal with one of my user variables.
How do I take care of the 2nd variable in the same Execute SQL Task, or do I need to have another Execute SQL Task? Based on my understanding, in order for me assign multiple variables in one Execute SQL Task, I need somehow construct a SQL statement to return as many columns as I have variables to assign, but this seems hard to do given the variable table structure I have.
Please let me know if my example is unclear.
View 13 Replies
View Related
Mar 2, 2007
I have 3 Execute SQL tasks to find the max value of some fields. In two tasks the SQL
SELECT MAX(field bigint)
FROM TABLE
Is returned and stored as a string variable. My 3rd execute SQL task is
SELECT MAX(field int)
FROM TABLE
Is returned and stored as an object type. If I try to save it as a string, the execution fails with a mismatch.
WHY? Why aren't they all strings? What am I missing/doing wrong? This is very frustrating behavior. The queries are running against tables on SQL Server 2005 the field types are listed in the SQL above.
Thanks,
Matt
View 7 Replies
View Related
Aug 28, 2007
Hi All,
I was wondering if it is possible to assign values to multiple variables from within the same execute sql task, ie I want to use only one execute sql task and have multiple T-SQL statements within it and then assign the results of these sql statemenst as values to multiple variable.
Typically I would declare variables var1 , var2 and var3 , then can I just add one execute sql task and have mutiple sql statements within it? something like this
select max(id) from table1
select max(id) from table2
select max(id) from table3
Thanks
View 3 Replies
View Related
Mar 3, 2015
I have built the following query in SSMS, when I add it to an Execute SQL Task in SSIS. I get this error -
"[Execute SQL Task] Error: Executing the query "SELECT @columnz = COALESCE(@columnz + ',[' + times..." failed with the following error:
"Must declare the scalar variable "@columnz".". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly."
Query:
use design
drop table tmpNCPCNCDownstreamMaxUtilization3wks
select node, max(utilization) as max_Utilization, DATE
into tmpNCPCNCDownstreamMaxUtilization3wks
from stage_ncpcncdownstream_temporal
WHERE Date BETWEEN DATEADD(day, -20, GETDATE()) AND GETDATE()
[code]....
View 1 Replies
View Related
Nov 9, 2007
I am running my "execute process task" in a foreach loop that uses two variables (file paths) from the recordset that feeds into it.
My executable line looks like this: C:Program FilesWinZipWZZIP.EXE ,
which runs the command line Winzip executable and my argument line looks like this: -rp @ZipArch @ZipTxt.
When I replace the variables with literal strings, the process works (only once, of course).
My Argument won't parse in the expression editor, nor does the process run when embedded in the foreach loop. What am I doing wrong? Thanks for your help.
View 12 Replies
View Related
Jan 24, 2006
Hi,
I would like to design a SSIS package, which have couple of variables. It loads a xls file specified in a variable [varExcelFileFullPath] .
I will run it by commands: exec xp_cmdshell 'dtexec /SQL ....' (pls see an example below).
It seems it does not get the values passed in for those variables. I deployed the package to a sql server.
are there any grammar errors here? I copied it from dtexecui. It worked inside Dtexecui not in dos command.
exec xp_cmdshell 'dtexec /SQL "LoadExcelDB" /SERVER test /USER *** /PASSWORD ****
/MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW
/LOGGER "{6AA833A1-E4B2-4431-831B-DE695049DC61}";"Test.SuperBowl"
/Set Package.Variables[User::varExcelFileName].Properties[Value];"TestAdHocLayer"
/Set Package.Variables[User::varExcelWorkbookName].Value;"Sheet1$"
/Set Package.Variables[User::varExcelFileFullPath].Value;"D: estshareTestAdHocLayer.xls"
/Set Package.Variables[User::varDestinationTableName].Value;"FeaturesTmp"
/Set Package.Variables[User::varPreSQLAction].Value;"delete from FeaturesTmp"
'
thanks,
Guangming
View 2 Replies
View Related
Dec 20, 2006
I am having a problem with passing variables into my SSIS package from C#. The variable names match ("Variable1, ...), however they do not seem to be assigned the proper values once the package is executed. The package does run and returns a FALURE notice saying there is a problem with my expressions.
When I added a new data flow, derived all the variables into columns and wrote their values to a flat file I noticed that the values still contain my default values from the SSIS package itself as though nothing was passed in from C#. I am hoping that it is a simple configuration/user error.
Any ideas?
---- C# ----
Reference to Microsoft.SQLServer.ManagedDTS
using Microsoft.SqlServer.Dts.Runtime;
Application DTSApp = new Application();
Package DTSPack = DTSApp.LoadPackage("d:\SSISPackages\Package.dtsx", null);
DTSPack.Variables.Add("Variable1", true, "", var1.ToString());
DTSPack.Variables.Add("Variable2", true, "", var2);
DTSPack.Variables.Add("Variable3", true, "", 100);
DTSPack.Variables.Add("Variable4", true, "", var4.ToString());
DTSExecResult pkgResult = DTSPack.Execute(null, DTSPack.Variables, null, null, null);
View 16 Replies
View Related
Jul 3, 2006
Hi Everyone,
I created a script task that uploads a file to a HTTPs site. The script task works well and now I want to turn this script task into a custom component...
I'm having problems on how to call the variables I have in the package, I already locked them for reading, but how am I suppose to access them and how can I put their values into strings so I can easily call them in the WebClient.UploadFile method?
Also, I am very new to custom tasks in SSIS.. and would appreciate if anyone of you can send me something (code, link, etc.) of a very simple custom task without any form or properties, that I can try to study.
Basically, what I want my custom task to do is,
1. Read the varialbes in the package.
2. use the variables values as input in my WebClient.UploadFile("varURLstring", "PUT", "varFilePath")
as you can see, my custom task is really simple... but I'm a noob here and I don't know where to go.
Thanks in advance,
Kervy
View 1 Replies
View Related
Feb 1, 2007
Hi,
I'm trying to simplify the deployment process of my project. I already had some troubles with the config files but lets say I solved that issue. I'm going to read a flat file and set the variables of my packages from this file. I was thinking to use a Script Task to do that but I will need to copy this task in every package (I have at least 30). So if I want to make some change this will be painful.
Then, I came up with the idea of creating a Custom Task called Config File Task. I'm working on this but I got stuck trying to get the variables from the package that is running my Config File Task.
This is the code I had in the Script Task:
Dim streamReader As New StreamReader(Dts.Variables("ConfigFilePath").Value.ToString)
Dim line As String
Dim lineArray As String()
Dim variableName As String
Dim variableValue As String
Dim readConfigurations As Boolean = False
While (streamReader.Peek() <> -1)
line = streamReader.ReadLine()
If line = "[CONFIGURATIONS]" Then
readConfigurations = True
ElseIf line = "[/CONFIGURATIONS]" Then
readConfigurations = False
Else
If readConfigurations And line <> "" Then
lineArray = line.Split("|".ToCharArray())
variableName = lineArray(0).Trim()
variableValue = lineArray(1).Trim()
If Dts.Variables.Contains(variableName) Then
Dts.Variables(variableName).Value = variableValue
End If
End If
End If
End While
Dts.TaskResult = Dts.Results.Success
All I want to do is set the variables that exists in each package from the config file. In my UI Class (ConfigFileTaskUI.cs) I can have access to the variables via the TaskHost which is passed as an argument of Initialize() method.
Any thoughts? I'd really appreciate some help!
P.S. I've been working on this for 2 days!
View 11 Replies
View Related
May 15, 2015
I am using SSIS 2012 to dynamically backup stored procedures on a list of Servers and Databases.Here are the steps in my package,
1. Execute SQL Task: Captures a result set (configured to save the data set in an Object variable) with all the Servers and Databases on which stored procedures exist.
2. For each loop that is configured to get each each row(server name @[User::Server_Name] and databases name @[User::DataBase_Name]) from the object variable (@[User::Connection_Strings])and pass it to a connection manager that has an expression for servername
and database name.
2a) Â Within the for each loop, i have an execute process task that is configured as
 i) Executable:  C:WindowsSystem32WindowsPowerShellv1.0powershell.exe
 ii) Arguments:  Configured this to fetch value from an expression. The expression i am using is,'C:batch - CopyPowerShell Scripts to Backup Stored ProceduresScriptOutSPs.ps1' -$Server_Name "+ @[User::Server_Name]+ " -$Database_Name "+ @[User::DataBase_Name]
      Â
Note:  @[User::Server_Name] is the Servername from object variable and so is @[User::DataBase_Name] for database name . The execute task is to run a command line that triggers a powershell script with parameters. Here is the powershell script that i am using,
param([String]$Server_Name,[String]$Database_Name)
$Server = $Server_Name
$Database = $Database_Name
$savePath = "SalesDepartmentsData ScienceUsersSANDEEP PStoredProcedures_Backup"
[code]...
When i execute the script, by passing parameters from arguments, it executes successfully but nothing happens. Passing wrong arguments in the expression?
View 3 Replies
View Related
Jul 3, 2006
I have a requirement to create many SSIS packages and no datatransform is required so the BCP task looks a good contender providing it can do both import & export
is it possible to parse the values in bold as package variables into the BCP task. If so how?
BULK INSERT ipcs_wvg.dbo.extract
FROM 'D:IPCSextract.csv'
WITH (FORMATFILE = 'D:ipcsqueryextract.xml');
Thanks in advance
Dave
I have a global database called ETL Configuration for all my SSIS packages that uses a single table. So I can create three global variables
USE [ETLConfiguration]
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]
Here is the data I would put in here
ConfigurationFilter = 'MySSISPackageName'
ConfiguredValue = 'D:IPCSextract.csv'
PackagePath = 'Package.Variables[User::gsFileName].Properties[Value]'
ConfiguredValueType = 'String'
ConfigurationFilter = 'MySSISPackageName'
ConfiguredValue = 'D:ipcsqueryextract.xml'
PackagePath = 'Package.Variables[User::gsFormatFile].Properties[Value]'
ConfiguredValueType = 'String'
ConfigurationFilter = 'MySSISPackageName'
ConfiguredValue = 'ipcs_wvg.dbo.extract'
PackagePath = 'Package.Variables[User::gsTableName].Properties[Value]'
ConfiguredValueType = 'String'
--Database connection info
ConfigurationFilter = 'MySSISPackageName'
ConfiguredValue = '.mssql2005'
PackagePath = 'Package.Connections[MyDatabaseName].Properties[ServerName]'
ConfiguredValueType = 'String'
ConfigurationFilter = 'MySSISPackageName'
ConfiguredValue = 'MyDatabaseName'
PackagePath = 'Package.Connections[MyDatabaseName].Properties[InitialCatalog]'
ConfiguredValueType = 'String'
I have looked at lots of options to automaticly create SSIS packages and have a hunch that that simple can be better: All these solutions look way to complex to what I want to achieve--
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=17484&SiteID=1
http://lakshmik.blogspot.com/2005/05/how-to-programmatically-create-ssis.html
Create DataFlow Package Sample
http://msdn2.microsoft.com/en-us/library/ms161541.aspx
SMOTableList Sample
http://msdn2.microsoft.com/en-us/library/ms161564.aspx
View 4 Replies
View Related
Oct 13, 2006
Hello all,
I have been struggling trying to read and/or write package level variables from within my custom task. I'd like to be able to get and set values from within the Execute method of my custom task. I have searched this forum and the books online and can't seem to find the answer. I thought maybe I could use an expression on my task (mapping the package variable to a custom task public property) but that doesn't seem to be working for me. I also would have thought I could use the VariableDispenser object from within my task but the collection is empty. I have 3 package level variables configured and can't seem to find a way to access them (with intentions of getting/setting). Could someone point me to a good doc or provide an example that may accomplish this? Thanks!
(I'm using package level variables as a means of passing simple information between tasks that are not using a DB, if there is a better way I'm open to suggestions.)
Jay_G
View 3 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
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
Apr 26, 2006
I have imported a series of SQL 2000 DTS packages to our 64bit AMD SQL 2005 server. I then created SSIS pkgs to run each SQL 2000 package using the Execute DTS 2000 package task. Then I deployed these packages to the Yukon server as well.
I can run these packages when launching them via Integration Services from Management Studio on my workstation, but when I try to run them via a SQL Job or the DTExec command on the server, I get this error. Anyone know of a workaround?
OnError,xxxxxx,xxxxxxx,Execute DTS 2000 Package Task,{94769783-575C-4D1E-90F6-C2BDB3EA3CE2},{E05551C1-CDDE-40E3-87B4-C65D4E1B3A53},4/26/2006 3:01:37 PM,4/26/2006 3:01:37 PM,0,0x,This task does not support native Win64 environment. Please run the package in 32-bit WOW environment instead.
OnError,xxxxxx,xxxxxxx,Execute DTS 2000 Package Task,{94769783-575C-4D1E-90F6-C2BDB3EA3CE2},{E05551C1-CDDE-40E3-87B4-C65D4E1B3A53},4/26/2006 3:01:37 PM,4/26/2006 3:01:37 PM,-1073594105,0x,There were errors during task validation.
Is the DTS 2000 Package Task not supported on Win64?
I have installed the SQL Server 2000 DTS components from the November feature pack on both my workstation and the server.
Thanks for your help!
View 4 Replies
View Related
Dec 17, 2007
Hello all,
I am running a package on a 64-bit server using the 32-bit dtexec. It contains an embedded Execute DTS 2000 package. I deployed the package to the server using the sa account. I set up a SQL Agent job that runs under an account that should have complete admin privileges. The network guys tell me that Legacy components have been installed (although I believe that shouldn't be necessary because SSIS is installed). This is SQL Server 2005, SP2.
When I execute this job, I receive this error message:
Executed as user: Domainuser. ...age Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 11:13:18 AM Error: 2007-12-17 11:13:35.65 Code: 0xC0010018 Source: Execute DTS 2000 Package Task Description: Error loading a task. The contact information for the task is "Execute DTS 2000 Package Task;Microsoft Corporation; Microsoft SQL Server v9; ? 2004 Microsoft Corporation; All Rights Reserved;http://www.microsoft.com/sql/support/default.asp;1". This happens when loading a task fails. End Error Error: 2007-12-17 11:13:35.71 Code: 0xC0010026 Source: Execute DTS 2000 Package Task Description: The task has failed to load. The contact information for this task is "Execute DTS 2000 Package Task;Microsoft Corporation; Microsoft SQL Server v9; ? 2004 Microsoft Corporation; All Rights Reserved;http://www.microsoft.com/sql/support/default.asp;1". End Error Error: 2007-12-17 11:13:35.71 Code: 0xC0024. The step failed.
Is there any way to get this to run?
Thank you very much,
Jessica
View 7 Replies
View Related
Jul 11, 2006
hi !
I have problem here with sql task editor. i have a stored proceedure with 1 input value and 1 output value which needs to be updated in a variable.
stored proc - exec GetRDate ? , ? output
input value is set in a varaible.
I tried giving 1 input and 1 output in the parameter box and result set as fullresultset 1 value for variable -
" failed with the following error: "No disconnected record set is available for the specified SQL statement.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Get Retention Date
what should i give in the parameter and result set
Thanks,
Jasmine
View 1 Replies
View Related
Feb 1, 2008
I have a "Execute DTS Package 2000 " task in SSIS. The SQL 2000 DTS has one task which precedence is "completion". Using SQL2000 it works properly, but when I invoke it from SSIS it doesn€™t respect the precedence. So, when the task above fails it ends the DTS execution.
Is it possible to configure the task to respect the precedences?
View 6 Replies
View Related
May 24, 2007
Hi there,
I'm creating an SSIS package that will execute legacy dts packages. The package to be executed is decided at runtime using a sql query task.
Executing a dts package statically works fine, but when I try to set the details of the dts to be run via expressions, I get the error below.
To make it dynamic, I created a variable of type string, and put the package name in here. I also have a string variable for the packageid. Then I set up an expression on the Execute DTS 2000 Package Task that sets the PackageName & PackageID property to this variable.
The PackageId is a string variable I've retrieved using:
select top 1 name,
cast(id AS varchar(50)) id,
cast(versionid AS varchar(50)) versionid
from sysdtspackages
where name = @PackageName
order by createdate desc
When I use the task to set the package id it works find (by selecting a dts, then changing the name), but when I try to provide the package id I get this message:
Error: 0x0 at Execute DTS 2000 Package Task: System.Runtime.InteropServices.COMException (0x8004040E): Invalid GUID specified.
at DTS.PackageClass.LoadFromSQLServer(String ServerName, String ServerUserName, String ServerPassword, DTSSQLServerStorageFlags Flags, String PackagePassword, String PackageGuid, String PackageVersionGuid, String PackageName, Object& pVarPersistStgOfHost)
at Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.PackageUtils.LoadDTS8Package(String pkgSrc, String sourceUser, String sourcePwd, Int32 srcType, Boolean bUseTrustedConnection, String packageName, String packagePassword, String packageID, String packageVersionGUID)
at Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.Exec80PackageTask.LoadPackage()
at Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.Exec80PackageTask.ExecuteThread()
Task failed: Execute DTS 2000 Package Task
but the method signature specifies String PackageGuid, and it is a string..
any ideas??
i tried casting the variable like so:
(DT_GUID) @[User:TSPkgId], as the versionid is a uniqueidentifier on sysdtspackages.. It didn't like that at all (can't cast from type DT_WSTR to DT_GUID error code 0xC00470C2)
View 8 Replies
View Related
Jul 25, 2006
Hi,
I have a problem in my SSIS-package: If I try to execute a SQL-statement which uses a variable, it say "syntax-error". Here is how I tried:
1. a) Declared a variable. Name: tableName; Bereich (Sorry, I use the german version, no clue whats that on english versions): Package2; Type: STRING; Value: staticDB.StaticData_provider.dbo.C0123
b) Assigned this variable on SQL-Task->"Parameterzuordnung" as VARCHAR, parametername= NewParameterName
c) Used this on my SQL-Statement, SQLSourceType is directinput. Statement: "DELETE
FROM @NewParameterName"
d) Running this results in following error: "Der Parametername wird nicht erkannt" Translation: "Parametername was not recognized."
2. a) see 1.a)
b) see 1.b)
c) Used this on my SQL-Statement, SQLSourceType is directinput. Statement: "DELETE
FROM ?"
d) Running this results in following error: "Syntaxfehler, Berechtigungsverstoß oder anderer allgemeiner Fehler" Translation: "Syntaxerror, permision violation or other common error."
Any ideas?
Regards,
Jan
View 3 Replies
View Related
