February 8, 2013 at 1:16 pm
We are in the process of moving to a new SQL 2012 production database, and one of the things on my plate is to convert some SSIS packages.
One in particular has me frustrated.
Basically what it does is run a for-each loop against a folder. The folder will contain multiple comma-delimited text files. The package imports each one into a table, and then moves it to an archive folder. It was originally created in SSIS 2005.
I pulled it into Visual Studio 2010 (the Data Tools version that installs with SQL Server 2012) and am able to run it successfully against the new server. I have verified that it does everything it needs to do; it truncates the temp table, loops through the files in the folder and loads each one, and moves each one to the archive folder. Everything is good.
I've also uploaded the package into SSIS on my SQL Server, and if I execute the package from there, again everything works great. I've verified that it does everything it needs to do.
The problem is when I try to run the file as part of a SQL job. It finishes with a "Success" status, but it doesn't actually do the file imports or the file moves. In the job history I see the following:
[font="Courier New"]Description: The connection manager "Files To Import" is an incorrect type. The type required is "FILE". The type available to the component is "FLATFILE". End Error Error: 2013-02-08 15:05:32.81 Code: 0xC020801E Source: ImportCallLogs Log provider "{AD5A122D-D7A4-4EA1-9298-9F11952C44BD}" Description: The connection manager "Files To Import" is an incorrect type. The type required is "FILE". The type available to the component is "FLATFILE". End Error DTExec: The package execution returned DTSER_SUCCESS (0). Started: 3:05:30 PM Finished: 3:05:33 PM Elapsed: 2.683 seconds. The package executed successfully. The step succeeded.[/font]
I've looked at the connection manager and I can't see any way to change it from FLATFILE to FILE or vice-versa. In fact, I don't even see that property anywhere.
If anyone has any idea how to resolve this, it would be greatly appreciated.
February 8, 2013 at 1:47 pm
Fixed it myself.
It turned out to be an access issue - the account that SQL Server Agent was running under didn't have rights to the folders on the network. Not sure why the job didn't throw an error like "Access Denied" or something along those lines, instead of "Success" but oh well...
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply