SSIS package and package in a SQL server agent Job
-
Hello All, I have a SSIS package wherein the first step is excution of C# program to convert a csv file to excel file. The second step is the loading of this excel file into a table. This programs with no errors when execute from IDE. Now, i am setting the process as SQL server agent Job, and i give the path to the package for execution. The job fails with message that "Access is denied". I moved the package to C:\ drive. Now the program fails on first step with null reference. why the difference in results when executed from SSIS package environment versus the SQL server agent job? I did resolve this issue by removing the C# program execution and set it up as scheduled task, but would like to understand the behaviour. Many thanks
-
Hello All, I have a SSIS package wherein the first step is excution of C# program to convert a csv file to excel file. The second step is the loading of this excel file into a table. This programs with no errors when execute from IDE. Now, i am setting the process as SQL server agent Job, and i give the path to the package for execution. The job fails with message that "Access is denied". I moved the package to C:\ drive. Now the program fails on first step with null reference. why the difference in results when executed from SSIS package environment versus the SQL server agent job? I did resolve this issue by removing the C# program execution and set it up as scheduled task, but would like to understand the behaviour. Many thanks
-
Sorry I can't help with the problem, but I am intriqued as to why you would convert a CSV to "excel" just to load it to a table... the step is totally unnecessary unless you need the xlsx file for something else as well.
There is one field in the csv file, a freeform field. The users have ability to type anything in this field, along with special characters. Since there was no common delimiter, had to go with converting the csv file to excel file route to load this file using SSIS.
-
Hello All, I have a SSIS package wherein the first step is excution of C# program to convert a csv file to excel file. The second step is the loading of this excel file into a table. This programs with no errors when execute from IDE. Now, i am setting the process as SQL server agent Job, and i give the path to the package for execution. The job fails with message that "Access is denied". I moved the package to C:\ drive. Now the program fails on first step with null reference. why the difference in results when executed from SSIS package environment versus the SQL server agent job? I did resolve this issue by removing the C# program execution and set it up as scheduled task, but would like to understand the behaviour. Many thanks
You should specify UNC paths when you're loading files in your packages. That way, it will work no matter where you put the file (as long as you're using the correct UNC path of course). BTW, you can write a script task in a package that will import that data (as opposed to running an external application). I wrote an article not to long ago that includes code to import excel and CSV files - SQLXAgent - Jobs for SQL Express - Part 3 of 6[^] I think you might find it useful. I'm currently extracting that code into it's own assembly for easier inclusion into projects like yours. It even goes to some length to determine the data type represented in each column.
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013 -
There is one field in the csv file, a freeform field. The users have ability to type anything in this field, along with special characters. Since there was no common delimiter, had to go with converting the csv file to excel file route to load this file using SSIS.
