SSIS task not importing data sometimes...

[coder_t2]

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.

 

[Oyster]

Without looking at your workflow, I'd be hard to imagine what's happening where. I'd suggest you post pictures of your SSIS workflow (including the data flows) and link them here.

One immediate thing (assuming you're executing SQL statements) you can do is use the Profiler to monitor what is happening on the SQL Server side. Similarly, use the Enterprise Manager on the Oracle side to monitor the data extract session.

This could simply be a bug...

 

[ForumMaster]

while i agree with Oyster, i have a question: why do you truncate the table and then insert the data? This seems ineffective and also leaves you with a windows where the table is truncated.

Why not instead use merge?
Wiki Link
Oracle Link
Sql Server Link

This lets you update existing rows then they don't match the source, insert new rows when the don't exist, and also delete rows from the target that no longer exist in the source. Better then truncate i would say...

 

[coder_t2]

Merge looks useful, but I am using SQL Server 2005. It looks like merge only works 2008. Secondly, I am not sure how this would work for the dataflow, unless I import a temporary table with data and then merge it over. I'll be posting pictures in a bit.

 

[coder_t2]

Link to dataflow picture.

http://img64.imageshack.us/img64/5958/datafloe.png

 

[Oyster]

What are the warnings in your tasks?

Also, what is the logic you use for the derived column (that is, the SELECT statement from the OLE DB Source)? What is the command in your OLE DB Destination task?

Is there a reason you're not using the SQL Server Destination task? It bulk inserts and is super-fast.

Overall, this looks like a pretty simple workflow, but again, I am kind of thrown off by those warning signs you have...

 

[coder_t2]

The derived columns are just 4 new columns that are concatenations of other columns.

The OLE DB Destination Task is just the table that the data is getting imported to. So Table fast load, and Table Name.

I am not using SQL Server Destination because I kept getting a bulk insert error. I researched it a while back and it seems like it is just a bug in SQL Server 2005. The only solutions people came up with were to use OLE DB Destination instead.

I am going to post the warning in a minute. But it basically has something to do with determining the column types. I get them with every dataflow.

 

[coder_t2]

Here is the error.

http://img101.imageshack.us/img101/6266/errorzc.png

 

[Oyster]

I think I have seen that error, and our DBA did some magic to make it go away. I remember vaguely that it happened when SSIS makes the first request for the metadata.

In any case, a more constructive thing you can do immediately to debug is add a row counter and dump the extracted data to a CSV or a text file (via a flat file destination) between each data flow task. So, for example, before linking your OLE DB source directly to the derived column, link to the flat file destination and a row counter, and log these details. Next time you encounter the error, this should help you pinpoint the source of the error.

Also, I am assuming you have SP3 installed? There were a whole bunch of generic bugs fixed in SSIS in SP2 and in SP3. I have never ran into an error with a SQL Server Destination task (although your destination DB configuration might be different than mine).

 

[coder_t2]

Sorry for the delay in response. Long weekend plus other work stuff popping up. I do have SP3 installed. Only significant updates I see are SP2 for the .netFramework2.0 and 3.0. Going to avoid installing them for now since I don't think they will fix any SQL Server issues.

But I have moved on to replication, which may help solve this issue. And I was hoping to get some help with it. I am finally at the point where I have a replication userid with all the correct permissions. But I get a collation error when trying to set up the distribution. I tried changing the collation of the distribution database but I get the following error.

Warning: Changing default collation for database 'distribution', which is used in replication. All replication databases should have the same default collation.

I was hoping to avoid changing the collation of all databases for fear of it causing problems with reports that are already running. Any Ideas?

 

[Oyster]

I'd suggest you start a new post for your duplication issue. It's more of a DBA thing, and a feature that I haven't played with personally. I know we have got some SQL Server brains on the forum who may be able to respond contextually.

 

[coder_t2]

ok, i will start a new thread.

 

[Blueoak]

Did you copy the "OLE DB Source" task from another SSIS package? If you did, make sure the Connection is properly set inside it. It should be displaying the Connection Manager name itself and not a GUID.

 

[Oyster]

Did you copy the "OLE DB Source" task from another SSIS package? If you did, make sure the Connection is properly set inside it. It should be displaying the Connection Manager name itself and not a GUID.

If that was the case, the package wouldn't import data every time. Unless I missed something, the OP mentioned that the package is unable to import data occasionally.

 

[coder_t2]

If that was the case, the package wouldn't import data every time. Unless I missed something, the OP mentioned that the package is unable to import data occasionally.

Yea, its the fact that occasionally data isn't getting imported. Which is weird. My only assumption still is that sometimes the connection must get interrupted and the data never gets transferred. I am going to start importing the data into a separate table and then copy it over to the table used in reports. This way if nothing gets returned, I'll just be using a week old table versus reports not running correctly. The tables aren't updated much anyway.