March 20, 2013 at 1:16 pm
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
March 20, 2013 at 3:38 pm
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?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 20, 2013 at 3:51 pm
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.
March 20, 2013 at 3:53 pm
Any package configurations that might steer the package to another source?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 20, 2013 at 3:56 pm
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.
March 20, 2013 at 3:59 pm
What's the source?
Which conditions are used in the conditional split?
Under which account is the SQL Server Agent job run?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 20, 2013 at 4:27 pm
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.
March 21, 2013 at 3:25 am
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?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 21, 2013 at 1:20 pm
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.
March 21, 2013 at 1:43 pm
Ah, great that you found the solution and thanks for posting back!
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply