Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server & have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for

SSIS EventHandler (ONERROR)

hello all,

im looping on a folder with excel files via a script and a foreach loop, what i need to do is:

while looping ifa file generate an error, to storethat errorin a row in a SQL table and continue with the next file.

any help is appreciated.

View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
SSIS : ErrorCode OnInformation EventHandler

Within my SSIS packages I use a logging logic quite similar as the one described by Jamie Thomson here :

But I still have a small "problem". When my package end with success the ErrorCode returned at the OnInformation event handler level is :




Committing distributed transaction started by this container.

Instead of :




Package "__" finished successfully.

Another example.

When there's an integrity constraint violation, I've got the following ErrorCode returned :




Thread "__" has exited with error code __.

Instead of :




The data value violates integrity constraints.

Anyone has a clue ?

Thanks a lot by advance.



View Replies !   View Related
SSIS OnError Calling Package Level Event Handler Twice..little Help Here!
My project currently has task which have their own individual event handlers that get called onError (setup event messages). I also have a package level event handler that performs a generic task (sending events to the windows eventviewer) In the package level event handler there is a script task that decides on a boolean variable whether to "Success" or "Failure" to different task. When I fail one task of the main control flow, the task levelevent handler runs, then the package level event handler runs, and then it also runs again for some unknown reason. The second time it runs it picks up the value of a variable set in the variables window. However, I change this value at runtime to the value from a database. I can't understand why it would run the second time, and if it did run why it would have the value from the variables window and not the value that is set in memory. It's like the event handler runs with the value from memory and then runs and picks the values back out of the variables window, replacing the db valuesand re-runs.

Maybe the package itself is failing all together and then re-runing the package level event handler?

Any help would be greatly appreciated.


View Replies !   View Related
Is There An Eventhandler That Can Be Triggered When Package Execution Is Terminated?
i have a package which logs to a table the status of its execution. i wonder if there is a way to trigger an event when package execution is terminated.

View Replies !   View Related
Log Within OnError Even Handler
I'm using the logging

Dim VARS As Variables


Dts.Log("************** Version Build failed on " + VARS.Item("Table_Name").Value.ToString() + " table Roll Back in process for : " + VARS.Item("Version").Value.ToString(), CInt(VARS.Item("Global_Execution_Code").Value), Nothing)
MsgBox("Version Build failed on " + VARS.Item("Table_Name").Value.ToString() + " table Roll Back in process for : " + VARS.Item("Version").Value.ToString(), MsgBoxStyle.OkOnly, "Version Failed")

which it works fine and logs the messages to a text file

The issue is the if I use an event handler and for onError event, SSIS won't let me use the logging with in the onError Event handlers design.
is there anyway around this issue.
simply I would like to be able to log some messages, when SSIS is running the OnError part of the package.


View Replies !   View Related
Bug? OnTaskFailed Vs. OnError
Hi everyone,

I have a package setup like this:

PackageA -> ContainerA -> ContainerB -> ExecutePackageTaskA (calls PackageB)
PackageB -> ScriptTaskA

If an error occurs in ScriptTaskAfrom PackageB, the OnError event handler in PackageA fires once and catches the event from ScriptTaskA; that is, the output of the SourceName system variable is "ScriptTaskA" from PackageB. So far so expected.

Now, the same error is handled differently by PackageA's OnTaskFailed handler. The OnTaskFailed handler fires twice - once for ScriptTaskA and once for ExecutePackageTaskA; that is, two outputs are returned - one for "ScriptTaskA" and the second one for "ExecutePackageTaskA". That's strange to me.

Why does the OnError handler only fire once and the OnTaskFailed twice? Is there a setting that does this?

Thanks in advance,

View Replies !   View Related
OnError Script Error
I am using following script to combine all the System:ErrorDescription on OnError




Dts.VariableDispenser.LockOneForWrite("Errordesc", vars)


errNumber = CType(vars("ErrorCode").Value, Integer)

errDescription = vars("ErrorDescription").Value.ToString()

errSource = vars("SourceName").Value.ToString()

errdesc = vars("Errordesc").Value.ToString()

vars("Errordesc").Value = errdesc + errNumber.ToString() + vbNewLine + vbNewLine + _

errDescription(+vbNewLine + vbNewLine + _


Please note that "Errordesc" is the variable which I declared on script as readandwrite variable.

But on execution of this script I get error

Error: A deadlock was detected while trying to lock variables "User::Errordesc" for read/write access. A lock cannot be acquired after 16 attempts. The locks timed out.

any sugestion...

Please note that without ErrorDesc, the script runs successfully

View Replies !   View Related
OnError Event Handeling

I am having a few problems getting the opomum configuring the OnError event hendler for my new package. What im trying to do is log the Error event and sound out an email (which i am achiving) where i am encountering a problem is trying to determin the severity of the error.

What i would like to achive is to send an email and log the error (as i currently do) but ifthe error has the power to stop the package from executing i would like to fire an additional script to move the loading filesto a failed location.

My initial thoughs were to put an expression the on the procdural constraintin he error handeler but i cant find which System variable to apply the logic to.

Anyone any suggestions?



View Replies !   View Related
Onerror Event Handler
Is the Onerror Event Handler from the tabbed window in the IDEthe same as the red arrow paths that can be used in the control flows and Data Flows?

If yes, whatand where is the best practice to use?

If not, can you elaborate?


View Replies !   View Related
Logging Using OnError Event

We are generating log file in our SSIS package by enabling the built-in feature of SSIS tool. We are generating log for the "OnError" event. This also recorded the error/failed task messages in the text file "log.txt". That error information is too complex with more unwanted information like below

----------------OnError,,,pkgExtract,,,8/30/2006 11:50:04 AM,8/30/2006 11:50:04 AM,-1071636471,0x,An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

OnError,,,pkgExtract,,,8/30/2006 11:50:04 AM,8/30/2006 11:50:04 AM,-1071607780,0x,There was an error with input column "create_user_id" (116) on input "OLE DB Destination Input" (103). The column status returned was: "The value violated the integrity constraints for the column.".

OnError,,,pkgExtract,,,8/30/2006 11:50:04 AM,8/30/2006 11:50:04 AM,-1071607767,0x,The "input "OLE DB Destination Input" (103)" failed because error code 0xC020907D occurred, and the error row disposition on "input "OLE DB Destination Input" (103)" specifies failure on error. An error occurred on the specified object of the specified component.

This is infact not in a better readable format. We also don't want to do our error logging in database.

Is there any way of defining our error log and create error error log with customization of our messages . Can we do it using OnError event handler.

Please help us with some good solution to avoid giving this confused error log messages.



View Replies !   View Related
What Does The Return Value Of IDtsEvents.Onerror() Do?

The IDtsEvents.OnError method has a boolean return type whereas most of the other methods on that class are voids.

Whydoes OnError return a boolean?
Whatis theeffectof returning true?
Whatis theeffectof returning false?

Thanks in advance


[Microsoft follow-up]

View Replies !   View Related
Question About OnError Event
Hi guys,

I have a package name "Package1" and I have a few data-flow tasks in it.
whenever anything fails in the "Package1", I want to send out the failed email to alert myself.
So first step, I went to EventHandler tab, and select Executable as "Package1" and Event Handler as "OnError".
and thenI added SendMailTask. I manually ran SendMailTask,failed email send to me okay.
But when I run package from Microsoft visual Studio (IDE), the package failed but it doesn't send out failed email.

Error code:
[174]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "xxx" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

Maybe I misunderstand OnError eventhandler, or I set up it wrong.


View Replies !   View Related
Send Email Message - OnError
I used onError event to send email in case ssis pckage fails

but it send multiple email with errordescription. for ex below are the errordescription of four diferent emails i received.

Thread "WorkThread0" has exited with error code 0xC0047039.
An error occurred with the following error message: "The connection "{01AF859A-CF97-4F6C-9C78-1AA4B1C9C27B}" is not found. This error is thrown by Connections collection when the specific connection element is not found.".
Thread "SourceThread0" has exited with error code 0xC0047038.
The PrimeOutput method on component "Flat File Source - Read from source file" (1) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

Can anyone suggest if we can combine all this error description and send this as one email.

View Replies !   View Related
Send Email -OnError Event
I have included the Send email task and sending email from SSIS package on Error event.

But for an error I am receiving multiple atleast 10-15 email generated. i got only 2 emails with revelant error message and other email gives message like "

Thread "WorkThread1" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown."

I have included ''@[System::ErrorDescription]' as an email attachment.

Is there any way to get only specific relevant error email.

Please suggest

View Replies !   View Related
Get The Err?r Message In OnError Event Hanlder

I want insertdatainto table from csv file,so I put a data flow task on control flow tab,

a flat source and a ole db dest on data flow tab. They work well.

Then to get the error log I put a script task on event handler tab, in this script task I want to

output the error description to a txt file while thedata flow taskfailsfor any reasons

such asconstraint failure etc.

In this script task I think I can get the error description by using property Err.Description,

but Err.Description is empty while data flow taskfails.

How can I get the error description in the script task on event handler tab.


View Replies !   View Related
OnError Alert User Though Interface??
I have a SSIS package which takes excel spreadsheets (exported from MS Project) and imports them into tables in my database.I have eachstep of my control flow set up with "On Success" so it only continues if the previous step was successful.

My next step is to create a page and/or web part so that I can have my user click a button to launch this SSIS and import this data. I have seen articles on how this could be done via code - so I assume it can be done. But what I cannot find ishow alert the user if an error has occured in the execution of the SSIS.

How can you alert a user that a package you executed though in the front end errored?

Could I build something in to the OnError handler that returns the error and have that error returned to the front end App??

Any insight/examplesfor this would be much appreciated.


View Replies !   View Related
OnError And OnTaskFailed Can Not Fire Event
I have a package which have two sequence container, first container is used to transfer data to a staging area and second sequence container is used to transfer to destination from that staging area. And I also apply transaction required to second sequence container.
There are several execute sql tasks and several data flow tasks inside two sequence container.
first sequence container( 1.execution sql task-> flow ->3.execution sql task) ->
second sequence container(4.execution sql task-> 5.execution sql task-> flow-> flow -> 8.execution sql task-> flow...)

I create ExecutionLog table which is used to log status for this package on our sql server. First this status field is null, then during this package run , it change to 'in process', and after this package finished, it change to 'success' or 'failure' depending this package can run successfully or not.
This package can be run only if status is 'success' ,'failure' or null. So I need to change this status field during package execution.
For updating package to failure, I need to add event handler to change that status using execute sql task.

First time I perform to execute sql task on onError event handler tab (this event handler is applyed on package level ) .
And for testing envent handler I use old schema version to make sure I get failure for '8 execution sql task'.
But package seems to get stuck at '8 execution sql task' inside second sequence container( always yellow when I run from ssis) and never fire envent handler. '8 execution sql task' is used to update related table.

Second time I remove onError envent handler and change to use on onTaskFailed event handler tab (this event handler is applyed on package level ) .
But everything is the same as using onError event handler except I got error output but still can not fire event.
Why '8.execution sql task' can not fire onError or onTaskFailed?
For this case what kind of event handler I need to use, what kind of level I need to apply for this event handler.

View Replies !   View Related
Locked Excel File Due To OnError
I have a package the looks for any Excel files in a folder, moves the data to a SQL table, then archives the file to one of two archive folders--a success folder or an error folder. I have an OnError handler on the Data Flow that sets a flag that lets the archive process know where to move the file.

This works when the processing is successful. It also works when the error in the Data Flow occurs right off the bat, i.e., in the Source. When the error occurs later on, say in the Destination, it doesn't work correctly. In this case, the OnError sets the flag, but when the archive process tries to move the Excel file, it can't because it's locked. I assume this is because OnError interrupted the Data Flow before the Excel file could be closed properly.

Any ideas on how I can avoid this problem? Can I manually get the Data Flow to close the Excel connection somehow?

View Replies !   View Related
How Can I Handle OnError Event In My Stored Proc.
Dear All:
I want to ask how can I handle OnError events in stored procedure in MSSQL.

Actually I wanted to place some Rollback procedure on this.

Can you suggest some methods for me?


View Replies !   View Related
In Debug OnError Sends Email But Does Not Finish

I've setup the option to mail the error to a person. When the option is on I get the error message by mail but the package does not finish (eg. the failing task does not become red and the output windows never says anything about the error) - if I set the option to off the task fails as expected.

Is there something I havn't set up correctly?


View Replies !   View Related
Script Task Raising OnError Events
Is there a way I can stop a script task of a control flow from raising OnError Events? or Is there a way I can Set Dts.Results to Fail without having the script task raise OnError event. 

View Replies !   View Related
How To Abort Package Execution Programmatically OnError
Context: I have a web page that uplaods a file that is then imported to the database via an SSIS package.

Problem: If and when the package encounters an error, I want to surface that error to the user via the web page.

Approach: I derived my own class from the DefaultEvents class and then overrode the OnError event handler to catch any errors raised during package execution. I then pass my class to the package Execute method.

Results: The OnError is triggered, but I don't knowhow to abort package execution nor how to pass the error context to the user. I tried raising a custom exception in the OnError handler, but it failed to propogate to the calling code (ie the code that executes the package).

Another approach would be to write to a database table and then do a query from my web page after the package is done executing (alternatively, I could generate a log file). Seems like a lot of work though. All I want is to pass the error context back to my calling code so I can tell the user what happened.

Thanks in advance for your help.

View Replies !   View Related
OnError Event Handler And Custom Logging
I'm trying to implement a custom log table. To keep the discussion simple, let's say I only have 1 column in this table and all I want to write in it are

"Start" when the package starts
"Error" when it encounters an error
"Finish" when the package finishes. Even if there was an error, I still want to enter "Finish'.

My Control Flow has 3 task objects, 2 Execute SQL Tasks, and 1 Data Flow Task in between them.

The first Execute SQL Task does an insert statement for the Start and the second Execute SQL Task does an insert for the Finish.

To capture any package errors, I also have an Execute SQL Task (to insert "Error") in the Event Handler for OnError. I see that when I cause an error in my package it can raise multiple OnError events, which will envoke my Execute SQL Task multiple times. (This is good because it will allow me to write a lineper error event with theerror description.)

The problem I have is, how do I write the "Finish" log when I have an error? If I put the insert for the finish in the same Execute SQL Task with the errors, then it will write a "Finish" for every error. But I can't put it anywhere else because if I put it anywhere else, the package never makes it there because it stops at the OnError Event Handler.

Or is there a way for me to tell the package to do the 2nd Execute SQL Task all the time?

Lastly, is there a better way to do this kind of custom logging?

View Replies !   View Related
System:ErrorDescription Vs. OnError Error Details
Hi all,

I have a Script Task in an OnError EventHandler, the Script Task is used to send an Email to notify about the Execution Error. I'm currently using the System:ErrorDescription variable in my Script Task to provide details about the error but I have noticed that the Error Description written in the Windows Event Log when using OnError Logging is much more detailed than the content of the System:ErrorDescription variable. How can I use the same Error Description in my Script Task ?

I'll appreciate any help.

View Replies !   View Related
OnError Event Firing Multiple Times
I have an SSIS package that contains a For Each Loop Container. I have three Data Flow tasks within the container. I have an OnError event handler associated with the encapsulating container. When one of the Data Flow tasks within the For Each Loop Container fails, the OnError for the Loop Container gets called 5 times. The OnError handler is just a script task that sends a notification email. I am not explicitly Dts.taskresult = failure, nor am I calling FireError.

View Replies !   View Related
Rerun Data Flow Task OnError
I'm very new to using SSIS. I have a large number of SQL databases (archived .mdf/.ldf files) with data that needs to be imported into one giant database. My problem is that half of the databases are missing a column that was introduced in a newer version of the database schema and when my package executes my data flow task it errors out. I basically get a message stating that it cannot prepare any statements because the SQL command that I am using is trying to select column data from a column that doesn't exist.

I figured that I could set the OLEDB Source inside of my Data Flow to have a Data Access Mode = SQL command from variable. The variable bound to the component is called "MySelectQuery" and is initially set to my "new format" query. From there I could create an event handler for the OnError event of the Data Flow Task, switch value of "MySelectQuery" to equal the "old format" query and rerun the Data Flow Task.

I'm not sure how to accomplish restarting my Data Flow Task after changing "MySelectQuery" in the OnError event. I just tried setting up the package to use CheckPoints but it doesn't seem to be working right for me. When I restart the package it starts over even though there is a checkpoint file that seems to have captured the state after the failure.

Any ideas?


View Replies !   View Related
Package Level OnError Event Handler Not Firing (as If Not There)
Hi all,

I have a package on which i've applied a package level OnError event handler. The OnError event handler includes a Script Task (that builds up a string of errorCode, errorDescription, MachineName etc...) and a WebService Task that calls a webservice to send an email including the built up string from the script task in the body of the email. This has worked fine in one package where i've applied it but for some reason in a second package the existence of an OnError package level event handler seems to be completed ignored. I'm causing various package object to fail but the OnError handler never fires. I know the obvious answer is find what's different between the two packages but i can't see that any is different (in relation to package level OnError event handling).

Has anyone else come across this? Any suggestions?



View Replies !   View Related
Will OnError Event Fire If I Use Try/Catch In Script Task?

Wehave a package that runs on 3 servers, all based in different countries and time zones. Each package does logging to a common SQL Server based in New York. (We log to the same server to make monitoring of the package run results easier.) We expect that our overseas servers will occassionally fail tomake a database connection and have coded a Try/Catch block in our Script task to ignore errors that occur whenattempting the SQL Server logging.

The problem is that the OnError event handler defined at the package level is being called whenever a server fails to make a database connection. The Catch block appears to not be called at all. I've included belowthe short method that does the SQL Server logging. The "Dts.Log" line logs to a SQL Server and usually works. However, when it fails, the "WriteEventLogEntry" method is supposed to be called to write an error to the server's event log. (I haven't included the "WriteEventLogEntry" code but it works fine when called elsewhere in the package to do our non-error event logging.)

Public Sub WriteSqlServerLogEntry(ByVal logMessage As String, ByVal dataCode As Integer, ByVal dataBytes() As Byte, ByVal rowsInserted As Integer)


Dts.Log(logMessage, dataCode, dataBytes)

Catch ex As Exception

WriteEventLogEntry("An exception was thrown while attempting to log the following message to the SQL Server provider: " + logMessage, EventLogEntryType.Error, rowsInserted)

End Try

End Sub

Shouldn't a Catch block prevent the OnError event from being raised? How can I change my package to ignore all errors that occur when attempting SQL Server logging?



View Replies !   View Related
Stored Procedure - Proper Way To Use &"GOTO&" And &"EXIT WHILE&" For Onerror?
I wonder what is the proper way to exit the loop while using the GOTO error handler in STORED PROCEDURE... I noticed that if I use the GOTO then that's where it go and I wonder if hte loop would kept on going in this case.



IF @@ERROR <> 0
SET @ErrorCode = @@ERROR
SET @ErrorDescription = 'blah...'
SET @ErrorRecord = 0

FROM #tmpTblPurchaseRaw
WHERE RawID = @TblPurchaseRaw_RawID


View Replies !   View Related
Running Ssis Package With Ssis Run Time Compoenents And Sql Server 2000...
running ssis package with ssis run time compoenents and sql server 2000...

Is it possible to run ssis packages that point to servers on sql server 2000
without installing sql server 2005 ?

Can we just install runtime for ssis and run the packages ?

If you links or documentation which explain this scenario then it will help me to forward it to our DBAs and do the needful.

thanks a lot


View Replies !   View Related
Running Ssis Package With Ssis Run Time Compoenents And Sql Server 2000...
running ssis package with ssis run time compoenents and sql server 2000...

Is it possible to run ssis packages that point to servers on sql server 2000
without installing sql server 2005 ?

Can we just install runtime for ssis and run the packages ?

Please explian with links if possible

thanks a lot

View Replies !   View Related
SSIS Called From Agent Job Not Calling New Child SSIS Package Addition
I have an SSIS package (TransAgentMaster) that I recently modified to include a call to a child package via the file system. The child package creates a text file. When I run the package in dev studio then the child package/text file is produced.

I then imported the TransAgentMaster as a stored packagesfilesystem package into SQL SSIS and executed the package. The child package produced the text file.

I then ran the SQL Server Agent to see if the child package would work and it did not generate the text file. Thus after updating a SSIS package importing the package into SSIS the job that calls the package will not call the child package. Please not that the TransAgentMaster package calls 7 children packages just not my new one.

Any thoughts why the agent will not run the child newly crated childe package?

View Replies !   View Related
Source Code For The Standard SSIS Components That Come With SSIS?

I'm finding that the standard components often just don't quite meet my
needs, but would only need some fairly minor changes to save me and my team a lot of
work (and produce more elegant solutions). So I was just wondering whether the source code was available for the standard components that come with SSIS, or if there is anyway to extend their functionality?
Or do you just have to start form scratch?


View Replies !   View Related
SSIS: Excel Import: SSIS Not Reading Dates
Hi. I need to import excel file in database. i first need to do an unpivot task. the column names are dates and SSIS seems to be unable to pick up the column name as it is replaced by F2 F3 F4etc Can you advise of a solution. thanks ken

View Replies !   View Related
Use Of SSIS In Reading And XSL File. Should I Use SSIS Or Just Straight C#
I need to build an application to read values from an Excel spreadsheet. Once the values are read from the spreadsheet, the C# code will do some elementary statistics on the values read. Then the values read and their computations will be written to a sql server database.
My manager suggested that SSIS might be a good candidate technology for doing this type of work. Does that sound correct? My only hesitation with using SSIS is that I want to keep the application as simple as possible, so that the code can be more portable. Maybe might argument is not a good one, but maybe someone can help me out here.

View Replies !   View Related
Ssis Select From Ssis User Table
I have two variables
1. strStatement datatype (string)
2. objTable object (user tables)

the string strStatment returns a dataset
and is assigned to objTable.

I want write the data from objTable to a sql table
and it seems not to work.

Here is what I did.
I used data flow task to either select from a a string or objectTable.
but it give me an error.

View Replies !   View Related
Creating GUI With SSIS Or Passing Parameter(s) In SSIS
Hello All!

I have two questions to ask in this one thread. I would appreciate any feedback.

1. Is it possible to create GUI from SSIS using macro so that it can display forms or dialogs? If so how can I create a form that can be used to pass the parameters for the execution of the SSIS??

2. Is it possible to pass parameter(s) to SSIS? If yes, how can we do it...Please provide me with any example.

I wait to hear from you!


View Replies !   View Related
Convert SSIS 2008 To SSIS 2005
We have SQL 2008 in development but only SQL 2005 in production. I have an SSIS package that was created in 2008 but need to deploy it to a SQL 2005 server. The '05 server will not import the package because of its version. Is there a way to convert back or 'save as' SSIS '05?

View Replies !   View Related
SSIS Design Question - New To SSIS, Please Comment! :)
Hello everyone!!

I am completely new to SSIS and have been given a large project (of course with a tight deadline) that has the absolute requirement of using SSIS. I am/was very, very good with DTS and could easily accomplish what I need to do with an ActiveX script task in DTS in no time, but as this is new development, we are not to use ActiveX script tasks within SSIS since it will not be supported in the next SQL Server release. I'm thinking script task, but please give some comments on how you would accomplish the following in SSIS (please remember I'm new to SSIS, so don't assume I know anything. )

I must accomplish this: in a nutshell, I need to create separate tab delimited text files of customer informaion. One for each region. Each region consists of X amount of states and we have X amount of regions. (Pseudo code followed by standard explanation)

Select a max value fromregion lookup table in SQL (this is the # of regions)

for N=1 to MyMaxValue

selectstates fromregion lookup table where region code = N (the current region we are on)
'this returns a list of states in a region, need these in array or recordset object or something
Open an output file which will be a tab delimited text file we will write results below in loop to (in DTS I would programatically kick off a transformation task in the package)
'loop thru states returned, so if in a rs object...
do while not rs.eof

execute customer stored procedure, passing as a variable the current state we are on
'this will return all customers within a state, this whole result set (approx 1 million) needs to go to the tab delimited file
'I have to execute this stored procedure for each state & then write results to the SAME file, until we are onto a different region

close file

OK, so basically, as you can see, Its sort of simple in a way what I need to do, i just have no idea how to go about doing this in SSIS. I can not hard code any state or region values. I MUST read them in from the lookup tables as region codes are constanatly changing and we are constantly adding in new states and new regions, so with above coding idea, it would always dynamically pick up any new states, new regions or changes.

So in a nutshell, I need to create separate tab delimited text files of customer informaion. One for each region. Each region consists of X amount of states and there are X amount of regions. Pretty strait forward, huh? The requirements are strait forward, but SSIS is throwing me for a loop... it does not seem flexible enough to be as dynamic as I need it to be to do this. I'm sure it is, just my understanding of it is very basic so far.

Please provide your suggestions! I think a lot of newbies would benefit from some SSIS design info... how to do common things in SSIS, but beyond just retrieving a recordset and writing it to a file... what do you do when you need to add just a few layers of decision processing, and retriving recordsets and writing files based on that decision processing?????


View Replies !   View Related
SSIS - Get ServerName From A XML Or TXT File Into SSIS Package
Dear Friends,
I store several configurations in the main database of my SSIS packages. I need to get the servername from a xml or txt file in order to get those configurations stored in my database.
How you think is the better way to do that?
Using a FlatFileSource to read the file and a script to save the value into a SSIS variable?
Using the package configuration I cant do that... maybe I dont know, but I can save the SSIS variale in the configuration file, but what I need is to do the inverse, read the configuration file and save the value in the SSIS variable.
How the best way you suggest?!

View Replies !   View Related
SSIS Newbie - Separating SSIS &&amp; DB Engine
Hi Gurus,

I am new to SSIS.I am trying to install just the SSIS in one machine("SSIS Machine")and just the DB Engine ("SQL Server Machine")in another machine. What I am trying to do is, separating the SSIS service and packages from the Database Engine and trying to run in in another machine. I have few questions on this topic. I searched on this forum but I couldn't find a concrete answer to those questions. Forgive me if it already answered/asked multiple times.

1. When I install SSIS in "SSIS Machine", do I need to install client components also in the same machine?
2. I already established this setup (SSIS with client components in one machine and SQL Server in another) but when I tried to connect to the SSIS thro' Management Studio from Sql Server machine, I keep getting "Access Denied" error. Is it possible to connect to SSIS server from another machine (using Management studio)? I tried the DCOM security permission options I found in the internet(I don't have domain id so I gave "Everyone" full access)but still I get the same error. Any help would be appriciated.
3. Do I need 2 SQL Server Licenses (Enterprise) if I go with this environment?
4. Is it possible toconfigure SQL Job to run SSIS installed in another machine?

I would appriciate any help on this issue.


View Replies !   View Related
SSIS - Tracking An SSIS Package Execution
I would like to see if someone can help me out.

1.- SSIS Package execute taskson 2000 SQL Server Database
2.- Execution takes places using Business Intelligence Studio
1.- How can I tracked that SQl 2000 tasks took place using a SSIS Package?


View Replies !   View Related
&&"could Not Serialize The SSIS Runtime Objects&&" When Attempting To Copy Tasks Created By SSIS Wizard
Run the SSIS import/export wizard.

(xls -> sql table)

Select the tasks created.


Here is the error that occurs.

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.

For help, click:

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)

View Replies !   View Related
Loading SSIS In ActiveXscript Within SSIS
I am trying to load a dtsx file in the SSIS package ActiveXscript.
But i am getting the following error

Error retreiving the filename for a component failed

In DTS the syntax was

and for SSIS Sql Server 2005 its :
app.LoadPackage("C:ProjectsTestPackage.dtsx", Nothing)

some how its not working for me.Is it because of me using ActiveX scipt
Please help
Thanks in advance.

View Replies !   View Related
Can One SSIS Packet1 Run SSIS Packet2?


If I have 2 SSIS packets, can one start the other?

Thank you.

View Replies !   View Related
Job Running SSIS Package Keeps Failing But The SSIS Package By Itself Runs Perfectly Fine
Hey, I've a few jobs which call SSIS packages. If I run the SSIS package, it runs fine but if I try to run the job which calls this package, it fails. Can someone help me troubleshoot this issue? None of my jobs that call an SSIS package work. All of them fail.

Thank you


View Replies !   View Related
Can I Return A Value In A Variable From A SSIS Program Back To C# After The SSIS Program Is Run From C#?
Can I return a value in a variable from a SSIS program back to C# after the SSIS program is run from C#?

View Replies !   View Related
Fuzzy Search - Exposing SSIS Fuzzy Capabilities Outside Of SSIS?

I've been looking into ways to accomplish a fuzzy search and SSIS makes that possible if I want to do a bulk import or something like it. But what it I just want to look stuff up at any given time not haveing to run the package?

Is it possible to expose the fuzzy lookup outside of SSIS to for example t-sql?

Here's an example:
I want to lookup the music artist "Notorious BIG" but in the database it is "Notorious B.I.G." if I use the SSIS fuzzy lookup I basically get what I'm looking for. But how would I call this from a web application? So then I tried Full text search but this doesn't really work out as well.

Will I have to re-write the logic that the fuzzy lookup uses to enable it to work? i.e. using Full Text Indexes and FreeTextTable, ContainsTable, SoundEx and the like to somewhat even come close to what the Fuzzy Lookup has?

View Replies !   View Related
FTP In SSIS And .bat Files In SSIS
I am new to using FTP and executing .bat files in SSIS.
First, the .ftp file should run. Whenit runs the FTP process, the user should be prompted for username and password. Then, it should run the .bat file.
What all are the steps I need to do and how to set the properties for FTP and the Execute process task (which holds the .bat file).

Thank you.

View Replies !   View Related
Error While Executing SSIS Package From Other SSIS Package

In ourproject we have two SSIS package.

And there is a task (Execute SSIS package) in First package that calls the execution of second package.

I m continuously receiving an error "Failed to decrypt protected XML node "PackagePassword" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available."

As we are running first package by job, job runs successfully logging above error

The protection level of second package is set to "EncryptSensitiveWithUserKey"

Can anybody please suggest how to handle it?

View Replies !   View Related
How SSIS Works With Cluster SQL And Non Cluster SSIS Service?

According to microsoft, we can cluster SSIS service but it is NOT RECOMMENDED.

Now this is the situation that I have where I need to understand how SSIS works?

Enviornment: Active Active cluster enviornment for SQL server with SSIS server installed as stand alone as default on both node.

Name:Node 1Node 2
Server name:Nd1Nd2
SQL server name:cs-nd1in01cs-nd2in02
SSIS server name:Nd1Nd2

BTW, this is cosolidated enviornment so there are more than one application expected and resides on each instance of SQL server.

The question is around SSIS, what would be the best practice to develop SSIS package that can work with above envoinrment.

Secnario: What if my Nd1 fails. SQL server cs-nd1IN01 will be failover to Nd2 and it will be available. But How about SSIS packages? How that understands
to use Nd2 SSIS as Nd1 SSIS is not available. Is anyone has similar experience to setup SSIS in cluster envionrment but as non-cluster service?

Please help. Thanks in adavance.

View Replies !   View Related
Difference With SQL Server 2k5 SSIS Dev Edition Vs SQL Server 2k5 SSIS Enterprise Edition

Just want to ask if there will be any difference if I start to develeope a a package from the SSIS dev? rather than the enterprise edition?

View Replies !   View Related

Copyright 2005-08, All rights reserved