A Fine Slice Of SQL Server
How many times you need to stream reading from a file and writing into another well here it is…Here we are using special character char(30) and char(31) as delimited so a quick replace using regex did it. It’s Simple,Quick, and Dirty đ
The last ten lines are the important ones. Â You’d use this stream read+write pattern when dealing with files larger than your total amount of available RAM.
Comments closedGinger Grant argues in favor of the project deployment model in SSIS:
Prior to SQL Server 2012, there was no project deployment. SSIS code was all deployed as packages. These packages could be stored within MSDB or they could be stored and run from the file system. In disorganized places like the one where I worked, they were deployed in both. Assuming nothing much changed since I left, they have all versions of SQL Server which were released prior to the day the new IT Director started in 2012. There was DTS on SQL Server 97, 2000 and SSIS on 2005, 2008 and 2008 R2. No reason to upgrade anything which still worked was their motto. When space was a problem, one could always go build another server. I think the LAN administrator was happiest when he was able to justify building a new server as he could spend hours shopping for parts on the internet and building the latest server.
I believe that, in the business, we call this a “nightmare scenario.” Â Read on for ways in which a project model would help.
Comments closedAndy Leonard shows how to use Catalog Compare to migrate SSIS 2014 to 2016:
I recently tried to use the SSISDB Upgrade Wizard to upgrade a restored SSISDB (backed up in an earlier version) to SQL Server 2016. It didnât go well.
I decided to use SSIS Catalog Compare to generate the scripts and ISPAC files from the previous instance, and deploy them to the SSIS 2016 Catalog.
âYou Can Do That?â
Yes. Yes you can. Hereâs howâŚ
This is a paid product, but if you want to perform this upgrade, it sounds like a good tool for the job.
Comments closedAndy Leonard shows us how to use Biml to remove spaces from flat file column names:
This Biml declares an AstFlatFileFormatNode named ffformat and sets it to the FileFormat named âFFCM Provider Dataâ found the RootNodeâs FileFormats collection. I next loop through each column in the ffformat AstFlatFileFormatNode object. I use Biml to generate the <Column> object, replacing the spaces with an empty string for the Name attribute. The results in Mist appear as shown below
I loved the “I had 5 minutes, so I decided to fix it with Biml” line. Â Both funny and true.
Comments closedThat SSIS change just wonât stick! There is a new business requirement, so now you have to change an existing SSIS package. You opened up a new project, imported the package, and made your changes.
But the changes just arenât showing up. You back and look again and sure enough, the changes you made are there. You run your job (or whatever) and still the change arenât taking effect. So what happened?
Read on for the cause and the solution.
Comments closedKoos van Strien puts together some code to figure out commuting distance using Google Maps data:
Think well about this third step: you can save quite a few dollars annually by just keeping a local cache of travel distances, and only querying when the distance is unknown. My ETL process for this part consists of three global steps:
Add unknown departure/destination pairs to the ‘to be queried’ table (PK of this table is start & end point address in less-structured format, ensuring uniqueness of commutes)
Query Maps API for unknown travel distances. Add retrieved distances (or known unknowns) to the local cache table of travel distances
Use the local cache of travel distance (as complete as it gets at this moment) as the primary lookup for travel distance
The Google Maps API allows free tier users to make about 1000 requests per day.  If you don’t need to pull more than that many data points back (or can queue them to run over the necessary time frame), there’s no marginal cost to calls.  Otherwise, it ends up being a few dollars per thousand calls, so that shouldn’t break your company’s budget.
Comments closedMeagan Longoria explains why you should use Biml if you’re building Integration Services packages:
Biml provides a way automate SSIS design patterns. This reduces the time required to complete a data integration project, and it helps employ consistent design patterns within and across projects. Re-generating multiple packages after making a change to a design pattern takes just a few minutes, so small changes to several similar packages are no longer a significant effort.
Automating SSIS design patterns allows teams to work more efficiently. Senior developers can stop solving the same problems over and over again. Instead, they can solve them once, automate the solution, and move on to new and interesting challenges. Junior developers still learn good development practices with Biml, but they require less supervision to create quality output in a shorter amount of time. SSIS developers that prefer typing code over the drag-and-drop interface of SQL Server Data Tools now get a better way to work in addition to the automation capabilities.
If there’s one piece of advice I can give ETL developers, it’s “learn Biml.”
Comments closedRegis Baccaro ran into a rather lengthy error when trying to create an SSISDB catalog:
The error I got was :
Method not found: âVoid Microsoft.SqlServer.Management.IntegrationServices.EnableSsisSupportAlwaysOnSqmHelper.Initialize()â. (Microsoft.SqlServer.IntegrationServices.UITasks)
Looking at the documentation for the namespaceMicrosoft.SqlServer.Management.IntegrationServices I quickly figured out that I would be able to create the SSIS Catalog manually using PowerShell.
But then I couldnât locate the Microsoft.SqlServer.Management.IntegrationServices dll anywhere except from in the GAC so I had to load it a somewhat cumbersome way (with help from Remo). Below is the script I used for doing that.
It’s a strange error, but Regis does provide a workaround.
Comments closedSQL Sasquatch has some SSIS package issues stemming from a lack of NUMA awareness:
So the server had plenty of free RAM. But NUMA node 1 was in a pinch. And SSIS spooled its buffers to disk. Doggone it.
I guess I’d figured that notifications were sent based on server-wide memory state. But I guess maybe memory state on each NUMA node can lead to a memory notification?
The target SQL Server instance, a beefy one, was also on this physical server. There’s 1.5 TB of RAM on the server. đ
It also looks like the easiest fix is something which was deprecated in Windows Server 2012 R2.
Comments closedBeing on SSIS presentation recently, I’ve realized that a lot of people, who are working with SSIS for years, still do not know what “Parameterizing” is and how to do it.
SSIS has been changed a lot in SQL Server 2012, where Microsoft announced “Project Deployment Model”. Since then you can deploy Project, and you can assign Parameters to that project, which can be passed to it for execution. Before that, developers used Configurations to supply values for internal variables and connections.
Adding parameters to packages grants you a huge level of flexibility when moving between environments or reusing components.
Comments closed