CSV INPUT SSIS Visio Studio 2019

John Burks 11 Reputation points
2022-07-10T11:20:28.97+00:00

I finally got SSIS to work and loaded in Visio. I can create a table to my SqlServer with conversion errors. I'm trying to create a new column but it fails because of calculation/computation. If create a new table the data flows over . If I try to append it to the existing table the new column doesn't flow over. I notice on the csv input and I looked at the config, I notice all the columns have conversion errors. I tried changing all columns to unicode_string[DT_WSTR] and that didn't work. I tried using the suggested data type and that didn't work. I also used the conversion tool and that didn't work. Not sure what else to use.

219281-error-csv-input.png

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,645 questions
{count} votes

6 answers

Sort by: Most helpful
  1. Olaf Helper 46,031 Reputation points
    2022-07-11T07:39:01.6+00:00

    cannot insert duplicate key in object 'dbo.Testdata2'. The duplicate key value is (10107).

    You got a pretty clear error message, so what are you complaining about?

    1 person found this answer helpful.
    0 comments No comments

  2. John Burks 11 Reputation points
    2022-07-11T01:33:51.713+00:00

    This software is horrible …one minute it works the next it doesn’t. Alteryx is way better than this. This software is full of problems you can create the same workflow one minute it works the next it doesn’t

    0 comments No comments

  3. John Burks 11 Reputation points
    2022-07-11T01:47:35.5+00:00

    See the workflow I created. It puts the data into the database sometimes but I have a bunch of errors. This is going into an empty table.

    219248-error-ssis.png

    Error: 0xC0202009 at Data Flow Task, OLE DB Destination [136]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E2F.
    An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E2F Description: "The statement has been terminated.".
    An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E2F Description: "Violation of PRIMARY KEY constraint 'PK__Testdata__9F66DFD322BCC41B'. Cannot insert duplicate key in object 'dbo.Testdata2'. The duplicate key value is (10107).".
    Error: 0xC0209029 at Data Flow Task, OLE DB Destination [136]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "OLE DB Destination.Inputs[OLE DB Destination Input]" failed because error code 0xC020907B occurred, and the error row disposition on "OLE DB Destination.Inputs[OLE DB Destination Input]" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
    Error: 0xC0047022 at Data Flow Task, SSIS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "OLE DB Destination" (136) failed with error code 0xC0209029 while processing input "OLE DB Destination Input" (149). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.

    0 comments No comments

  4. ZoeHui-MSFT 40,661 Reputation points
    2022-07-11T08:16:51.867+00:00

    Hi @John Burks ,

    It's clear that you are inserting duplicate key into your table and then thrown the error.

    Which is why > one minute it works the next it doesn’t.

    Make sure the data source does not have duplicate values on the fields that are part of the OLEDB Destination primary key. To remove duplicates that exist on the data source you can use the Sort Transformation Task (SSIS Toolbox -> Common -> Sort) and make use of the checkbox named "Remove rows with duplicate sort values". You can find a practical example here.

    You can use the Lookup Transformation within the Data Flow task to check for data that already exist on the destination table.

    lookup-transformation-in-ssis

    Regards,

    Zoe


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

  5. John Burks 11 Reputation points
    2022-07-11T10:44:04.37+00:00

    That’s the problem the table is empty all I’m doing is moving the csv data from the file to an empty table.What I’m not supposed to bring in the key data. What would be the point of bring in the data without the keys. Again the data is going into an empty table. I do it with Alteryx with no issues. I can use Python and it has no issues.Even when it creates a new table it ssis throws errors.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.