Issues with data transfer with SSIS and SQL Agent

Question

Issues with data transfer with SSIS and SQL Agent

HBhagat

Hall of Fame

Points: 3341
More actions
June 22, 2022 at 1:30 pm

#4053286

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.

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

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

Phil Parkin SSC Guru Points: 246979 More actions · Answer 1

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.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

HBhagat Hall of Fame Points: 3341 More actions · Answer 2

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.

Phil Parkin SSC Guru Points: 246979 More actions · Answer 3

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?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

HBhagat Hall of Fame Points: 3341 More actions · Answer 4

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

Phil Parkin SSC Guru Points: 246979 More actions · Answer 5

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?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

HBhagat Hall of Fame Points: 3341 More actions · Answer 6

HBhagat

Hall of Fame

Points: 3341

June 22, 2022 at 2:19 pm

#4053326

yes

Phil Parkin SSC Guru Points: 246979 More actions · Answer 7

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

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

HBhagat Hall of Fame Points: 3341 More actions · Answer 8

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.

Thom A SSC Guru Points: 99133 More actions · Answer 9

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

HBhagat Hall of Fame Points: 3341 More actions · Answer 10

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.

HBhagat Hall of Fame Points: 3341 More actions · Answer 11

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