Answer My Searches

So I set up a SQL Agent Job to run my SSIS package following these steps.

Here’s the crazy thing. The package would run perfectly when I ran it directly or via a batch file, but when I ran it through the SQL Agent Job it would die and I would see a message like this in the log file I set up:

DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Connection 1" failed with error code 0xC0209303. There may be error messages posted before this with more information on why the AcquireConnection method call failed.


I tried all of the work-arounds mentioned on this page. However none of them helped.

The answer it turns out is that 64 bit SQL Server Integration Services can’t handle Excel (xls) files! The trick is to use the 32 bit runtime of dtexec. To make the SQL Agent Job do this, go to the execution options tab of the job step and select “use 32 bit runtime”.

I tried making a flat file in SQL Server Integration Services but SSIS decided to put all of the output on one line. I couldn’t figure out a way to get it to make one line per record using a fixed width file type.

Finally what I ended up doing was creating a ragged right file, and making a dummy field with 0 width as the last field. You see, ragged right format works perfectly for exporting fixed width flat files with one record per line except that it trims the trailing spaces after that last field.

So making a dummy 0 width field lets you keep all of the trailing spaces in the last field you care about but still lets SSIS do its thing to add carriage returns/line breaks.

Source

So I got this error when running a report in SQL Server Reporting Services that takes several minutes to run:

'/' is an unexpected token. The expected token is '='. Line 9, position 33.

It turns out you just have to go to the web config file in C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer and change the timeout value to something high enough.

(Be sure to back up your web config file before editing it.)

Source

One way to do this is to change the interactive size of the report to something very long or short. For example if you want 1000 rows per page (don’t ask!) you can change the interactive size length to something like 200 inches.

It takes some trial and error to find the right length to use.

More discussion here.

The trick is that the grand total needs to be the first group. So delete your existing groups, and then add your grand total group first. Then add back in your original groups.

I also set the group on expression to =1 so that it effectively groups on nothing and gives you a grand total over all the rows.