Monday, February 6, 2017

ssis dataflow excel connection in a foreach loop

Recently when using a foreach enumater to set an excel file path in a foreach loop in SSIS I ran in to a problem. In this case it was where the loop would act as if it were successfully processing several excel files, but the data would not change. I even put a script output on the data flow to write the filenames to a log to make sure I wasn't just running the same file over and over. Turns out on the excel connection manager properties.. The same place where you go to set the expression to change the excelFilePath for each iteration of the loop, there is a setting called RetainSameConnection.
Having this set to true was not allowing the expression to overried the file path.

I feel like in older versions of Visual Studio I was able to processed multiple files in a foreach loop without checking this box.

Friday, May 8, 2015

passing parameters from crm 2011 to SSRS iframe

i needed to create a report that ran off one of the lookup guids on a form.
First I created a stored procedure that accepted a parameter that was the form guid, then I created the ssrs report and deployed it to a local server.

next, the tricky part, on the CRM form I created an iframe with a simple name and entered a bogus URL. Then on the Form properties page, accesible from the form edit ribbon I added this script as an on load event ... I set up the alerts only as checks when testing and removed them for the final publish

the only way I could get the guid value was to create an array, lookup the first value from that array and set it to a new variable (in the case new_id). This script also hides the iframe if there is no guid value

function Change_Report_Url()
{
var id = new Array();
id = Xrm.Page.getAttribute("guid_from_form").getValue();

if(id[0] != null)
    {
    var new_id = id[0].id;
    alert(new_id);
    }

var url = 'your_reportServer_reportUrl&id='+new_id;
alert(url);
var iFrame = 'IFRAME_with_bogus_URL_name';

if (id == null)
{
Xrm.Page.getControl(iFrame).setVisible(false);
}
else {
Xrm.Page.getControl(iFrame).setSrc(url);

}
}

Tuesday, April 7, 2015

get specific day previous month t - sql

get specific day previous month t - sql
there is about six million different ways to compose these queries

select convert(date, concat( Datepart(m,DATEADD(m,-1,getdate())),'/','10','/',datepart(year,getdate())))

Thursday, February 12, 2015

ssrs report on crm dashboard

experienced some issues showing a ssrs report on a CRM 2011 dashboard.
the  standard criteria filters for the view settings were insufficient for the requested query so hence the need to put a rs report up and link to it in a crm dashboard. using a linked report or just a standard iframe both resulted in error city. A work around is possible by writing a html iframe, loading it into crm as a web resource, and then calling the web resource from the dashboard...
heres the sample code


<html><head><meta><meta><meta><meta></head><body style="word-wrap: break-word;"><iframe id="IFRAME_madeUPname" name="IFRAME_madeUPname" class="ms-crm-Custom" tabindex="0" url="http://yourRSURL&amp;rs:Command=Render" scrolling="auto" preload="0" src="http://yourRSURL&amp;rs:Command=Render" frameborder="0" height="100%" width="100%"></iframe></body></html>

Tuesday, February 10, 2015

crm 2011 workflow email issue

experienced and issue with a crm 2011 workflow failing to send and email, and remaining stuck in the status reason 'waiting for resource'. it seems the cause was from changing the cc, and bcc fields. there are other workflows that send emails with no issue.. so it it likely not the email router message Cannot create the given type without the required parameters. Plugin Trace: [Microsoft.Crm.Workflow: Microsoft.Crm.Workflow.Activities.EvaluateExpression] [EvaluateExpression] Error Message: the work around i found here proved insufficient https://rule30.wordpress.com/2014/04/10/fix-microsoft-crm-crmexception-cannot-create-the-given-type-without-the-required-parameters/ and ultimately ended up recreating the workflow

Friday, May 25, 2012

ohio short track 2012

Columbus has a promising short track series starting this summer http://capcityshorttrack.wordpress.com/series-information/ I can't wait for it!

Monday, May 14, 2012

spring

Spring news: Joined the CSU crew team. Fell in love with rowing. Most certainly want to stay with this sport. I think it is a great complement to cycling. Road: Road biking for me has kind of fallen to the side a bit. Though I did one road race in the Cuyahoga Valley National Park. I kept up for a few laps before being shot of that the back into no-mans-land. I will probably do more road races as the season progresses but not for a minute. Other: Bought a single speed mountain bike it is awesome and I spend everyday on it. Went back to polo finally to find that my skills have severely deteriorated. Finally graduated with a Master's, now for the easy part, finding a job.