Microsoft SQL - Integration Services SSIS
UPDATE on 2017-05-31:It seems to be a bug with version 17.1 (build 14.0.61705.170). I uninstalled that version (including some prerequisite and other components from program list), did a repair on Visual Studio 2015, then installed build 14.0.61021.0. I was able to see the ADO components working again (but lost the ability to deploy to SQL 2017, as expected). I will report this to the SSDT team.ORIGINAL POST:I set up Visual Studio 2017, but the SSDT for SQL 2016 did not integrate so I am in Visual ......
I'm a recent convert to bcp. Actually, to put it more correctly, I haven't had any ETL requirement that would require complex data flow. I am a huge SSIS fan, but until 2012 came along, logging and debugging has always been an issue, so slowly I was moving back to SQL for basic tasks, breaking the uniformity of the project. Along the way I started to use bcp more heavily So imagine my surprise when I found this article: http://tech.wakayos.com/?p=... Basically saying "Data Flow Destination Fast ......
In case you haven't heard. Microsoft claims to be deprecating OLEDB in favor of ODBC. Seems like they want to push as many people to the cloud as possible... http://blogs.msdn.com/b/sql... http://social.technet.micro... ......
I am very honored that one of my blogs is being referenced by MSSQLTips as exam material help. I started this blog a while ago because I got frustrated with forgetting some cool stuff I've done before, and I am very glad that it not only helped me, but help some in the community. Microsoft developer community has been so great, I'm happy to do my part. http://www.mssqltips.com/sq... My blog being ......
I am very honored that one of my blogs is being referenced by MSSQLTips as exam material help. I started this blog a while ago because I got frustrated with forgetting some cool stuff I've done before, and I am very glad that it not only helped me, but help some in the community. Microsoft developer community has been so great, I'm happy to do my part. http://www.mssqltips.com/sq... My blog being ......
I love doing database design with people. I've done my fair share of database modeling on my own; those are the easy projects, sure, but with no feedback. However, working with people on database never fails to help me look at things from different perspective.A very simple thing like whether a NOT NULL table column should have default values get very different reaction from a BI guy and a System Integration guy. Let me put some of the priorities out from different projects I've been in to help understand ......
Here is a good blog on how to create a SSIS Catalog and setting up environments. http://sqlblog.com/blogs/ja... Here I will summarize 3 ways I know so far to execute a package while using variables set up in SSIS Catalog environment. First way, we have SSIS project having reference to environment, and having one of the project parameter using a value set up in the environment called "Development". ......
There are so many funny quirks in SSIS 2012 that I have to list them, to save other people from the misery. If you want to move items to one direction, make sure you "grab" the opposite side. For example, you want a whole bunch of data flows to move up, select them all and grab the lowest item. When you drag the arrow to connect Precendence Constraint, make sure you drop it on the area of target that has no text, otherwise, it thinks you want to edit the text and change the target item layout ......
Always was able to do it in a .NET application and a reporting project. This took me a while to realize. In SSIS 2012 project, once you create project.params, you can add them to your configuration settings by clicking this button
A dialogue pops up where you can add your project params and specify their values in different configuration set.
Refer to my previous post (http://geekswithblogs.net/... about dynamic package calling and multiple packages execution in these posts: I only saw this twice, other times the stored procedure was able to call the packages successfully. After the service pack, I haven't seen it...yet. http://support.microsoft.co... ......
There are quite a few good articles/blogs on this. For a straight forward deployment, read this (http://www.bibits.co/post/... For a more dynamic and comprehensive understanding about all the different settings, read part 1 (http://www.mssqltips.com/s... and part 2 (http://www.mssqltips.com/s... ......
Somebody told me this and I'm ready to try some of the components:
http://www.konesans.com/products.aspx
FileWatcher, Regex Clean, RowNumber Transform
"The " + @[System::TaskName] + " task captured error in the " + @[System::PackageName] + " package at " + (DT_WSTR, 50) @[System::EventHandlerStartTime] + ". The message is:
" + @[System::ErrorDescription]
The following is my experience with SSIS building: Make sure you understand the 3 modes of Lookup. General, I would only do full cache if the lookup table only return less than 200 rows Never use the table or view select unless you really need every field on that table or view. select * from a data source will break in the future if more columns are added to the table select * in a lookup brings back unnecessary fields and impact performance Never use "Sort" in SSIS unless it's absolutely necessary. ......
For the environment that does not have SCCM to monitor BizTalk server health, the following SQL query can be incorporate into a SQL job and have BizTalk server at least on a "constant" check. Note: be aware that the following was tested only against a single suspended instance with a send port. Since BizTalk SQL server does not allow select against the ServiceClass table, I couldn't use a single table join. With the data I currently have, I am not 100% sure the Receive Port, Pipeline, Orchestration ......
Granted, if you are just transforming data in the same SQL server, why would you do it in SSIS instead of just using Pivot and Unpivot in T-SQL query? But what about in the case where data comes from various sources? What I had fun doing was the ability to skip using Linked server, pull in data from different sources, like XML over web service. Another point that made me happy was the ability to simulate ROW_NUMBER() function with the merged data by using a Script Component. Anyway, I highly recommend ......
Not much experience with these products yet.
Free: http://developer.nvidia.com/object/nsight.html
Not free: http://software.intel.com/en-us/articles/intel-parallel-studio-purchase/
For SSIS, this looks very interesting:
http://extendedssispkgexec.codeplex.com/
It is actually quite simple when I think this through: Make the first job step with this T-SQL query: IF ((SELECT DAY(GETDATE())) < 6) BEGIN SELECT 1/0 END Go to the Advanced Tab of the Job Step property page. Select "Quit the job reporting sucess" on the "On Failure Action" drop down. Note: You can use RAISERROR, but the Severity has to be set to greater than 10. To avoid future maintenance confusion, I decided to go with the basic fatal error generation ......
Reminder:
Always turn logging on as the last step just before you are ready to do test runs. The reason being if you need to copy any task between 2 packages and the source package has logging turned on, it will throw an error.
This surprised me. I have a BizTalk SQL adapter calling a stored procedure to insert a row in some table. The table has an "AFTER INSERT" trigger on it.
The original design had the trigger fire off an SSIS package. But when the SSIS is called, the row status is still not committed. I thought since I'm using an "AFTER INSERT" trigger, I was allowed to do that. Need to read more on the transaction nature of triggers, I guess.
I start off the process with either a BizTalk Scheduler (http://biztalkscheduledtas... or a manual file drop of the XML message. The manual file drop is to allow the SQL Job to call a "File Copy" SSIS step to copy the trigger file for the next process and allows SQL Job to be linked back into BizTalk processing. The Process Trigger XML looks like the following. It is basically the configuration hub of the business process <ns0:MsgSchedulerTrigger... xmlns:ns0="urn:com:something ......
For the record, this can be just as easily done with a .NET class with a DLL call. The two major reasons for this ending up as a SSIS package are: There are a lot of SQL resources in the organization for maintenance, but not as many .NET developers. There is an existing automated process that links up SQL Jobs (more on that in the next post), and this is part of that process. To start, this is what the SSIS looks like: The first part of the control flow is just for the override scenario. In the Execute ......
I tried to use the "ForcedExecutionValue" on several Send Mail Tasks and log the execution into a ExecValueVariable so that at the end of the package I can log into a table to say whether the data check is successful or not (by determine whether an email was sent out) I set up a Boolean variable that is accessible at the package level, then set up my Send Mail Task as the screenshot below with Boolean as my ForcedExecutionValueType. When I run the package, I got the error described below. Just to ......
[Update: 2010-07-14] Don't know what difference does one day make, but I re-tried this today and the file on the network looks fine. I know I'm not crazy because I tried the same steps yesterday twice and got screenshots in the email to prove it. At this point, I'm willing to take anyone's theory. [Original post] I ran into this problem and have yet to solve it. We needed to create a data dump pipe delimited flat file out of a table of 4 million+ rows. The output was created just fine on my local ......
I've always used the Expression property to dynamically build the connection strings. It has always worked without any problem, so I never gave it much thoughts. However, a package we converted from DTS was built using script task to build the connection string before writing to the file in a data flow task. The reason it was converted into a script task was because the DTS package was using an ActiveX script to generate the file path, and it was a quick and dirty 5-minutes-job to convert that into ......
The async one was very easy to do. However, in the process automation task I was given, there was a need to execute SQL Job and waiting for the execution result. I couldn't find anything on the web to do what I wanted, so I make the following stored procedure. In addition, this stored procedure is quasi-synchronous. I start the job and then check the status. You can change the timing of the status check, but for the example below, it is "synchronous within 30 seconds." I wish there is a better way ......
Business Scenario: The client gets some kind of feeds that writes into a process table of their client processing system. The data is stored directly as the raw XML file inside a SQL data column. The XML could store multiple enrollment information for the same member. We need to use this info to determine whether a member is eligible, then insert into a staging table. The staging table is then used as a feed to drive a report that generates an image for each claim and sent off to the different provider. ......
[Update: 2009-12-30] We got it working using SSIS. Read this post: http://geekswithblogs.net/L... [Original post] Would love to know if there is a way to kick off a SSRS report and pass some parameters into it via T-SQL or SSIS. Unfortunately, we can't have MS SQL Enterprise edition. Guess this is the true list of possible solutions: VB script PowerShell WMI script batch file T-SQL Sad thing is I know how to make this ......
This is the code: DECLARE @SQLCommand AS VARCHAR(1000) SET @SQLCommand = '("dtexec location" /F "package location and name" ' + '/SET \package.Variables[User::your variable name].Value;' + CONVERT(VARCHAR(2), MONTH(@Date)) + '/' + CONVERT(VARCHAR(2), DAY(@Date)) + '/' + CONVERT(VARCHAR(4), YEAR(@Date)) + ')' EXEC xp_cmdshell @SQLCommand Some notes: I tried to pass in the date without formatting it, I get a "Option "1" is not valid" message. I had to convert the date into a string to include in the ......
In a Script Task (in control flow), it is Dts.Variables("variable_name")
In a Script Component (in dataflow), it is Me.Variables.variable_name
Script task to catch all SSIS errors. I then used an email task to send out the error report. Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Run... Public Class ScriptMain 'Capture Error Public Sub Main() Dim msgs As Collections.ArrayList Try msgs = CType(Dts.Variables("ErrorC... Collections.ArrayList) Catch ex As Exception msgs = New Collections.ArrayList End Try msgs.Add(Dts.Variables("Err... Dts.Variables("ErrorCollect... ......
Code: Man, I looked at this code and was wondering what the heck I was doing. Then I remember this was done to convert a MONEY datatype to a number with 2 decimal places. If you are working with an integer, the code is simple: REPLICATE("0",(5 - LEN(TRIM((DT_WSTR,4)[field_... + TRIM((DT_WSTR,4)[field_name]) The code above returns a 5-character string with leading 0's. I believe the following code returns a 15-character long string, can't be 100% sure at this moment because I can't remember ......