Hey guys, I have a SSIS task that I run weekly that pulls data from a table on a Oracle server and imports into my SQL Server table. However, sometimes it seems like the data isn't importing. At times I go to access the SQL Server table and it is blank. The SSIS task does a TRUNCATE on the SQL Server table, then uses a dataflow to import the data from Oracle. The SQL Agent history says the task has completed successfully. Any ideas what is going on? Or where I should look to figure out the issue? Thanks.
![[H]ard|Forum](/styles/hardforum/xenforo/logo_dark.png){kind=logo}
SSIS completing but not importing data?
- Thread starter coder_t2
- Start date
Any errors being returned from the SSIS job?
How are you notified when a job is started, finished, and know when any error(s) occurred?
Is the SSIS job pulling from Oracle tables that might have a lock on them due to other interactions with the Oracle DB outside of your SSIS job?
How are you notified when a job is started, finished, and know when any error(s) occurred?
Is the SSIS job pulling from Oracle tables that might have a lock on them due to other interactions with the Oracle DB outside of your SSIS job?
I do not get any errors that I know of on the SSIS job. I have it set to fail on any errors, but it completes successfully according to the history.
The job is on a schedule. I currently have 0 notifications set up, but I am currently working on setting up a notification if the job fails. However, just to reiterate, the history does say the job completed successfully.
I called one of the DBAs and he said nothing of the sort was happening. I should probably call the other guy, just in case. Although I doubt anything should be happening that would cause a lock out, cause I have had multiple discussions about how I run stuff at night and that they need to let me know if they will be doing anything that would cause an issue.
The job is on a schedule. I currently have 0 notifications set up, but I am currently working on setting up a notification if the job fails. However, just to reiterate, the history does say the job completed successfully.
I called one of the DBAs and he said nothing of the sort was happening. I should probably call the other guy, just in case. Although I doubt anything should be happening that would cause a lock out, cause I have had multiple discussions about how I run stuff at night and that they need to let me know if they will be doing anything that would cause an issue.
You can attempt to debug this the long way if you choose.
Inspect your SSIS package and see the SQL statements its preforming. Execute each of those statements in SMSS and watch the result (be sure to pass any variables into it that may be created on the fly). Continue manually performing each step of the package to ensure that you are getting the expected output.
We had a similar issue a while ago, and it turns out one of our SQL statements was returning nothing, so none of the other executions on the SSIS package had any data to work with. They all passed "success" as a result, even though it didn't return the result we expected to see.
Inspect your SSIS package and see the SQL statements its preforming. Execute each of those statements in SMSS and watch the result (be sure to pass any variables into it that may be created on the fly). Continue manually performing each step of the package to ensure that you are getting the expected output.
We had a similar issue a while ago, and it turns out one of our SQL statements was returning nothing, so none of the other executions on the SSIS package had any data to work with. They all passed "success" as a result, even though it didn't return the result we expected to see.
You might want to add some extra logging or notifications as the SSIS process executes. If no rows are returned from your query, then you obviously won't get an error on the package. Add some additional step-through checks in your SSIS package for scenarios like this.
*Edit*
Walked away from the computer, posted when I got back, and saw that abudhu was thinking the same thing.
*Edit*
Walked away from the computer, posted when I got back, and saw that abudhu was thinking the same thing.
There are only 2 queries. A truncate on the local table and then the query to select the data.
Link to dataflow picture.
http://img64.imageshack.us/img64/5958/datafloe.png
Link to warning message on the dataflow.
http://img101.imageshack.us/img101/6266/errorzc.png
I get about 40million rows with this query.
Link to dataflow picture.
http://img64.imageshack.us/img64/5958/datafloe.png
Link to warning message on the dataflow.
http://img101.imageshack.us/img101/6266/errorzc.png
I get about 40million rows with this query.
What you're calling an error is actually a warning; it's pointing out that you're implicitly converting single-byte characters to Unicode without specifying a code page.
You haven't run the data flow; that's why it is showing no row counts at each step. When you run it, you can put a break point on it in the designer, and also look at the log in the UI to see what errors it might have generated.
You haven't run the data flow; that's why it is showing no row counts at each step. When you run it, you can put a break point on it in the designer, and also look at the log in the UI to see what errors it might have generated.
Ok, the query takes about 4 hrs to run, so I am going to wait to run it later today. But on a side note, I am trying to set up email notifications for my SSIS jobs, however I need to enable Broker on msdb. However, whenever I try to do that it just hangs. I believe this is because I am connected to the server through SSMS. Any ideas on how I can get broker enabled? Not really sure to how to enable it without using SSMS. Thanks.
/Side Question - Semi Related:
Why does the execution of the query take 4 hours to run? Are you querying the entire table data?
You should alter your statements to only pull 100-200 rows instead of everything. So you can verify the break more quickly.
Or is the execution taking that long due to hardware / network limitation?
Why does the execution of the query take 4 hours to run? Are you querying the entire table data?
You should alter your statements to only pull 100-200 rows instead of everything. So you can verify the break more quickly.
Or is the execution taking that long due to hardware / network limitation?
I am querying the whole table. I am currently trying to work with the DBA to create a replication user so I can just replicate the table. But in the mean time I have to do it this way until we can get that set up. We have been running into issues where SQL Server keeps saying the user doesn't have the correct privileges.
This SSIS task has worked fine up until last week. When it didn't populate the table. I have another SSIS task that is similar, that seems to not populate the table either quite often. So I am trying to figure out the issue.
So what I am trying to say is that it has been working fine for 2 months, and the past week it had some sort of hiccup that made it not work.
This SSIS task has worked fine up until last week. When it didn't populate the table. I have another SSIS task that is similar, that seems to not populate the table either quite often. So I am trying to figure out the issue.
So what I am trying to say is that it has been working fine for 2 months, and the past week it had some sort of hiccup that made it not work.
As you mentioned (or hinted at) I would look into the user permissions on both the SQL and ORACLE side.
If your accounts have any timeouts (oracle) then this might explain why you can't query anything. Perhaps the account has become locked.
What is the issue with the SQL User? If you login to SMSS as that user can you run queries and selects? If not check to see the permissions on the DB for the user.
You might want to try issuing the following command as well:
http://technet.microsoft.com/en-us/library/ms174378.aspx
To resolve any permissions errors with the DB against the user.
If your accounts have any timeouts (oracle) then this might explain why you can't query anything. Perhaps the account has become locked.
What is the issue with the SQL User? If you login to SMSS as that user can you run queries and selects? If not check to see the permissions on the DB for the user.
You might want to try issuing the following command as well:
http://technet.microsoft.com/en-us/library/ms174378.aspx
To resolve any permissions errors with the DB against the user.
Ok thanks. I'll look into it for my SSIS Tasks. Right now I am actually focusing on getting a Replication set up for SQL Server. However, I keep getting Error: 21684. The error says I have insufficient priveleges on Oracle. I have been working with our Oracle DBA to get this taken care of and he says that he has given me all the privileges directly to the user, not through a role. I checked and with "SELECT * from session_privs" and all looks in order. So not sure what else the problem could be. I have spent quite a bit of time on google trying to figure out, to no avail. Any ideas guys? Thanks.
Why do you believe a log in failure would allow the task to complete successfully?
Before, you said the task was failing without errors. Now, you're saying that the task is failing with this error. Or are you saying that setting up replication is failing with that error? The error number makes it look like that's a message from the Microsoft Oracle data provider.
Have you solved your problem with broker yet?
Seen it happen with poorly written SSIS packages at my last job is why. Our packages would pass the Oracle credentials, and if locked -- could not query the table, thus return 0 rows, but would continue through and manipulate 0 rows through the rest of the package. It would state completed success -- though it did nothing.
Hope that clears up why I might have though that was a similar case here.
Sorry for being unclear. I meant that setting up replication was giving me an error. That error has been fixed, but running into a new one now for replication. I know I switched topics which may be confusing, just different things have taken priority, and right now I am doing my best to get replication set up. But I'll eventually get back to my original post.
Ok now I am running into a collation error with replication. I tried changing the collation on the distribution database but I get this Warning.
Warning: Changing default collation for database 'distribution', which is used in replication. All replication databases should have the same default collation.
I am trying to avoid changing the collation on all the databases, so I don't run into collation errors with stuff I already have set up. Any ideas?
Warning: Changing default collation for database 'distribution', which is used in replication. All replication databases should have the same default collation.
I am trying to avoid changing the collation on all the databases, so I don't run into collation errors with stuff I already have set up. Any ideas?