SSIS
Somebody told me this and I'm ready to try some of the components: http://www.konesans.com/pro... FileWatcher, Regex Clean, RowNumber Transform
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... Not free: http://software.intel.com/e... For SSIS, this looks very interesting: http://extendedssispkgexec.
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...
"The " + @[System::TaskName] + " task captured error in the " + @[System::PackageName] + " package at " + (DT_WSTR, 50) @[System::EventHandlerStart... + ". The message is: " + @[System::ErrorDescription]
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...
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...
[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...
Gotta try this: http://www.mssqltips.com/ti
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...
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
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...