I'm facing a problem with my SSIS package regarding the execution order of tasks. I am using my package for the purpose of loading data from XML to staging tables in the database, and have a loop to process all XML files.
As a precondition to the loading action itself, I am running a stored procedure against the database (using the ExecuteSQL task) to check whether all staging tables are empty. The output parameter of that stored procedure is mapped to a variable I have defined in the SSIS package, so I can use it as a basis for the decision whether to run the loading action or not.
In order to test my package I added a script task, right after the execution of the stored procedure with a precedence constraint, that pops up a messagebox with the value of the stored procedure return value stored in the package variable.
The problem is that for the first iteration of the loop, the variable value presented by the messagebox is incorrect (equals to the initial value I assigned to the variable), and it looks like the script task starts before the stored procedure execution task finishes and its output value is stored in the package variable.
i got a foreach loop that has about 20 data flow tasks(same database connections but different extractions) but i notice that when i execute the project it only runs 4 data flow tasks at a time.
i know that there is an option for each data flow to set the "Engine Threads", but is there a way to set the thereads in a foreach loop or for the whole project so it will execute all data flow tasks in one go for each loop.
p.s. Does anyone have any needles I can borrow? I think sticking them in my eyes would be nicer than working with SSIS.
===================================
An error occurred while objects were being copied. SSIS Designer could not serialize the SSIS runtime objects. (Microsoft Visual Studio)
===================================
Could not copy object 'Preparation SQL Task' to the clipboard. (Microsoft.DataTransformationServices.Design)
------------------------------ For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.762&EvtSrc=Microsoft.DataTransformationServices.Design.SR&EvtID=SerializeComponentsFailed&LinkId=20476
------------------------------ Program Location:
at Microsoft.DataTransformationServices.Design.DtsClipboardCommandHelper.SerializeRuntimeObjects(ICollection logicalObjects) at Microsoft.DataTransformationServices.Design.ControlFlowClipboardCommandHelper.InternalMenuCopy(MenuCommand sender, CommandHandlingArgs args)
===================================
Invalid access to memory location. (Exception from HRESULT: 0x800703E6) (Microsoft.SqlServer.ManagedDTS)
------------------------------ Program Location:
at Microsoft.SqlServer.Dts.Runtime.PersistImpl.SaveToXML(XmlDocument& doc, XmlNode node, IDTSEvents events) at Microsoft.SqlServer.Dts.Runtime.DtsContainer.SaveToXML(XmlDocument& doc, XmlNode node, IDTSEvents events) at Microsoft.DataTransformationServices.Design.DtsClipboardCommandHelper.SerializeRuntimeObjects(ICollection logicalObjects)
If I have a List with three tables in it, and let's say that in the first two tables, I'm adding something to a global variable and in the 3rd table I'm calling that global variable...can I be 100% sure that the call to that global variable in the 3rd table will always be the result from the first two tables?
I mean will it always be true that the first two tables are analyzed and "executed" before that third table?
Thanks for any help. If there are any links on this topic on the microsoft web site, please specify them. I have not been able to find anything
I have a package that has 12 data pump tasks all executing in parallel.
It is transferring raw data from an AS400 DW to a MSSQLSvr Staging area.
Each pump task on completion assigns values to a set of global variables, then having done this passes these as parameters to a sproc which inserts them into a table.
This seems to work for 4 or 5 of the pump tasks but, the rest of the rows in the table are all the same because the remaining pump tasks are all executing before the sprocs.
Is there a way to make sure that the entire set of job steps completes, before starting another job set of steps while still keeping them running in parallel.
I had wondered if there was a way to use the PumpComplete phase of each pump step to fire off the sproc, but can't see how you execute the step.
Is there any way to enforce task execution order within a control flow? I need to have tasks execute after each other, because for example Task B depends on the execution of Task A.
Doesn't the success constraint enforce execution order? When I run my package the tasks seem to execute in a random order which is not what I want.
What I am trying to do is:
Loop through all Xml files in directory
For each Xml file: For categories, products & fields: Truncate staging table Insert data from Xml file into staging table
After all Xml files have been processed then import data from staging to main tables
Here is my control flow: http://www.myfootballonline.com/flow.png
I have nested lists and I want to set a global value in my custom code AFTER a specific table footer row. Does anybody know in what order the table elements are rendered? I have tried adding my piece of code into a group value, the hidden property, the color property, and sending it as a parameter to a subreport, but it still sets that variable first before rendering the table footer row that I want to display before I set that variable. I have been pulling my hair out trying to do this one! Help!
Is the order of execution guaranteed to go from top to bottom in a transaction that has multiple statements like below?
BEGIN TRAN T1; UPDATE table1 ...; UPDATE table2 ...; SELECT * from table1; UPDATE table3 ...; COMMIT TRAN T1;
How about here?
BEGIN TRAN T1; UPDATE table1 ...; BEGIN TRAN M2 ; UPDATE table2 ...; SELECT * from table1; COMMIT TRAN M2; UPDATE table3 ...; COMMIT TRAN T1;
how can i guarantee that statements will be executed from top to bottom in a transaction batch like above? I am not interested in the errors in statements. I just want whole thing to either execute fully from top to bottom or none executes
How can I get and or set the order in which the cascading deletes of a table are executed?? I have table A with cascading deletes to Table B and Table C. Records in table B can not be deleted if they are referenced from table C. So if I delete C, then B and then A; that would work. But B then C and then A might be prohibited due to the constraint between B and C. Therefor the order of execution of the cascading delete is important.
I have a Conditional Split, where there are two outputs.
First output is a dataset which is to be inserted into the database.
Second output is a dataset for which the data already exists in the DB. I just need to update those data.
I have a doubt here. I want the insertion to be done first and then updation. Is there any property to be set for insertion or updation, something that maintains the order of execution or priority of execution.
Please do ask me if you need any further clarification.
I am designing a ETL system to extract data from multiple systems. I have designed a batch control application and database to manage the process. I was thinking of extending this to include the execution of the SSIS packages. I would basically store all of the package details in the database, and when I am executing a particular systems load, I would get the list of packages required and loop through them in a ForEach loop. The question I have is can I guarantee the order of execution? I will put an order or execution in the DB and when I select the data, I can order by these columns. I am concerned that in putting the data into a record set in SSIS its order could be changed resulting in the packages executing incorrectly.
Has anyone done anything similar to this and and run into problems, or is it not an issue? Many Thanks Michael
Curious if I have the code below as an example and I execute this code does sql execute from top to bottom? And does the Update run and complete before the delete occurs? Or does SQL execute the update and delete in parallel?
I want to transfer a few access mdb kept in a folder to sql server. I am using foreach file enumerator. I want to perform the following actions :
1. At time of loading the file, check if the file name starts with "ED". If not, move the file to error folder. 2. Count no. of records that were there in the access db and that were transferred to the sql table. 3. I also want to run the package from command line. Even though i figured out that, i want to display only limited messages on the console. For eg: File processing begins, File currently being processed, TOtal rows transferred, File processing completed etc.
I need some suggesstion regarding which SSIS tasks to use regarding my job in hand. Basically what I need is to extract data from joining multiple tables and then load to a particular output database.
For example in a Select Statement we have many tables and we have Where Clause with many conditions with AND operations. Do the SQL SERVER would apply the Where clause after all fetch or can dynamically decide about to include the related Tables from Select Statement Orderly with respect to where clause predicates? (SQL SERVER would not fetch data of those tables for its Select, where the AND condition in Where clause fails or by logic would be fruitless/not-related.)
What are peoples opinions on using SSIS as a "central repository" and replacement for all scheduled tasks. Example, we have a bunch of servers which we have installed services which we have written. Currently we have scheduled tasks on each machine to stop and start the services. One of my collegues is using SSIS to run a system which runs tasks on multiple machines by remotely running programs on other machines via scheduled tasks and then collects the data and puts it into a database.
He's now pitching the idea that we remove the scheduled tasks on each machine and start and stop our services via SSIS so that it's centralized. In addition, we can also check for holidays in our database before starting services. Since it doesn't seem like SSIS was meant for this type of use, I'm weary of using the tool to do something it wasn't intended for.
Any opinions? I'm also worried that the learnnig curve for everyone is going to be too high.
I have this procedure to remove certain characters from file names.
The SQL Task has this: exec dbo.spCleanseFileName @strFileName = ?, @strFileNameCleansed = ?
The stored procedure: CREATE PROCEDURE [dbo].[spCleanseFileName](@strFileName varchar(40),@strFileNameCleansed varchar(40) output)
I have it in an SSIS package and my problem is that, after that SQL Task completes, the value for the ),@strFileNameCleansed variable is blank. I HAVE confirmed that the procedure DOES set the correct value inside the SP.
I have developed a big SSIS package to extract data from flat-files ( + 200 Dataflows ).
The situation is the following, inside de SSIS package, there are a lot of validations before extracting & loading the flat-files, i'm running this validations in paralell, so that when a file arrives, it enters the "validation process" and start extracting the file.
When i run the SSIS package from BIDS it works the way i have concepted it... but when i run the ssis in the server, the tables that are loaded through the process are only "available" when the SSIS PACKAGE ends, it is imperative that trough the process, when a table receives new data, it becomes ready, and don't just be available when the SSIS package finishes...
I have attached the an lousing .jpeg.
It is importart for the tables to be available, so the stored procedures(OUTSIDE SSIS PACKAGE) that are dependent of some tables, start working before the SSIS package Ends.
Hi I have a strange problem with SSIS packages. (brief description - packages select some data from DBs , write them to CSV file and then CSV file is copied and renamed to a folder made up of the date) I have 5 packages scheduled to run, these jobs run perfectly when test scheduled during the day, (so its not a user permissions problem). However it seems the 1st package to run at night will fail. The reason I say 1st is the following, I had Package A scheduled at 11:20PM and package B at 11:30PM , Package B always succeeded but package A always failed. I would test A during the day and it would run fine (the jobs would run successfully aswell as just executing the package manually) .
The I changed the time with B to 11:50PM and it succeeds and A fails ! without changing the packages themselves.
This counts out a possibilty of a DB backup causing the problem (pack always succeeded at 1130, now fails at the same time)
I was thinking maybe as the folder wasn't created when the 1st pack ran this was causing the fail, but when i test run the job this morning it succeeds .. and todays folder doesn't exist either !
Hi I have migrated a DTS that had some activeX transformation tasks within data pump flow tasks.
Those parts were migrated as "DTS 2000 tasks" .. so activeX transformation tasks aren't possible in SSIS ? I know ActiveX script tasks are but for transformations ?
1. IF i leave these Encapsulated DTS 2000 tasks in the migrated SSIS package, will it run independently of the original DTS or does it need the old DTS running to "call" that part from ? (I hope im making sense here) is it possible to load this functionality internally into the new SSIS ?
2. How could I (if i can't do ActiveX transformation tasks) achieve this is SSIS ? can I achive this using the script tasks in SSIS ?
HI All, I have Uzip Files to be downloaded From ftp.companyname.com, the zip files get updated everyday thus i have to download the newly added files, the Zip file has got 13 text Files within it, the issue is: 1. how do i download it for the new zip file only, the zip files are shown below, i am trying using FTP Task, but need more info or other alternative. 2. (Optional) How do i UnZip it and and take the text files and then load them to sql server 2005, each text file has to be loaded to sql server tables. 3. How do i automate it, i mean every time i run the package (on Job based) it has to look the new file only, see the zip file below to understand what i am saying about.
As you can see the Zip file names are the blues color, they are added at different time, when you browse the ftp.companyname.com you will get the blue color files, thus what i need is to download only the current Zip file (i mean i have to downLoad only the newly added Zip file (recent one)). Thus the SSIS Task has to go to this FTP server and look the newly added zip file. (Optional) if possible, After that i have Unzip it b/se i have 13 text files there, and then Load them to Sql Server 2005.
Please get help on this, the deadline is near by time, if possible try as soon as possible, I would like to say Thank you for every help you do and try. Thanks,
I'm trying to do some custom SSIS logging using event handlers, similar to the ideas provided by Jamie Thomson in the past. My problem is that when I use System:ourceID as one of the items to be logged, I can't match up the SourceID to any of the GUIDs that are displayed in the property window for the various tasks in my package.
Where is this sourceID coming from and how can I track it down?
I can't find information on how you are suppose to handle the TransactionOption setting inside of a custom task.
I have a custom task that I have developed, and it basically calls a COM+ object that writes data to a database. When I have the task inside of a container that has the transactionoption set to required, and my custom task is set to supported, if one of the X items fail to execute in my custom task I am telling my task to fail the parent, which I thought would rollback everything. But it does not.
Is there someplace that I need to write rollback code in custom SSIS tasks? If there is I can't find any mention of it anywhere. Any examples out there on how to build custom SSIS tasks that support the TransactionOption parameter?
I have developed a big SSIS package to extract data from flat-files ( + 200 Dataflows ).
The situation is the following, inside de SSIS package, there are a lot of validations before extracting & loading the flat-files, i'm running this validations in paralell, so that when a file arrives, it enters the "validation process" and start extracting the file.
When i run the SSIS package from BIDS it works the way i have concepted it... but when i run the ssis in the server, the tables that are loaded through the process are only "available" when the SSIS PACKAGE ends, it is imperative that trough the process, when a table receives new data, it becomes ready, and don't just be available when the SSIS package finishes...
I have attached the an lousing .jpeg.
It is importart for the tables to be available, so the stored procedures(OUTSIDE SSIS PACKAGE) that are dependent of some tables, start working before the SSIS package Ends.
I have a SQL 2000 script which I use to automatically schedule various backup tasks. This script adds and schedules a full backup once a week, differential backups nightly, and log backups hourly, in addition to a couple other maintenance tasks such as rebuilding indexes.
The idea is that a less technically savvy person can set a few variables at the top of the script (such as DB name and backup file folders) and click 'execute' to run the script and schedule the backups etc all in one go, for different clients.
Since some of the stored procs I use in this script are deprecated in SQL 2005, I am trying to replicate this functionality in SSIS but am having trouble figuring out how I can get all of this functionality encapsulated in the same 'click and go' manner where the user can simply execute the package and all the jobs will be scheduled without any user interaction.
Is this even possible? Where should I be looking for examples of how to do this?
