In SSIS 2012 there is a great new feature called environments. They can be thought of as a collection of parameters or variables. One of the questions I get asked when teaching SSIS 2012 is “Can I duplicate an environment?”. There is a move feature already included. But this moves the environment instead of copying.

Now you can write T-SQL Scripts like the one at the bottom of this blog to create an environment and create the variables in the environment, but this is time consuming. You can script this out when you first create the environment and the variables. But if you have an already existing environment then this is not possible.

So how do you copy an environments? Here is a little trick to use.

First, to create the new Environment, right click on the new folder and select create environment. This is the easy part. The hard part is getting all of the variables inserted. You may have 50 variables in the previous environment to copy over. We need an easy way to copy them all over to the new environment.

Next, right click on the new environment and select properties. Take note of the environment identifier. Do the same for the previous environment.

Next, go to the SSISDB and take a look at the [SSISDB].[internal].[environment_variables] table. This table contains all of the variables in your environments. You will find the rows for the previous environment for each variable in that environment. Look for the previous environment id you just noted.

Now we need to write a T-SQL statement to duplicate these rows with the new environment ID. This is a simple Insert into statement followed by a select statement. You will need to add in the Select statement a hard coded value of the new environment ID in the columns and a where clause looking for the previous environment id.

INSERT INTO [internal].[environment_variables]
([environment_id]
,[name]
,[description]
,[type]
,[sensitive]
,[value]
,[sensitive_value]
,[base_data_type])
SELECT  10 as environment_id  –New Environment ID
,[name]
,[description]
,[type]
,[sensitive]
,[value]
,[sensitive_value]
,[base_data_type]
FROM [SSISDB].[internal].[environment_variables]
where environment_id = 9  –Previous Environment ID

Make sure you create the new environment first and get the id’s correct in this script and you should be all set.

Also…

Here is the T-SQL code that can be scripted out when you first create an environment and the variables in it. But this can only be done when you first create them. The above solution works on any existing environments.

EXEC [SSISDB].[catalog].[create_environment] @environment_name=N’Test’, @environment_description=N”, @folder_name=N’SSISDemo’

GO

DECLARE @var sql_variant = N’test’
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N’test’, @sensitive=False, @description=N”, @environment_name=N’Test’, @folder_name=N’SSISDemo’, @value=@var, @data_type=N’String’
GO

DECLARE @var sql_variant = N’test1′
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N’test1′, @sensitive=False, @description=N”, @environment_name=N’Test’, @folder_name=N’SSISDemo’, @value=@var, @data_type=N’String’
GO

DECLARE @var sql_variant = N’test3′
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N’test3′, @sensitive=False, @description=N”, @environment_name=N’Test’, @folder_name=N’SSISDemo’, @value=@var, @data_type=N’String’
GO

I did a webinar during the 12 days of 2012 on how to use Report Builder 3.0. In this webinar I showed how to pull data from Stored Procedures and Shared Data sets. I also showed the issues that come with some of these items.You can see the webinar here: http://swrt.worktankseattle.com/series/59/seriessignup.aspx

If you want to execute a set of SSIS packages in SQL Server 2008 or 2005, you can do this using T-SQL. First you will  need a table with all of your package names on it. Then a While loop to execute each package.

Here is the example code:

Declare @FilePath varchar(2000)

Declare @cmd varchar(2000)
 
DECLARE @package_name varchar(200)

Declare @PackageCount int

Declare @X int

Set @X = 1

Set @PackageCount = (Select COUNT(*) from Packages)

set @FilePath = ‘C:\Package Path’

While (@X <= @PackageCount)
Begin
 
    With PackageList as
    (
    Select PackageName, Row_Number() Over(Order by PackageName) as  Rownum
    From Packages
    )
    SELECT @package_name = PackageName
    FROM PackageList
    Where Rownum = @X
 
    select @cmd = ‘DTExec /F “‘ + @FilePath + @Package_name + ‘”‘
 
    print @cmd
   
    Set @X = @X + 1
   
    exec master..xp_cmdshell @cmd
 
 
End

In the new version of SSIS 2012 you will be able to launch packages with T-SQL Natively.

I was working on a Cube in Analysis Services for a client recently and needed to sort on a field that was a varchar but contained numeric data. I wanted to sort as if it was numeric. I could not just convert this code to a number and sort on that, because the codes had multiple decimals as seen in this image below.

Notice the numbers are sorted as a string and not numeric. You want the number 1.1.2 to come before 1.1.10. Instead you can see it is lower in the order due to the string order. You will also notice 2.2.0 should be before 2.10.0. This is happening because a string is evaluated as alphabetical when sorted. So the number 10 would come before the number 2. This is because 1 is less than 2. The zero in ten is not even checked because is it alphabetizing when ordering these fields.

To fix this issue you need get the individual numbers between the decimals in the code as integers and sort on each one. To get the first number we simply use a char index to get up to the first decimal with this SQL code. This code it getting the location (CharIndex) of the decimal and then getting the substring up the character before the decimal. Then is it converting it to an Integer.

convert(int,SUBSTRING(code,1, CHARINDEX(‘.’, code) – 1)) as Num1,

Then we need to get the number after the first decimal but stop at the second decimal. This is a little harder as you can tell by the below code. First we get the location of the first decimal plus one to use as the starting point in our substring. The length of the substring takes a little math. It is the length of the code minus the first decimal location minus the index of the last decimal.

convert(int,SUBSTRING(code, CHARINDEX(‘.’, code) + 1, LEN(Code) – CHARINDEX(‘.’, code) – Charindex(‘.’,REVERSE(code)))) as Num2,

Then we need to get the number after the last decimal. The location of the start of the substring is calculated by subtracting the length of the code minus the location of the last decimal place plus 2. The length of the code is used as the length of the substring, this is too long but since it is the last digit it works fine.

Convert(int,SUBSTRING(code, len(code) – Charindex(‘.’,REVERSE(code)) + 2, len(code))) as Num3

The complete Query will be:

Select Code,

convert(int,SUBSTRING(code,1, CHARINDEX(‘.’, code) – 1)) as Num1,

convert(int,SUBSTRING(code, CHARINDEX(‘.’, code) + 1, LEN(Code) – CHARINDEX(‘.’, code) – Charindex(‘.’,REVERSE(code)))) as Num2,

Convert(int,SUBSTRING(code, len(code) – Charindex(‘.’,REVERSE(code)) + 2, len(code))) as Num3

from CodeOrder

Order by Num1, Num2, num3

And the results of this query are:

You can see that the codes are in the numerical order we wanted. Your SQL code may need to be adjusted for the number of decimals in your field.

You can add these new columns to your dimension and use them as the key to your attributes and order by the key. You can change the dimension table in the DSV to a Named Query and add these new number fields.

If you prefer, you can go back to the SSIS package loading this dimension and create these new number columns on the dimension table. In SSIS the derived column transform could be used to do the same conversion that I am doing in the Named Query to get the numeric fields.

Which is better, SSIS or SQL? Should you do this in derived columns in your package or should you use SQL statements like the one above to perform this work?

Maintainability and Performance are the two items to consider when making this decision. The SQL Query will perform much better than the derived columns but the query could confuse others that may need to maintain this after you. The SSIS derived column tends to be a little easier to understand and managing one derived column in an SSIS package could be considered easier. This is debatable and I know hard core T-SQL Gurus are going to disagree.

Here is the derived column that does the same thing as the SQL statement above.

(DT_I4)SUBSTRING(Code,1,FINDSTRING(Code,”.”,1) – 1)

(DT_I4)SUBSTRING(Code,FINDSTRING(Code,”.”,1) + 1,FINDSTRING(Code,”.”,2) – FINDSTRING(Code,”.”,1) – 1)

(DT_I4)SUBSTRING(Code,FINDSTRING(Code,”.”,2) + 1,LEN(Code))

The FINDSTRING function allows you to select the occurrence you want to find so there is no need for the reverse and the extra subtraction that was needed in the SQL query.

The results are the same and these columns can now be added to the dimension table and be used to sort. The SQL statement did perform 20% faster than the derived column. But the Derived column could be considered easier to maintain depending on your level of T-SQL and your level of SSIS.

With some calculated members in MDX it only makes sense to see the calculation if a certain Hierarchy is used.

For example:

Aggregate(YTD([Date].[Calendar].CurrentMember),[Internet Sales Amount])

This calculation only works in the Date.Calendar Hierarchy. I wanted to show the end users a message informing them about this and hide the calculation at the same time.

Here is how I did this:

Case when ([Date].[Calendar].level is [Date].[Calendar].[Calendar Year] or
[Date].[Calendar].level is [Date].[Calendar].[Calendar Semester] or
[Date].[Calendar].level is [Date].[Calendar].[Calendar Quarter] or
[Date].[Calendar].level is [Date].[Calendar].[Month]) then
aggregate(ytd([Date].[Calendar].currentmember),[Internet Sales Amount])
else “Use Date Hierarchy”
End

Now the user will see the message “Use Date Hierarchy”  if they are not at a level in the correct Hierarchy. The only downside to this is the grand total shows the message instead of the total.

If you can figure out a way to show the grand total when the user is in the correct hierarchy, let me know.

Auditing in SSIS can be a real headache. In this two part series blog I am going to try to make it a little easier. You can also download my white paper and sample files on Auditing SSIS here. You can also see this post and more at SQL University. Part 1 of the blog is here.

Creating a Custom Auditing Framework

Creating an auditing solution is time consuming but once built gives you the detailed information you want in your table and allows you to filter unnecessary data. You will create this custom auditing by adding tasks to the Event Handlers of the package.

Event Handlers

There are several event handlers listed under the event handler tab. Click on the event handlers tab at the top of the example package and you will see two drop down menus at the top. On the left there is a drop down with the tasks in the package and the package itself as seen in figure 8. You can create specific auditing for each task if desired. In this example you will create auditing for the entire package, so ensure the package name is selected.

Figure 8

The right drop down menu contains the events available for the package. Select the onError event handler and click the blue link in the center to create the onError event handler. Before you can start auditing you will need to create a table to write the data too. For this example you will be auditing the package name, task name, error description, and the date. Open SSMS and run the following SQL in your auditing database to create the auditing table.

CREATE TABLE [SSISErrorLog](

[RunTime] [datetime] NULL,

[Package] [varchar](500) NULL,

[Task] [varchar](500) NULL,

[Error] [varchar](500) NULL

) ON [PRIMARY]

Execute SQL Task

Now you are ready to insert data into this table. Before we insert data we need to create one more variable. There is a problem with the date format in SSIS. The DateTime format in SQL is different than the System variables in SSIS. The format in SQL is 1900-01-01 00:00:00.000, and the format in SSIS is 1/1/1900 12:00:00 AM. So you will need to convert the SSIS date to the SQL format. To do this, create a variable on the package named strStartTime and set the type to string. Set the variable to evaluate as an expression in the properties of the variable. Click on the expression ellipsis and enter the following code:

(DT_WSTR, 10)(DT_DBDATE)@[System::ContainerStartTime] + ” ” + (DT_WSTR, (DT_DBTIME)@[System::ContainerStartTime]

This is the SSIS Script language. It will convert the start time of the current container to a format SQL will recognize.

Go back to the package onError Event Handler. Drag in an Execute SQL Task. Open this task and set the connection to the testing database. Enter the following SQL into the Execute SQL Task. Notice the convert function used to convert the string value of the date to a datetime for SQL.

INSERT INTO SSISErrorLog

([RunTime]

,[Package]

,[Task]

,[Error])

VALUES(CONVERT(datetime,(?)),?,?,?)

Click on the Parameters tab of the Execute SQL Task and enter the parameters as shown in figure 9 below. Notice the first parameter is the variable you create previously, the rest are system variables. Click ok to close the task and return to the control flow of the package.

Figure 9

You will need to cause an error in the package to have the Event Handler fire. Open the first Execute SQL Task in the For Each Loop and put the letter ‘X’ in front of the SQL command. This will cause a syntax error. Run the package. The package should fail. Open SSMS and query the SSISErrorLog table and you should see the data from the package run as seen in figure 10 below.

Figure 10

If you do not see any data, return to the package and look under the Progress/Execution Results tab and find the error on the event handler. It should tell you why the insert statement failed.

Expressions

This was a simple example of writing data to a table to audit a package. You can use more variables and expressions to make the package more customized. For example you can create some of the variables below and use the corresponding expressions. These variables would be the parameters in the Execute SQL Task instead of the system variables. Of course you would need to alter your table to write these new columns.

VariableName	Variable Type	Expression
strUser	String	@[System::MachineName] + “\\” + @[System::UserName]
strDate	String	(DT_WSTR, 10) (DT_DBDATE) @[System::ContainerStartTime]
strPackageLoc	String	@[System::MachineName]+ “\\”+@[System::PackageName]
strExecBy	String	@[System::InteractiveMode] == true ? @[System::UserName] : @[System::MachineName]

You can see by this small example that creating and maintaining a robust auditing solution will take quite a bit of time. This type of solution would need to be added to every package in your environment that you need to audit. You can use a package as a template and make any adjustments to the auditing as needed during package development.

To avoid this time consuming work, you can use a tool by Pragmatic Works that can do this work for you. That tool is BI xPress.

Auditing in SSIS can be a real headache. In this two part series blog I am going to try to make it a little easier. You can also download my white paper and sample files on Auditing SSIS here. You can also see this post and more at SQL University.

SQL Server Integration Services (SSIS) is a powerful tool used to move data. Once you have created several SSIS packages and scheduled them to run in your production system you inevitable have failures at some time. Having a robust auditing framework will make troubleshooting and performance tracking your packages much easier.

Imagine you have dozens of SSIS packages that run in SQL Agent jobs throughout the night in your production system. You arrive at work the next morning you find data is missing for reports that are critical to the business. It is your job to find out why this data is missing. First you need to know the package that is loading this data. Next you need to find the error (if there is one) or any other issues with the package or packages.

Figure 2

The first stop for most SSIS troubleshooters is the SQL Agent job log. In this example all of the packages ran with no error last night. You can see this job history in Figure 2. What now? If you have logging or a form of detailed auditing on your packages then you would be able to track down the issue. In this example the issue was the package loaded no rows due to a where clause in a Data Flow Source so there was no error to find. This problem would be obvious if you have auditing on the row counts of your Data Flows.

Figure 3

With packages spread across multiple servers, having all of the auditing data in one location makes tracking SSIS package issues and performance easier. A form of centralized auditing would log the run time information of packages from each of your servers. In Figure 3 you can see an example of this type of setup. There is a central database that holds all of the auditing data. Several methods exist for monitoring SSIS, native logging, and custom logging frameworks.

1.1 Logging

The native logging feature in SSIS can write information about the package run into several locations. To open the logging screen right click in the control flow of an SSIS package and select logging. The logging menu can be seen in Figure 4.

Figure 4

Logging to Files

Once in the Logging window you will need to place a check next to the package name in the top left. Select SSIS log Provider for Text Files and click add. Then place a check next to the newly added log provider. Under Configuration select a file name where the logging information will be saved. See figure 5 for an example of this set up.

Figure 5

Under the Details tab you can select which options you would like to save and on which events. Select the following event handlers, OnError, OnWarning, OnPostExecute, and OnPreExecute. These Events call any tasks you have added to the corresponding Event Handler window. For Example, when a task is run in the Control Flow the onPreExcute task is called, if you have placed an Execute SQL Task in the onPreExecute event handler window, the Excute SQL Task would execute in the Event Handler. If the Control Flow task causes an error, the onError event will be called and execute any task under the onError. These are the most common event handlers logged for packages. They tell you about errors, warning, start times, and stop times.

The advanced button at the bottom of the details screen allows you to select the information collected at each event. Leave the advanced settings at default for this example.

Figure 6

Once you have configured the text file logging for the package run the package one time by right clicking on the package name in the solution explorer and click execute package, and then open the log file.

Note: There is a Reset Package in the solution you can run to truncate the tables and move the file back.

In figure 7 you can see a small extract from the log file. This file is approximately 550 lines of text. This is a lot of information about a small package. A lot of the information is repeated also. Some of this repeating is due to the how the event handlers are fired in SSIS. The SSIS packages fire some events multiple times, once for the package and once for the tasks. This makes the logging cumbersome to read and hard to find the important information you are looking for in a logging solution.

Figure 7

Since this log file is a CSV you can open it in excel. A major problem occurs in trying to read through the error messages in Excel. Some SSIS errors contain commas in there description. This breaks the error up into separate columns. Trying to load this file into a table or some other data driven tool would be problematic at best. If you want to log the information to a table, you should select the SQL Server provider in the logging options in the first place.

Logging to SQL Server

Logging to a SQL Server table gives you querying power of the database engine. To set this up, go back to the logging menu by right clicking in the control flow of the package and select logging. Delete the text logging if it still exists. Once in the Logging window you will need to place a check next to the package name in the top left. Select SSIS log Provider for SQL Server and click add. Then place a check next to the newly added log provider. Under Configuration select a Database where the logging information will be saved and run the package. This example will log to a database named testing.

The same logging details and options exist as in the text file logging example above. Select the following event handlers, OnError, OnWarning, OnPostExecute, and OnPreExecute. Now run the package. After the package has completed, open SQL Server Management Studio (SSMS) and run the following query in in the Testing database selected in the logging options.

Select * From SysSSISLog

This will return the logging data. This table is found in the system table folder of the database. Now that the data is in a table you have a lot more control of how to display the data. The issue still exist where the messages (which is the information you need) is duplicated. It is shown once for the package and once for the tasks that sent the message. To control how often the data is written to the table you will need to build a custom solution in the event handler of the package.

SSIS packages are great ETL tools and can do just about anything you need in terms of ETL. Most organizations start out creating SSIS package one by one until they have dozens, hundreds, or even thousands of packages. I have worked with one client that ran over 4,000 packages. This can be a nightmare to maintain. You can save yourself a lot of work by deciding upfront how to configure your packages using configuration files or tables. We are going to discuss configuration tables in this article.

We are going to look at a simple example of passing information to a package with a configuration table. Then we will go over using configuration tables on multiple packages. Imagine running dozens of packages that point to a server and the server name changes. If you have a configuration table that is feeding this server name to every package you can make a single change to the configuration table and all the packages are updated. This can reduce your maintenance time significantly.

We are going to build a couple of simple packages with connections. These packages will connect to the adventure works database which is freely available on codeplex. Since you will not being pulling any data you can use any database you would like.

1. Drag in an Execute SQL Task into a blank SSIS package.

2. Double click on the Execute SQL Task to edit it.

3. Click on the connection drop down menu and select New Connection.

4. Click New.

5. Enter in your server name and the adventure works database in the Connection Manager.

6. Click ok twice to get back to the Execute SQL Task.

7. Enter “Select 1” as the SQL statement

(This query will not pull any data. It is just used to test the connection)

8. Click ok and debug the package. The Execute SQL task should turn Green indicating success.

Now you will create a configuration table to feed the value of the connection to the package.

9. Stop the package from debugging.

10. Right click in the connection manager and select new OLEDB connection

11. Create a connection to a blank database. In this example we will use a database called Config.

12. Right click in the control flow and select Package Configurations

13. Place a check next to Enable Package Configurations.

14. Click add; (Click next if the welcome screen appears.)

15. Select SQL Server in the Configuration Type drop down menu.

16. Set the connection to the Config Database

17. Click the New button next to the Configuration Table Dropdown menu.

18. Click ok. This will create a table in the Adventure Works database.

19. In the configuration filter type Development.

20. Click next

21. Place a check next to the connection string property of the adventure works connection

22. Click Next, Finish, and Close.

23. Debug the package again, the Execute SQL Task will still turn Green

The Execute SQL task is not using the connection saved in the connection manager. It is using the value that was saved in the config database on the SSIS Configurations table. You do not have to leave the name of this table. In fact most businesses have a practice of no spaces in table names. So you could have created the table name SSISConfig or any name you prefer. You can test where the package is getting the adventure works connection string by changing the connection on the package to a database that does not exist.

24. Double click on the adventure works connection in the connection manager.

25. Change the database name to adventureworks1

26. Debug the package and you should see the Execute SQL task turn Green.

(There is a blue dot next to the Adventureworks2008 connection indicating the connection is fed from the configuration manger, this is a feature of because BI xPress.)

27. Open SQL Server Management Studio and view the values in the table.

28. Return to the SSIS package and make a copy of the package in the project. Click on the package in the Solution explorer and pres Ctrl+C then Ctrl+V. A copy of the package will appear below it in the project.

29. Double Click on the package

30. Debug this package.

Notice this new package runs successfully. It is using the same configuration table the first package is using. If you make a change to the table both packages will be updated. This is a major time saver in maintaining the packages in the future. Imagine having one hundred packages that have a single change like the name of a server or database name. Updating these packages becomes a one minute task instead of a one hour task. A proper configuration setup also saves you from having to redeploy the packages.

Data mining is a great way to help your company make decisions and predict future values. The Data Mining Algorithms built into SQL Server Analysis Services gives you this power.
The Adventure works data base comes with views that are already set up to perform data mining. Let’s take a look at one of these views. Open the Target Mal view by right clicking on it in SSMS and select Design.
 
The design view shows you the query used to create the view. The dim Customer Table and another view are used to create the Target Mail view. From Dim Customer we get a lot of the customer information and the other view has the bike purchase information. This gives you a simple view of who has bought a bike and the attributes of those customers.
In the query you can see a Case Statement that just gives a one for bike buyers and a zero for non bike buyers.
 

Now that you understand the data you can go into BIDS and perform some data mining.
In BIDS start an Analysis Services project, and then create a Data Source and a Data Source View containing the Target Mail View.
The first step is to create a mining structure. You can think of the mining structure as the blue print for the data mining models that are going to be created on the mining structures.
1. Right Click on the Mining Structures folder in the Solution Explorer and select New Mining Structure. Next->
 

2. Click the radio button next to From Existing relational database or data warehouse. Next->
 

3. Select Microsoft Decision Trees. Next->
 

4 Select the Data source view that contains the Target Mail View. Next ->
5. Place a check next to the vTargetMail view under case. Next –>

 
Now you are on the screen asking what data you want to use to train your mining model. Here you will place a check next to the columns you want to use in determining which members will most likely be bike buyers based on your existing customers. You also need to select a Key and a Predict column. The Key will be the Customer Key and should already be checked. The Predict columns will be the BikeBuyer because that is what we are trying to predict.
6. Place a check next to Bike Buyer under Predict.
7 .Place a check under input next to the following columns: Age, Commute Distance, Gender, House OwnerFlag, Mairtal Status, NumberCarsOwned, NumberChildrenHome, Region, TotalChildren, and Yearly Income. Next->
 

8. Click the detect button. This is setting the data types for the mining model.  A description of each of these types can be found here. http://msdn.microsoft.com/en-us/library/ms174572.aspx 
 

9. Leave the Percentage for testing set to 30%. Next->
 

10. Name the Structure and Model and select Allow Drill Through. Finish->
 

You have now created the Mining Structure and a Mining model using the Decision Tree Algorithm. Now it is time to process and deploy the model. Click on the Mining Models Tab and view the Model. Notice the Bike Buyer is set to Predict Only and the Customer Key is the Key. The rest of the columns are set to input.
 

11. Click on the Mining Model Viewer, you will receive a popup asking to process and deploy, click yes. You might receive other popup warning about the number of rows, click yes.
 

12. Click Run in the process screen.
 

13. Click Close on the process screen once the process is complete.

 

14. Click Close the process screen too.
Now you will be in the Mining Model Viewer and be able to see the Decision Tree model Notice how age is in the first level of the tree. Now browse the model and you can see what traits of the customers are most likely to buy a bike. Change the background to 1 to see the most likely bike buyers. Slide the level over to the right to see all levels.
Click on a level and look at the properties on the right and see the probability of them being a bike buyer.
 

Congratulations you have created you first Data Mining Structure and Model.

Previously I wrote a blog on how to do a for each loop to look through each col in an SSIS data flow here. Well things have changed since I wrote that blog, in fact I believe that old  code only works in SSIS 2005. So I thought I would be good to update to SSIS 2008 and show you new and better way to loop through all of the columns in a script tasks.

You could do the same work with a derived column. The problem comes when you have hundreds of columns and you need to do the same work on each column. The for each column saves a ton of time for development and make maintaining the code easier. It does hurt performance though. Derived columns perform much faster than script task.

The below code I found on the MSDN forums here. This code will replace all double quotes in every column with empty string. You can replace that one line of code with any work you need to do on multiple columns.

Private inputBuffer As PipelineBuffer

   Public Overrides Sub ProcessInput(ByVal InputID As Integer, ByVal Buffer As Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer)
       inputBuffer = Buffer
       MyBase.ProcessInput(InputID, Buffer)
   End Sub

   Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
       Dim counter As Integer = 0
       Dim colstr As String = “”
       For counter = 0 To inputBuffer.ColumnCount – 1
           colstr = (inputBuffer.Item(counter).ToString())

           inputBuffer.Item(counter) = Replace(colstr, Chr(34), “”)

       Next
   End Sub

I tested  this code in SSIS 2008 r2. Let me know if it works in your version.