How To Call A Variable Declared In Parent Package
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.
View Complete Forum Thread with Replies
Related Forum Messages:
Is It Possible To Use Twice Declared. Variable Names- KILL And After Declared. Variable
is it possible to use twice declared. Variable names- declared. Variable and after KILL and use the same declared. Variable like DECLARE @StartDate datetime KILL @StartDate datetime (remove from memory) use after with the same name i have 2 big stored PROCEDURE i need to put one after one and psss only 1 Variable name to the second stored PROCEDURE like this i don't get this error The variable name '@Start_Date' has already been declared. Variable names must be unique within a query batch or stored procedure. Msg 134, Level 15, State 1, Line 146 The variable name '@End_Date' has already been declared. Variable names must be unique within a query batch or stored procedure. i use like KILL @endDate ?? KILL @StartDate ?? TNX
View Replies !
Parent Package Call To Child Package
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.
View Replies !
Parent Package Variable Assignment Issue In Child Package.
We have one main package from which 7 other child packages are called. We are using ParentPackage variables to assign values for variables and database connections. While the values from ParentPackage variable get assigned to some of the packages properly, to others it doesn€™t assign the value. For example: Except for one of the packages the database connection string gets assigned properly to all other packages. Similarly, in another package one of the variables doesn€™t get assigned. In other packages it is assigned properly. We have checked all the other property values and they are exactly the same. We cannot make any head or tail of this erratic behavior. Please Help.
View Replies !
Parent Package Variable Issue?
I have noticed an issue with parent package variables. I have a package with multiple parent package variables defined, call them X, Y, and Z. I also have a parent package that calls this other package. The parent package has variable definitions for X and Z. It seems that the value for X will be passed along, and Y will give a warning since there is no variable of that name in the parent. The issue is that Z will not be passed along. It seems like the parent package configuration process stops after it encounters one missing variable. Is this a know issue? Is it by design?
View Replies !
Programmatically Add Package Configuration - Parent Variable
How can I specify the target object when trying to set a parent variable package configuration programatically? I am trying to set the Target Object to be "ParentLogID" and the Target Property to be it's "Value". I think this is how I'd add the configuration and set the source. 'Add a Configuration Dim config1 As Configuration config1 = package.Configurations.Add() config1.Description = "Set ParentLogID" config1.ConfigurationType = DTSConfigurationType.ParentVariable config1.ConfigurationString = "User::LogID"
View Replies !
Parent Package Variable Visibility When Designing Packages
I've created an SSIS package that uses parent package variables at several steps in the data flow. However, those parent package variables are only visible during runtime, making debugging the package practically impossible. Let me give you a simplified example: In the parent package, I have a string variable named "sqlLookup" that looks like this: "SELECT * from tblTest WHERE city = " + @city Also in the parent package is a variable named "city" which I can set to the name of the city that I want to query on. This dynamically updates the sqlLookup variable, which is being evaluated as an expression. In the child package, I have an OLE DB Source control which is using the "sqlLookup" variable for its query. I have set up my parent package variable configuration, and it works when I run the package. The problem is this...when I open the child package, I get an error on the OLE DB Source control using the parent package variable, "Command text was not set for the command object". Presumably, this is because the parent package variable is only available during run-time, and not at design time. And, if metadata changes (which it has), I can't get into the OLE DB Source control to edit it, because it throws the error. So, my question is this: are there any workarounds for this problem? Is there a better way to do this? This seems like such an obvious problem that I'm wondering if I either missed a step somewhere, or if I'm just doing it the wrong way. Thanks!
View Replies !
SSIS Parent/Child Package Variable Passing
Hi I have having trouble getting my hands around how to retrieve variables from a parent package. I read about the Environment variables and Configuration File at the parent package level and the Parent Package variable at the child level. Here are my questions: 1. Can you only store/retrieve 1 variable in a config file at a time? 2. Does the child package have to define the variables and if so, do they have to be the same names as the parent package? This seems so more more complex then the DTS2000 way of passing variables to and from packages. Any help would be appreciated. Thanks in advance, Gordon Radley
View Replies !
Checking For Existence Of Parent Variable In Child Package
I have a parent package that calls a child package, when I run the parent package the child package picks up a variable value from the parent in a script task and runs fine, the problem I'm facing is when I run just the child package, the script task fails because it doesn't know about the parent variable. The dilemma I'm facing is in my child script task, if I add the parent variable to the ReadOnlyVariables list then the task fails because the parent variable doesn't exist when I just run the child. If I don't add the parent variable to the ReadOnlyVariabls list then if I try to use it then the task fails saying that the variable doesn't exist in the variables collection. Is there a way to check for the existence of the parent variable, so when I just run the child package I don't get an error and I don't have to change my task every time I choose to run the child package only vs running the parent/child? Thanks.
View Replies !
SSIS Parent/Child Package Variable Handshake Issue
Hi, I having a problem getting the child package variable values into a parent package. Here is the scenario; I have parent package(Master) calling two child packages using execute package task. I have no problem to pass variable values from the parent to child using the parent configuration but not able to get the variable values from the child to parent. I am setting the package status in each child package based on certain conditions and determine the final status in a parent based on child package variable statuses. Does any body have any idea? I will appreciate your thoughts.Thanks..Mako
View Replies !
Passing A Variable Between A Parent And Child Package Doesn't Work
Has someone managed to pass successfully a variable from a parent package to a child package? I€™ve tried a zillion permutations and I can€™t get it to work. The strange thing was that I was able to successfully do this with pre-RTM builds. Basically, what I am trying to do is: The parent package has a variable, e.g. ExecutionID which I set using a script to System::ExecutionInstanceGUID. I verified that the variable is set correctly by dumping it to a SQL Server table. I created a child package variable with the same name. In the child package, I€™ve created a parent package configuration that points to the ExecutionID variable. I am trying to read the variable in a Derived Column Task in which I have a column linked to @ExecutionID. This doesn€™t work. Step-by-step instructions from someone who managed to concur this will be greatly appreciated. Oh, I didn€™t have any luck hitting a breakpoint in a script task inside a child package with both in and out of process execution also.
View Replies !
Passing A SSIS Global Variable To A Declared Variable In A Query In SQL Task
I have a SQL Task that updates running totals on a record inserted using a Data Flow Task. The package runs without error, but the actual row does not calculate the running totals. I suspect that the inserted record is not committed until the package completes and the SQL Task is seeing the previous record as the current. Here is the code in the SQL Task: DECLARE @DV INT; SET @DV = (SELECT MAX(DateValue) FROM tblTG); DECLARE @PV INT; SET @PV = @DV - 1; I've not been successful in passing a SSIS global variable to a declared parameter, but is it possible to do this: DECLARE @DV INT; SET @DV = ?; DECLARE @PV INT; SET @PV = @DV - 1; I have almost 50 references to these parameters in the query so a substitution would be helpful. Dan
View Replies !
Set Value For Variable In A Declared Cursor
Hi, I have a problem on setting the value for the variable in a declared cursor. Below is my example, I have declared the cursor c1 once at the top in a stored procedure and open it many times in a loop by setting the variable @str_var to different values. It seems the variable cannot be set after the cursor declared. Please advise how can I solve this issue. ------------------------------------------------------------------------ DECLARE @str_var VARCHAR(10) DECLARE @field_val VARCHAR(10) DECLARE c1 CURSOR LOCAL FOR SELECT field1 FROM tableA WHERE field1 = @str_var WHILE (Sometime TRUE) BEGIN .... SET @str_var = 'set to some values, eg. ABC123, XYZ123' OPEN c1 FETCH c1 INTO @field_val WHILE (@@fetch_status != -1) BEGIN PRINT @field_val ... FETCH c1 INTO @field_val END CLOSE c1 END DEALLOCATE c1 ---------------------------------------------------------------------- Thanks a lots, Vincent
View Replies !
Cursor Declared With Variable In Where Clause
When I execute next query on sqlserver 6.5 nested in stored procedure I can see that 'open testCursor' selected rows using new value of @var. When I execute query on sqlserver 7.0 I can see that 'open testCursor' selected rows using value of @var before 'declare ... cursor'. Is there any way to force sqlserver 7.0 to proccess cursor like it did it before. select @var = oldValue declare testCursor cursor for select someColumns from someTable where someColumn = @var select @var = newValue open testCursor fetch next from testCursor into @someColumns Thank's in advance. Mirko.
View Replies !
Problem Returning A Declared Variable
when I run this sproc all I get out of it is "the commands completed successfully" and doesn't return the value. If anyone can point out where the error is I would really appreciate it. Thanks Code: Create Procedure LookupLeagueIdByUserName(@userName as varchar(40) = '') as begin if (@userName = '') raiserror('LookupLeagueIdByUserName: Missing parameters', 16,1) else begin Declare @leagueId int Set @leagueId = -1 --Check if the username belong to a player Select @leagueId = leagueId From Users u inner join players p on p.userId = u.userId inner join teams t on p.teamId = t.teamId where u.userName = @userName if (@leagueId > 0) begin return @leagueId end else begin --Check if the username belong to a teamUser Select @leagueId = leagueId From Users u inner join teamUsers tu on tu.userId = u.userId inner join teams t on tu.teamId = t.teamId where u.userName = @userName if (@leagueId > 0) begin return @leagueId end else begin --Check if the username belong to a leagueUser Select @leagueId = leagueId From Users u inner join leagueUsers lu on lu.userId = u.userId where u.userName = @userName if (@leagueId > 0) begin return @leagueId end else begin --username is not in db or is an admin user return -1 end end end end end return -- when I run this I get no results returned LookupLeagueIdByUserName 'chris'
View Replies !
Obtaining Collation Length Of Declared Variable Within SP
Morning All,Can I have some help with this one please, I am having to make a fixed length text file based on information from the DBDeclare @EDIString varchar(MAX)Declare @RecordType varchar(2)Declare @RegistrationMark varchar(7)Declare @Model_Chassis varchar(11)Declare @LocationCode Varchar(4)Declare @MovementDate varchar(8)Declare @IMSAccountCode varchar(5)Declare @MovementType varchar(8)Declare @NotUsed1 Varchar(28)Declare @NotUsed2 varchar(7)Select @RecordType = RecordType, @RegistrationMark = RegistrationMark, @Model_Chassis = Model_And_Chassis, @LocationCode = LocationCode, @MovementDate = MovementDate, @IMSAccountCode = IMSAccountCode, @Movementtype = MovementTypeCode from Fiat_OutBoundOnce I have selected the information from the DB I need to ensure that each field is the correct length. I therefore want to pass the variable and the length of the variable into a function to return the correct length.So if location Code = 'AB' this needs to be four characters long so want to pass it into a function and return 'AB 'As I need to do this for 70+ variables is there an easy way to obtain the length of the collation for the variable?regardsTom
View Replies !
Select Declared Variable With Case Statements
I am trying to gather counts for table imports made for files from friday - sunday and create a variable that will be the body of an email. I'd like to use either a case statement or a while statement since the query is the same but the values must be collected for each day (friday, saturday and sunday) and will all be included in the same email. I have declared all variables appropriately but I will leave that section of the code out. Select @ifiledate = iFileDate from tblTelemark where iFileDate = CASE WHEN iFileDate = REPLACE(CONVERT(VARCHAR(10), GETDATE()-3, 101), '/','') THEN Select @countfri1 = Count(*) from tbl1 Select @countfri2 = Count(*) from tbl2 Select @countfri3 = Count(*) from tbl3 Select @countfri4 = Count(*) from tbl4 WHEN iFileDate = REPLACE(CONVERT(VARCHAR(10), GETDATE()-2, 101), '/','') THEN Select @countsat1 = Count(*) from tbl1 Select @countsat2 = Count(*) from tbl2 Select @countsat3 = Count(*) from tbl3 Select @countsat4 = Count(*) from tbl4 WHEN iFileDate = REPLACE(CONVERT(VARCHAR(10), GETDATE()-1, 101), '/','') THEN Select @countsun1 = Count(*) from tbl1 Select @countsun2 = Count(*) from tbl2 Select @countsun3 = Count(*) from tbl3 Select @countsun4 = Count(*) from tbl4 END Is there a way to do what this that works???
View Replies !
Using Declared Variable As Passphrase Slows Query
I have two tables - gift_cards and history - each related by a field called "card_number". This field is encrypted in the history table but not in the gift_cards table. Let's say the passphrase is 'mypassphrase'. The following query takes about 1 second to execute with a fairly large amount of data in both tables: SELECT max([history].[date_of_wash]) AS LastUse FROM gift_cards AS gc LEFT JOIN history ON gc.card_number=CAST(DecryptByPassPhrase('mypassphrase', HISTORY.CARD_NUMBER) AS VARCHAR(50)) GROUP BY gc.card_number When I use a declared variable to contain the passphrase, the same query takes over 40 seconds. For example, declare @vchPassphrase as nvarchar(20) select @vchPassphrase = 'mypassphrase' SELECT max([history].[date_of_wash]) AS LastUse FROM gift_cards AS gc LEFT JOIN history ON gc.card_number=CAST(DecryptByPassPhrase(@vchPassphrase, HISTORY.CARD_NUMBER) AS VARCHAR(50)) GROUP BY gc.card_number This query is part of a stored procedure and, for security reasons, I can't embed the passphrase in it. Can anyone explain the discrepancy between execution times and suggest a way to make the second query execute faster? Thanks, SJonesy
View Replies !
Stored Procedure Using A Declared Variable In Insert Query (inline Or Using EXEC)
Hello, I have a stored procedure where I run an insert statement. I want to knwo if it is possible to do it using a variable for the table name (either in-line or with an EXEC statement without building a string first and executing that string. See examples of what I am talking about in both cases below: I want to be able to do this (with or without the EXEC) : ------------------------------------------------------------------------------------ DECLARE @NewTableNameOut as varchar(100) Set @NewTableNameOut = 'TableToInsertInto' EXEC( Insert Into @NewTableNameOut Select * From tableToSelectFrom ) ------------------------------------------------------------------------------------ I can not do the above because it says I need to declare/set the @NewTableNameOut variable (assuming it is only looking at this for the specific insert statement and not at the variable I set earlier in the stored procedure. I can do it like this by creating a string with the variable built into the string and then executing the string but I want to know if I can do it like I have listed above. ------------------------------------------------------------------------------------ DECLARE @NewTableNameOut as varchar(100) Set @NewTableNameOut = 'TableToInsertInto' EXEC( 'Insert Into ' + @NewTableNameOut + ' ' + 'Select * From tableToSelectFrom' ) ------------------------------------------------------------------------------------ It is not an issue for my simple example above but I have some rather large queries that I am building and I want to run as described above without having to build it into a string. Is this possible at all? If you need more info please let me know.
View Replies !
Update Statement Using Declared Variable Produces Unexpected Results On SQL Server 2005
We are getting unexpected results from the following update statement when it is executed on SQL Server 2005. The strange thing is that we get duplicated values for QM_UID (although when run under SQL Server 2000 we don't get duplicated values) Can anyone explain why this happens or suggest another way of populating this column without using a cursor or adding a temporary autoincrement column and copying the values over? declare @NextID int; set @NextID = 1; update tmp set QM_UID=@NextID, @NextID = @NextID + 1; select QM_UID, count(*) from tmp group by QM_UID having count(*) > 1 order by QM_UID QM_UID count(*) 25 2 26 3 27 4 28 4 29 4 30 4 31 4 32 4 33 4 34 4 35 5 36 4 37 4 38 4 39 4 40 3 ... --- Script to replicate problem -- NB: The number of rows that must be added to tmp before this problem will occur is machine dependant -- 100000 rows is sufficient on one of our servers but another (faster) server doesn't show the error -- at 100000 rows but does at 1000000 rows. -- Create a table CREATE TABLE tmp ( [QM_ADD_OP] [char](6) DEFAULT '' NOT NULL, [QM_ADD_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_ADD_TIME] [int] DEFAULT -1 NOT NULL, [QM_EDIT_OP] [char](6) DEFAULT '' NOT NULL, [QM_EDIT_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_EDIT_TIME] [int] DEFAULT -1 NOT NULL, [QM_LOCK_OP] [char](6) DEFAULT '' NOT NULL, [QM_QUOTE_JOB] [smallint] DEFAULT 0 NOT NULL, [QM_QUOTE_NUM] [char](12) DEFAULT '' NOT NULL, [QM_JOB_NUM] [char](12) DEFAULT '' NOT NULL, [QM_PRJ_NUM] [char](12) DEFAULT '' NOT NULL, [QM_NUMBER] [char](12) DEFAULT '' NOT NULL, [QM_REV_NUM] [char](6) DEFAULT '' NOT NULL, [QM_REV_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_REV_TIME] [int] DEFAULT -1 NOT NULL, [QM_REV_OPR] [char](6) DEFAULT '' NOT NULL, [QM_STYLE_CODE] [char](4) DEFAULT '' NOT NULL, [QM_REP_JOB_NUM] [char](12) DEFAULT '' NOT NULL, [QM_REP_COLUMN] [smallint] DEFAULT 0 NOT NULL, [QM_REP_PART] [char](6) DEFAULT '' NOT NULL, [QM_REP_MODEL] [smallint] DEFAULT 0 NOT NULL, [QM_REP_TYPE] [smallint] DEFAULT 0 NOT NULL, [QM_MODEL_QUOTE] [char](12) DEFAULT '' NOT NULL, [QM_RUN_NUM] [int] DEFAULT 0 NOT NULL, [QM_SOURCE_QUOTE] [char](12) DEFAULT '' NOT NULL, [QM_SOURCE_VAR] [smallint] DEFAULT 0 NOT NULL, [QM_SOURCE_QTY] [char](12) DEFAULT '' NOT NULL, [QM_SOURCE_PART] [char](6) DEFAULT '' NOT NULL, [QM_SOURCE_MODEL] [smallint] DEFAULT 0 NOT NULL, [QM_ORIG_QUOTE] [char](12) DEFAULT '' NOT NULL, [QM_ORIG_VAR] [smallint] DEFAULT 0 NOT NULL, [QM_ORIG_QTY] [char](12) DEFAULT '' NOT NULL, [QM_ORIG_PART] [char](6) DEFAULT '' NOT NULL, [QM_COPY_JOB] [char](12) DEFAULT '' NOT NULL, [QM_COPY_COLUMN] [smallint] DEFAULT 0 NOT NULL, [QM_COPY_J_PART] [char](6) DEFAULT '' NOT NULL, [QM_COPY_QUOTE] [char](12) DEFAULT '' NOT NULL, [QM_COPY_VAR] [smallint] DEFAULT 0 NOT NULL, [QM_COPY_QTY] [char](12) DEFAULT '' NOT NULL, [QM_COPY_Q_PART] [char](6) DEFAULT '' NOT NULL, [QM_JOINT_STATUS] [smallint] DEFAULT 0 NOT NULL, [QM_QUOTE_STATUS] [smallint] DEFAULT 0 NOT NULL, [QM_JOB_STATUS] [smallint] DEFAULT 0 NOT NULL, [QM_LIVE_STATUS] [smallint] DEFAULT 0 NOT NULL, [QM_USER_STATUS] [smallint] DEFAULT 0 NOT NULL, [QM_DEL_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_IS_CONVERTED] [smallint] DEFAULT 0 NOT NULL, [QM_PRINTED] [smallint] DEFAULT 0 NOT NULL, [QM_COPY_RATES] [smallint] DEFAULT 0 NOT NULL, [QM_IMPORT_UPDATE] [smallint] DEFAULT 0 NOT NULL, [QM_CRED_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_CRED_TIME] [int] DEFAULT -1 NOT NULL, [QM_CRED_AMT] numeric(26,8) DEFAULT 0 NOT NULL, [QM_CRED_OP] [char](6) DEFAULT '' NOT NULL, [QM_HELD] [smallint] DEFAULT 0 NOT NULL, [QM_PROOF] [char](12) DEFAULT '' NOT NULL, [QM_DELIV_METHOD] [char](12) DEFAULT '' NOT NULL, [QM_ART_METHOD] [char](12) DEFAULT '' NOT NULL, [QM_DES_TYPE] [smallint] DEFAULT 0 NOT NULL, [QM_REC_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_REC_TIME] [int] DEFAULT -1 NOT NULL, [QM_OWN_OP] [char](6) DEFAULT '' NOT NULL, [QM_RESP_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_RESP_TIME] [int] DEFAULT -1 NOT NULL, [QM_RESP_OP] [char](6) DEFAULT '' NOT NULL, [QM_RESP_OP_1] [char](6) DEFAULT '' NOT NULL, [QM_RESP_OP_2] [char](6) DEFAULT '' NOT NULL, [QM_RESP_OP_3] [char](6) DEFAULT '' NOT NULL, [QM_RESP_OP_4] [char](6) DEFAULT '' NOT NULL, [QM_RESP_OP_5] [char](6) DEFAULT '' NOT NULL, [QM_RECONTACT] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_REQ_FLAG] [smallint] DEFAULT 0 NOT NULL, [QM_ORIG_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_ORIG_TIME] [int] DEFAULT -1 NOT NULL, [QM_PREF_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_PREF_TIME] [int] DEFAULT -1 NOT NULL, [QM_LATE_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_LATE_TIME] [int] DEFAULT -1 NOT NULL, [QM_TITLE] [char](72) DEFAULT '' NOT NULL, [QM_DELIV_CODE] [char](12) DEFAULT '' NOT NULL, [QM_CLT_SPEC] [char](12) DEFAULT '' NOT NULL, [QM_TAX_REF] [char](22) DEFAULT '' NOT NULL, [QM_CONTACT] [char](36) DEFAULT '' NOT NULL, [QM_PHONE] [char](22) DEFAULT '' NOT NULL, [QM_FAX] [char](22) DEFAULT '' NOT NULL, [QM_ORDER] [char](20) DEFAULT '' NOT NULL, [QM_ORDER_CFM] [smallint] DEFAULT 0 NOT NULL, [QM_ORDER_REL] [char](6) DEFAULT '' NOT NULL, [QM_REP] [char](12) DEFAULT '' NOT NULL, [QM_REP_1] [char](12) DEFAULT '' NOT NULL, [QM_REP_2] [char](12) DEFAULT '' NOT NULL, [QM_REP_3] [char](12) DEFAULT '' NOT NULL, [QM_REP_4] [char](12) DEFAULT '' NOT NULL, [QM_REP_5] [char](12) DEFAULT '' NOT NULL, [QM_COORDINATOR] [char](12) DEFAULT '' NOT NULL, [QM_PRIORITY] [smallint] DEFAULT 0 NOT NULL, [QM_TYPE_CODE] [char](12) DEFAULT '' NOT NULL, [QM_GRADE] [smallint] DEFAULT 0 NOT NULL, [QM_FIN_SIZE_CODE] [char](12) DEFAULT '' NOT NULL, [QM_FIN_WID] numeric(26,8) DEFAULT 0 NOT NULL, [QM_FIN_LEN] numeric(26,8) DEFAULT 0 NOT NULL, [QM_FIN_DEP] numeric(26,8) DEFAULT 0 NOT NULL, [QM_FIN_GUSS] numeric(26,8) DEFAULT 0 NOT NULL, [QM_FIN_GSM] numeric(26,8) DEFAULT 0 NOT NULL, [QM_FIN_UNIT] [char](12) DEFAULT '' NOT NULL, [QM_ORIENT] [smallint] DEFAULT 0 NOT NULL, [QM_PROD_CODE] [char](22) DEFAULT '' NOT NULL, [QM_FIN_GOOD] [char](22) DEFAULT '' NOT NULL, [QM_CUST_CODE] [char](12) DEFAULT '' NOT NULL, [QM_CUST_CODE_1] [char](12) DEFAULT '' NOT NULL, [QM_CUST_CODE_2] [char](12) DEFAULT '' NOT NULL, [QM_CUST_PROS] [smallint] DEFAULT 0 NOT NULL, [QM_REQD_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_REQD_TIME] [int] DEFAULT -1 NOT NULL, [QM_FOLIO] [char](12) DEFAULT '' NOT NULL, [QM_FOLIO_1] [char](12) DEFAULT '' NOT NULL, [QM_FOLIO_2] [char](12) DEFAULT '' NOT NULL, [QM_PACK_QTY] numeric(26,8) DEFAULT 0 NOT NULL, [QM_USAGE] numeric(26,8) DEFAULT 0 NOT NULL, [QM_REORDER] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_EACH_WGT] numeric(26,8) DEFAULT 0 NOT NULL, [QM_WGT_UNIT] [char](12) DEFAULT '' NOT NULL, [QM_RFQ_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_RFQ_TIME] [int] DEFAULT -1 NOT NULL, [QM_RFQ_OPR] [char](6) DEFAULT '' NOT NULL, [QM_SALES_TYPE] [smallint] DEFAULT 0 NOT NULL, [QM_SALES_SRC] [char](12) DEFAULT '' NOT NULL, [QM_SALES_RSN] [char](12) DEFAULT '' NOT NULL, [QM_PROFILE] [char](12) DEFAULT '' NOT NULL, [QM_JOB_QTY] numeric(26,8) DEFAULT 0 NOT NULL, [QM_PREV_QTY] numeric(26,8) DEFAULT 0 NOT NULL, [QM_JOB_UNIT] [char](12) DEFAULT '' NOT NULL, [QM_PO_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_PO_TIME] [int] DEFAULT -1 NOT NULL, [QM_PO_OP] [char](6) DEFAULT '' NOT NULL, [QM_DLY_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_DLY_TIME] [int] DEFAULT -1 NOT NULL, [QM_QTY_DESP] numeric(26,8) DEFAULT 0 NOT NULL, [QM_TOTAL_DLY] [int] DEFAULT 0 NOT NULL, [QM_SCHED_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_SCHED_TIME] [int] DEFAULT -1 NOT NULL, [QM_CLOSE_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_CLOSE_TIME] [int] DEFAULT -1 NOT NULL, [QM_INV_NUM] [char](12) DEFAULT '' NOT NULL, [QM_PACK_NUM] [char](12) DEFAULT '' NOT NULL, [QM_DOWN_LOAD] [smallint] DEFAULT 0 NOT NULL, [QM_TRACK_CODE] [char](4) DEFAULT '' NOT NULL, [QM_TAX_TYPE] [smallint] DEFAULT 0 NOT NULL, [QM_TAX_CODE] [char](6) DEFAULT '' NOT NULL, [QM_CURR] [char](6) DEFAULT '' NOT NULL, [QM_EXCH_RATE] numeric(18,8) DEFAULT 0 NOT NULL, [QM_UNIT_QTY] numeric(26,8) DEFAULT 0 NOT NULL, [QM_UNIT_FLAG] [smallint] DEFAULT 0 NOT NULL, [QM_RUNON_QTY] numeric(26,8) DEFAULT 0 NOT NULL, [QM_SPEC_QTY] numeric(26,8) DEFAULT 0 NOT NULL, [QM_CHARGEABLE] [smallint] DEFAULT 0 NOT NULL, [QM_NC_REASON] [char](22) DEFAULT '' NOT NULL, [QM_CUST_MKUP] numeric(18,8) DEFAULT 0 NOT NULL, [QM_JOB_MKUP] numeric(18,8) DEFAULT 0 NOT NULL, [QM_BROKERAGE] numeric(18,8) DEFAULT 0 NOT NULL, [QM_CUST_DISC] numeric(18,8) DEFAULT 0 NOT NULL, [QM_INVOKED_BTNS] [int] DEFAULT 0 NOT NULL, [QM_IMPORTED] [smallint] DEFAULT 0 NOT NULL, [QM_IMPORT_RECALC] [smallint] DEFAULT 0 NOT NULL, [QM_IMPORT_CONVERT] [smallint] DEFAULT 0 NOT NULL, [QM_BRANCH] [char](6) DEFAULT '' NOT NULL, [QM_CODE] [char](36) DEFAULT '' NOT NULL, [QM_TEMPLATE] [smallint] DEFAULT 0 NOT NULL, [QM_REPEAT_PERIOD] [int] DEFAULT 0 NOT NULL, [QM_REOPEN_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_CAT_OPTION] [char](16) DEFAULT '' NOT NULL, [QM_UNIT_ID] [char](10) DEFAULT '' NOT NULL, [QM_PROD_BRANCH] [char](6) DEFAULT '' NOT NULL, [QM_UID] [int] DEFAULT 0 NOT NULL, [QM_AVAIL_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_AVAIL_TIME] [int] DEFAULT -1 NOT NULL ) ON [PRIMARY] GO -- Create an index on the table CREATE unique INDEX [QM_NUMBER_ORDER] ON tmp([QM_QUOTE_JOB], [QM_NUMBER]) ON [PRIMARY] GO -- Populate the table declare @Counter as int SET NOCOUNT ON set @Counter = 1 while @Counter < 100000 begin insert into tmp (QM_ADD_TIME, QM_NUMBER) values (1,@Counter); set @Counter = @Counter + 1 end GO -- Update QM_UID to a sequential value declare @NextID int; set @NextID = 1; update tmp set QM_UID=@NextID, @NextID = @NextID + 1; -- Find rows with a duplicate QM_UID (there should be no duplicate) select QM_UID, count(*) from tmp group by QM_UID having count(*) > 1 order by QM_UID --drop table tmp -- output from select @@VERSION -- Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23 2007 16:28:52 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
View Replies !
Execution Of Child Package From Parent Package In Sql Server 2005 Integration Services
Hi, I created a package which passes some infornmations( through parameters) to its child package. I need to do some processing in parent package based on execution status of child package.i.e. if child fails then some operation and if child succeeds then other operation. To determine the status of execution of child package I am using two differnt constraint ..one constraint is having value "Success" and other having value "Failure". My problem is that when child packge is executed successfully the constraint with value = "Success" works properly but when child fails the constraint with value "Failure" does not work. -Prashant
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 !
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 !
Resume Parent Package After Child Package Failure...
Hello All, I have a SSIS Package with a "Execute Package Task" to call a child package. I am trying to have the master/parent package complete its execution regardless the outcome (failure or success) of the child package. The overall structure of the master package is: 1. Perform Pre-load tasks (stored procedure). 2. Execute Package Task (call child package) 3. Perform Post-load Tasks (stored procedure) I have try everything and cannot get the results that I want... I have tried the combination of "failparentonfailure", "forceexecutionvalue", etc. The master package stops at childs failure. I would like to resume to completion Any ideas out there? Rohan
View Replies !
SSIS Parent Package Can Not Find The Child Package
I have two SSIS packages in a project, one calling the other. The parent package works fine in my local mechine. After they are deployed to the production, I schedeul jobs to run the packages in the SqlServer. The child package works fine if I run it alone, but the parent package could not find its child package if I run the parent package . As I checked, all xml config files and the connection string pointing to the child package were set correctly. It seems the parent package did not use the xml config file. Can someone help me? Thanks in advance.
View Replies !
Parent Package Is Losing A Child Package
I have a parent package which calls a number of child packages. Occasionally, I have one child package which starts up and appears to finish but the parent package never gets notification that the child has finished. If I view this in Management Studio's in Integration Services, I can view the packages that are running and only the parent package is running. Because it never gets control back, the next child package will never get started and the parent package will continue to run and never finish. Any idea what could be happening??
View Replies !
Parent Package Variables And SQL Package Store
Hello All, I have a Master Package which calls a group of other packages out on the SSIS Package Store using parent / child and a number of variables. These seem to work when passing various audit information (audit key, record counts, etc) but when I try to pass a variable for the connection string and assign it via an expression to the connection manager, I get the Master Package writing to the correct database and the children package writing to their "default" database which is supplied as the default value in the variables which should be populated by the parent task. i.e., I end up with my master package audit information in database a and child package audit and transactions in database b, even though the packages associated with these transactions are supposed to be reading the connection string from a parent variable. Any clues or suggestions? Edit: It is still passing along the correct parent audit information and record counts, it's just not connecting to the correct database by the variables holding the connect string Thanks for your time.
View Replies !
How To Run Encrypted Child Package From Parent Package
I am trying to figure out how to run an SSIS package that is encrypted but also has a child package which is encrypted. I am trying to run from the cmd line using dtexec and specifying DECRYPT <pwd> for the parent package but how do you specify for the child package. Please help if you have seen such an issue before or you have a work around. Thanks Newbie
View Replies !
Parent Variable Warning In SSIS
Hello, When I try to execute a Integration Services with parent variables, always appears this warning for each parent variable: Configuration from a parent variable "%1!s!" did not occur because there was no parent variable collection. The IS is executed correctly, but we don't know the reason why this warning appear, if the value is correctly assigned. Thanks, Pablo Orte
View Replies !
Parent-Child Variable Issue
Hello, I'm having problem to pass variable from a package to a another one. I having 2 packages: the parent (parent.dtsx) use a Execute Package Task to execute the child package (child.dtsx). In the parent package, I have a variable named var1 (data type String) with value=test In my child package, I having a script task using the variable var1 to make a MsgBox (var1 is in readonly in the script): MsgBox(CStr(Dts.Variables("var1").Value)) In this case, I having an error (cause the variable may no exist) but i can see the value of the variable in the MessageBox and the execute package task fail because of this error. After I tried this solution: (Found in this Forum) In the child package, I create the variable var1 (same type, no value). Then in the control flow, I right click on the background and select "package configurations". I enable package configurations. Then I add a new one. I change the configuration type to "parent package variable." Then, in the specify configuration settings entry, I enter the name of the variable in the parent package. On the following screen, I select the "value" property of the variable created in this package (Child package). At last, I give the configuration a name and hit finish. If I execute my parent package, the result is no value in the MessageBox. I also tried the same thing (with the package configuration) to pass a a variable with the connection string from a package to the connection of an another one (I set the connection string of the connection) and it doesn't work. What did I miss ? Thanks in advance for your help. Regards
View Replies !
Parent / Child Variable Frustration
Can't figure out why either of the two setups I've tried on this aren't working. Any help would be appreciated. Business need: Clients upload six MS Access (mdb) data files to server, data files loaded into SS2005 database. Because of legacy concerns, even though each client's six files contain the same data, the individual file names are different. Setup: Package 1 searches the client's ftp folder for new file. If found, contents are unzipped and moved to client's staging folder while uploaded files are archived. Package 1 then calls package 2, passing to it the end date for this particular file, along with client-specific data (e.g. the variables used in the connection strings for the six mdb files and package 2's log file, which appends the client name to a constant file name). Package 1 uses a different config file for each client, and the package is run with that applicable config file. Problem: For some reason, the parent variables are not being passed properly to Package 2. If I run it with the first client, everything works fine. If I run it with the second, Package 1 works fine (finds the file, does the naming, etc.), but Package 2 is using the default variable values that are coded in the Value column in the Variables pane. The first time I tried it, the first step of Package 2 was a script step whose only function was to set Package 2's variables to the values in Package 1: Dts.Variables("package2variablename").Value = Dts.Variables("package1variablename").Value This didn't work at all. The log file's name still used the default column value instead of what Package 1 had, and the package was trying to find the wrong file name in the staging directory. The second time, I set Package 2 up to use configurations and have one parent package entry for each variable I want to set. This worked slightly better, as while there was still a log file with the Value-column value in it, there was also one for the client-specific name. However, the package still failed as Package 2 is still looking for the wrong file name. Obviously there's something in there I haven't set correctly, but I'm at a loss as to what it is.
View Replies !
Using Variable/Expression To Log With Parent/Child Packages
I have one package that executes 4 child packages (5 total). All 5 packages are set to log information using a connection string with a variable set at runtime for the location. The child packages use a parent variable to get the log connection string from the parent package. The parent package logs fine, but a strange behavior occurs with the child packages. The child packages all log data to the location used in the configuration file variable during runtime (like it's supposed to). However, I would get an error right before the first child package finishes execution saying path not found. Just for giggles, I created the folder I use in my development environment on the production environment. The error goes away, but the log file is created in that folder with no data in it. Subsequently, the log file with the data is created in the location set in the configuration file. Now I have two log files!!!! Anybody know how to fix this?!
View Replies !
Parent-child Variable Issues That May Impact Deployment
We are using SSIS for the first time. My team is working on a project that involves putting a date time stamp into a series of tables. These tables are being assembled in a series of child packages being executed by the parent. When the parent runs, we evaluate our timestamp variable as a GETDATE() expression and pass it to the children to be included as a derived column. We don't want the actual runtime of each step to be the timestamp, just the start of the batch (parent). In order to get the variable to pass over to the child, we needed to set the package location to "file system"instead of "SQL Server". It seems unusual that this would be so. Are we doing something wrong? What implications does this have for deployment? Will we need to customize the packages for each instance we plan to run this on? Can you have a parent run a child package on a different instance? This would be a performance plus since we have really huge source databases and would like to distribute the processing. Hmmm, my boss just told me to scratch the whole idea of parent-child and go with a control table to store the variable for all the packages to access. Oh well, I'm still interested in why this is so cumbersome when really its just passing a parameter from one procedure to another. Oh, and I think you could use a spellchecker on this message box. At least I could use one.
View Replies !
Problems With Parent Variable Configuration After Moving Packages To New Machine...
Hi everybody, I moved my packages to the new machine, and have problem with parent variables. When child package tries to get parent variable value I get an error: Information: 0x40016042 at LoopPackage: The package is attempting to configure from the parent variable "MAIN_SesId". Warning: 0x80012028 at LoopPackage: Process configuration failed to set the destination at the package path of "Package.Variables[User::MAIN_SesId].Properties[Value]". This occurs when attempting to set the destination property or variable fails. Check the destination property or variable. Thanks, for any information.
View Replies !
Help With Parent Package Variables ...
Hi Folks, Followed the instructions here (http://msdn2.microsoft.com/en-us/library/ms345179(SQL.90).aspx) to be able to pass a package variable to it's child packages BUT something is not working. The basic process I followed: 1. Created a variable named BatchID in Parent package (type= DBNull). A script runs in this package that sets it equal to a GUID (varsWrite("BatchID").Value = vars("System::ExecutionInstanceGUID").Value). 2. For each of the child packages I ... a. Create a variable named BatchID (type=DBNull). b. Create a Parent package variable configuration : i. ParentVariable = BatchID ii. Target Property = Variables.BatchID.Properties. Value 3. I save everything, run from the top level package ... and i get different BatchIDs in the tables. Is there something I'm maybe missing??? thanks - wg
View Replies !
Order In Parent Package Configurations
Hi all, I am pretty new to SSIS and i found some, (to me) unexpected behaviour. Maybe you guys can help me out understanding it. I am currently building a multi package ETL solution, that uses parent-package configurations to "distribute" variable values from the "root" package to the lower level and "leaf" packages. Each package contains some 8 parent-package variables. When i ran the entire solution (i.e. the whole tree) I found that some packages aren't getting the correct values for some of their variables. It seems that if a parent-package configuration that wasn't configured correctly (misspelled parent variable name in this case) blocked the other parent-package configurations in the same package from evaluating. When i moved the correct parent-package configuration to execute prior to the incorrect configuration, it ran just ok. So apperently parent-package configurations are evaluated one by one, and if one of them fails, the "later" configurations aren't evaluated any more. Is this a feature? or a bug? Why don't i get a warning in the error list? Should i maybe configure my BIDS in a different fashion? Hope someone can help me out. Cheers, Tom Kronenburg
View Replies !
How To Get The Return/execution Value Of A Package From A Parent?
Hi there, I'm trying to get the return value of a package. I see there is a ForcedExecutionValue property which I set using an expression (variable). What I'm executing are 2 packages, Package1 contains an Execute Package Task that calls Package 2. Package 2 contains a Script Task that sets the value of variable Max. I want to get the value of Max in Package 1 then how can I do this? My first approach is toset the return value of Package 2 = Max and then I thought I could retrieve this value from Package 1 but I'm not able to do that yet. Any thoughts? Thanks for any help!
View Replies !
Assigning And Using Parent Package Variables
I have read a few different threads on this and I am still not having any luck. I have 4 dtsx files in my project. One represents the parent package. I have set up 3 Parent Package Variables and have assigned each to the value property of 3 variables within the parent package. Not sure if I did this correct because when I execute the parent package, it in turn executes the first child package, but the child fails because the variables needed did not set the correct value for a connection string in the child's data connection. I just need to know how to use these parent variables from Package A within the child (Package B) when setting up my expression for a connection- example: "@[User::varParentFilePath] " - This is the local variable name in the child. This is the variable I am assuming that I am supposed to be able to set the value property of from the parent variable? I feel that I am close, but may need a little more direction. Thanks
View Replies !
Parent Child Package With Expressions
Hi there, sorry if the message seems a bit garbled i cannot see the textbox properly with ie7/ff anyways. the situation. childpackage contains a loop-container. in there i use an ole-db-source with a variable based on an expression. ie. "select * from foo where bar=" + len(@[bar]) == 0 ? "initial" : @[bar] + " and etc=1" where bar is the variable that is set by the loop. this works great. however i need to call this package several times, only the expression is a tad different. so i need a scripttask that sets the expression correctly, then i call the childpackage and map the current-expression to the expression in childpackage. how do i do that? or am i doing it wrong? my script-task looks something like: dts.Variable("theVar").Expression = " ""select * from foo where bar="" + len(@[bar]) == 0 ? ""initial"" : @[bar] + "" and etc=1"" " in the childpackage i have a package-conf that maps thevar to thequery with target-object expression.
View Replies !
Parent-Child Package Configurations
Hi All, May be I am doing something wrong over here, but I have been trying in vain to test out a simple scenario where I can use my Parent Package configurations in my Child package. I have two packages ready and can someone please walk me through this process. Appreciate all help Thanks
View Replies !
Parent-&&>Child Package Editing Bug?
Before I submit this to connect, I'd like to see if someone can verify: Create a package that executes another package. When you start debuging (build) the parent package it opens up the child package and executes. Once you're done and stop debugging, the child package remains open. When you make edits to the child package and save and close, none of the edits are saved. You must close the package and re-open it before you make your edits...
View Replies !
Parent Package Configurations Failing
Has anyone seen a problem where if you have several parent configurations setup and one fails to find the variable in the parent, gives a warning, and then does NOT load the rest of the parent configruations? I realize order matters in how your configurations are processed, but I wouldn't expect the rest of my configurations to not work simply because it could not find one parent variable. The problem only seems to come up when I'm dealing with multiple parent configurations. If I'm loading a variable from a config file and then loading the same variable from a parent variable the process works fine. This way I can handle both cases, when I'm debugging it pulls from the config file, when it's running in production it pulls from the parent variable.
View Replies !
Parent Package Configuration Error
Hi, I have a child package running under main parent pakage. I am passing value of integer & string type variables from parent package to child package's variables ( as disscussed in following artical http://msdn.microsoft.com/en-us/library/ms345179(SQL.100).aspx) ,values assigned in child package variables are futher used to build connection string for flat file connection manager in child package. I have been using this whole SSIS package sucessfully for last three months,but suddenly the following error arises "OnError,,,,,,,The result of the expression Code Snippet "@[User::StrTextFileImpDirectory] +"SomeTextStringHere"+ @[User::StrANTTextFileName] +(DT_STR,30,1252) @[User::Staging_Date_Key]+ "SomeTextStringHere" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property." When I see the result of above mentioned expression through Log file than it appears that my string variable Code Snippet"@[User::StrTextFileImpDirectory] in child package has not been populated with any value from parent package variable, I.e it has been populated with empty string(While this is not desired value). Same problem arises with my integer type variable Code Snippet@[User::Staging_Date_Key] ,it has ben populated with value zero (i.e default value at design time for integer type),while this is not desired value. Can some one guide me how to figure out the cause for error.... Is it possible that such kind of error may arise due to any change in enviornment of server....
View Replies !
Parent Package Encryption .........please Help Me(its Orgent)...
Hi all, Please help me . please anybody reply ASAP. I have Package_1 in which i have enabled encrypt sensitive info with password. and password is given for this package. I am calling Package_1 from parent package using Execute Package task. when parent package is executed its asking for password for child package. can any one please tel me how can i pass password from parent package or specify password in Execute package task for calling package(PACKAGE_1) Any help will be highly appreaciated. Thanks in advance. Mahasweta
View Replies !
Parent Package Configurations...or Not...What Do You Guys Do?
Finding myself in a bit of a lull at work, I'm trying to create some template packages / solutions, encapsulating some of the best practices that get discussed on here. I'm thinking about passing configurations from Parent to Child packages, something we all must do on a regular basis. I've always done this a particular way, without thinking too much about it, but I'm not sure if this way is the best. I'd be interested in your opinions. Scenario: Parent.dtsx calls Child1.dtsx, Child2.dtsx etc, using Execute Package Task. Parent has variable Source, and variable Destination, populated from a configuration (my preference is SQL table, but whatever). These variables are ultimately used to populate the ConnectionString property of the source and destination Connection Managers of the Child packages. Here's the question: Is it better for the Child packages to look up to their parent, and inherit the values of these variables through Parent Package Configurations, or to look directly to the configuration, and pick them up from there? Or does it not matter? I tend to assign lots and lots of variables in Parent.dtsx, and pull them down into Child.dtsx with Parent Package Configs, but I'm thinking "is this good/bad/unimportant?"
View Replies !
Error Running Package From Parent
I am very new to SQL Server 2005, and I am currently trying to migrate a 2000 db to 2005. I have now migrated all the DTS to SSIS, removing any legacy & replacing with new functionality. I have been through a group of related packages ironing out any problems. They ALL now run fine when called individually. However, I am now having problems with the parent package that is used to string these all together. It will not even run the 1st package. For info, the first task of this package is a "Transfer SQL Server Objects Task". I am copying a few tables with data, but WITHOUT indexes, etc. Following this, there is an "Execute SQL Task" that creates an index on one of those copied tables. To get past another issue I had to set the "CopySchema" property of the "Transfer..." task to true, but did not select any schemas to transfer. Now, when running the PARENT package, it is failing at the 1st step of the 1st package as follows: Code BlockError: 0xC002F325 at Copy LT Data Tables, Transfer SQL Server Objects Task: Execution failed with the following error: "ERROR : errorCode=-1071636471 description=SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "The statement has been terminated.".An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Cannot insert duplicate key row in object 'dbo.Hidden Hearing Limited$Telemarketer' with unique index 'IX_Hidden Hearing Limited$Telemarketer1'.". helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}". I have been searching and searching, finding similar problems, and even the same error codes, but nothing quite matching this particular scenario. I have been trying to set the "DelayValidation" at various levels, but with no luck. I would greatly appreciate any help in trying to resolve this. Has anyone else had the same, or similar, issue? Many Thanks, Simon
View Replies !
Package Is Getting Parent Variables Too Late?...
Hi, I am sending variables from package to package by configuration parent packages in each package... I usually set logging to text files in a certain path that should be received from the parent variable... the strange thing is that if i let the variable in the child package with an incorrect path, my package just through an error even if i am passing the variable from the parent with a correct value... So... my question is... does my package starts execution without any synch over the parent packages? When are they set? How can i be sure to start my logging with the right parent variable settings? You can try for example to create a package... add a new variable like "A" and "B" set its value to a default string like "I DONT WANT THIS", add an Execute SQL Task that will record the values of "A" variable to a temporary table... Then use dtexecui.exe to execute the package and set variable "A" to "THIS IS WHAT I NEED" you will see that in the table will appear one record with the "I DONT WANT THIS" value... I can't understand this... Also... if i have 100 variables configured to get values from the parent... is the execution synch or asynch? Best Regards,
View Replies !
Problem Of Parent-Child Package With ASP.Net
I have an ASP.NET application that calls a SSIS package. The SSIS package internally calls some other child packages. I observed that sometimes some child pacakges are not even called by the parent. and the behaviour is very indeterminate. sometimes they work fine. sometimes they don't. There is no clue available for this behaviour when the pacakges are not executed. (One general observation is that the memory consumption is very high. but that is the case always.) I have enabled logging on all child packages. The log is not updated at all when the child packages failed to execute. i.e. the package execution does not start. Could somebody explain why this is happening? any suggestions/ similar experiences? Regards Saurabh
View Replies !
Error Running Package From Parent
I am very new to SQL Server 2005, and I am currently trying to migrate a 2000 db to 2005. I have now migrated all the DTS to SSIS, removing any legacy & replacing with new functionality. I have been through a group of related packages ironing out any problems. They ALL now run fine when called individually. However, I am now having problems with the parent package that is used to string these all together. It will not even run the 1st package. For info, the first task of this package is a "Transfer SQL Server Objects Task". I am copying a few tables with data, but WITHOUT indexes, etc. Following this, there is an "Execute SQL Task" that creates an index on one of those copied tables. To get past another issue I had to set the "CopySchema" property of the "Transfer..." task to true, but did not select any schemas to transfer. Now, when running the PARENT package, it is failing at the 1st step of the 1st package as follows: Error: 0xC002F325 at Copy LT Data Tables, Transfer SQL Server Objects Task: Execution failed with the following error: "ERROR : errorCode=-1071636471 description=SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "The statement has been terminated.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Cannot insert duplicate key row in object 'dbo.Hidden Hearing Limited$Telemarketer' with unique index 'IX_Hidden Hearing Limited$Telemarketer1'.". helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}". I have been searching and searching, finding similar problems, and even the same error codes, but nothing quite matching this particular scenario. I have been trying to set the "DelayValidation" at various levels, but with no luck. I would greatly appreciate any help in trying to resolve this. Has anyone else had the same, or similar, issue? Many Thanks, Simon
View Replies !
Using A Variable To Call A Stored Procedure Name
Hello; I am using an Access 2003 front-end, and an SQL backend to run my application. I have a pretty good handle on using stored procedures and assigning variables for criteria within the SPROC. I am however having a problem when I try to use a variable in place of a named procedure. For example I have a function that runs 2 procedures, therefore I "Call" the function with the code that runs my procedure, and simply change the name of the SPROC with each call. My problem is that I cannot figure out the syntax to use a variable for the named procedure. My code always errors on the line "objConn.MySProc MyCalendar, objRs" because MySproc is of course not a named procedure. So how do I refer to a procedures name using a variable? Here's my code; Function LieuBen() MyCalendar = CurrTSCalendar Call PopulateTmpFile("sp_DelTmpProctimesheetCalc") Call PopulateTmpFile("sp_PopTmpCalcLieuBen") End Function Function PopulateTmpFile(MySProc As Variant) Dim sp_PopulateTempOTTable As String Const DS = "SOS-1" Const db = "TIMS" Const DP = "SQLOLEDB" Dim objConn As New ADODB.Connection Dim objRs As New ADODB.Recordset Dim objComm As New ADODB.Command ConnectionString = "Provider=" & DP & _ ";Data Source=" & DS & _ ";Initial Catalog=" & db & _ ";Integrated Security=SSPI;" ' Connect to the data source. objConn.Open ConnectionString ' Set a stored procedure objComm.CommandText = MySProc objComm.CommandType = adCmdStoredProc Set objComm.ActiveConnection = objConn objConn.MySProc MyCalendar, objRs objConn.Close Set objRs = Nothing Set objConn = Nothing Set objComm = Nothing End Function
View Replies !
How To Call A Variable In ADO.NET Data Reader
Hello everybody. I recently started a short topic about staging an incremental load, and the answer was to use a variable. I've been fiddling around with it have run into a question about calling the variables. I've gotten my "Execute SQL Task" to generate a single row result set and store it as a global / package Variable. Now I'm not sure how to call / use this variable when I'm loading data from an ADO.NET Data Reader Source. Do I need to map the variable to a parameter first? Can I call the variable in the SELECT statement like this? Code Snippet SELECT * FROM source WHERE date > @variable I was reading an MSDN page on Using Variables in Packages where they recommended the following syntax: Code SnippetSELECT * from Production.Product WHERE ProductID = ? But I'm not sure how to get the variable or parameter mapped to my Data Reader Source. Any help would be greatly appreciated. Thanks in advance, - Trevor
View Replies !
|
TRACKER
