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

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

Using User Variables (ONLY) in SSIS Conditional Split

Posted By:      Posted Date: September 10, 2010    Points: 0   Category :Sql Server
After considerable effort I have not been able to perform a successful conditional split on a user variable. The idea is that I am reading a flat file, and I place the number of rows read into a variable (variable A). I then do some data conversion and perform another row count (variable B) I would like to do a conditional split if the value of variable A <> value of variable B. I am not referring to any of the query columns in making this comparison. It is always evaluating to the value of the variables before the package is run. For example if variable a=0 and variable b=0 at the start of the package, the conditional split evaluates to true (since 0=0) What I am failing to see is a's value getting updated and b's value getting updated in the conditional split. I have added watches for the variable and they are updating, but the boolean a==b is not. Any help is appreciated.

View Complete Post

More Related Resource Links

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.

Adding exception table to incremental Load with SSIS Lookup task and conditional split



I have built an incremental load ETL Process to load some flat files with an SSIS Lookup and Conditional Split. I only have one path in the conditional split and that is for New Records.

I have two questions:

1.       For the new records path, I have to check to see if a record exists and I don’t have a single key that is unique, therefore, I have to use a multiple keys to make the record unique.


Below is what I have put in the conditional transformation editor for my first output condition:


DateTime Validation in SSIS using conditional split



I am facing issue with null validation for DateTime. I have input csv file which contains DateTime field as Date datatype. In the destination database table, I am storing this field as datetime. I am using conditional split feature of SSIS package and try to validate this field before inserting into the database.

The value which is going into the db is "1899-12-30 00:00:00.000". I dont know why?

I have tried various expressions like ISNULL(DateTime), LEN(DTSTR, DateTime) == 0, ISNULL(DateTime) ? False : DateTime. Nothing is working.

Req is simple, if date is valid insert in db otherwise write is as bad data in error log file.

Can anyone provide suggestion on this.




SSIS Conditional Split


 i created a small DTS flow which is fetching data from OLD DB source to two  flat files using condition given in Conditional Split Transformation


Conditional Spilt has two condition according to source table column as given

Case1      Department == "IT"

Case2      Department == "Management"

Case3      Department == "Sales"

now as per this Case "IT", "Management" and "Sales" i am generating the flatfiles in three location C:\IT , C:\Management , C:\ Sales folder respectevly


this package is running fine but here the logical problem occurs, All the files are generating with accurate records in there respective folders but simultanuasly two other file also generating in all folders. which is not required,

it seems the condition which get satisfied executes and generate the file in there respective folders and fill the record in that file but other 2 case2 is also executing simultaneously and generating blank files

Assistance is highly appreciable.

Thanks in advance






Creating SSIS Package Variables Programmatically

Hi There, I am fairly new to SSIS.  I am creating a number of custom SSIS Tasks that I want to be able to share information between at runtime.  I figured that the best way to do this was through package variables, as in the Execute() method each custom task has access to the package variables through the VariableDispenser object.  I have managed to read variables at runtime that I create in the designer, however I am having trouble creating and writing to them.  I have created a very simple custom task below that I would expect to create the specified variable... [DtsTask(DisplayName = "TestVariableWriter")] public class Variab : Task { public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log, object transaction) { try { string varName = "TestVariable"; string varValue = "TestValue"; Variables vars = null; variableDispenser.LockForWrite(varName); variableDispenser.GetVariables(ref vars); if (variableDispenser.Contains(varName)) vars[varName].Value = varValue; else vars.Add(varName, false, string.Empty, varValue); vars.Unlock(); return DTSExecResult.Success; } catch (Exception ex) {

if ssis package writes to excel file that might be open by a user

I have written a package that writes to an excel file to a network folder. Eventually this package will run as a job. What is the best way to handle the situation where someone opens the file or inadvertantly leaving it open while the package is running and and stop the package.? No one needs to modify or write anything to this file after it is finished

SSIS Query requiring two variables

I have a query in which I need to use two variables. How do I implement this in SQL task editor for the intake of two variables????

what is the purpose of "evaluateasexpression" in SSIS variables? I mean when I need to set it as TRU

Friends, I have some SSIS package which is using variables. I built an expression for some purpose and I am using the following expressin in my ssis package. "_GROUP" + (DT_WSTR, 2) (((DT_I4 )(SUBSTRING( @[System::MachineName] , 12, 3 )) + 1)/2) + "_" The purpose of this is to get a group number basing on Machine name (taking the last two digits in the machine name) and dividing that number by 2. I am getting data conversion error if evaluateasexpression is set to TRUE. Can some tell me why? Thanks in advance.

C# Allowing user to select variables to use in a foreach loop

Hi guys, First project, first post. My app builds webpages and personalises the content by running a foreach loop through rows in a dataset. My original web page build was hardcoded and works fine. I want to take this one stage further and allow users to paste their own html into a text box and use button controls to insert values from the dataset arrays.  Doing this however leads to the app writing the string verbatim. Some examples at this point might help:  Original code here System.IO.StreamWriter objWriter; objWriter = new System.IO.StreamWriter(webpage);                     objWriter.WriteLine("<!DOCTYPE HTML PUBLIC '-//W3C//DTD HTML 4.01 Transitional//EN' 'http://www.w3.org/TR/html4/loose.dtd'>"); objWriter.WriteLine("<head>"); objWriter.WriteLine("<title>" + row.ItemArray.GetValue(0).ToString() + "'s very own webpage" + "</title>";); objWriter.WriteLine("<meta http-equiv='Content-Type' content='text/html; charset=iso-8859-1'>"); objWriter.WriteLine(""); objWriter.WriteLine("</head>"); objWriter.WriteLine("<body>"); objWriter.WriteLine("<h1>Well hello there " + row.ItemArray.GetValue(5).ToStri

SSIS: How to set the values of the variables, other ways of setting the values of the variables?

Hi Gurus, Is this the only way in setting the values of the variables. Please see steps below: 1. Build the SSIS Project. From the package's property pages, Deployment Utility, set the CreateDeploymentUtility to TRUE (It will create a Deployment Folder and inside the folder 3 files will be created a) SSISDeploymentManifest b) dtsConfig c)dtsx ) 2. Double click the SSISDeploymentManisfest file. A Package installation will be set up. 3. Follow the steps until you reach the Configuration Packages: Edit package configurations window. 4. From the Configuration part, set the values from the Values(column). 5. To Finish. Is this the only way of setting the values of the variable?

SSIS User Defined Data Type (Alias Data Types) and OLE DB Command validation

Hello everyone, I've been having an issue with trying to run my SSIS package on a server, and it seems to be failing on the OLE DB Command step.  What we have in our SQL 2005 DB, is a User-Defined Data Type (base type char(7)) and the OLE DB Command is supposed to call a proc that passes in a value of this data type. ie:  CREATE PROCEDURE myProcedure ( @passedInFromSSIS MY_DATATYPE ) AS .... In my SSIS package, I have the type defined as DT_STR with a length of 7.  Now, when I run the package locally (via Visual Studio), the process runs with success.  However, once the package is deployed on a server and run from an application (note: it is run under a different user), the process fails on a validation step with a "Invalid Parameter Number" error. Now, if I change the input parameter in my proc to the base type of the user-defined data type, the process works again. Has anybody run into a similar issue or know what may be causing this issue?  I first suspected perhaps I needed to grant permissions on the user-defined data type (since I was able to run it under my security context, but not under the application's), however noticed that there isn't security tied to the types.  Any other thoughts?  Please let me know if you need further explanation.  Thanks!

start SSIS package via script task with package configuration via variables



it is possible to start ssis packages with dtexec and set variables as package configuration like this:

dtexec.exe /SQL "\mart1\import_fact_calls" /SERVER "R08" /SET \package.variables[FullImport].Value;True

Now I'm trying to start a package via script task with this code:

Public Sub Main()
    Dim pkg As New Package
    Dim app As New Application
    Dim pkgResults As DTSExecResult

    If Dts.Variables("package").Value <> "" Then
      pkg = app.LoadFromSqlServer(Dts.Variables("package").Value, "R08", "USER", "PASS", Nothing)
      pkgResults = pkg.Execute()
    End If

    Dts.TaskResult = ScriptResults.Success
End Sub

How can I add a package configuration like the one with dtexec to the script code?

I like any ideas, links, docus ... Thanks!

how to use split in iif conditional expression in ssrs 2008 r2



how can i use conditional iif combined with split?

i'm trying to get values from check boxes(from sharepoint custom list), the condition is like this :

if the value is null/blank, the return value would be "-"

if the value is not blank, the return value would be the value of the check box field that already splitted from the ";#" character

as you might know, values from check box is written along with ";#" character before and after the value itself.

i tried to use this expression:

=IIF(Fields!Action__For_User_Access_.Value Is Nothing," - ",(Split(Fields!Action__For_User_Access_.Value, ";#")(1)))

the split worked just fine, but the conditional iif didn't work.

can anybody help me?

i used that expression in the calculated field.

btw, i'm using report builder content type from ssrs 2008 r2


thx in advance.



Conditional Split



I have a problem using conditional split,

I've got the raw txt data that i need to insert into sql database, having set the delimiter as ",", i encountered a problem;

Raw data example,



00101,Andrew, Jackson, Nurse


Data inserted to SQL ended up to be...

ID Name Job
00100 Harris Student
00101 Andrew Jackson ,Nurse

But when i initiate the conditional

If it is inserted correctly to the SQL Database,

It should be... (Take note of "Jackson")

SSIS package not able to save encypted user sensitive password


i have an ftp fetch package in bus development studio 2008.  it runs from that interface.  i imported it into Integration Services, set the password along with the EncryptSensitiveWithPassword and if i manually enter password each time i can run successfully from sql integration services.  but i cannot schedule as a job because the password won't stick.

any help out there would be great.

Can a user with SQL express deploy a SSIS to a remote SQL server (how)


sql express does not have integration services.

dtutil does not work without integration services.  This is therefore not an option.

The deployment manifest needs dtutil to execute.  This is also not an option.

The documentation on microsoft states that a person can connect to remote integration services by editing the ini file and then connecting locally. http://msdn.microsoft.com/en-us/library/ms137789.aspx.  Without integration service, one can not connect locally to integration services so using the transfer package of sql server is also not an option.

Also connecting remotely to an integration without editing the ini seems to require local admin rights on the target server.  This is a bit too much to ask.  (this is what I experimented let me know if you know the exact requirements)

Is there a possibility for a person with sql express, without local admin rights to the target server, to push his ssis to his sql server test instance?

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