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.
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.
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
I have taken a backup of Database A from Server A1 then I restored this Database with name B on Server B1, now the application is working fine with Database A on Server A1 but not working with Database B on Server B1, I have checked the users, I have the users and I have given access to the users when it didn't work I have dropped the User which is connecting from Web application and recreated that user and granted the same permissions it had previously on this Database but still I am not able to use the application.
Here is the error that I am getting:
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
Any help would be greatly helpful thanks in advance.
"There is already enough hatred in the world lets spread love, compassion and affection."
It seems to be correct.
My Logs are in C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Log
Alternatively you can see the log in SSMS under Object Explorer -> Management -> SQL Server Logs
What is the nature of this job? Does it create records ? Or read the data? How big is this database ? How are many disks are installed on the server? How are they being used by the database engine? Are the Operating System and database files on different disks?
On the database server from a command prompt type "perfmon" to bring up the Performance Monitoring Utility. You want to add a counter for Logical disk / Avg Disk Queue Length (All disks) Look at the graph while the job is running, if you see high activity on one disk and low activity on another disk, you may want to consider moving some DB files around.
Here are a couple of things to consider:
1) Count the number of rows in the tables involved. Check these values daily, weekly, monthly.
2) Identify the tables that are growing.
3) Are there indexes on these tables? Check the fragmentation of the indexes. Possible create new indexes or revise existing ones.
NOTE: Be careful when adding indexes, it may help your one job, but have a negative impact on the rest of the system.
With the limited information given, I'm giving you some basic areas to investigate. I'll be glad to help you.
Just a random question since I already have a different solution but I had the following situation.
I have a table with a billion rows (actually probably about 1.1 billion.)
I was trying to span the table, read every row and do an analysis.
Certainly couldn't load the entire table. I was using a paged query (limit/count). Each page took about 90 minutes for the query itself. So not really something that was going to allow me to do much analysis.
Any other ideas on spanning it or speeding it up?
At one point I was even considering just dumping it and writing an app to do the analysis outside of the database. That was about the only other solution I had.
The database was MySQL (AWS Aurora actually).
The relevant parts of the table were as follows and the id has a primary key. (I didn't design the table.)