Error Handling Prevents Control Flow From Stopping. Is This Correct Behavior?
Dec 20, 2007
I have two tasks on a control flow. First task is Execute SQL task which drop an index. Second one is a Data Flow task. I also have an error handler for packcage_onerror. Because there is no index in the database, the first task rasies an error and package on error catches the error. The precedence constraint for the Data Flow task in "success". I don't expect the data flow task to execute because of the error. But it does. Is this the right behavior because I have already handle the error? I don't want the the job to continue if there is any error. I believe I should raise error in the error handler. Pleae help me how to do this. Thanks
View 12 Replies
ADVERTISEMENT
Jun 23, 2006
Hopefully this is an easy question:
Inside of a for each loop (looping through an ADO record set of objects to import) I have a data flow task (along with many other processes).... if the dataflow task suceeds I log success in a table. If it errors I want it to fail the dataflow task (which will fire off my Event Handler for that data flow and log the failure, email etc) BUT I want it to continue the loop - I can't seem to figure out how to get the data flow object not to fail the whole loop. If any other objects inside the foreach, other than the data flow, fail I would like the whole loop to fail. Also if possible (but not a requirement) I would like it to have a threshold where if the data flow fails X variable times it will fail the package.
I am having difficulty how to not fail the loop when the import data fails..... just looking for a simple "on error next" type logic for that specific object in the foreach but not the rest. Thanks in advance for the help/advice.
View 4 Replies
View Related
Sep 11, 2007
I am trying to execute a SP like below in OLEDB source in data flow... and this statement include the insert stament ( row by row transaction).. I would like to creat an error hadling logic so that if the trasaction fail to insert the row then ignore that particular row then, move to the next row without stopping the whole process.. how can i do this?
exec usp_Inert_Registration_Episodes_Assessments
@Unique_ID=?,
@Gender_Cd=?,
@Birth_Date=?,
@Race_Ind=?,
@Ethnicity_Cd=?,
@Registration_Dt=? ,
--
--@Object_Key
View 16 Replies
View Related
Mar 26, 2008
Hi,
I'm trying to copy and paste an 'Execute SQL Tasks' within one of my packages.
I've managed to solve the copy part of the problem by registering the following dlls:
regsvr32.exe msxml3.dll
regsvr32.exe msxml6.dll
However, I can't paste the copied task onto my control flow. When I paste I get the following error:
The designer could not paste one or more executables.
Additional information:
At least one executable could not be pasted correctly.
the executable with the name 'Record Row Count' could not be pasted successfully.
Information about the state of the executable with the name
'Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask, Microsoft.SqlServer.SQLTask,
Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' could not be loaded from the clipboard.
Exception from HRESULT: 0xC0010028 (Microsoft.DataTransformationServices.Design)
Any help getting this fixed would be appreciated.
Tom
View 5 Replies
View Related
Mar 1, 2007
Hi all,
I have a Send Mail Task in my control flow to notify users that the processing is done. I want to avoid the package to fall in error if the Send Mail task failed.
What is the best practice to do that ?
Should I raise the MaximumErrorCount of theSend Mail Task ? Should I play with ErrorHandler ?
View 1 Replies
View Related
May 22, 2008
My SSIS control flow includes a standard "textbook" WMI Event Watcher Task that monitors a folder for incoming files. Generally it works fine, but now I regularly see the error message:
"Watching for the Wql Query caused the following system exception: "Quota Violation." Check the query for errors or WMI connection for access rights/permissions"
I do not see any indication of trouble in the event logs. The SSIS log simply states that it failed.
Is there any magic about WMI Event Watcher?
When I restart, it runs fine for hours.
SQL05 is 9.0.3054 on W2003 with all microsoft updates applied. It is basically a bare machine with SQL Server, SSIS running and a service that kicks in occasionally.
Thanks for reading!
View 3 Replies
View Related
Dec 26, 2007
Why isn't there some documentation on how to do this. This should be really simple and it has taken me 2 weeks and I still haven't gotten an answer. Please Help Does anyone know the answner or some place where there is some documentation!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
I get the following error when I try to substitute the strings in the databasedetails collection with variables:
Error: Object reference not set to an instance of an object. StackTrace: at Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.CheckLocalandDestinationStatus(Database srcDatabase, DatabaseInfo dbDetail) at Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.TransferDatabasesUsingSpAttachDetach()
I created the following variables:
strDestinationDB = AirCL2Exp_new3
strDestinationDBPath = C:Program FilesMicrosoft SQL ServerMSSQL.2MSSQLDATAAirCL2Exp_new3_Data.mdf
strDestinationLGPath = C:Program FilesMicrosoft SQL ServerMSSQL.2MSSQLDATAAirCL2Exp_new3_Data.ldf
strSourceDB = AirCL2Exp
strSourceDBPath = C:Program FilesMicrosoft SQL ServerMSSQL.2MSSQLDataDataNewAirCL2Exp_Data.mdf
strSourceLGPath = C:Program FilesMicrosoft SQL ServerMSSQL.2MSSQLDataDataNewAirCL2Exp_Log.ldf
I then assigned those variable to DatabaseDetails Collection:
DatabaseName = @strSourceDB
DestinationDatabaseName = @strDestinationDB
Inaddtion I also assigned the following to the two DatabaseFiles Collection:
for 0:
DatabaseFileSize = 0
DestinationFilePath = @strDestinationDBPath
FileType = DatabaseFile
SourceFilePath = @strSourceDBPath
SourceSharePath = @strSourceDBPath
for 1:
DatabaseFileSize = 0
DestinationFilePath = @strDestinationLGPath
FileType = LogFile
SourceFilePath = @strSourceLGPath
SourceSharePath = @strSourceLGPath
View 13 Replies
View Related
Feb 14, 2006
Hi, All,
I need to pass a parameter from control flow to data flow. The data flow will use this parameter to get data from a Oracle source.
I have an Execute SQL task in control flow to assign value to the Parameter, next step is a data flow which will need take a parameter in the SQL statement to query the Oracle source,
The SQL Looks like this:
select * from ccst_acctsys_account
where to_char(LAST_MODIFIED_DATE, 'YYYYMMDD') >?
THe problem is the OLE DB source Edit doesn€™t have anything for mapping parameter.
Thanks in Advance
View 2 Replies
View Related
Mar 9, 2007
I have an Execute SQL Task that returns a Full Rowset from a SQL Server table and assigns it to a variable objRecs. I connect that to a foreach container with an ADO enumerator using objRecs variable and Rows in first table mode. I defined variables and mapped them to the columns.
I tested this by placing a Script task inside the foreach container and displaying the variables in a messagebox.
Now, for each row, I want to write a record to an MS Access table and then update a column back in the original SQL Server table where I retreived data in the Execute SQL task (i have the primary key). If I drop a Data Flow Task inside my foreach container, how do I pass the variables as input to an OLE DB Destination on the Data Flow?
Also, how would I update the original source table where source.id = objRects.id?
Thank you for your assistance. I have spent the day trying to figure this out (and thought it would be simple), but I am just not getting SSIS. Sorry if this has been covered.
Thanks,
Steve
View 17 Replies
View Related
Jan 17, 2008
Dear All!
My package has a Data Flow Task. In Data Flow Task, I use a Script Component and a OLE BD Destination to transform data from txt file to database.
Within Data Flow Task, I want to call File System Task to move file to a folder or any Task of "Control Flow" Tab. So, Does SSIS support this task? Please show me if any
Thanks
View 3 Replies
View Related
May 17, 2007
Hi everyone,
Primary platform is 64 bit cluster.
How to move information allocated in SSIS variables from Data Flow to Control Flow layers??
We've got a SSIS package which load a value into a variable inside a Data Flow. Going back to Control Flow how could we retrive that value again????
Thanks in advance and regards,
View 4 Replies
View Related
Jan 12, 2006
I'm currently setting variables at the package level with an ExecuteSQL task. This works fine. However, I'm now starting to think about restartability midway through a package. It would be nice to have the variable(s) needed in a data flow set within the data flow so that I only have to restart that task.
Is there a way to do that using an SQL statement as the source of the value in a data flow?
OR, when using checkpoints will it save variable settings so that they are available when the package is restarted? This would make my issue a moot point.
View 2 Replies
View Related
May 18, 2008
SSIS Newbie Question:
I have a simple Control Flow setup that checks to see if a particular table exists. If the table does not exists, the table is created in an alternate path, if it does exist, the table is truncated before moving to a file import Data Flow that uses an OLE DB Destination to output the imported data.
My problem is, that I get OLE DB package errors if the table the OLE DB Destination Container references does not exist when I load the package.
How can I over come this issue? I need to be able to dynamically create the table in an earlier step, then use that table to import data into in a later step in the workflow.
Is there a switch I can use to turn off checking in the OLE DB Destination Container so that it will allow me to hook up the table creation step?
Seems like this would be a common task...
Steps:
1. Execute SQL Task to see if the required table exists
2. Use expresions to test a variable to check the results of step 1
3. If table exists, truncate the table and reload it from file in Data Flow using OLE DB Destination
4. If table does not exist, 1st create it, then follow the normal Data Flow
Can someone help me with this?
Signed: Clueless with a deadline approaching...
View 3 Replies
View Related
Jul 22, 2007
Hi all! I recently started working with SSIS and one of the things that is puzzling me the most is what's the best way to go:
A small control flow, with large data flow tasks
A control flow with more, but smaller, data flow tasksAny help will be greatly appreciated.
Thanks,
Ricardo
View 7 Replies
View Related
Mar 12, 2015
We have some pre-defined stored procedures(around 4-5) which deletes/ truncates the tables.
- Is there a way I can know the correct order of running the SP.May be I should check that child tables are deleted first and then the parent tables.
- How can I test the same. Since I am not aware of the flow thereby should I try running with NO eligible records for deletion. Will this ensure that I have sufficient privileges for the SP or do I need to delete a record (may b an older record).
View 3 Replies
View Related
Apr 20, 2008
I am new to SSIS can anyone tell me the diff between control flow and dataflow. if all the transformation are done using dataflow than why do we use control flow. Sorry if I am asking you very basic question.
View 5 Replies
View Related
May 22, 2008
Hi All,
I have a requirement here to import data from XML file to SQL Database. The XML schema contains of various elements and one of the element is recursive ie. Parameter node contains parameter node within it and it can have n number of iterations. I have given the sample schema below:
<xs:element minOccurs="0" name="Parameter">
<xs:complexType>
<xs: sequence>
<xs:element minOccurs="0" name="ID" type="xs: string" />
<xs:element minOccurs="0" name="Description" type="xs: string" />
<xs:element minOccurs="0" name="Type" type="xs: string" />
<xs:element minOccurs="0" name="Parameter">
<xs:complexType>
<xs: sequence>
<xs:element minOccurs="0" name="ID" type="xs: string" />
<xs:element minOccurs="0" name="Description" type="xs: string" />
<xs:element minOccurs="0" name="Type" type="xs: string" />
<xs:element minOccurs="0" name="Parameter">
<xs:complexType>
<xs: sequence>
<xs:element minOccurs="0" name="ID" type="xs: string" />
<xs:element minOccurs="0" name="Description" type="xs: string" />
<xs:element minOccurs="0" name="Type" type="xs: string" />
</xs: sequence>
</xs:complexType>
</xs:element>
<xs:element minOccurs="0" name="Parameter">
...............
</xs: sequence>
</xs:complexType>
</xs:element>
</xs: sequence>
</xs:complexType>
</xs:element>
But all the nodes contain the data which has to be imported to a single table dbo.Parameters. I cannot use Union ALL since i dont know how many iterations I will have in the file. Is there any way to do this operation in Data Flow Task using XML Source? Can anyone help me on this?
Thanks,
Dhileep
View 1 Replies
View Related
Oct 23, 2003
I would like to be able to have a "while" statement execute until all af the rows in a temporary table have be processed.
I know I can do this with counters.
However, I was wondering if anyone knows of a SQL equivalent of
"WHILE NOT EOF" as in "do this until you hit the end of the table".
Thanks in advance for any input.
View 5 Replies
View Related
Aug 22, 2007
I am having a hard time with what appears to be something simple. I want to import an excel spreadsheet into a table on a daily basis from a command line. I created a package from the Import Wizzard in the SQL Management Studio and saved it. Since I want a clean table each day, my process needs to be create a temp table, import from the Excel file into the temp table. If that is successful, delete the original table and rename the temp table the original name. The point of this process is to provide for a fail-safe if there is some unforseen problem downloading the data on a particular day.
When I run the package, the first thing it does is delete the original table. I know this because the process shows the time that it finished is before anything else has started or finished. The time shown for the completion of the data flow task is about 2 minutes after that time.
This is maddening!!! The one thing I do not want to happen I can not seem to prevent. I have my control flow set on success. Why does it do this?
View 3 Replies
View Related
May 24, 2006
Is it possible to setup a Control Flow at the solution level rather than at the package level? I'd like to setup a Control Flow that truncates multiple tables in a staging database then runs multiple packages that reloads those tables. Each package has a Control Flow tab that seems to be specific to the package. Is it possible to set something up that governs the execution of multiple packages?
Thanks.
View 4 Replies
View Related
Apr 22, 2004
I'm writing my first serious stored procedure.
Essentially I have an incoming file, each line in the file is a record.
The records share the same initial key fields for the first 10 columns, then the field structure varies depending on a rectype and sequence number.
My initial plan was load the keys into fields, and load the remaining data into a long varchar field.
Then the stored procedure would evaluate the Rectype and Seqno of each record and chop up the Varchar accordingly.
So I set up a cursor to read the temporary table, do a fetch into variables, and go to evaluate the variables.
I want to be able to use a CASE statement to evaluate the fields and then perform various logic, but it's giving me fits because it seems like CASE only really works in Select statements, and won't really allow you to do any sort of GOTO logic.
I chopped the following SQL up and put in a rough cut of what I thought I was doing.
DROP TABLE #FOO
GO
CREATE TABLE #FOO (
[planno] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ssn] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[location] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[eligdate] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[rectype] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[seqno] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[empno] [char] (13) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[payrollcode] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[companycode] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[department] [char] (13) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[filler] [char] (34) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[data] [varchar] (366) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
BULK INSERT #FOO FROM '\l32b0021foo.dat'
WITH (FORMATFILE = '\l32b0021foo.fmt')
declare @sessionid varchar(12)
declare @dedcode varchar(5)
declare @eeamt money
declare @eepct decimal
declare @effdt datetime
set @effdt = getdate()
set @sessionid = 'PENDING_BI'
DECLARE Transaction_Cursor CURSOR FOR
SELECT ssn,rectype,seqno,data
from #foo
OPEN Transaction_Cursor
declare @eepssn varchar(9)
declare @rectype varchar(3)
declare @seqno varchar(3)
declare @data varchar(366)
declare @eeccoid varchar(6)
declare @eeceeid varchar(12)
declare @eecempno varchar(9)
declare @companycode varchar(5)
declare @type varchar(20)
FETCH NEXT FROM Transaction_Cursor into @eepssn,@rectype,@seqno,@data
WHILE @@FETCH_STATUS = 0
BEGIN
select @companycode = cmpcompanycode,
@eeccoid = eeccoid,
@eeceeid = eeceeid,
@eecempno = eecempno
from company,empcomp
where eeceeid = (select eepeeid from emppers where eepssn = @eepssn )
and eecemplstatus = 'A'
and cmpcoid = eeccoid
CASE @RECTYPE+@SEQNO
when '001001' then goto parse_pretax
when '002001' then goto parse_LOAN
else select @RECTYPE+@SEQNO+' not recognized!'
end
process_it:
insert into foo2 (empno,companycode,amt,pct)
values (@eecempno,@companycode,@eeamt,@eepct)
FETCH NEXT FROM Transaction_Cursor into @eepssn,@rectype,@seqno,@data
END
CLOSE Transaction_Cursor
DEALLOCATE Transaction_Cursor
goto bypass
parse_pretax:
let @eepct = substring(@data,1,5)
goto process_it
parse_loan:
let @eeamt = substring(@data,27,11)
goto process_it
bypass:
I could sketch it out a little bit better in Northwind or Pubs, but I think I just need a smack upside the head and a little edification.
View 1 Replies
View Related
Mar 11, 2008
Hi,
I would like to have a decision maker component in Control Flow to decide which Data Flow to run.
For example:
if myVar=A then run data flowA
if myVar=B then run data flowB
......
We have Conditional Split component available in data flow but not in Control Flow. What component in control flow can be used to do the same job that conditional flow is doing in data flow? Or what's a work around for this.
Thanks in advance.
View 1 Replies
View Related
Jan 11, 2006
I have a problem when using nested loops in my Control Flow. The package contains an outer Foreach Loop using the Foreach File Enumerator which in my test case will loop over two files found in a directory. Inside this loop is another Foreach Loop using the Foreach Nodelist Enumerator. Before entering the inner loop a variable, xpath, is set to a value that depends on the current file, i e /file[name = '@CurrentFileName']/content. The Nodelist Enumerator is set to use this variable as its OuterXPATHString. Now, this is what happens:
First Iteration:
The first file is found and the value of xpath = /file[name = 'test1.txt']/content. When the inner loop is entered it iterates over the content elements under the file with name test1.txt as expected.
Second Iteration:
The second file is found and the value of xpath = /file[name = 'test2.txt']/content. When the inner loop is entered it unexpectedly still iterates over the content elements under the file with name test1.txt.
My question is: Should it not be possible to change the loop condition of an inner loop in an outer loop such that the next time it is entered it will be done based on the new condition? It seems that the xpath variable is read once, the first time, and never again. If that is the case, does anyone know of a workaround?
Regards,
Lars Rönnbäck
View 8 Replies
View Related
Mar 6, 2007
Hi all
I recently installed sql server 2005 with integration services. While trying to drag control flow items in control flow pane, I am having below error...
The task with the name 'Execute sql task' and creattion name "Microsoft.sqlserver.dts.tasks.executesqltask, Microsoft.sqlserver.SqlTask,Version=9.0.242.0,culture=neutral,public token=7895cd6666345' is not registered for use on this computer.
Contact Information:
Execute SQL Task
Can anybody please help me why this and how we solve this problem...please
Mike
View 1 Replies
View Related
Jun 12, 2007
Hello,
My package "splits" into a number of work flows by the use of precedent management.
After a number of steps I want to combine the flows back to one and finish the package.
What is the mechanism to combine the flows into a singular, linear process again so that I do not have to multiply define identical tasks to finish each flow leg?
View 1 Replies
View Related
Apr 9, 2008
Greetings,
I have run into a problem while creating a simple UDF on SQL Server 2000.
Code Snippet
CREATE FUNCTION [dbo].[GetSectionNum] (@section varchar(4))
RETURNS varchar(2)
AS
BEGIN
DECLARE @sTemp varchar(2),@s char
DECLARE @count int
DECLARE @length int
set @length = LEN(@section)
set @count = 1
WHILE (@count <= @length)
BEGIN
SET @s = SUBSTRING(@section,@count,1)
IF(ISNUMERIC(@s))
BEGIN
SET @sTemp = @sTemp + @s
END
SET @count = @count + 1
END
IF(LEN(@sTemp) = 1)
BEGIN
SET @sTemp = '0' + @sTemp
END
RETURN @sTemp
ENDWhen I perform a syntax check I receive and error about "Error 156: incorrect syntax near keyword 'BEGIN'. I have narrowed the problem to the IF statement inside the While block. If I remove the IF statement the syntax check is successful. This is the first UDF I have written so I'm swimming in uncharted water. Thanks ahead of time for your help.
View 3 Replies
View Related
Jan 1, 2007
Hello,
I am using the event handling mechanism to do my custom logging. This works fine. Using the OnPreExecute and OnPostExecute my log tables fill up with the start- and enddates of all the containers and tasks in the complete package hierarchy.
However, what I am missing (e.g. in a system variable) is the source ID of the parent container that started the task or container. In other words, in my logging reports, I would like to build up a tree starting with the topmost package, like the progress indication in the IDE.
The built-in Logging features do not log this information either as far as I can tell.
Does anyone know how to do this?
Kind regards,
Jeroen
View 3 Replies
View Related
May 22, 2006
Hi,
I have a package which has 3 data flows and a script component to write log file. Those 3 data flows are in sequence (mean upon success, next will run). If the first 2 data flows fails, control will go to script and upon completion of 3rd, control will go to script to create log file.
Now, my question is, within my script component, is there any way to find out what was the last ran data flow and its status? What I know is, adding a 'activex task' between each data flow and script component which updates a variable and then I can use that variable in the script component to know the last ran data flow. But what I want to know is, is there any system variable (or something) which will do the samething without any need to add additional task?
Thx.
View 4 Replies
View Related
Jun 13, 2006
hi all of you,
I haven€™t idea if the following description is a issue or not, anyway:
I begin from Control Flow layer.
I€™ve created a sequence container and inside I€™ve got two groups, one own a sql task and another one own a Data Flow task. Both are linked for a completion conector. Up to here everything is fine. But when I collapse my sequence container the arrow remains there for these tasks and you can see the sequence container €śclosed€? and the arrow lonely.
Not very esthetic, not practical.
Any clarification or though will be as usual welcomed
View 6 Replies
View Related
Mar 1, 2007
SSIS GURUS:
I have read several posting about various modes of trapping errors, but none seem to directly address what I am looking for (SQLIS.com.MSDN, etc)
Coming from a Java/C# background, I am looking for a way to trap errors that arise within the ssis control flow much like the said languges:
try {
do something
} catch(AnExceptionType myException) {
handle my exception
}
/ ** my code at this point is unperterbed by the exception unless I explicitly raise the exception out of the scope of the exception handler. */
To make the analogy in SSIS, I want to be able to handle an error within a "container" and not have to handle the same error in surrounding containers.
Example:
I have a "Foreach" container (call it container FEC) that contains several other containers. One of the subordinate containers is a "For Loop" (call it FLC). The FLC in turn has some nested tasks, some of which are expected to fail and therefore I want to handle in a graceful manner. The tasks that are expected to fail have a "fail" constraint that links them to a task that I want to occur when the failure occurs, and that works, but the failure is not trapped as it percolates out of the container to the FEC. I also tried to trap it with event handler, but that is also an incorrrect trail to follow.
I don't want the failure to percolate up to the FEC. I have set the max errors to a reasonable value for FLC and my "program" is not exceeding that value; however, the FEC still sees that error so it fails. How do I keep FEC from seeing the error (without upping the max errors for the FEC)?
BTW, I am using the script task to set a variable value to indicate successes or fails for those tasks where I can set the max errors to a high enough level (allow the error to occur, then let the fail/success precedent constraint pass control to the script task so that the variable can be set). This is only a partial solution.
I am new to SSIS, in fact to the MS world having been a code slinger for Java and Oracle. So far I have been very impressed with SSIS. Analogous structures that I expect to find in modern development environments have been within easy reach. This is my first serious challenge. Please help.
SCott
View 4 Replies
View Related
Jun 22, 2007
Hi:
A branching element is critical to any process flow. Currently, as far as I know, there;s only a Conditional Split Data Flow Element. There is no direct way I can branch out in a control flow.
In some cases, I could branch by using a conditional operator ?: to either create a dynamic sql string for each patch, or a package name for a Package Execution task and so on.
This approach is not always good enough.
Are there others out there who want a Conditional Control Flow element?
TIA
Kar
View 3 Replies
View Related
Jan 3, 2007
I am somewhat new to SSIS and have a question on branching/control flow. We have 3 manufacturing facilities on AS400. Requirement were to create independent SSIS packages in case certain facilities were down, to not interrupt or fail other facilities. to be honest, I didn't want to maintain 3 * (each plant package) packages and decided to create one that takes in a command line parameter and will create seperate batch files (requirements for our archaic scheduling system) that pass in the plant to be processed.
Everything works fine and the packages analyze the passed parameter with no problem and evaluate it when calling stored procedures int a SQL Task. I am required (I think - let me know if I'm wrong here) to have 3 seperate Data Flow tasks depending on which plant is being processed because they are different connections (same server, different DBs - AS400). Which Data Flow Taks run is based on a precedence constraint that analyzes the passed in plant number.
After the Data Flow Tasks are complete, I'd like to merge back into one path again (even though1 out of N will only run at a time) because they are all simply stored proc calls with different parameters. It seemed silly to me to have the same tasks repeated 3 different times. I looked into started up tasks after events, but I don't know a lot about it and it seems as though I would still be maintaining 3 different paths anyway.
Can anyone point me in the right direction?
P.S. (Even if the sollution is to have 1 Data Flow Task I'm still interested in knowing how to converge paths back together)
View 5 Replies
View Related
Aug 15, 2007
I've been using Konesan's FileWatcher control-flow item successfully in design-mode on my PC, which runs the package on a remote server.
I have installed the Konesan's FileWatcher on the remote SQLServer machine. I then imported the package to the server (Files System folder). I then select the package, right-click 'Run Package', then Execute, and receive the error:
"Error: The task 'File Watcher Task' cannot run on this edition of Integration Services. It requires a higher level edition"
..in the 'Package Execution Progeress' dialog. All other validation seem to be ok.
(Note, I'm executing the above steps using SQLServer Mgt Studio from my PC ; I'm not doing it from the SQLServer machine itself...not sure if this matters or not.)
The SSIS version installed on the server is 9.0.3054. It shouldn't be an "SSIS version issue", as it is the same SQLServer that I used (successfully) from my PC in design mode...
Thanks,
Allen
View 10 Replies
View Related
