Parameters not passing through:
Are they the same collation?
Are they both trimmed?
Check the parameter is being passed through, not just the name of the parameter
Wednesday, 21 October 2020
Tuesday, 15 September 2020
SSIS and Excel Bugs
Some helpful links for importing data from Excel to SQL Server using SSIS:
Choose to install the 32 bit version of the driver above, and then you will see, in the Connection manager, the following option for connecting to Excel:
Friday, 26 June 2020
A Guide to DAX
What is DAX?
DAX is a great language developed by Microsoft for querying data in tables. It is used in Power BI, Power Query in Excel, and SSAS Tabular Models.What can I do with DAX?
With DAX you can query data in three ways:- By creating measures that aggregate or sum up columns
- By creating calculated columns
- By creating calculated tables
Note that the calculated tables can be embedded into measures, effectively giving you the ability to do what would be called a subquery in SQL. That is, aggregating data into a dataset, against which you then base another query.
Comparison to Excel Formula Language and SQL
DAX is far more powerful than Excel formula language. Its ability to produce tabular datasets, which can then be queried themselves (i.e. subqueries) makes it more like SQL. But it resembles Excel formula language far more.
Basic Element of DAX
- DAX has variables, just like SQL. Declare them by writing VAR followed by the variable name.
- DAX has aggregation functions. These look like SUM and AVERAGE.
- DAX has relationships. These are accomplished in Power BI without the need for any JOIN statements at all. You simply connect the fields from multiple tables together, in a GUI.
- DAX has filters (the equivalent to WHERE clauses in SQL). These are accomplished by using the CALCULATE function.
- DAX can use subqueries (queries within queries). These can be used by either creating new calculated tables, and then basing a query off of that, or by using the SUMMERIZE within a DAX measure to do it all in one go.
Difficulties Learning DAX
- Learning what iterator functions do can be tricky. Effectively they do the same as creating a calculated column and then aggregating the created column.
- Learning why you can't input variables as parameters in certain functions. Note that variables are not calculated independently or before the RETURN statement. So the variable is calculated for every row of data you display. It does not return a single value for every row. It is calculated for every row you display in your visualisation, so may return different values for different rows.
- Learning why, sometimes, you need to perform an aggregation within a measure, rather than refer to another measure that performs the very same aggregation. Again, I have no idea why this is.
Tuesday, 5 May 2020
SSIS Bugs - It runs on local but not in live
SSIS package running on local but not when deployed?
Here is a check list of things to look at:
Here is a check list of things to look at:
- Check whether the Agent is set to run as 32 or 64 bit. Change and test again.
- Check whether the 64 bit mode is enabled or not on the package itself
- Check that the latest version of the OLEDB driver is installed on the machine you deployed to:
- Especially if the error message says "The requested OLE DB provider MSOLEDBDQL.1 is not registered." or "Class not registered."
- download it from here: https://www.microsoft.com/en-us/download/confirmation.aspx?id=56730
- If the owner of the Agent Job has sysadmin or CONTROL server permissions, then the job will run using the SQL Server Agent service account (unless its set to use a proxy). Otherwise it will run as the Job owner. Make sure the relevant account has the right permissions on the server you are trying to connect to. So....
- Ensure the SQL Server Agent account has permission to run on all servers the job touches, if the Job is running as the SQL Server Agent
- Ensure the relevant account (see above) has correct file system permissions if the package is looking at the file system.
- Check that the package Target Server Version is the same version as your server.
- If you can't get it to run with SQL Server Agent, try using a Proxy.
- First create a system login (use your personal one to test, then have service desk create a security login with a password that never expires)
- Give the system login sysadmin to test
- Then create a credential under Security > Credentials
- Then create a Proxy, adding that credential to it, and put the credential into the principles
- Then in the Job Step, set the Run As property to the credential.
Helpful link:
Subscribe to:
Posts (Atom)