Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS Conditional Split not working when run on SQL 2008 R2 using SQL Server Agent Expand / Collapse
Author
Message
Posted Wednesday, March 20, 2013 1:16 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 29, 2014 9:28 AM
Points: 17, Visits: 91
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
Post #1433442
Posted Wednesday, March 20, 2013 3:38 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:54 AM
Points: 13,562, Visits: 11,371
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1433512
Posted Wednesday, March 20, 2013 3:51 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 29, 2014 9:28 AM
Points: 17, Visits: 91
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.
Post #1433520
Posted Wednesday, March 20, 2013 3:53 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:54 AM
Points: 13,562, Visits: 11,371
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1433524
Posted Wednesday, March 20, 2013 3:56 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 29, 2014 9:28 AM
Points: 17, Visits: 91
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.
Post #1433527
Posted Wednesday, March 20, 2013 3:59 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:54 AM
Points: 13,562, Visits: 11,371
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1433531
Posted Wednesday, March 20, 2013 4:27 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 29, 2014 9:28 AM
Points: 17, Visits: 91
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.
Post #1433540
Posted Thursday, March 21, 2013 3:25 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:54 AM
Points: 13,562, Visits: 11,371
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1433657
Posted Thursday, March 21, 2013 1:20 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 29, 2014 9:28 AM
Points: 17, Visits: 91
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.
Post #1433998
Posted Thursday, March 21, 2013 1:43 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:54 AM
Points: 13,562, Visits: 11,371
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1434010
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse