SSIS - How To Read Object Type Variable In SSIS Script Task

 

Scenario:

It is frequently asked question, I can read object type variable by using For-each Loop in SSIS but How  can I read the object type variable in Script Task.

Solution: Here is quick demo that will show you step by step approach to read Object Type variable in Script Task.

Step 1: ­­­­­­­­­­­­­

Create connect to your SQL Server and then use Query in Execute SQL Task as given below and save the result set to VarObject Variable that is object type variable.

Select 1 as ID, 'AAMIR' AS Name

Union All

Select 2 as ID, 'Raza' AS Name

Set the Result Set to VarObject Variable

Step 2:

Configure Script Task as shown in snapshots for your SSIS Package

Select the Script Language and Variable you need to use in Script Task

Add the Code as shown in snapshot to your Script task

Code that you see in snapshot added by me in Script task

using System.Data.OleDb;
OleDbDataAdapter A = new OleDbDataAdapter();
            System.Data.DataTable dt = new System.Data.DataTable();
            A.Fill(dt, Dts.Variables["User::VarObject"].Value);
// TODO: Add your code here
foreach (DataRow row in dt.Rows)

            {

                string ID;
                string Name;
                object[] array=row.ItemArray;
                ID = array[0].ToString();
                Name = array[1].ToString();
                // I Declare both variables ID And Name as String, So I can show in Messagebox. You can Declare ID as INT and set the value and use
                //However you want to use.
                MessageBox.Show("ID Value="+ ID +" AND Name Value=" + Name);

     }

Final Output:

Run your SSIS Package, It is going to Run Execute SQL Task in which it will load the Result Set to VarObject Variable and then inside Script task we are using VarObject variable to read values from and displaying them.

http://sqlage.blogspot.in/2013/07/ssis-how-to-read-object-type-variable.html

Top 10 SSIS 2012 Improvements

1. ODBC Support

The ODBC support is becoming first class now I guess because of the future full integration with Hadoop and an increased demand to integrate more easily with various open source platforms. So I guess the days when you will be able to easily connect to a Linux machine from a SQL Server are coming. Attunity connectors also get more readily available and covering more vendors.

2. Change Data Capture for SSIS

The Change Data Capture (CDC) is not new to SQL Server, but it is a new kind of an animal to SSIS:

Now with CDC one can easily capture the changes in data sources and provide them for reporting, data analysis or feed into the Data Warehouse.

3. Support for Variable Number of Columns in a Flat File

This is a productivity enhancement that potentially pays for a good portion of the upgrade fee (IMHO). I just happen to see how many developers stumble upon such a reality unable to overcome this barrier resorting to various online forums or blogs. No longer!
If you see a file as depicted below:

No fear, it will be understood by the SSIS engine and handled without incidents:

Hooray! No more time wasted and scratching your head!

4. Revamped Configurations

This is another big improvement.
Did you ever wonder why you deployed a package and it took the design time parameters? Did you struggle to deploy your config files or a database along with the package?
No longer! You now can have several configurations, for Dev and Prod, no problem. If you envied your fellow C# or VB .Net developer being able to store parameters right in the Visual Studio, no more, now you can, too. As an aside, there is no more BIDS, there is the new Data Tools, but to me it is a Visual Studio 2010, I just develop special projects in it, and it is a 1st class tool! And how about this: you can even add parameters after the package has been deployed? Do you feel thrilled as me? Not yet, then how about the possibility of sharing parameters across many packages within a project?

5. Script Component – you can debug it, finally!

If your heart is not beating faster by now, then let’s recall how much you struggled to find out why a Script Component does not work as expected? A value, or worse yet, three are not right?
Remember? No? I do, I remember how I needed to build a console app till 10 PM to just solve the mystery why the values were wrong sitting along in the office biting nails because at midnight a package just had to load the latest flight data. I wish I could just debug the mysterious component with 400 lines of code. Sigh and smile, now I will:

Better yet, all my runtime values are captured. Did I say it is a Visual Studio?

6. SSIS Package Format Changed and the Specs are Open Source!

Bye-bye the lineage IDs, cryptic, long XML! Hello comparable, mergable packages!
vs.
Easily compare packages with Diff tools now! Full specs are at: http://msdn.microsoft.com/en-us/library/gg587140.aspx

7. Built-in Reporting

Yes, there will be three canned reports provided for You, dear developer to benchmark, troubleshoot and just better support a live implementation:

8. Data Taps

This is totally new: have you ever been asked to fix a package with no rights to access the data source? I had such an “opportunity”, their DBA just shrugged off my requests to provide with a read only account. But now you are more in control, you can now turn on and off small data dumps to a CSV file for an ad-hock analysis. Those, most often, are instrumental in finding metadata differences and thus allowing a real quick fix to many issues. More on this topic is here: http://goo.gl/AUBP5

9. Deploying Projects from Visual Studio

Yes, like I said, Visual Studio is the centerpiece to developing and deploying a SSIS solution. Now you need to think more project oriented as a result, so there is a bit of paradigm shift, or I would say you need to think of a project as unit more than of a package now in SSIS 2012 (for those not ready for the change the old deployment model still works, so not to worry).
So what is different, actually all and more simple, you just deploy with a right-click on the project, no more fiddling around with the Deployment manifest or manual copy and paste, import, etc.
The configurations are taken care of automatically!

(picture is taken from Rafael Salas blog http://www.rafael-salas.com/2012/01/ssis-2012-project-deployment-model-and.html).

10. Manage with PowerShell

Did I mention about the PowerShell book at the beginning of the post? I did this on purpose . SSIS 2012 provides with 1st class support to managing the SSIS indices as the SSIS catalog, package deployment and maintenance. You can craft and automate most tasks using an editor, just reference the needed module:

There are also the APIs to validate a package, configure and deploy a package:

Oh, I have just already covered 10 improvements, wait but there are more:

• Un-do and Re-do are now possible (I can hear the wow!);
• New designer surface (AKA canvas) with adorners

• Shared (across a project) Connection Managers (no more click and copy, pastes)!
• Shared (across packages in project) Cache Managers
• Do you remember the dreaded errors all over the package after some metadata changed? Now you can resolve them all up the stream with a single click!
• Group items to reduce clutter without resorting to sequence containers:

• The ability to rollback to an older (and working) version of a package:

I can hear the applause…

source http://geekswithblogs.net/Compudicted/archive/2012/03/06/top-10-ssis-2012-improvements.aspx

Integration Services 2012 new development features

Project Connection Managers

This release allows you to create connection managers at the project level that can shared by multiple packages in the project. The connection manager you create at the project level is automatically visible in the Connection Managers tab of the SSIS Designer window for all packages. For details, see Add, Delete, or Share a Connection Manager in a Package.

Offline Connection Managers

Integration Services now validates all connections before validating all of the data flow components when a package is opened and sets any connections that are slow or unavailable to work offline. This helps to reduce the delay in validating the package data flow. After a package is opened, you can also turn off a connection by right-clicking the connection manager in the Connection Managers area and then clicking Work Offline. This can speed up operations in the SSIS Designer.
For more information, see Troubleshooting Tools for Package Development.

Flat File Connection Manager Changes

The Flat File connection manager now supports parsing files with embedded qualifiers. The connection manager also by default always checks for row delimiters to enable the correct parsing of files with rows that are missing column fields
For more information, see Flat File Connection Manager.

Parameters

Integration Services (SSIS) parameters allow you to assign values to properties within packages at the time of package execution. You can create project parameters at the project level and package parameters at the package level. Project parameters are used to supply any external input the project receives to one or more packages in the project. Package parameters allow you to modify package execution without having to edit and redeploy the package. For more information, see Integration Services (SSIS) Parameters.

Execute Package Task and Parameters

In this release, you can configure the Execute Package task to to make values available to the child package by mapping variables or parameters to child package parameters. For more information, see the Passing Values to Child Packages section of Execute Package Task.

Comparing and Merging Packages

The structure of package .dtsx files has been modified to make it easier for you to compare packages. You can also more reliably merge packages that don’t contain conflicting changes or changes stored in binary format.
For more information, see SSIS Package Format.

Undo/Redo in SSIS Designer

You can undo and redo up to 20 actions in the SSIS Designer. For a package, undo/redo is available in the Control Flow, Data Flow, Event Handlers, and Parameters tabs, and in the Variables window. For a project, undo/redo is available in the Project Parameters window.
For details, see SSIS Designer.

Column Mapping

When an error occurs due to a column mapping problem in the data flow, such as missing columns, the error icon appears on the path or above the component if no path is connected. In addition, you can still edit the component when there is a mapping problem.
The SSIS Designer is now smarter about re-mapping columns when a new data source is connected. Columns are re-mapped based on their name and data type rather than by lineage ID.
You can more easily resolve issues with disconnected input paths and unmapped columns in an execution tree, by using the Resolve Column Reference Editor.

Script Task and Script Component

If you create a package by using SQL Server Data Tools - Business Intelligence for Visual Studio 2012 that includes a Script task or Script component, you can configure the script project to target the .NET Framework 4.5 class library. You set the Target framework property in Microsoft Visual Studio Tools for Applications (VSTA).You can install SQL Server Data Tools - Business Intelligence for Visual Studio 2012 side by side with SQL Server Data Tools (SSDT) that is included in SQL Server 2012.Script tasks and Script components that target the .NET Framework 4.5 class library, cannot be edited in SQL Server Data Tools (SSDT) that is included in SQL Server 2012. The workaround is to load the package in SQL Server Data Tools - Business Intelligence for Visual Studio 2012 and change the script project to target the .NET Framework 4.0 class library.
It is recommended that if you plan to edit the package in both SQL Server Data Tools - Business Intelligence for Visual Studio 2012 and SQL Server Data Tools (SSDT) that is included in SQL Server 2012, to configure the script project to target the .NET Framework 4.0 class library.

Reduced Memory Usage by the Merge and Merge Join Transformations

Microsoft has made the Integration Services Merge and Merge Join transformations more robust and reliable. This is achieved by reducing the risk that these components will consume excessive memory when the multiple inputs produce data at uneven rates. This improvement helps packages that use the Merge or Merge Join transformations to use memory more efficiently.
Microsoft has also provided new properties and methods for developers of custom data flow components to implement a similar solution in their own components. This improvement makes it more feasible to develop a robust custom data flow component that supports multiple inputs. For more information, see Developing Data Flow Components with Multiple Inputs.

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

Read write Excel using SSIS without installing MS Office

Many times, SSIS deployment is the quirt difficult in some cases. Suppose our SSIS is using excel files but MS office does not installed on the target environment.in that case, missing of data access component on the machine stops working of SSIS package. Then following any one installation on the machine can solve this issue.
2007 Office System Driver: Data Connectivity Components 

This download will install a set of components that facilitate the transfer of data between existing Microsoft Office files such as Microsoft Office Access 2007 (*.mdb and *.accdb) files and Microsoft Office Excel 2007 (*.xls, *.xlsx, and *.xlsb) files to other data sources such as Microsoft SQL Server. Connectivity to existing text files is also supported.ODBC and OLEDB drivers are installed for application developers to use in developing their applications with connectivity to Office file formats.
For download this installer go to the URL
http://www.microsoft.com/en-us/download/details.aspx?id=23734

Microsoft Access Database Engine 2010 Redistributable 
This download will install a set of components that facilitate the transfer of data between existing Microsoft Office files such as Microsoft Office Access 2010 (*.mdb and *.accdb) files and Microsoft Office Excel 2010 (*.xls, *.xlsx, and *.xlsb) files to other data sources such as Microsoft SQL Server. Connectivity to existing text files is also supported. ODBC and OLEDB drivers are installed for application developers to use in developing their applications with connectivity to Office file formats.

For download this installer go to the URL
http://www.microsoft.com/en-us/download/details.aspx?id=13255

ETL naming conventions - SSIS

During the developing of ETL for the Data Warehouse and the ETL Meta Data (EMD) framework a range of objects will need to be named and recorded in the EMD repository. To ensure that EMD objects are given meaningful and consistent names , the following set of naming conventions is provided. Below are the SSIS Naming Conventions we always use to append and prepend to the visual controls provided in BIDS. Well a truth to be said is that ETL using SSIS tends to become spaghetti or Amazonia. Worst that’s hard to debug once done deployed. Hence we used to stick to the naming conventions in all of the projects we worked on in order for us to better troubleshoot.

The acronyms below should be used at the beginning of the names of tasks to identify what type of task it is:

	1. Control
	Task	Prefix
	For Loop Container	FRLC
	Foreach Loop Container	FELC
	Sequence Container	SEQC
	ActiveX Script	AXSC
	Analysis Services Execute DDL	ASED
	Analysis Services Processing	ASSP
	Bulk Insert	BLKI
	Data Flow	DTFL
	Data Mining Query	DMQR
	Execute DTS 2000 Package	EDPK
	Execute Package	EPKG
	Execute Processs	EPRC
	Execute SQL	ESQL
	File System	FSYS
	FTP	FTP
	Message Queue	MSMQ
	Script	SCRP
	Send Mail	SNDM
	Transfer Database	TRDB
	Transfer Error Messages	TREM
	Transfer Jobs	TRJB
	Transfer Logins	TRLI
	Transfer Master Stored Procedures	TMSP
	Transfer SQL Server Objects	TRSO
	Web Service	WSRV
	WMI Data Reader	WMID
	WMI Event Watcher	WMIE
	XML	XML
	2. Maintenance
	Task	Prefix
	Back Up Database Task	BUDB_TSK
	Check Database Integrity Task	CDBI_TSK
	Execute SQL Server Agent Job Task	SSAJ_TSK
	Execute T-SQL Statement Task	TSQL_TSK
	History Cleanup Task	HCLN_TSK
	Maintenance Clean Task	MCLN_TSK
	Notify Operator Task	NTFO_TSK
	Rebuild Index Task	RBDI_TSK
	Reorganize Index Task	RORI_TSK
	Shrink Database Task	SHDB_TSK
	Update Statistics Task	USTA_TSK
	3. Data Flow Components - Source
	Component	Prefix
	ADO NET Source	ADO_SRC
	Excel Source	EX_SRC
	Flat File Source	FF_SRC
	OLE DB Source	OLE_SRC
	Raw File Source	RF_SRC
	XML Source	XML_SRC
	4. Data Flow Components - Transformation
	Component	Prefix
	Aggregate	AGRG
	Audit	AUDT
	Cache Transform	CFRM
	Character Map	CHRM
	Conditional Split	CSPL
	Copy Column	CPYC
	Data Conversion	DCNV
	Data Mining Query	DMQR
	Derived Column	DERC
	Export Column	EXPC
	Fuzzy Grouping	FZGR
	Fuzzy Lookup	FZLK
	Import Column	IMPC
	Lookup	LKUP
	Merge	MRGE
	Merge Join	MRGJ
	Multicast	MLTC
	OLE DB Command	OLEC
	Percentage Sampling	PSMP
	Pivot	PIVT
	Row Count	RCNT
	Raw Sampling	RSMP
	Script Component	SCRC
	Slowly Changing Dimension	SLCD
	Sort	SORT
	Term Extraction	TEXT
	Term Lookup	TLKP
	Union All	UALL
	Unpivot	UPVT
	5. Data Flow Components - Destination
	Component	Prefix
	ADO Net Destination	ADO_DST
	Data Mining Model Training	DMMT_DST
	DataReader Destination	DR_DST
	Dimension Processing	DP_DST
	Excel Destination	EXL_DST
	Flat File Destination	FF_DST
	OLE DB Destination	OLE_DST
	Partition Processing	PP_DST
	Raw File Destination	RF_DST
	Recordset Destination	RS_DST
	SQL Server Compact Destination	SC_DST
	SQL Server Destination	SS_DST
	SQL Server Mobile Destination	SM_DST