SSIS Package Development - Issues and Solutions

Content Listing :

1.Load Data from flat file(CSV file) to temp table

2.In SSIS2012, differentiate variables and parameters.

3.For DB connection, when you change to new SQL server, you need to update the connectionString, especially Provider.

4.Error when run in SQL2012 (although the same package work well with SQL2005) :"Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AI" in the equal to operation.".

5.Deploy SSIS project to SQL 2012

6. EncryptionLevel

7.Write to Event log in SSIS package 

1.Load Data from flat file(CSV file) to temp table

example to follow :

1) http://www.sql-server-performance.com/2013/temp-tables-ssis/

2) http://stackoverflow.com/questions/5631010/how-to-create-a-temporary-table-in-ssis-control-flow-task-and-then-use-it-in-dat

需要注意的是

1.Set the property RetainSameConnection on the DB Connection Manager to True so that temporary table created in one Control Flow task can be retained in another task.

2.Set DelayValidation for that task as “True” for all tasks that related to temp table

3.特别注意上面1)例子中，如何使用temp table的。 要通过menu - > SSIS-> work offline ,reset SQL statment using temp table. then, uncheck work offline. and test it. 

4.Data conversion component can be used for data type conversion.

5.The most important thing in my case, is that, when data is loaded to temp table, 

   i)I need to select data by combing temple table and existing table. 

   ii)Then insert/update  existing table with selected data.

In this case, need to create TWO DB connection, although everything happens in the same database.  Just as sample 1).

Conn1 : a)  create temp table

              b) load data to temp table

              c) In the DFT(data flow task) , data source is the selection base on temp table and existing table

Conn2: a) In the DFT, the destination , to update the master table(existing one) 

It DOES NOT WORK if using one connection only.

2.In SSIS2012, differentiate variables and parameters.

variables are like private variable, while parameters are like public/global variables.Parameters can be change before executing the package.

refer to http://www.angelsbiblog.com/2012/12/ssis-2012-favorites-parameters.html

3.For DB connection, when you change to new SQL server, you need to update the connectionString, especially Provider.

Here are samples for SQL2005 and SQL 2012

SQL2012  : Data Source=XX.XX.XX.XX;User ID=abc;Initial Catalog=STA1_ENV;Provider=SQLOLEDB;Persist Security Info=True;Auto Translate=False;

SQL2005 : Data Source=XX.XX.XX.XX;User ID=abc;Initial Catalog=STA1_ENV;Provider=SQLNCLI11.1;Persist Security Info=True;Auto Translate=False;

Connection String for Windows Authentication : Data Source=XX.XX.XX.XX;Initial Catalog=GRTX_STA1_ENV;Provider=SQLOLEDB;Integrated Security=SSPI;

How to know the provider, you can refer to this link :

http://www.gotknowhow.com/articles/test-a-database-connection-string-using-notepad

I just came across a way to test a data providers connection string (like a SQL Server database) with the help of a plain text file using Notepad.  To investigate and test out if your connection string works, your going to want to create a UDL file. To do this, follow these steps:

1. Open up Notepad and create an empty text file, then click File -> clickSave -> and  save it with the File name: TestConnection.udl to your desktop.
2. Go to your desktop and double-click on the TestConnection.udl file you just created and the Data Link Properties box will popup.
3. Select the Provider tab and Find the provider that you want to connect with and click Next >>.
4. Now from the Connection tab, select or enter your source/ server name -> then enter information to log on to server -> and select the database on the server. 
5. Click Test Connection and click OK to save the file.

Note: If errors occur during testing of your connection string, you will get a popup box with the error message.

Once, you've successfully tested your connection string, now go and compare the details of your TestConnection.udl with your (website) project connection string to see if they are similiar.

4.Error when run in SQL2012 (although the same package work well with SQL2005)

SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available.  Source: "Microsoft OLE DB Provider for SQL Server"  Hresult: 0x80040E14  Description: "Statement(s) could not be prepared.".
An OLE DB record is available.  Source: "Microsoft OLE DB Provider for SQL Server"  Hresult: 0x80040E14  Description: "Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AI" in the equal to operation.".

Solution　：　http://social.msdn.microsoft.com/Forums/en-US/4e15daed-d347-484d-b7bb-7000a46822e3/getting-err-cannot-resolve-the-collation-conflict-between-sqllatin1generalcp1cias-and?forum=sqlsetupandupgrade

select * from [Product] p join [category] c 
on  
c.[Name] collate SQL_Latin1_General_CP1_CI_AS 
= 
p.[Name] collate SQL_Latin1_General_CP1_CI_AS

sample :

SELECT 
t1.AcquirerID,t1.ShopID,t1.DeskID,
SoftwareGroup,
getdate() UpdateDate,
'TMSCallUpdateJob_UPD'  UpdateUser
FROM  dbo.[#tmpTMSCallData]  t1
LEFT JOIN MATERMCFG t2 
ON  t1.AcquirerID collate SQL_Latin1_General_CP1_CI_AS   = t2.ACQ_ID  collate SQL_Latin1_General_CP1_CI_AS  
AND t1.ShopID collate SQL_Latin1_General_CP1_CI_AS  = t2.MERCHANT_ID collate SQL_Latin1_General_CP1_CI_AS  
AND t1.DeskID collate SQL_Latin1_General_CP1_CI_AS  = t2.TERMINAL_ID collate SQL_Latin1_General_CP1_CI_AS  
Where t1.SoftwareGroup IS NOT NULL  and len(t1.SoftwareGroup)>0

5.Deploy SSIS project to SQL 2012

refer to http://www.sqlservergeeks.com/blogs/frombhagwan/sql-server-bi/668/sql-server-steps-to-create-integration-services-catalogs-and-deploy-ssis-package-in-sql-server-2012-ssisdb

6. EncryptionLevel

http://technet.microsoft.com/en-us/library/ms141747.aspx

http://joethebusinessintelligenceguy.wordpress.com/2013/08/14/ssis-2012-using-sql-authentication-with-dont-save-sensitive-successfully/

1) By default , SSIS2012 set Encryption level as EncryptSensitiveWithUserKey for both project level and package  (The Encryption for project and package should be same level)

2)How to set  "DontSaveSensitive"

i)when the project tested properly with the default set (EncryptSensitiveWithUserKey)

ii)set workoffline  (menu SSIS->Work Offline)

iii)In project properties window, in "Security" section , select "DontSaveSensitive"

iiii)In package properties window,(if "Security" section not showing,click the Designer), in "Security" section , select "DontSaveSensitive"

V) Right click the project and click "Rebuilt", then the built project file can be deployed to different domain server with different user without warning 

7.Write to Event log in SSIS package 

http://msdn.microsoft.com/en-us/library/6s7642se(v=vs.110).aspx

if(!EventLog.SourceExists("MySource"))
        {
            // An event log source should not be created and immediately used. 
            // There is a latency time to enable the source, it should be created 
            // prior to executing the application that uses the source. 
            // Execute this sample a second time to use the new source.
            EventLog.CreateEventSource("MySource", "MyNewLog");
            Console.WriteLine("CreatingEventSource");
            Console.WriteLine("Exiting, execute the application a second time to use the source.");
            // The source is created.  Exit the application to allow it to be registered. 
            return;
        }

        // Create an EventLog instance and assign its source.
        EventLog myLog = new EventLog();
        myLog.Source = "MySource";

        // Write an informational entry to the event log.    
        myLog.WriteEntry("Writing to event log.");

        Console.WriteLine("Message written to event log.");   

1) for the above codes(EventLog.SourceExists("MySource")), there is permission issues, use try .. catch to see what the exception

my issue is :  permission denied when  checking the SourceExists or not. Then I add system user to Administrator group. and  run the Visual Studio by Administrator. It works.

2) When writing event entry, there is no access issue. To make is simple, I just use power shell script(  [System.Diagnostics.EventLog]::CreateEventSource("TMSCallUpdate", "Application")

) to create the source first, then in the SSIS script to write event  without checking the source existence.