Issues with data transfer with SSIS and SQL Agent

  • Hi,

    I am having a strange problem with the SSIS .

    A simple insert from Source to destination is working perfectly. It gets me 19 rows into my destination.

    The moment the SSIS package is running via SQL Job, its getting only 9 rows.

    I have given all the level of user permission to the destination database and server but still the problem persists.

    Any help will be appreciated.

  • I suggest you identify the 10 missing rows and look for something that differentiates them from the 9 that make it. Also confirm that the data source and target are exactly the same as the ones which you are testing on.

    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.

  • I had checked the data.

    When I run the ssis package via SSIS package it runs without any issues.

    But the same package I am running, its not getting the full data. There are no issues with the server connection.

  • HBhagat wrote:

    I had checked the data.

    When I run the ssis package via SSIS package it runs without any issues.

    But the same package I am running, its not getting the full data. There are no issues with the server connection.

    OK. So what differentiates the rows?

    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.

  • I cannot see any difference between the data. Its consistence with the ignore list of the data.

     

  • HBhagat wrote:

    I cannot see any difference between the data. Its consistence with the ignore list of the data.

    You mean exactly the same rows are selected and ignored every time?

    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.

  • yes

  • OK, weird. If you run the package manually from SSISDB, by right click and Execute, does the problem still occur?

    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.

  • I am running the package via file system.

    I have server admin access to the server.

    If I execute the package via SSDT or from the folder(after double clicking on it) , it gives me all the data. The moment I run that via SQL job, it provide me those 9 rows only.

  • Can you not use SSISDB? The logging available would significantly make this easier to see where things might be going wrong. The deployment method is far better than both the old File System and MSDB deployment methods.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • Sorry, I am not able to deploy it as a package. I have to create a different solution etc to make it do.

    I had done error handling also, but there are no errors in the package.

  • Hi, I am comparing the data and manually inserting via ssis package, so I can see one or two been inserted into the table.

    I will try more with the data which is not entering and will let you know.

    Thank you

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply