I had to implement this today…
http://colinkirkby.blogspot.com/2007/01/ssis-creating-error-output-from-script.html
I have a script component in SSIS that could have exceptions that need to be handled gracefully, row-by-row, and logged as such. This blog post contained all I needed!
I have a pretty large ETL project I’m working on. I was getting the generic (read unhelpful) error message when records were not writing to my target database. It told me that there was a constraint violation, and gave me a column number.
Mind you, this isn’t the number corresponding with the column in the database. Rather, it was a number corresponding to a derived column in the SSIS package. Ugh!!! How was I to know what column it was? Enter the SSIS DataFlow Discoverer.
I found this blog:
http://bennyaustin.wordpress.com/2011/02/04/ssis-name-of-errorcolumn/
Referencing this tool:
After running the tool on my DTSX file, I was able to use the column number in a query against the database the tool generated and, voila! It helped get to the column in question much more quickly than having to do it manually by process of elimination!
Enjoy!
This blog post saved me from many bruises to my forehead from banging it on my desk.
http://beyondrelational.com/modules/2/blogs/66/posts/9927/fun-with-ssis-and-guids.aspx
Handling uniqueidentifier data types in SSIS is not as intuitive as it may seem. This helped me out a lot!
More SQL Server work of late…this came in handy yesterday!
http://blogs.msdn.com/b/amitjet/archive/2009/12/11/sql-server-comma-separated-string-to-table.aspx
I am grateful for the interwebz!
Needed this today…
We are doing some ETL work into a data warehouse for a client. One of the dimensions has a “smart key” value for a date dimension. So, we figured we could forego looking up the date key to populate an associated fact with and instead transform the incoming date to the smart key value. This worked great…except when it didn’t! Specifically, when we ran across a date that exceeded the data in the date dimension table. Yuck!
The solution was to do an initial lookup of the max date in the date dimension and capture that into a variable. Then, in the derived column where we were doing the smart key transformation, we would also check to see if the date exceeded the max in the dimension. If it did, we would set it to the default key value.
Happy coding!
This came in handy today. We had an issue where we had tested a SSIS package in our development environment. When we deployed it to QA, we received an error:
VS_ISBROKEN – column [x] and column [y] have incompatible types
This is on SQL Server 2014. We confirmed that the dev server was on a slightly older build than the qa server. What fixed the issue was changing the provider on the connection string for the package to SQLNCLI11.1 instead of SQLOLEDB. The data type in question was a datetimeoffset.
This is the link where we came upon the solution:
http://stackoverflow.com/questions/15372396/ssis-lookup-transform-fails-on-date-column
I am posting this because I came across the same thing. As the original author states – maybe it will be useful for someone else.
SSIS 2012 – Package Configurations Menu Option Missing
Happy coding!
Most of us who have been using BizTalk Server for a while use the SSO database as a secure store of configuration values. Microsoft made this much easier a few years ago when they released the SSO Configuration Application MMC Snap-In. Better yet, it comes with a sample class you can use to implement in your applications. This frees developers from having to add AppConfig segments to the BizTalk config file. In multi-server deployments, this gets really nasty because you have to change the config on every application server. SSO is by default available to the cluster. So, configuration changes are automatically made available to the entire BizTalk Group.
One common use of the SSO Config Store is a repository for database connection strings required by BizTalk maps performing database look-ups. A script component can call out to a class which wraps the client code that ships with the MMC Snap-In. Or you could use the BizTalk Mapper Extensions created by Sandro Pereira, which ship with a custom functoid to SSO config.
However, there is a problem. When you use the tool in a looping BizTalk map, it will make a call to the SSO database to retrieve config values for each element in your loop. In the case of a connection string, you are making unnecessary calls to SSO. Just open up the SQL Server Profiler, and you can watch all of the extra calls to the SSO database. With large messages, I have seen this severely degrade performance.
I have opted for creating a simple wrapper method, which uses a module-level dictionary object to cache configuration values needed by the map. This reduces the number of calls to the SSO database to one per config value. As you might expect, this significantly increases performance with even moderately large messages. The durability of the cache is only for the life of a single instance of the map. So, if you want something even more durable, you would need to rely on something like the AppFabric Cache.
Here is some sample code for caching values from the SSO config store…
private Dictionary<string, string> _ssoConfigurationValues = new Dictionary<string, string>();
public string GetSSOConfiguration(string appName, string propName)
{
try
{
string key = string.Concat(appName, propName);
string value = string.Empty;
if (_ssoConfigurationValues.ContainsKey(key))
{
return _ssoConfigurationValues[key];
}
else
{
value = SSO.Utility.SSOClientHelper.Read(appName, propName);
if(!_ssoConfigurationValues.ContainsKey(key))
_ssoConfigurationValues.Add(key, value);
return value;
}
}
catch (Exception ex)
{
throw new ApplicationException("GetSSOConfiguration function call read failed", ex);
}
}
Once again, here we are talking about the BRE!
I have a set of rules that utilize the OR operator. I found a very, very interesting article describing in detail how the BRE handles the OR operator.
http://geekswithblogs.net/cyoung/archive/2006/05/24/79500.aspx
Cliff Notes:
I worked on what ended up being a surprisingly interesting prototype to answer a question in social.msdn.microsoft.com.
Question:
I want to save whole XML message in sql table field which is with datatype XML(.).. how can i achieve this.
Answer:
I did a prototype to do what you are asking. I actually haven’t inserted into the xml data type in SQL Server before, so I was curious if that added any complexity. I’ll just go through the mapping pieces and assume that you can easily find documentation on how to insert data into SQL Server using the Add Generated Items –> Consume Adapter Service function.
The interesting piece was writing the map. I used a simple PO schema. My sample table was XmlRepository with a ID column, RepositoryID, and the RawXml field.
The script is an inline XSLT script that is as follows:
<xsl:element name="ns3:RawXml"> <xsl:text disable-output-escaping="yes"><![CDATA[</xsl:text> <xsl:copy-of select="/" /> <xsl:text disable-output-escaping="yes">]]></xsl:text> </xsl:element>
The question right off is why the CDATA? Well, since the value of the RawXml element that needs to be inserted into SQL Server is XML data, it has to be wrapped in a CDATA tag. Otherwise, the XML data gets validated and will not properly make it to the SQL Server. You can try it and see what I mean. Don’t worry, the CDATA gets stripped before the XML gets written to the table.
The interesting pieces are the xsl:text elements, which manually create a CDATA wrapper for the XML that ends up in the RawXml field. Apparently, the normal Grid Property for creating CDATA output doesn’t work in conjunction with the xsl:copy-of element. That tripped me up for a while!
What the map outputs is a document that looks like the following:
<ns0:Insert xmlns:array="http://schemas.microsoft.com/2003/10/Serialization/Arrays" xmlns:ns3="http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo" xmlns:ns0="http://schemas.microsoft.com/Sql/2008/05/TableOp/dbo/XmlRepository"> <ns0:Rows> <ns3:XmlRepository> <ns3:RawXml> <![CDATA[<ns0:PurchaseOrder xmlns:ns0="http://XmlRepositoryDemo.PurchaseOrder"> <PONumber>PONumber_0</PONumber> <CustomerNumber>CustomerNumber_0</CustomerNumber> <PODate>PODate_0</PODate> <Items> <Item> <ProductID>ProductID_0</ProductID> <Quantity>Quantity_0</Quantity> <Price>Price_0</Price> </Item> </Items> </ns0:PurchaseOrder>]]> </ns3:RawXml> </ns3:XmlRepository> </ns0:Rows> </ns0:Insert>
The map can be called from a simple Orchestration, or even a messaging-only scenario, and sent across the wire in a request/reply as you would for normally sending data to SQL Server.
I hope this helps someone. It ended up being more than I expected!
John Glisson - Geek of the Cloth 