March 14, 2018 at 10:24 am
I'm on day 3 of trying to address an error and Google isn't helping. While I've found forum/blog posts where people have received this same error, none of their situations apply to mine. Most responses point to a date field being the wrong format or pulling in blank fields from a flat file source.
Error:
Error: 0xC0202009 at Data Flow Task, OLE DB Source [9]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E21 Description: "Invalid character value for cast specification".
Error: 0xC004701A at Data Flow Task, SSIS.Pipeline: OLE DB Source failed the pre-execute phase and returned error code 0x80040E21.
Goal:
I'm trying to create a package that runs a report 86 times into 86 different csv files. I've read about the data driven reports option for SSRS but we don't have enterprise version of SQL Server. I know people have figured out a work around and have blogged about it for the Standard version of SQL server. The 4-5 blogs I've found leave pretty big holes and I'm unable to bridge the gap to figure out a solution for what I need it for so I'm using SSIS.
Info:
SQL 2016 SSIS
I have a sql task that feeds chapter numbers into a result set. I've connected that sql task to a Foreach Loop container that uses Foreach ADO Enumerator. Within the foreach loop container, I inserted a data flow task that pulls from an ole db source and into a flat file destination. The ole db source utilizes a parameter pointing to the record set from the sql task. The sql script in the ole db source is only pulling one field....it's an account id field. I was pulling 42 fields but have narrowed it down to 1 for troubleshooting purposes.
Can anyone give me some direction on what to look for?...or troubleshooting steps?
TIA,
John
March 14, 2018 at 10:38 am
J M-314995 - Wednesday, March 14, 2018 10:24 AMI'm on day 3 of trying to address an error and Google isn't helping. While I've found forum/blog posts where people have received this same error, none of their situations apply to mine. Most responses point to a date field being the wrong format or pulling in blank fields from a flat file source.Error:
Error: 0xC0202009 at Data Flow Task, OLE DB Source [9]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E21 Description: "Invalid character value for cast specification".
Error: 0xC004701A at Data Flow Task, SSIS.Pipeline: OLE DB Source failed the pre-execute phase and returned error code 0x80040E21.Goal:
I'm trying to create a package that runs a report 86 times into 86 different csv files. I've read about the data driven reports option for SSRS but we don't have enterprise version of SQL Server. I know people have figured out a work around and have blogged about it for the Standard version of SQL server. The 4-5 blogs I've found leave pretty big holes and I'm unable to bridge the gap to figure out a solution for what I need it for so I'm using SSIS.Info:
SQL 2016 SSIS
I have a sql task that feeds chapter numbers into a result set. I've connected that sql task to a Foreach Loop container that uses Foreach ADO Enumerator. Within the foreach loop container, I inserted a data flow task that pulls from an ole db source and into a flat file destination. The ole db source utilizes a parameter pointing to the record set from the sql task. The sql script in the ole db source is only pulling one field....it's an account id field. I was pulling 42 fields but have narrowed it down to 1 for troubleshooting purposes.Can anyone give me some direction on what to look for?...or troubleshooting steps?
TIA,
John
What does the input data look like? What is the data type of the 'External Column' to which the input data column is mapped?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
March 14, 2018 at 10:38 am
Seems likely that something in a character based column is being CAST into a more restrictive data type, such as date or any kind of numeric, and that some data just isn't going to succeed at that. I'd try running a query in SSMS against the source data and doing all the CAST or CONVERT things that take place in the package and figure out which one of them fails It may be more than one of them,,,
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
March 14, 2018 at 11:50 am
I'm not using CAST() or CONVERT() anywhere in my scripts.
Turns out I had the wrong variable setup in the Variable Mappings section of the Foreach Loop editor.
Thanks for the responses. I've got another question but I'll start a new post for it.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply