0

This package had worked in the past but, for whatever reason, hasn't been working as intended recently. The package has a script task that sets a file path variable to the file path for the most recent csv file in a folder and then uses this file path variable in a ConnectionString expression used in the flat file connection. There is a little "massaging" done with the csv file, skipping top x rows (file header info), specifying the delimeter, text qualifier, etc. If I use a hard coded CSV file path and preview, all looks as expected. The flat file connection is specified as the source in the Data Flow Task and a SQL table is specified as the destination. I run the package in VS, and nothing errors. VS reports "Information: 0x4004300B at MyDataFlowTask, SSIS.Pipeline: "Destination - MySQLServer Table" wrote 871 rows." but when I go query the table, none of the rows are there. What might I be missing?

user3799279
  • 143
  • 1
  • 11
  • 1
    Checking the wrong server/table? Rows don't just "disappear". – Thom A Jun 12 '23 at 14:10
  • I've verified it's the correct server/table. This did work previously. It does work if I explicitly specify the csv file and run it. It is reporting the correct number of rows. I don't know that the rows necessarily "disappear" in as much as VS is reporting that they were written but in fact were not. I get the same message saying x rows were written whether I specify the filename/path explicitly in the flat file connection manager or if I use an expression using the filepath parameter for the connectionstring in the connection manager. But the latter appears to not actually write any rows. – user3799279 Jun 12 '23 at 14:25

1 Answers1

0

This isn't an answer in as much as a "what I did to move on from the issue." I never did find data actually being written anywhere. I created a new table in the same database and the data was written there as expected. I tried renaming the offending table (it had spaces in the table name) and still had issue (also checked for uncommited transactions and found none). I dumped the data from the offending table to the new table, removed the offending table and renamed the new table name equal to the offending table name and it worked fine. There were no constraints or triggers on the offending table, I didn't end up modifying the SSIS package at all. I just "changed out" the offending table and things then worked as expected. I have no idea where the records were being written "if" they were being written, but removing and recreating the table took care of it.

user3799279
  • 143
  • 1
  • 11