Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS Conditional Split not working when run on SQL 2008 R2 using SQL Server Agent


SSIS Conditional Split not working when run on SQL 2008 R2 using SQL Server Agent

Author
Message
Jason.Robinson
Jason.Robinson
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 94
I'm having an odd problem after upgrading our SQL 2008 server to 2008 R2: An SSIS package containing a conditional split is not passing rows through its outputs according to the set conditions when executed via a SQL Server Agent job. The SSIS package operates normally when run from Visual Studio, and it runs normally when executed via Management Studio connected to the SSIS instance--it only has issues when run via the SQL Agent. Anyone experience this before or have ideas on what to check?

Note that I did not yet upgrade the SSIS package, so it's still in the SQL 2008 format (not R2).

Current SQL Server 2008 R2 version: 10.50.1600
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18659 Visits: 13249
There's almost no difference between 2008 and 2008R2 in SSIS and I would be surprised if 2008R2 introduced a bug that isn't fixed yet.
So I would look into another direction. Are you 100% sure you are executing the same package as in BIDS?


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Jason.Robinson
Jason.Robinson
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 94
Thanks for the reply. I'm sure I'm running the same package in BIDS. Just to confirm, I backed up the original in SSIS, then saved the package I'm testing in BIDS over the old one in SSIS. The package also runs just fine when I use "Run Package" from SQL Management Studio connected to the SSIS instance, where it isn't possible to accidentally run a different package.
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18659 Visits: 13249
Any package configurations that might steer the package to another source?


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Jason.Robinson
Jason.Robinson
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 94
Nope. I should also point out that the package itself doesn't fail, as other operations it's doing are executed correctly and the package reports "success." It's just the part of a Data Flow using a Conditional Split component that stops passing along rows when run via SQL Agent.
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18659 Visits: 13249
What's the source?
Which conditions are used in the conditional split?
Under which account is the SQL Server Agent job run?


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Jason.Robinson
Jason.Robinson
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 94
The source is a small CSV file with something around 10 rows. I'm using a flat file connection.

The conditions are checking two string fields for different value combinations. There are three conditions total:

CreditDebit == "CREDIT" && TransCode != "750"
CreditDebit == "DEBIT"
CreditDebit == "CREDIT" && TransCode == "750"

The first two conditions are met when running via BDIS and SSIS directly, but pass no rows when run via SQL Agent. There's nothing in the source file that meets the third condition.

The account used by the SQL Agent is a dedicated Windows-integrated account. We use a Windows-integrated account because both the source and target files reside on other servers.
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18659 Visits: 13249
Jason.Robinson (3/20/2013)
The source is a small CSV file with something around 10 rows. I'm using a flat file connection.

The conditions are checking two string fields for different value combinations. There are three conditions total:

CreditDebit == "CREDIT" && TransCode != "750"
CreditDebit == "DEBIT"
CreditDebit == "CREDIT" && TransCode == "750"

The first two conditions are met when running via BDIS and SSIS directly, but pass no rows when run via SQL Agent. There's nothing in the source file that meets the third condition.

The account used by the SQL Agent is a dedicated Windows-integrated account. We use a Windows-integrated account because both the source and target files reside on other servers.


What if you login into the server using the dedicated Windows account, log into SSMS and run the package there?


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Jason.Robinson
Jason.Robinson
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 94
I figured out the problem: There were double quotes enclosing the tested fields in the data source, but not other fields. 2008R2 appears to pass the quote marks down from the data source whereas 2008 does not. The Conditional Split was not getting matches becuase of the quote marks.

I determined this by trying the package from BIDS on the 2008R2 server itself rather than my workstation. I could then see what was going on in the data flow using data viewers.

It appears that 2008R2 handles text delimiters differently. We had another problem that I fixed before posting this one: We originally had the target files' text delimiters set to nothing (blank). After upgrading to 2008R2, the destination files started showing "_x003C_none_x003E_" as the text delimiter. This problem was common enough for me to find a quick solution on the Web. I assume that the issue with the source file is similar. The source has no text delimiters, but three of the fields are encased in double quotes. From what I can tell, 2008 dropped the quotes around those fields, but 2008R2 (correctly) considers them part of the field contents since they are not delimitiers.

I ultimately fixed the problem by using REPLACE to replace the double quotes with empty strings before the Conditional Split. I could have just changed the conditions, but then the package would work from the SQL Agent but not from my workstation.

Thanks, Koen, for the help. I was trying to think of how I could login to SSIS using the SQL Agent account when it occured to me to try BIDS from the server.
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18659 Visits: 13249
Ah, great that you found the solution and thanks for posting back!


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search