February 16, 2017 at 1:02 pm
Hi,
I have created a SSIS project. In that I have created 3 dtsx packages (Mastertable.dtsx , FullLoad.dtsx,Fullload1.dtsx) Now when I merge them into single dtsx package it is showing failed to lock variable error. In first package(MasterTable.dtsx) I created 3 variables 1. CreatedDateTime 2.ModifiedDateTime and 3.getdate() as CurrentDateTime.
In second(FullLoad.dtsxt) package I have created a variable getdate() as CurrentDatetime.
In Third(FullLoad1.dtsxt) package I have created a variable getdate() as CurrentDatetime.
When I merged the first and second package into the third package into the Third package then for the second package it is showing Failed lo lock Variable user::CurrentDateTime. How should I solve this.
Now even in the third package i have created all the three variables which are there in the first package. All these three packages i put together in a sequence container.
May be my question is not so in details. My did my best to put it in this way. Looking forward for your help
February 16, 2017 at 1:10 pm
SQL-Learner - Thursday, February 16, 2017 1:02 PMHi,I have created a SSIS project. In that I have created 3 dtsx packages (Mastertable.dtsx , FullLoad.dtsx,Fullload1.dtsx) Now when I merge them into single dtsx package it is showing failed to lock variable error. In first package(MasterTable.dtsx) I created 3 variables 1. CreatedDateTime 2.ModifiedDateTime and 3.getdate() as CurrentDateTime.
In second(FullLoad.dtsxt) package I have created a variable getdate() as CurrentDatetime.
In Third(FullLoad1.dtsxt) package I have created a variable getdate() as CurrentDatetime.
When I merged the first and second package into the third package into the Third package then for the second package it is showing Failed lo lock Variable user::CurrentDateTime. How should I solve this.
Now even in the third package i have created all the three variables which are there in the first package. All these three packages i put together in a sequence container.
May be my question is not so in details. My did my best to put it in this way. Looking forward for your help
I haven't heard any words that tell me what your end goal is, and you've said nothing about what these packages are supposed to do, nor anything about why you "merged" them. For that matter, I'm not even sure what you mean when you say "merged". SSIS is rather finicky about what it will let you do or not do, and my guess is you tried to do something that the right conditions for, didn't exist. But to get to the bottom of it, we'll need to know what you're trying to do, what you mean when you say "merged", and why. That last question is usually the most important one.
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
February 16, 2017 at 1:11 pm
SQL-Learner - Thursday, February 16, 2017 1:02 PMHi,I have created a SSIS project. In that I have created 3 dtsx packages (Mastertable.dtsx , FullLoad.dtsx,Fullload1.dtsx) Now when I merge them into single dtsx package it is showing failed to lock variable error. In first package(MasterTable.dtsx) I created 3 variables 1. CreatedDateTime 2.ModifiedDateTime and 3.getdate() as CurrentDateTime.
In second(FullLoad.dtsxt) package I have created a variable getdate() as CurrentDatetime.
In Third(FullLoad1.dtsxt) package I have created a variable getdate() as CurrentDatetime.
When I merged the first and second package into the third package into the Third package then for the second package it is showing Failed lo lock Variable user::CurrentDateTime. How should I solve this.
Now even in the third package i have created all the three variables which are there in the first package. All these three packages i put together in a sequence container.
May be my question is not so in details. My did my best to put it in this way. Looking forward for your help
Please explain what you mean by 'merge them into a single dtsx package'. I know of no way of doing this except manually.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 16, 2017 at 1:20 pm
For all the three packages my source connection manager and my destination connection manager are same. Initially for time being i have created different dtsx packages. In each dtsx package i have created a sequence container and run the differ packages in parallel way. Same i have done with Third Package. But now I want to put all those packages in a single dtsx package. So i copied the first package and second package sequence container and pasted it in a third package. My goal is to put all those in a single dtsx package
February 16, 2017 at 1:24 pm
SQL-Learner - Thursday, February 16, 2017 1:20 PMFor all the three packages my source connection manager and my destination connection manager are same. Initially for time being i have created different dtsx packages. In each dtsx package i have created a sequence container and run the differ packages in parallel way. Same i have done with Third Package. But now I want to put all those packages in a single dtsx package. So i copied the first package and second package sequence container and pasted it in a third package. My goal is to put all those in a single dtsx package
Why do you need 3 separate packages at all? You can just connect 3 different Data Flows together in sequence in the Control Flow...
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
February 16, 2017 at 1:37 pm
My aim is to get all the data from one server to other server on some weekly basis. I have one database in production i'm currently working on. I has some 70 tables which has master tables, Dim tables and fact tables.
For master tables i have created a incremental load package and put together in a single package in a squence container.
For Dim tables i have created a Full load package and put all the tables in a second package in a sequence container
For Fact tables we need to apply logic and get selected records instead of all records. For those kind of tables i have created third package and pull the tables in a sequence container.
All these tables are from one database. Now i run the packages and got the data. now i thought of using all these tables in a single package. For that reason i have copied first and second package and pasted in the third package.
Then it was showing the error Failed to lock varaibe user:currentdatetime for the second package.
Sorry for bothering you
February 16, 2017 at 1:43 pm
SQL-Learner - Thursday, February 16, 2017 1:37 PMMy aim is to get all the data from one server to other server on some weekly basis. I have one database in production i'm currently working on. I has some 70 tables which has master tables, Dim tables and fact tables.
For master tables i have created a incremental load package and put together in a single package in a squence container.
For Dim tables i have created a Full load package and put all the tables in a second package in a sequence container
For Fact tables we need to apply logic and get selected records instead of all records. For those kind of tables i have created third package and pull the tables in a sequence container.All these tables are from one database. Now i run the packages and got the data. now i thought of using all these tables in a single package. For that reason i have copied first and second package and pasted in the third package.
Then it was showing the error Failed to lock varaibe user:currentdatetime for the second package.
Okay, but as we can't see your packages, we can't determine exactly how they are now constructed, but my guess is that it can't lock a variable because that variable is in some form of exclusive use by one portion of the overall package, and now that an additional part of the package needs access to that same variable, you've got the functional equivalent of a deadlock. Find out where that variable is used in the portion of the package you most recently added, and see how the package runs to see what portions of the package are running simultaneously, and there's a pretty good bet something in there is locking a variable. Script Tasks are good for that.
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
February 16, 2017 at 2:16 pm
Note that having mega packages which do everything is generally regarded as a bad design practice. When things go wrong, errors become difficult to track down and solve and maintenance of the package becomes a nightmare.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 16, 2017 at 3:04 pm
Thanks for all.
I have tried in different ways. But just i have dropped the variable from the parameter mapping and selected again. Its working now.
I appreciate your effort and time.
Thanks.
February 16, 2017 at 3:04 pm
Phil Parkin - Thursday, February 16, 2017 2:16 PMNote that having mega packages which do everything is generally regarded as a bad design practice. When things go wrong, errors become difficult to track down and solve and maintenance of the package becomes a nightmare.
+++ to this.
Troubleshooting, maintenance and enhancements are horrible with big packages.
More than once someone would execute the package instead of just the new data flow they are adding, all dev data is gone until you let the package complete.
We moved from mega packages to individual packages called by a master package. A package is now the unit of work to completely transform and load a specific entity.
Wes
(A solid design is always preferable to a creative workaround)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply