SSIS Grammer Series - Article 1

• Hi,
  
  I am writing here some SSIS basics and SSIS glossary. I think it may be useful for quick reference.
  
  
  
  Control Flow Items
  Various Control Flow Items:
  · Bulk Insert task: The Bulk Insert task lets you perform a fast load of data from flat files into a target table. It’s similar to using BCP to bulk copy a file into a table, and is appropriate for loading clean data. If you need to transform data that’s stored in a flat file, you should instead use a data flow task that uses the flat file as a source.
  · Execute SQL task: The Execute SQL task, as you might expect, will execute a SQL statement against a specified database. The database need not be SQL Server, but the statement must be written in the appropriate dialect of SQL. Use this task to perform database operations like creating views, tables, or even databases. A common use of this task is to query data or metadata, and to put the query results into a variable.
  · File Transfer Protocol and File System tasks: Use the File Transfer Protocol task to transfer files and sets of files. Use the File System task to perform file system operations like copying, moving, or deleting files and folders.
  · Execute Package, Execute DTS2000 Package, and Execute Process tasks: The Execute Package task executes an Integration Services package. With it you can (and should) break a complex workflow into smaller packages, and define a parent or master package to execute them. Create a separate package to populate each table in your data warehouse database. The use of parent and children packages enables the modularization and reuse of complex logic. If you have DTS2000 packages running already in your production system, you can run them with the Execute DTS2000 Package task. The Execute Process task will run any operating system process. For example, you may have a custom process that will generate the source system extract, or you may invoke a non-SQL Server relational database’s bulk loader.
  · Send Mail task: The Send Mail task sends an email message. You will almost certainly use this task in an event handler, for example to send a message to an operator about a processing failure.
  · Script and ActiveX Script tasks: These tasks are available to perform an endless array of operations that are beyond the scope of the standard tasks. The ActiveX Script task is provided for backwards compatibility to DTS2000; use the Script task for new work. The Script task uses Visual Basic .NET from the Visual Studio for Applications environment. Or, you can use any .NET language to create a custom task that will become available in the list of control flow tasks. Defining a custom task is a programming job, rather than simply scripting, but has the significant benefit of re-use.
  · Data Mining and Analysis Services Processing tasks: The Data Mining task runs an Analysis Services data mining query and saves the results to a table. The Analysis Services Processing task will launch processing on Analysis Services dimensions and databases. Use the Analysis Services DDL task to create new Analysis Services partitions, or perform any data definition language operation. There are Data Mining and Analysis Services Data Flow transforms, as well as these control flow tasks. Use the Analysis Services control flow tasks to fully or incrementally update your databases and models. The use of the corresponding Data Flow transforms is discussed in the next section.
  · XML and Web Services tasks: The XML task retrieves XML documents and applies XML operations to them. Use the XML task to validate an XML document against its XSD schema, or to compare or merge two XML documents. Use the Web Services task to make calls to a web service.
  · Message Queue, WMI Data Reader, and WMI Event Watcher tasks: These tasks are useful for building an automated ETL system. The Message Queue task uses Microsoft Message Queue (MSMQ) to manage tasks in a distributed system. You can use the WMI tasks to coordinate with the Windows Management Interface, and automate the execution of a package or set of tasks when a specific system event has occurred.
  · ForEach Loop, For Loop, and Sequence containers: Use containers like the ForEach Loop and For Loop to execute a set of tasks multiple times. For example, you can loop over all the tables in a database, performing a standard set of operations like updating index statistics. The Sequence container groups together several tasks. Use it to define a transaction boundary around a set of tasks so they all fail or succeed together. Or, use it simply to reduce the clutter on the design surface by hiding the detailed steps within the sequence. We used a sequence container in the package, for the Sales Reason and Sales Reason Bridge tables. You can also group control flow objects, and collapse or expand those groups. There’s no task for grouping. Simply select several objects, right-click, and choose Group..
  · Data Flow task: The Data Flow task is where most ETL work is performed. The Data Flow task is discussed in the next section.

SQL Server 2005 BI Video Tutorials

Hi Guys,

I am posting here some of good free SQL Serever BI video tutorials websites. I believe these web sites are good enough one who want to grab basics as well as some tips and tricks. I will keep update of this page as well as I find any new one. If any one knows any other web sites please post your comment here.

http://www.learnmicrosoftbi.com/ ( Only for SSAS )
http://www.jumpstarttv.com/ (All)

The value violated the schema's constraint for the column

HI,

Problem Source:

In general we get this type of error when we try to move data from Sql Server table ( or any other Source) to Excel 2007, If any of column data having more than 255 character length the transfermation gets fail and it will throw the above error.

Reason:

We need to remember we are exporting to Excel 2007 ( Connect thorugh OLEDB Connection Manager) not to Excel 2003 which will connect thorugh Excel connection manager.

There are siginificant differences between OLEDB Connection Manager and Excel Connection Manager.


When we try to export excel 2003 SSIS will convert all source data types to corresponding SSIS data types, becuase excel connection manager internally uses excel driver for its funtionality.

The Excel Connection Manager uses the Microsoft OLE DB Provider for Jet 4.0 and its supporting Excel ISAM (Indexed Sequential Access Method) driver to connect and read and write data to Excel data sources

The Excel driver recognizes only a limited set of data types.

For example, all numeric columns are interpreted as doubles (DT_R8), and all string columns (other than memo columns) are interpreted as 255-character Unicode strings (DT_WSTR).

Integration Services maps the Excel data types as follows:

• Numeric double-precision float (DT_R8)
• Currency currency (DT_CY)
• Boolean Boolean (DT_BOOL)
• Date/time datetime (DT_DATE)
• String Unicode string, length 255 (DT_WSTR)
• Memo Unicode text stream (DT_NTEXT)

So when we export data into excel 2003, SSIS first get 8 rows as sample from source, if any column data length > 255 character length in those 8 sample rows the excel connection manager will assign DT_Ntext by default else it assign DT_WSTR 255.

you can check it by click Advanced Editor on Excel Destination task. In the advanced editor on the External column and Properies tab check on External column s list the Column > 255 will assign DT_Ntext data type.

Hence it work in Excel 2003.

But the case in excel 2007 is different because we connect excel 2007 by using OLEDB connction amanager. It doesn't support DT_Ntext data type. So it always assign DT_WSTR 255 irrespective of source column length. So transfer mation will fail and throw above error.


Solution:

Export to excel 2003 instead of excel 2007. It's may be dirty solution, but no option.
If you have more than 65536 rows try to split into multiple sheets.

Note: Excel 2003 supports only 65536 rows per sheet. while Excel 2007 supports 10,00,000 rows per sheet.

Its all my experience only, it may be worng.

Please place any Suggestions and Comments .

Thanks,

SQl Queries to find Last and First date of Current Month, Previous Month, And Next Month

Hi All,

We might have encounter at many places write quries for dates like Last and First date of Current Month, Previous Month, And Next Month. Its very common we may forget some quries which we have written earlier in time, so there is no exception for these above simple quries too.

So I have written here below to find last and first of any month. Hope this will helps at least who are new to Sql.



Declare @InputDate DATETIME

Set @InputDate = GETDATE()

First Day Of Current Month =CAST(CAST(YEAR(@InputDate) AS VARCHAR(4)) + '/' +CAST(MONTH(@InputDate) AS VARCHAR(2)) + '/01' AS DATETIME)

Last Day Of Current Month = CAST(CONVERT(VARCHAR(10),DATEADD(DD, -1, DATEADD(M, 1, CAST(YEAR(@InputDate) AS VARCHAR(4)) + '/' +CAST(MONTH(@InputDate) AS VARCHAR(2)) + '/01')),101) +' 23:59:59' AS DATETIME)

హాయ్,
మనము ఒక ప్యాకేజీ డెవలప్ చేసినపుడు అది మన లోకల్ సిస్టం లో బాగా వర్క్ అవుతుంది. ఐతే ఎప్పుడయితే మనము ఆ ప్యాకేజీ ని ప్రొడక్షన్ సెర్వెర్స్ లో పెట్టినపుడు అది వర్క్ అవదు ఎందుకు అంటే మన లోకల్ సిస్టం జనరల్ గా 32 బిట్ ఆపరేటింగ్ సిస్టం మరియు 32 బిట్ సర్వర్ వెర్షన్ సాఫ్ట్వేర్ వాడుతుంటము. కాని మన ప్రొడక్షన్ సెర్వెర్స్ లో 64bit ఆపరేటింగ్ సిస్టం మరియు ౬౪ బిట్ సర్వర్ ఎడిసన్ ఊంటాయి. కనుక 32 బిట్ లో డెవలప్ చేసిన ప్యాకేజీ 64bit ఎన్విరాన్మెంట్ లో వర్క్ అవదు. ఇలా వర్క్ అవాలంటే ఫస్ట్ ప్యాకేజీ ని 64bit సర్వర్ లో కి మూవ్ చేసినపుడు మనము బిడ్స్ లో ప్రాజెక్ట్ ప్రోపెర్తీస్ కి వెల్లి సెట్టింగ్స్ చెంజు చెయ్యాలి. స్క్రీన్ షాట్ చుడండి. ఈ సెట్టింగ్స్ తర్వాత ఎర్రర్ రాదు.

ఈ ప్యాకేజీ ని రన్ చెయ్యడానికి జాబు క్రేఅతే చేస్తే అది కూడా ఎర్రర్ వస్తుంది. అప్పుడు ఈ ప్యాకేజీ ని 32 బిట్ కమాండ్ లైన్ టూల్ ద్వార రన్ చెయ్యవచ్చు. జాబు క్రీ అటే చేసే డప్పుడు కంమ్నాద్ లైన్ ఆప్షన్స్ కి వెళ్ళి కంమ్నాద్ లైన్ టూల్ ద్వార ఎక్సెకుతె చెయ్య వచ్చు. "C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /F "C:Sample.dtsx".

ఇది వర్క్ అవుతుంది. ఏమైనా డవుటు ఉంటే నా కు మెయిల్ చెయ్యండి.

మీ,

సతీష్