.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

[SSIS 2008] : strange behavior when exec from job

Posted By:      Posted Date: April 14, 2011    Points: 0   Category :



Here's my configuration :


Windows Server 2008 R2

SQL Server 2008 R2

All 64bits



I designed SSIS packages running well in BIDS.

In these packages I have ScriptComponent whith stringbuilder preparing update queries I execute later.

In these strings I manipulate, there's dates.

As my database as well as my source are in French I use : 

  Dim myCIntl As New CultureInfo("fr-FR", False)
  Threading.Thread.CurrentThread.CurrentCulture = myCIntl

Else I get issues on date format.


Everything is ok when run under BIDS.


The SSIS packages are SQL Server stored and called from a SQL Server job.

Then I have error pointing to the ScriptComponent task where I do all my stringbuilder ...

I don't know why (of course as I'm asking for help ;-) )


Thanks a lot if you have any guess of why there's this different behavior.




View Complete Post

More Related Resource Links

EXEC(NULL) - Differing Behavior in SQL 2000 and SQL 2008


I'm working on converting a datbase currently in SQL 2000 over to SQL 2008 and found this peculiarity:

DECLARE @sql varchar(1)
SET @sql = NULL


On my SQL 2000 machine that code returns 0, but on my test box running SQL 2008 it returns 1 no matter the compatibility level of the database (80 or 100).  Is there a global setting / flag that might affect this behavior?  Or is it just a design difference in the two engines?

I haven't turned up anything when searching elsewhere, hoping someone can shed some light.

EXEC(NULL) - Differing Behavior in SQL 2000 and SQL 2008


I'm working on converting a datbase currently in SQL 2000 over to SQL 2008 and found this peculiarity:

DECLARE @sql varchar(1)
SET @sql = NULL


On my SQL 2000 machine that code returns 0, but on my test box running SQL 2008 it returns 1 no matter the compatibility level of the database (80 or 100).  Is there a global setting / flag that might affect this behavior?  Or is it just a design difference in the two engines?

I haven't turned up anything when searching elsewhere, hoping someone can shed some light.

Using Conditional Split data Transfer in SSIS 2008

This article uses the Integration Services Conditional Split Data Transformation element to filter and transfer data from a set of flat text files to SQL Server database table. The concept can be easily extended to apply to any other source or destination such as Microsoft Excel. This scenario is useful in creating denormalized database tables in a reporting and analysis situation.

Issues with SSIS and SQL 2008 - Integration services not running

Hey guys, I installed SQL 2008 with complete BI studio including Integration Services. When i check in my services - i cant see my integraiton services in there When i try to connect to Integration Service using localhost, I cant connect it And when i try to run setup of SQL 2008 - It shows that Integration Services already installed and that what i checked when i fetched my Installation tool report from SQL Server to verify i have it installed. Why can i access Integration services, if i already have it, Please suggest with ideas, checks i can make to ensure every setting is in place. Thanks. Would appreciate for quick response.  

Output parameters in OLEDB Command component - SSIS 2008

I have a package that I developed in SSIS 2005 and recently ported to 2008. Everything runs great except an OLE DB Command component that calls a stored procedure that uses output parameters. When I try to run this, I get this error: Error: 0xC0202009 at Data Flow Task, OLE DB Command [100]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Syntax error, permission violation, or other nonspecific error". Error: 0xC004701A at Data Flow Task, SSIS.Pipeline: component "OLE DB Command" (100) failed the pre-execute phase and returned error code 0xC0202009. When I do the same thing in SSIS2005, it works fine. So I setup a new package with 1 data flow. In that data flow, I added a source component, an OLE DB Command, and a destination.  I then tested this with a stored procedure that took no parameters. It worked fine. I then tested it with a stored procedure with 1 parameter defined as OUTPUT. This generated the error.   Is there an issue with output parameters in an OLE DB Command in SSIS 2008?   Thanks!http://bobp1339.blogspot.com

Post Upgade task, Upgrading SSIS Pacakges to SQL Server 2008.

Hi, I am trying to upgrade sql 2005 packages to sql 2008 after doing in-place upgrade of DE and SSIS. Can I know what packageformat column in msdb.dbo.sysssispackages refer to, as according to http://msdn.microsoft.com/en-us/library/cc879336.aspx the value should be 2 if the package is in sql 2005 and it should be 3 if it is upgraded. But I am seeing only 0 or 1.   Can I know any other method to figure out version of the ssis packages? I am having issues upgrading SSIS Packages from 2005 to 2008, using SSIS package upgrade wizard.   Thanks for your help. Regards, KRanp.

Strange Problem - SSIS Fails with "Syntax error, permission violation, or other nonspecific error".

Hello, I have a strange issue while I am deploying a package to one of the environment server. I have 2 XML Source, in the DataFlow, and one will extracted based on a value of variable that is passed from run time (package is executed from a Job) and other will be extracted all time. The next Step I have is a Execute SQL Task in ControlFlow wihich will execute after DataFlow. This has 2 input parameters and some SQL query that uses the param. Now this one fail on the target environment with below error: failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. I got the error when I did a SSIS Text File Log. Note: If I run the package in BIDS with the same XML files it work. Also when I deploy the package to my Dev Server it works. When I compare the Dev DB with the target environment DB - Both are same. The Service Acount has permission - as I can see the DataFlow task completed. Another Point: The XML Load Data Flow that executes based on the Variable Value does not execute on target environment, even if the value is passed as "True" (It is Boolean Type) But this variable is NOT an input for the Execute SQL task that fails. I am not sure w

Strange Authentication Behavior...

I have an installation of SSRS 2008 that is exhibiting some very odd behavior with regards to authentication and permissions. I have SSRS installed on a box called SERVER1 and I'm a local admin on the box and an Administrator on the SSRS instance. This box has an IP address of When I go to the Report Manager URL, I see everything I should (Home, Site Settings, etc.) I'm able to go in and set up security and create folders and everything. What's odd is that if I use the IP address instead of the server name in the Report Manager URL, I get to the home page but there is nothing displayed, only Home, My Subscriptions, and Help. There are no report folders visible and the options available to admins aren't visible. One thing that may shed some light on things: if I do a ping -a on the IP address, it resolves to the host name. If I ping the host name, I get back a reply from ::1: I have no idea why permissions would differ between a host name and an IP address if they're the same server?? Any insight would be appreciated!A. M. Robinson

ssis sql 2000 image to 2008 varbinary Failed to retrieve long data for column

Hi, I have a task that to migrate the image type column from sql 2000 to varbinary type in sql 2008. The source column having 3812353 max datalength size for the column. The package always failed with following error message. [OLE DB Source [13177]] Error: Failed to retrieve long data for column "attch_file_content_t". [OLE DB Source [13177]] Error: There was an error with output column "attch_file_content_t" (13209) on output "OLE DB Source Output" (13187). The column status returned was: "DBSTATUS_UNAVAILABLE". [OLE DB Source [13177]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "output column "attch_file_content_t" (13209)" failed because error code 0xC0209071 occurred, and the error row disposition on "output column "attch_file_content_t" (13209)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure. Meng Chew

WPF Window as Modal Dialog, strange behavior

Hopefully someone has a clue.... My application displays WPF Window as a modal dialog box. The dialog box has a few child user controls, one of which has combo box. When the user clicks this combo box to select... the whole dialog disappears except of the innards of the combo box. So the user only sees the list box of the combo box. Here are the details I got from the  client: The window itself is launched from non WPF application (Lotus Notes). This happens only on a few machines. The OS is Windows XP SP3. There is no issue with the parent handle, windows owner, etc... I've seen this behavior during the development phase, only when running under a debugger.    Thanks, Michael        

Diffrences in SSIS 2005 and SSIS 2008?

Hi All,   How to convince interviewer in  major differences in ssis 2005 and ssis 2008.In my system I have ssis 2008.Can I install ssis 2005? How to install? Thanks CMK…

ssis 2008 Loading MULTIPLE EXCEL file

HI it is possible for me to load multiple text file using for each loop,but if i try the same process for EXCEL file then my excel source throws an error. Also when i created my variable in value  i have given the path of my excel file ,still it didnt help me..because the moment i map the variable with the connection string of source excel connnection(property->expression->connection string) it start giving error if you could help me or provide some reference it will be of great help http://bi-polar23.blogspot.com/2007/08/loading-multiple-excel-files-with-ssis.html i tried this link but got stuck with it on step 12 and 14 (below  is my connection string) and variable is @Filename which i have created Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\mushtaq.sheikh\Desktop\ForEach\New Microsoft Excel Worksheet1.xlsx;Extended Properties="Excel 12.0;HDR=YES"; The moment i try to edit my connection string by comparing connection string provided in link it gives error(an OLEBD error has occured with error code 0x80040E73) on excel source Thanks in advance

How to create a SSIS package to import records form SQl Server 2008 tables to text files

I am a newbie to SSIS and would like to create a package to accomplish the task referenced in the above title. Will appreciate any links and pointers in the right direction.   Thanksakoranteng

Validation.Error giving strange behavior

I'm seeing some weird behavior with the Validation.Error attached event. I have a textbox bound to an object that implements the IDataErrorInfo interface. The textbox subscribes to the Validation.Error event. What is weird is that when you put the text box into an invalid state, the Validation.Error event gets triggered twice. Under .NET 3.5, the first time ValidationErrorEventArgs.Action == Removed. The second time ValidationErrorEventArgs.Action == Added. Under .NET 4, it's reversed. The first time ValidationErrorEventArgs.Action == Added. The second time ValidationErrorEventArgs.Action == Removed. Why would it be called twice and why would it work differently under .NET 3.5 and .NET 4? I'm trying to implement a validation summary control similar to the one from ASP.NET. There is a solution here: http://codeblitz.wordpress.com/2009/05/12/wpf-validation-summary-control/ but it doesn't work under .NET 4 because in order for it work work, when you put a control into an invalid state, the last call to the Validation.Error event has to be with ValidationErrorEventArgs.Action == Added. Here is my code:   <Window x:Class="WpfApplication1.MainWindow" xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation" xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" xmlns:local="clr-namespace:WpfApplicatio

SSIS 2008 generates Registration information for this application needs to be updated - VSTA.exe

I am getting the following error in SQL Server 2008 R2 (build 10.50.1734) x86 platform BI Development Studio (BIDS) 9.0.30729.4462 with .NET Framework 3.5 SP1 in a Integration Services project. I have a package with a Data Flow task that contains a Script Component. I edit it and attempt to edit script and I get the following error: Registration information for this application needs to be updated. To update, log on as an administrator and run this command: C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\VSTA.exe /HostID SSIS_ScriptComponent /setup About a minute later, another box displays with the following message Cannot show Visual Studio Tools for Applications editor. Additional information: The VSTA designer failed to load. “System.Runtime.InteropServices.COMExeption (0x80004005) Error HRESULT E_FAIL has been returned from a call to a COM component at VSTADTAProvider.Interop.VSTADTEProiderClass.GetDTE(String bysHostID, Uint32 dwTimeout). I execute the command above running as an administrator (there are no output results). BIDS displays the same symptoms. This happens on a Windows 7 Enterprise (I said Professional in an earlier but un-related post) workstation x86 platform. I get the error even if I create a new script component in a new data flow task in a new package. I also have VS 2010 Premium Edition (build 10.0.30319.1 RTMRel) i

SSIS 2008 generates Class not registered

I am getting the following error in SQL Server 2008 R2 (build 10.50.1734) x86 platform BI Development Studio (BIDS) 9.0.30729.4462 with .NET Framework 3.5 SP1 in a Integration Services project Data Source View table when I attempt to Explore Data: Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG)) This happens on a Windows 7 Professional workstation x86 platform, (but has not been observed on Windows XP). The error occurs on Data Source Views bound to a Data Source connected to a SQL Server 2005 SP3 (build 9.0.4053) and SQL Server 2008 R2 server (I'm sure it's not a server issue, but a workstation issue). I have successfully unregistered and re-registered DTS.DLL and DTSComm.DLL in the registry, with no resolution to the issue. Does anyone have any ideas? Thanks.Greg

Problem with Merge Join and condition splt trasnformations in SSIS 2008

  Hey, While working SSIS in 2008 we have encountering some weird problems with Merge, Merge Join and Condition Spilt transformations, here are the details... Condition Split and Merge Join and Condition Split: Please check the below diagram(not really just tried) for the complete details. Just FYI data is in sorted order   condition Split -> 420000      | 18                                                          | 419982  Merge Join(left)  -- src1(6000)      |10  Condition Split      |9     |1   the merge join transformation is not returning the complete 18 rows to the next transformation that's why we are missing 8 rows. But if we adds a sort transformation (getting warning as data is already sorted) before merge then complete 18 rows were passing to the condition split. Also tried modifing the source query just to return 18 rows then also it was passing the 18 rows to next trasnformation except with full load.       Merge: We have two sorted data sources and first one has 40000 records and second source have 12000 records and after the Merge transformation we have Condition Split transfor
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend