Another SQL Blog - Jason Yousef

SSIS iterate to update a blob column

2012-02-23T12:15:00.000-05:00

A recent question in EE about how to update a blob column using SSIS, so I decided to create a blog on it as it's a lot of steps and I'll need to send hi a lot of screen shots.

You have file names such as 04-191-388086.jpg & 04-191-388087.jpg and the 388086 & 388087 corresponds to the ID column on that table and you need to update these 2 IDs.

Preparations:

CREATE TABLE [Files](
[ID] [int] NOT NULL,
[FilePath] [varbinary](max) NULL)

Insert test records with no filePath...

INSERT INTO [Files](ID) VALUES (388086), (388087)

So I thought about using 3 variables:

1-String to hold the file name and path.
2-Int to hold the ID, An Expression of the FileNameLocation variable.
3-String to hold the update statement, and EXPRESSION using the ID and the FileNameLocation variable (Optional you could use a direct T-SQL in the execute SQL Task)

I've added a value to the FileNameLocation or SSIS will yell and gives you errors.

And constructed my package as follow:

1-Foreach Loop Container to iterate over the files.

2-Execute SQL Task to run the update statement.

The steps:

1-will configure the Foreach Loop Container to iterate over the folder and get me the file names into the STRING variable "FileNameLocation".

2-Configure the Execute SQL Task to use my database connection, and the Query variable to update the table.

3- Add that expression* on the ID variable, to shred the ID out of the file name.

Reverse(Substring(Reverse(right(@[User::StrFileNameLocation], FINDSTRING( REVERSE( @[User::StrFileNameLocation] ), "-",1) -1 ) ) ,5,20))

4-Add that expression* on the StrUpdate variable, which will be used as our query.

"Update Files set FilePath =( SELECT * FROM OPENROWSET (BULK '"+ @[User::StrFileNameLocation] +"', SINGLE_BLOB) FilePath) where ID =" + @[User::IntID]

To add expressions to the variables, check that post http://asqlb.blogspot.com/2012/01/ssis-expressions-in-variablesdynamic.html
5-Run the package and check your table...

You must use the Role Management Tool to install or configure Microsoft .NET Framework 3.5 SP1

2012-02-22T19:38:00.000-05:00

While installing SQL server 2012 RC0 on a Windows server 2008 R2 machine You might get an error telling you to install .Net 3.5 SP1.

Although I see it installed and did all the windows updates and .NET 4.0 is even installed.
So when you go to the download site and try to install it.

http://www.microsoft.com/download/en/confirmation.aspx?id=22

You get another error....

It seems that Windows server 2008 R2 comes pre-installed but not configured or enabled.

You just need to go to Sever Manager ---> Features--->Add a feature . and enable it...

Or run that command... %windir%\system32\ocsetup.exe NetFx3 to configure it too.

Hope that helps someone...

SSIS send e-mail from a SQL table

2012-02-13T21:52:00.002-05:00

Hi, This is simple but people ask about it all the time...instead of keep writing the steps, I finally decided to blog about it.

you have a table that holds some names along with their e-mail addresses.

You'll need 2 variables, one of type OBJECT, one of type STRING

1-Add a "Execute SQL Task"

Add the connection
Add your query that brings the e-mail addresses
Get back the FULL RESULT SET

In the result set, use your OBJECT type variable, to hold the result set

2- Add Foreach Loop Container

Use Foreach ADO
Select your OBJECT variable
Rows in the first table

In the variables mapping, map your STRING variable.

Now the container will iterate over the OBJECT variable and shred the contents into the STRING variable.

Now add your SEND MAIL TASK, configure it, then on the expressions, select To LINE and click on the ellipsis, add your STRING variable...

Now it'll iterate over the rows and send a mail one by one to the TO LINE from the string variable.

Hope that helps someone..

Get a folder's file list to SQL table using VB.net

2012-02-12T12:16:00.000-05:00

A Recent challenge about how to get the folder's list of files int oa SQL table for file comparision.

I create a table called "FileList" under my test database called "Admin"

Use [Admin]
GO

CREATE TABLE [dbo].[FileList](
    [ListID] [int] IDENTITY(1,1) NOT NULL,
    [DateTime] [datetime] NOT NULL ,
    [Folder] [varchar](100) NOT NULL,
    [FileName] [varchar](100) NOT NULL)

I'll use the below script in a script task in SSIS.

Make sure to change the scripting language to Visual Basic 2008

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO
Imports System.Data.SqlClient
<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
    Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    Enum ScriptResults
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    End Enum
    Public Sub Main()
        Dim SQLStr As String    'SQL string to hold the root query
        Dim ConnString As String
        'SQL server connection to "Localhost" and my Database called "Admin"
        ConnString = "Data Source=localhost;Initial Catalog=Admin;Integrated Security=SSPI;"
        Dim SQLConn As New SqlConnection()
        Dim SQLCmd As New SqlCommand()
        SQLConn.ConnectionString = ConnString
        SQLConn.Open() 'open connection
        SQLCmd.Connection = SQLConn
        'write root file list
        Dim di As New IO.DirectoryInfo("c:\")
        Dim fi As IO.FileInfo() = di.GetFiles()
        Dim f As IO.FileInfo
        'list the names of all files in the specified directory
        For Each f In fi
            SQLStr = "INSERT into FileList(DateTime,Folder,FileName) VALUES ('" + Now() + "','" + di.ToString + "','" + f.ToString.Trim + "')"
            SQLCmd.CommandText = SQLStr
            SQLCmd.ExecuteNonQuery()
        Next
        SQLConn.Close()
        Dts.TaskResult = ScriptResults.Success
    End Sub
End Class

The results...

Hope that helps someone...

Prevent a certain text string from being entered in a SQL Server database

2012-02-11T23:27:00.000-05:00

A weired requirement about how to prevent a string value at the database level...at EE I advised to use a cursor to iterate over the tables, grab all the string data types and add a check constraint to that column.

Test:

  CREATE TABLE help
 (id INT IDENTITY(1,1),
 Fname VARCHAR(100) )

 
 ALTER TABLE help
 ADD CONSTRAINT ckname CHECK (Fname not LIKE '%sauerkraut%' )
  
  INSERT  help SELECT  ('sau')
  INSERT  help SELECT  ('I love sauerkraut very much')
  INSERT  help SELECT  ('sauerkrautttttt')

Cursor Script:

DECLARE @Stmt         NVARCHAR(4000),
        @DBName       SYSNAME

SET @DBName = DB_NAME()

DECLARE @CName    VARCHAR(255),
        @TName    SYSNAME,
        @OName    SYSNAME,
        @Sql      VARCHAR(8000)
       
DECLARE curcolumns CURSOR READ_ONLY FORWARD_ONLY LOCAL FOR

SELECT C.Name AS Colname
  , T.Name AS DType
  , QUOTENAME(U.Name) + '.' + QUOTENAME(O.Name) AS Tbl


FROM
 syscolumns C
 INNER JOIN systypes T
  ON C.xtype = T.xtype
 INNER JOIN sysobjects O
  ON C.ID = O.ID
 INNER JOIN sysusers u
  ON O.uid = u.uid

WHERE
 T.Name IN ('varchar', 'char', 'text', 'nchar', 'nvarchar', 'ntext')
 AND O.xtype IN ('U')
 AND objectProperty(O.ID, 'ismsshipped') = 0

ORDER BY
 3
  , 1


OPEN curcolumns

SET XACT_ABORT ON

BEGIN TRAN

FETCH curcolumns INTO @CName, @TName, @OName

WHILE @@FETCH_STATUS = 0

BEGIN

 SET @Sql = 'ALTER TABLE ' + @OName + 
 ' ADD CONSTRAINT chk_' + @CName +' Check ( '+  @CName +' Not like ''%sauerkraut%'' )'


 --EXEC (@Sql) -- change this to print if you need only the script, not the action
PRINT @Sql

 FETCH curcolumns INTO @CName, @TName, @OName
END

CLOSE curcolumns

DEALLOCATE curcolumns

COMMIT TRAN

There was a failure to validate setting CTLRUSERS in validaton function ValidateUsers.

2012-02-08T12:15:00.000-05:00

You might get that error while installing SQL server 2012, it's when you select a non domain user while in the Distributed Replay Controller step.

You need to choose a domain user or no user at all...Hopefully they improve that error MSG to a meaning error in future releases :)

TITLE: SQL Server Setup failure.
------------------------------
SQL Server Setup has encountered the following error:
There was a failure to validate setting CTLRUSERS in validaton function ValidateUsers.
Error code 0x85640004.
For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft%20SQL%20Server&EvtSrc=setup.rll&EvtID=50000&EvtType=0xFB87BFDA%25400x601E39D7
------------------------------
BUTTONS:
OK
------------------------------

T-Sql Combine date and 6 digits time

2012-02-07T12:11:00.000-05:00

A recent challenge about how to combine 2 columns; The date column has 2011-08-24 00:00:00.000 and the Time column has 120622

DECLARE @D datetime, @T INT
select @D ='2011-08-24 00:00:00.000 ', @t = 120622

SELECT @D + CAST(DATEADD(SECOND, @T %100 + (60*( @T %10000 / 100)) + 3600*(@T /10000),0) AS time)

And that gives you:

2011-08-24 12:06:22.000

SQL Agent Account Through T-SQL

2012-02-07T08:27:00.000-05:00

I had recently a weired requirement to get the SQL server AGENT Account from multiple servers, so I'm sharing how to get it through T-SQL

DECLARE @SrvAccount varchar(100)
set @SrvAccount =''
EXECUTE master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'SYSTEM\CurrentControlSet\Services\SQLSERVERAGENT',
N'ObjectName',
@SrvAccount OUTPUT,
N'no_output'
SELECT @SrvAccount as SQLAgent_ServiceAccount

And the location the registry would be at ...

SYSTEM\CurrentControlSet\Services\SQLSERVERAGENT

Don't send that empty SSRS report.

2012-02-04T09:44:00.000-05:00

Some of us has a daily or weekly process that puts some data in the tables and has some reports that report on top of these data, part of the daily process in any business it schedule report subscriptions ...

Sometime you get empty reports if the data is missing or no data for that day and you need to filter and not to send these reports....

There's are many ways to do it...but the most easiest one is to schedule your report normally, SSRS will create a new SQL agent job with a GUID numbers and later, just edit this job, go to the steps and add your protection code.

The simplest code will check if therefore any rows or not, you can also get notified using DBMail if no data, instead of sending empty reports to your boss!!

You can alter this step, or add a step before... Up to you.

Well I'm lazy, i'll alter this one and add a simple code to check if the table has rows or not !!
You can also use your own query based on a date and time column that you have !! to check for new data that you'll report on.

If exists (select * from tableA where Date_Column = getdate() )
Begin
Exec ......
End
Else
Send yourself an E-mail....

Just be creative...
You can also use sp_send_dbmail and send yourself an e-mail if it's empty.

Hope that helps..

Get file names using Vb.net

2012-02-01T12:03:00.002-05:00

A recent question about how to check the value of a STRING variable in SSIS 2008 ?

If you need to check for file existence on the disk...use that

1-Imports System.IO
2- assuming your var holds a string of full pat ,ex: C:\file.txt

Public Sub Main()
        Dim varCollection As Variables = Nothing
        Dts.VariableDispenser.LockForRead("User::FilePath")
        Dts.VariableDispenser.GetVariables(varCollection)
        If File.Exists(varCollection("User::FilePath").Value.ToString()) Then
            MessageBox.Show("File found.")
        Else
            MessageBox.Show("File not found.")
        End If
  Dts.TaskResult = ScriptResults.Success
End Sub

You could pass 0 or 1 and use the 'Precedence constraints' to evaluate your next step.
Hope that helps someone.

Add SSRS 2008 named instance to the same server.

2012-01-27T11:33:00.001-05:00

A recent questions about how to add another instance of SSRS, couldn't find any that talking about 2008, anyway I decided to help by creating some screen shots.

The installation is straightforward, you just pop the CD, run the installer, select Reporting services from the "Feature Selections", then select "Named Instance", configure and you're done !

The installation files and additional checks.

Perform a new installation....

Select only Reporting Services ...

named instance or you'll get an error !

Confirmation...

Service account configuration...can be changed later..

Installation...& Completion...

Reporting Services Configuration Manager.

All Programs---> Microsoft SQL server 2008---> Configuration Tools

Selecting the new instance to configure... Hop to the database tab to install or point to an existing DB.

I'm already using ReportServer default DB for the first instance, so I'll need to come up with a new name for the new instance to use !

I'll create a new DB.

On which SQL server the DB will be created in !

and which account the wizard will use to connect to that SQL box.

Name of the new DB.

Credentials for the new DB.

Summary...

2 Mins and you're done.

Initialize the web service URL, which port and which virtual directory..

Report server URL to view the reports..for user access.

the 2 instances and the SSRS URLs.

http://jyousef-3cce217/Reports_TESTINST <---Newly added instance URL
http://jyousef-3cce217/Reports <---Default installed one.

That's it...
Hope that helps someone.

SQL Server 2012 Developer Training Kit

2012-01-25T12:59:00.000-05:00

Ms just released 2 days ago the SQL Server 2012 Developer Training Kit includes technical content designed to help you learn how to develop SQL Server 2012 database and BI solutions.
You'll need to have the Microsoft Web Platform Installer 3.0 installed before downloading

http://www.microsoft.com/web/downloads/platform.aspx

Then download the KIT from here... it'll need 269 MB space to download all the sets of LABS, Presentations and Demos.

http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=27721

In the system requirments, they listed windows 7, windows server 2008.

During the installation, it'll give you an options t odownload and install VS2010,SQL 2012 and such required S/W.

Microsoft Virtual Academy - Free Online Training!

2012-01-23T00:17:00.002-05:00

Aperantly MS has some goodies for the new year, I posted before a link to the new E-book, and now I just discovered this new GEM...

Microsoft Virtual Academy

Improve your IT skill set and help advance your career with a free, easy to access training portal. Learn at your own pace, focusing on Microsoft technologies, gain points and get recognition.

MVP Nominations!

2012-01-22T10:03:00.000-05:00

This is big, this tells me that someone is appreciating what I do and giving me the chance to do more under their recognition umbrella.
Tells me that Microsoft’s cares...

The Microsoft Most Valued Professional award recognizes the community activists who speaks, participate in forums activities, attends seminars and has influence in the technical community..
https://mvp.support.microsoft.com/

I started helping and blogging by the end of 2009 to help others, didn't even know about this MVP thing, when I started answering and participating in Experts Exchange due to feeling the pain of these people, when they're behind a dead line, when they have a boss on their shoulders...I've been there...done that...

Really more points on EE means "more contribution" but not "more knowledge", but of course, "more knowledge" is needed to provide "more answers"

I was looking for info just like you...I was struggling to get a silly easy query to work like you...and still a lot to learn.
I started doing what I’m doing to share the knowledge, I believe that you'll never learn unless you teach or feel the live experience, and here I’m seeing other's live examples and gaining even more experience by helping them!

If you want to help the community, just do it. Helping is rewarding in many ways, at least to your knowledge.

If the MVP program didn't select me, I'll still continue to help more and more, knowing that I’m not valuable the way I’m doing it and need to put more effort in helping others and sharing my knowledge.

If the MVP selects me, that means that I'll even have more responsibility to even do more, need to focus on the quality of my contributions and not the quantity, need to speak more as know I could have better chances to talk, before nobody gave me the opportunity to talk other than my work mates and my works place.

SSRS Subscriptions cannot be created because the credentials used to run the report are not stored, or if a linked report, the link is no longer valid.

2012-01-21T10:59:00.002-05:00

Have you ever got this message? "Subscriptions cannot be created because the credentials used to run the report are not stored, or if a linked report, the link is no longer valid."

It's simply as it says, just go back to your report and make sure to save the user credintials that runs the report, or go to Datasets under ReportServer.

Also you can refer to this MS 'How to' article.
http://msdn.microsoft.com/en-us/library/ms159736.aspx

Free ebook: Introducing Microsoft SQL Server Code Name “Denali” (DRAFT Preview)

2012-01-19T14:07:00.003-05:00

Microsoft Press Today, via their Twitter account, Tossed a link to their new E-book Introducing Microsoft SQL Server Code Name “Denali”.

http://blogs.msdn.com/b/microsoft_press/archive/2011/10/11/free-ebook-introducing-microsoft-sql-server-code-name-denali-draft-preview.aspx?CommentPosted=true#commentmessage

Check for file existance, copy or skip using script taks

2012-01-11T18:24:00.000-05:00

A recent question about how to check for file existance, skip or copy using script taks. from 2 different locations.

Here I'll ge ta list of files that needs to be copied from C:\Media, check if it exists in U:Media or not and act accordingly.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO
<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
Enum ScriptResults
  Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
  Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
End Enum
    Public Sub Main()
        Dim FileToCopy As String
        Dim NewCopy As String
        Dim di As New IO.DirectoryInfo("C:\Media")
        Dim fi As IO.FileInfo() = di.GetFiles()
        Dim f As IO.FileInfo
        For Each f In fi
            FileToCopy = "C:\Media\" + f.ToString
            NewCopy = "U:\Media\" + f.ToString
            If File.Exists(NewCopy) Then
                MsgBox(NewCopy + "File Exists")
            Else
                System.IO.File.Copy(FileToCopy, NewCopy)
                MsgBox(NewCopy + "File Copied")
            End If
        Next
        Dts.TaskResult = ScriptResults.Success
    End Sub
End Class

Hope that helps....

SSIS expressions in variables...dynamic variables

2012-01-10T18:14:00.000-05:00

A recent question about how to create dynamic variables using expressions in SSIS, well that's pretty easy, you just need to highlight the desired variable, press F4 to pull the properties window.

Then set "EvaluateAsExpression" to TRUE, then click on ellipsis to set the expression....

Hope that helps...

SQL Agnet Job Categories

2012-01-01T10:46:00.005-05:00

I'm excited to write my first blog post in 2012 with a new year resolution, 99% of the times when I create a SQL agent job I forget to set the category of the job to a meaning category instead of the default [Uncategorized - (Local) ]

Does your jobs looks like this?

Make it a habit and add the category..
To add more categories to the list...(Right-click Jobs, select Manage Job Categories, then Add)

Hope that helps you to get your server more organized !

SSRS check for NULL or Empty String

2011-12-05T15:44:00.001-05:00

Alright..I have a column called "Comments" and need to hide that column if it's NULL or empty or have spaces!

Solution:

Just set the visibility expression to:

=IIF(Fields!Comments.Value = "", IIF(Fields!Comments.Value is nothing, IIF(Len(Trim(Fields!Comments.Value)) = 0,True, False), False), False)

Submitted a SQL Tribal Knowledge abstract

2011-12-05T12:55:00.000-05:00

Jen McCown ( Twitter | Blog ) is calling all the unpublished SQL Server folk and non MVPs!

She has this brilliant idea about a community-written book of tribal SQL Server knowledge.
http://www.midnightdba.com/Jen/2011/11/sql-tribal-knowledge/

Anyway I've managed to contact her and she was generous to accpet my 3 abstracts after she closed the submition timeline.
I'm So excited that I could write for the SQL community and share the knowledge,These are actually what came to my mind back then, but really regret not sending anything about SQL 2012 as it would be more suitable.

Title: SSIS notifications, using “SEND MAILTASK”.

Category: Advanced SSIS

Level: 300

Goal: Learn about the various ways and the capabilitiesof SSIS notifications.

Abstract: Examiningthe common and the possible ways to send e-mail notifications for variousscenarios, on error, on success using the built in “Send Mail Task” andlearning abut it’s limitations, the ways to extend it using a script component utilizinga .NET code, sending HTML e-mails, sending Table results to E-mail, usingexpressions in description and e-mail body, addressing the way to minimize creatingnotifications by creating a portable template that could be plugged to any packageusing child / parent variables.

A-Overview,usage and its limitations

B-Using.NET in a Script Component.

C-Usingadd-on community tasks.

D-SendingHTML E-mail notifications.

E-HTMLTable Results.

F-Expressiondriven dynamic notifications.

G-Creatingportable notification templates using variables.

Title:SSIS Dynamiccapability, using variables and expressions.

Category: Advanced SSIS

Level: 400

Goal: Learn about the power of dynamic SSISusing expressions and the ways to pass values across components using variables.

Abstract: Learningabout moving your package to the next level and leverage the usage by utilizingvariables and the impressively expressions that can change and configure itselfat runtime.

Get to know the best naming conventions for your variables, how variablesare being created, it’s scope and its various data types.

Using variables in your script task, using dispensers, passing variablesto a child package.

A-Overview,usage and scope.

B-Variables data Types.

C-Namingbest practices.

D-VariablesUsing .NET in Script Task.

E-Expressionsand Dynamic SSIS

F-Usingthe expressions builder and the Expression Tester App.

G-Operators,Functions, Literals and Casting.

H-CommonsSting, Numeric, NULL, Conditional and Date & Time examples.

Title: SSIS SMO Tasks (SQL Server Management Objects)

Category: Advanced SSIS

Level: 200

Goal: Do you know how many times you’ve usedSSIS and you’re not aware! While using the import and export wizard withinSSMS, while scheduling maintenance plans within SSMS.

Learnabout the various tasks that could help the DBA to make their work faster,Move, copy, detach databases, logins and SQL agent jobs between servers.

Move,copy database objects such as tables, views and stored procedures withintables.

A- Transfer Database Task.

B- Transfer Error Messages Task

C- Transfer Jobs Task

D- Transfer Logins Task

E- Transfer Master Stored Procedures Task

F- Transfer SQL Server Objects Task

SSRS can be also used for user inputs!

2011-12-02T12:56:00.001-05:00

A recent question popped up and the discussion heated up regarding updating a COMMENTS (TXT) field in a table using SSRS.

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Q_27475269.html?cid=1572#a37227028

Of course it’s not the best way to use SSRS as it’s a presentation layer and so limited in doing that using Parameters and sub-Reports which will fire a code or a stored procedure to update or delete a record…but IT COULD BE DONE!!

And that’s what I was bargaining about…

1-I started by creating a new project in VS 2008, “Report Server Project” type.

2-I’m using SQL 2008 R2 as my testing platform…I’ve created a test DB called “Admin”
CREATE DATABASE [Admin]
3-I created a test table called “Employees” contains only 3 columns.

Create TABLE Employees
    (
    ID int IDENTITY (1,1),
    Name varchar(50),
    Comments varchar(MAX)
    )

4- Inserted 4 test records…

Insert Employees VALUES('Jason', 'Lazy Employee')
    ,('Yousef', NULL)
    ,('John', 'Anything Goes')
    ,('Smith', NULL)

5-back to VS, Created a Shared Data Source to my Admin DB

6-created 3 Blank reports, using the shared Data Source.
1-      To display the records which in the table.
2-      A user summary and confirm screen of which record will be updated and with what.
3-      A done screen, which will call a stored procedure or just have a simple inline T-SQL query.

That’s the trick in the second report, you need to hide the ID and keep the Comments Parameter visible and allow it to accept NULL Value, so it display the record before you need to enter the value. And also if you need an empty comment, such as to clear the comments field or whatever.

7-The design…

Report1: 1ReportsToDB.rdl

Just a basic table, drag the 3 fields to the table, added an extra field with an expression of =CHR(0254) and Font of: Wingdings Just to display a shape without using an image.
Please refer to my article for shapes in

SSRS http://asqlb.blogspot.com/2011/06/ever-wanted-shapes-and-check-boxes-in.html

Also I added an action to that text field to go to report 2 and pass the ID.

Report2: 2ReportsToDB-Confirm.rdl

Again a simple table with the basic 3 fields and added another field for the new Comments value which is using an expression of: =Parameters!Comments.Value and a field for a confirmation symbol, yes again using my favorite shapes font WINGDINGS , with action to run the 3rd report and pass the ID and the new comments.

Report3: 3ReportsToDB-Done.rdl
I added 3 text fields, but it’s all optional (for fun).

Just with an action to go back to the first report and the summary or what was done!!

Lets’ run and test it….

I’ll click next to my name on the check mark to update my comments…
That’s what you get at first because we allowed the NULL, that’s the trick to see the table before the parameter gets initialized.

Remove the NULL check mark and enter the new comments….

You’ll get the confirmation above…
Now click on the check mark to confirm the change, yes you guessed it right…it’s the wingdings shapes again ¿
And that’s what you get..

I’ll click on my GO Back, to the first report to check the data.
Done.. my record was updated in the DB.

Again that’s for the purpose of showing that SSRS is more than a presentation layer and that was the simplest part, it could get more complicated and done more professionally using Custom .NET code and functions.

Hope that helps someone…

The project can be downloaded from
http://www.box.com/s/sskhxfppful1l7sbytzh

References:
http://asqlb.blogspot.com/2011/06/ever-wanted-shapes-and-check-boxes-in.html
http://www.purplehell.com/riddletools/wingdingschart.htm

Thanks
Jason Yousef

Search every table and every CHAR columns for a keyword!

2011-11-17T14:42:00.001-05:00

I needed to search for a specific keyword and didn't have the chance to know what's the table's name or even what's the column's name!!

Just replace the KEYWORD in the below query to your needs.

DECLARE @Schema varchar(3)
DECLARE @Table Nvarchar(max)
DECLARE @Column varchar(30)
DECLARE @Keyword Nvarchar(30)

SELECT @Keyword = 'BMAC2120-01'

DECLARE Curs CURSOR FAST_FORWARD FOR

SELECT s.name As [Schema], o.Name AS [Table],c.Name AS [Column]
FROM sys.columns c JOIN sys.objects o ON o.object_id = c.object_id
JOIN sys.schemas s ON s.schema_id = o.schema_id
WHERE o.type = 'U' and c.collation_name IS NOT null
ORDER BY o.Name,c.Name

OPEN Curs

FETCH NEXT FROM Curs INTO @Schema,@Table, @Column
WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @Table = 'SELECT top 5 [' + @Column + '],''' + @Table + ''' as [Table Name] from ' + +@Schema +'.' + @Table + ' where [' + @Column + '] LIKE ''%' +@Keyword +'%'''
print @Table

FETCH NEXT FROM Curs INTO @Schema,@Table, @Column
END

CLOSE Curs
DEALLOCATE Curs

T-SQL Combine Date with Time

2011-11-04T12:29:00.000-04:00

I've a case where I have 2 columns, Date and Time.

Date column's datatype is DATETIME
Time Column's datatype is INT

Example:

Date: 2009-04-14 00:00:00.000
Time: can be 743 or 0834

I need to combine them in one DATETIME column.

Select 'DTColumn' = CASE WHEN len(convert(varchar(12),Time)) = 3
     THEN cast(CONVERT(char(8),Date, 112) + ' ' +
     convert(varchar(10),Substring(convert(varchar(12),Time, 108),1,1) + ':' + right(Time, 2) + ':00'
     ,108) AS datetime)
     WHEN len(convert(varchar(12),Time)) = 4
     THEN cast(CONVERT(char(8),Date, 112) + ' ' +
     convert(varchar(10),Substring(convert(varchar(12),Time),1,2) + ':' + right(Time, 2) + ':00'
     ,108) AS datetime)

     End
from Table

That's it...Hope it helps someone...

Error rows, Code and Description in SSIS

2011-11-04T12:25:00.002-04:00

1-double click the OLE DB destination to bring it's properties.

2-go to "Error Output" as highlighted in the screenshot, "select "Redirect Row" from the drop-down menu. that will redirect the bad rows to the output.

3-Add a 'script Component task' , go to the input columns, select ONLY 'Error Code" and 'ErrorColumn'., add an OUTPU column to hold the error description.

4-Go to the script screen, and add the following script, make sure to US VISUAL BASIC 2008 as your input language.

5-paste that script..

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
<Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute> _
<CLSCompliant(False)> _
Public Class ScriptMain
    Inherits UserComponent
    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
        Row.ErrorDescription = Me.ComponentMetaData.GetErrorDescription(Row.ErrorCode)
    End Sub
End Class

6- now you'll have 3 columns, the error code, error row and the description of the error in english.

7-connect a FLAT FILE DESTINATION to the script component so you can captuure the oputput to text, or connect another OLE DB DESTINATION to collect to a database.

Hope that helps someone...

SFTP in SSIS

2011-11-02T13:27:00.000-04:00

You have couple of options...

Free option will be a script task to an external WinSCP app, explained in detailes here:

http://www.codeproject.com/KB/database/SSIS_SFTP.aspx

Paid options:

1-http://www.cozyroc.com/   $399.95
2-http://www.rebex.net/sftp.net/ $349.00
3-http://www.nsoftware.com/ssis/   $249.00
4-http://xceed.com/FTP_NET_Features.html   $899.95 USD

Get excel sheet names in a script taks

2011-10-04T11:09:00.000-04:00

Hi,

I had seen a question asking about getting all the excel sheet names in a work book, so I've created the attached example package.

Basically I'll loop over a specific folder, get all the excel file names into a variable, pass it to a script task to get me the worksheet names.

In the SCRIPT TASK, add a reference to MICROSOFT.OFFICE.INTEROP.EXCEL

Then add this code block..

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.Office.Interop
<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
    Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    Enum ScriptResults
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    End Enum
    Public Sub Main()
        'opening the Variables for write and read for some of them
        Dim Vars As Variables = Nothing 'var dispenser
        Dts.VariableDispenser.LockForRead("User::StrExcelFilePath")
        Dts.VariableDispenser.LockForWrite("User::StrExcelSheetName")
        Dts.VariableDispenser.GetVariables(Vars)
        Dim objExcel As Excel.Application
        Dim objWorkBook As Excel.Workbook
        Dim totalWorkSheets As Excel.Worksheet
        Dim objWorkSheets As Excel.Worksheet
        Dim ExcelSheetName As String = ""
        objExcel = CreateObject("Excel.Application")
        objWorkBook = objExcel.Workbooks.Open(Vars("User::StrExcelFilePath").Value)
        ' this code gets the names off all the worksheets
        For Each totalWorkSheets In objWorkBook.Worksheets
            'ExcelSheetName += totalWorkSheets.Name
            Vars("User::StrExcelSheetName").Value = totalWorkSheets.Name.ToString
            MsgBox(totalWorkSheets.Name.ToString)
        Next totalWorkSheets

        Dts.TaskResult = ScriptResults.Success
        Vars.Unlock()
    End Sub
End Class

Hope that helps someone....

Strip Time out of date SSRS

2011-10-01T08:34:00.003-04:00

I've a column type "datetime" that reads 09/23/2011 12:00:00 AM, need to show only the month and day portion seprated by "/" or "-".

There're 2 ways to do it in SSRS:

1-the easiest way :) and the right one too...

=Month(Fields!Tuesday.Value) & "/" & Day(Fields!Tuesday.Value)

2-the long fiasco way ! I was just fooling around !!

=StrReverse(Replace(StrReverse(left(Fields!Sunday.Value).ToShortDateString,5)),"/","",1,1))

It's your call...

Visual Studio 11 Ultimate Developer Preview

2011-09-28T08:33:00.000-04:00

I'm so excited to hear about the new VS 2011, i've downloaded and played around to create new databases and do some stuff in denali and it has the new JUNEAU featues...
Give it a shot....HERE

SSRS - An item with the same key has already been added

2011-09-22T11:50:00.001-04:00

Ever got this message and wondered what does it means?
The query works fine in SSMS but fails to refresh and update the column names in SSRS !!

It happen to be that I've used the same column names in my query, but SQL handled it as I was using a table prefix in my join.

Just alias your column names !!! and remember not to select * !!!

SQL Server 2005 Express Tools is blocking SQL 2008 R2 upgrade!!

2011-09-12T14:59:00.000-04:00

I was upgrading SQL server 2008 to 2008 R2, the installtion stops with error "SQL Server 2005 Express Tools in installed" !! please uninstall, but unfortunately I don't have that!! and can't find it under the list of installed programs in the control panel!

Tried the skip check flag "/skiprules=Sql2005SsmsExpressFacet" with no use !!

I took a backup of the whole registry, and removed the whole "Registry Key 90" folder!
HKEY_LOCAL_MACHINE > Software > Microsoft > Microsoft SQL Server > 90

It worked!!

SSRS Day name of Date Time

2011-09-08T14:38:00.000-04:00

=WeekdayName(datepart("w", Fields!packagestart.Value))

TypeGuessRows for excel! SSIS Excel import NULLS !

2011-09-08T09:32:00.000-04:00

I have an Excel file that I want to import into SQL Server.

The package works fine! Except it nulls out some columns. and since these columns are null, it's picking null for the rows.

interesting, you could change the number of rows that's being used for guessing by the jet engine...

http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/54dc94c5-280b-41e5-82fe-26925f9eb95b/

I never had this problem before, so I'm learning now!!

Learned that a TypeGuessRows in registry which is 8 by default, and this is number of rows which Jet engine try to fetch "data type" can be changed up to 16.

At the following location.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\X.X\Engines\Excel

Another property named IMEX which is for Mixed types in excel. actually when Jet engine mark any column as mixed type ( like columns which character and numeric together ), then IMEX=1 excel will always fetch data as text.

so use IMEX=1 and set TypeGuessRows as number of rows which is appropriate.

Related MS KB:
http://support.microsoft.com/kb/189897

Hope that helps someone as it helped me.

SSRS Formatting

2011-09-07T12:21:00.000-04:00

SSRS is full of treasures, I recently came accross that great article and wanted to share it..
http://sqlsafety.blogspot.com/2010/02/ssrs-formatting.html

Enjoy!

SSIS Process files that has been downloaded in the last hour

2011-08-12T10:18:00.002-04:00

A recent question on SQL Server Central was asking about how to process the last hour downloaded files.

I think it's fairly simple using a script task to get the files creation date property, and then added a constant constraint to check the difference between now and the creation date, then pass through to the data flow task to process the file.

I've created 6 variables, I'm here getting more usable file properties, but you don't have to do that, I'm just here showing the concept more than doing the exact time difference.

Add a "foreachloop Container" and a "script task" inside.

edit the "Foreachloop Container" location to your file system location. (the one that has your files).

Map the 'FilePath' variable to the output of the "foreachloop"

Edit the "Script Task" ,I'm using VB.net language here.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO.File
Imports System.IO.FileInfo
<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
Enum ScriptResults
  Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
  Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    End Enum
    Public Sub Main()
        'opening the Variables for write and read for some of them
        Dim Vars As Variables = Nothing 'var dispenser
        Dts.VariableDispenser.LockForRead("User::FilePath")
        Dts.VariableDispenser.LockForWrite("User::FileSize")
        Dts.VariableDispenser.LockForWrite("User::FileCreationDate")
        Dts.VariableDispenser.LockForWrite("User::FileLastAccessedDate")
        Dts.VariableDispenser.LockForWrite("User::FileLastModifiedDate")
        Dts.VariableDispenser.LockForWrite("User::FileAge")
        Dts.VariableDispenser.GetVariables(Vars)
        Dim f As IO.FileInfo = New System.IO.FileInfo(CStr(Vars("User::FilePath").Value.ToString))
        Dim fileSize As Double = f.Length
        Dim fileDate As Date = f.CreationTime
        Dim fileLastAccess As Date = f.LastAccessTime
        Dim fileLastMod As Date = f.LastWriteTime
        Vars("FileSize").Value = fileSize / 1024   'get file size
        Vars("FileCreationDate").Value = fileDate
        Vars("FileLastAccessedDate").Value = fileLastAccess
        Vars("FileLastModifiedDate").Value = fileLastMod
        Vars("FileAge").Value = CInt(DateDiff(DateInterval.Minute, fileDate, Now()))
        'only for testing to visualize the results, comment out
        MsgBox("File Name: " & f.FullName & vbCrLf & _
                "File Size: " & Vars("FileSize").Value.ToString & vbCrLf & _
                "File Date: " & Vars("FileCreationDate").Value.ToString & vbCrLf & _
                "File Age: " & Vars("FileAge").Value.ToString)

        Dts.TaskResult = ScriptResults.Success
    End Sub
End Class

That's it....

Test it and you'll get results like that..

Now use a precedence constraint to check the file's age and process the less than 60 minutes.

The package could be downloaded from: http://www.box.net/shared/dgyb5z79bgdgdp9pkpi3

Hope that helps someone...

SSRS Data-driven subscription E-mail is not an option

2011-08-03T11:13:00.000-04:00

Like the title is saying, when you attempt to create a data-driven subscription in SSRS, E-mail is not one of the listed options.

That means you've not configured a SMTP server yet !

Start -> All Program -> Microsoft SQL Server 2008 / R2 -> Configuration Tools -> Reporting Service Configuration Manager

Than the option will be Appear.

Hope that helps.

Recent Horrible story with my toshiba Laptop!!!

2011-08-01T19:52:00.003-04:00

Recently I bought a Toshiba laptop from best buy and that was my biggest mistake, everybody warned me from Toshiba but I didn't listen.... the hard drive crashed after 3 months and it was a nightmare over the phone with them for hours so they can understand what I'm trying to say !!!!!

and the guy over the phone kept asking "did you pack your data before shipping the laptop" and he's processing a repair for a crashed hard drive...

Also to mention that you'll have to pay $25 so they can provide you with a UPS box and pre-paid shipping label.

The whole process took 2 weeks to call, send and receive.....

The bottom line that they're cheap company that doesn't stand by their product and by their customers, they dont' care about the customer to leave them without a laptop for 3 weeks!!

and you pay to get your stuff fixed while it's still under the warranty !!

That's only my personal experience and I'm sharing it for knowledge....

SSIS Performance considerations

2011-07-20T13:33:00.001-04:00

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternative technique you could gain much more performance out of your SSIS flow.

The recommendations and suggestions below represents real life experience and excerpts from the Microsoft Technet article : http://technet.microsoft.com/en-us/library/cc966529.aspx along with influence from the other links referred to at the bottom of this Article. While this Article presents a summary and major points to focus on from those resources, you are encouraged to follow the links for more specific and in depth discussion.

SSIS (SQL SERVER INTEGRATION SERVICES) is using a buffer-oriented architecture to efficiently load and manipulate datasets in memory, the benefits is avoiding the I/O to the disk and physically copying the data to the disk, and therefore it’s transferring the data from the source to the destination through the pipeline without touching the disk.

As this data flows through the pipeline, SSIS attempts to reuse data from prior buffers as much as possible when additional operations are performed. How buffers are used and reused depend on the type of transformations that you use in a pipeline.

There’re 3 types of transformations types:

1-Row Transformation (synchronous transformation) also known as (Non-blocking transformations):

These process the data in Row-by-row basis, Row transformations have the advantage of reusing existing buffers and do not require data to be copied to a new buffer to complete the transformation.

• Do not block data flow in the pipeline.
• Data is not copied around, only pointers.

Examples:

• Audit
• Cache Transform
• Character Map
• Conditional Split
• Copy Column
• Data Conversion
• Derived Column
• Export Column
• Import Column
• Lookup
• Multicast
• OLE DB Command
• Percentage Sampling
• Script Component
• Slowly Changing Dimension

2-Partially Blocking Transformation (asynchronous transformation):

Are often used to combine several datasets. They tend to have multiple data inputs. As a result, their output may have the same, greater, or fewer records than the total number of input records.

• Introduces new buffers in memory layout.
• Transformed data is copied into new buffers.

Examples:

• Data Mining
• Merge
• Merge Join
• Pivot
• Unpivot
• Term Lookup

3-Blocking Transformation (asynchronous transformation):

Blocking transformations must read and process all input records before creating any output records. Of all of the transformation types, these transformations perform the most work and can have the greatest impact on available resources.

• Must see all data before passing on rows.
• Blocks the data flow – can be heavy on memory
• May also use “private buffers” to assist with transforming data.

Examples:

• Aggregate
• Fuzzy grouping
• Fuzzy lookup
• Row Sampling
• Sort
• Term Extraction

Evaluating the performance:

1-You could monitor and observe the performance using the PROGRESS tab, while running the package, it’ll tell you when the task started, ended and how long it’s taking to finish, which would be a great way of discovering which tasks is pulling the performance down.

progressTab.png (42 KB) (File Type Details)

Progress Tab

2-You could also use a free add-on for BIDS (Business Intelligence Development Studio) called BIDS HELPER, can be downloaded from http://bidshelper.codeplex.com/

It offers whole new features for the BIDS environment which would help you such as deploying your packages, dtsConfig file formatting and SSIS performance Visualization which I’ll cover now.

Using the SSIS performance Visualization is simple, you just right click on your package after installing the BIDS HELPER and you’ll have new options, you need to select “Execute and Visualize Performance”.

bidsw.png (30 KB) (File Type Details)

Bids Helper

You’ll be introduced to another screen with Performance Tabs, and you choose to visualize the performance by looking at Gantt Bars, Statistics Grid or Statistics Trend, which compares the performance from one execution to another. It adds a new column for each execution and highlights whether the duration of that piece of the package was faster or slower than before.

SSISPerformanceTrendGrid.png (16 KB) (File Type Details)

Performance Trend

3-Using Perfmon (formerly known as Windows Performance Monitor and synonymously referred to as Perfmon)

There're several counters to monitor SSIS performance, such :

SQLServer:SSIS Service:

SSIS Package Instances - Total number of simultaneous SSIS Packages running

SQLServer:SSIS Pipeline:

BLOB bytes read - Total bytes read from binary large objects during the monitoring period.
BLOB bytes written - Total bytes written to binary large objects during the monitoring period.
BLOB files in use - Number of binary large objects files used during the data flow task during the monitoring period.
Buffer memory - The amount of physical or virtual memory used by the data flow task during the monitoring period.
Buffers in use - The number of buffers in use during the data flow task during the monitoring period.
Buffers spooled - The number of buffers written to disk during the data flow task during the monitoring period.
Flat buffer memory - The total number of blocks of memory in use by the data flow task during the monitoring period.
Flat buffers in use - The number of blocks of memory in use by the data flow task at a point in time.
Private buffer memory - The total amount of physical or virtual memory used by data transformation tasks in the data flow engine during the monitoring period.
Private buffers in use - The number of blocks of memory in use by the transformations in the data flow task at a point in time.
Rows read - Total number of input rows in use by the data flow task at a point in time.
Rows written - Total number of output rows in use by the data flow task at a point in time.

But basically you need to monitor the 3 famous kinds of memory buffers:

"Buffers"
"Private Buffers"
"Flat Buffers"

Read More....

Evaluating Design and performance considerations:

As I always say, everything is life could be done in many different ways; it’s just YOU who needs to decide which approach is better for you as it really depends on your environment and business requirements.

I’m going to point some points that you’ll need to do more research and also included some links so you can look it up in more depth.

1-Remember is that synchronous components reuse buffers and therefore are generally faster than asynchronous components, that needs a new buffer.

2-Break complex ETL tasks into logically distinct smaller packages. More...

3-Maximize Parallelism. Utilizes the available resources as much as you can. More ...

4-Maximum insert commit size and Rows per batch has a great effect for not treating the whole incoming rows as one patch.

5-Minimize staging as possible otherwise use RawFiles. More ...

6- Eliminate redundant columns:

• Use SELECT statements as opposed to selecting the tables dropdown.
• Be Picky! Select only what columns you need VS SELECT *

7-Minimize blocking as much as you can:

• Sort your query in the engine, or even using the SQL command in OLE DB Source instead of using Sort transformation.
• Merge transform requires Sort but not Union All, so use Union All wherever you can. More ...

8- Optimize the BufferTempStoragePath and BLOBTempStoragePath wisely. More ...

9-For Flat File Source use FAST PARSE option for columns of integer and date data types. More ...

10- If the SQL server is local, consider using SQL Server destination instead of OLE DB Destination.

11-Consider indexes fragmentation and performance when inserting high volume of data.

12-Use “Fast Load” when using OLEDB, it uses BULK INSERT and would be faster.

13-Optimize the packet size in your connection managers to 32K (32767) and that’s the fastest option.

More resources on the subject:

Top 10 SQL Server Integration Services Best Practices

Designing and Tuning for Performance your SSIS packages in the Enterprise (SQL Video Series)

Integration Services: Performance Tuning Techniques

That's it...I hope my article helps someone gaining more performance from their ETL solution, and feel free to add more performance considerations

Last Day of the Month SSRS Expression....

2011-07-19T08:38:00.000-04:00

A question came on one of the fourms about how to get the last day of the month using months as intgers, the person has 1,2,3....etc in his column and need to convert that to month and get the display only the last day of the month in the MM/DD/YYYY format....

Use that for January (1)

=Left(DateSerial(Year(Now()), 1, "1").AddMonths(1).AddDays(-1) , 10)

Use that for February (2)

=Left(DateSerial(Year(Now()), 2, "1").AddMonths(1).AddDays(-1) , 10)

Hope that helps...

Derived Column to convert string to DB Date.

2011-07-15T11:29:00.001-04:00

I have a date field in string format "0MMDDYY" and some value has "0000000".
I need to convert the string to normal db date and also replace "0000000" with NULL.

I'm using a derived column, and assuming that the column name is [Column].

[Column] == "0000000" ? "NULL" : (DT_WSTR,2)20 + (DT_WSTR,2)RIGHT([Column],2) + "-" + (DT_WSTR,2)SUBSTRING([Column],2,2) + "-" + (DT_WSTR,2)SUBSTRING([Column],4,2)

and if it was an INT field....

[Column] == (DT_WSTR,7)0000000 ? "NULL" : (DT_WSTR,2)20 + (DT_WSTR,2)RIGHT([Column],2) + "-" + (DT_WSTR,2)SUBSTRING([Column],2,2) + "-" + (DT_WSTR,2)SUBSTRING([Column],4,2)

That's assuming that your dates are in the year 2000 and above...

What if the String has a date such "0110540"

The above code will give you 2040-11-05

so we'll have to check and fix the year to 1940

@[User::Column] == "0000000" ? "NULL" : ( (DT_I4)(DT_WSTR,2) RIGHT(@[User::Column],2) <= 11 ? ((DT_WSTR,2)20 + (DT_WSTR,2)RIGHT(@[User::Column],2) + "-" + (DT_WSTR,2)SUBSTRING(@[User::Column],2,2) + "-" + (DT_WSTR,2)SUBSTRING(@[User::Column],4,2) ) : ( (DT_WSTR,2)19 + (DT_WSTR,2)RIGHT(@[User::Column],2) + "-" + (DT_WSTR,2)SUBSTRING(@[User::Column],2,2) + "-" + (DT_WSTR,2)SUBSTRING(@[User::Column],4,2) ))

That's the only way since we have only 2 digits for the year, plus i'm calculating patient's age, so I don't think any of them over 100 years old !

I'm open to discussion if you have a better way of doing it..

Hope that helps..

SSIS "MM/DD/YYYY" Format expression.

2011-07-10T15:04:00.005-04:00

(DT_DBTIMESTAMP)(SUBSTRING([date string], 7, 4) + "-" SUBSTRING([date string], 1, 2) + "-" + SUBSTRING([date string], 4, 2))

FTP files list to text file using SSIS.

2011-07-08T14:46:00.002-04:00

I got a requirment to dump all the FTP file listing for all folders and subfolders to a text file.
I'm using here a free FTP server which you could use too
ftp://ftp.secureftp-test.com/ User:"test" Password: "test")

what SSIS FTP Task can you do nativly?

The FTP Task, supports, sending, receiving, deleting folders and directories, but doesn’t support watching for a specific files or getting a list of the FTP directory listing.

Get list of files and folders to a text file.

A.Drag a script task

B. Select “Microsoft Visual Basic 2008” as the scripting language, then click “Edit Script”

C. Add the following script

And the result will be at D:\FTPList.txt

Hope that helps!!

Download the package from http://www.box.net/shared/77tqeitp99czj6feefxv

Imports System

Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Collections.Generic
Imports System.Text
Imports System.Net
Imports System.IO

<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _

<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

Enum ScriptResults

  Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
  Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
End Enum

    Public Sub Main()
        Dim FilePath As String = "D:\FTPList.txt"      'save to location - You can use a variable here.

'delete if file exists, Comment out if you need to append to the existing TXT file

        If File.Exists(FilePath) Then
            File.Delete(FilePath)
        End If

Try

            'Create the connection to the ftp server
            Dim cm As ConnectionManager = Dts.Connections.Add("FTP")
            Dim Folders As String()
            Dim Files As String()
            Dim FileName As String
            Dim Folder As String
            Dim Dir As String

'Set the properties like username & password

            cm.Properties("ServerName").SetValue(cm, "ftp://ftp.secureftp-test.com/")          'Server address
            cm.Properties("ServerUserName").SetValue(cm, "test")                        'user name
            cm.Properties("ServerPassword").SetValue(cm, "test")                        'password

Dim ftp As FtpClientConnection = New FtpClientConnection(cm.AcquireConnection(Nothing))

'Connects to the root of the ftp server,

            Dir = "/"                                'root directory
            ftp://ftp.connect/()
            ftp://ftp.setworkingdirectory(dir/)
            ftp://ftp.getlisting(folders/, Files)

Dim SW As System.IO.StreamWriter 'Writer to write to the file

SW = System.IO.File.AppendText(FilePath)

'Write date time header (getting fancy)

SW.WriteLine(Now().ToString + vbCrLf + vbCrLf + vbCrLf)

'write root file list

            SW.WriteLine("Folder : " + Dir + vbCrLf + "----------------------" + vbCrLf)
            If Not Files Is Nothing Then 'checking for no files
                For Each FileName In Files
                    SW.WriteLine(FileName)
                Next
                SW.WriteLine(vbCrLf)
            End If

'write other folders and their files' list

            If Not Folders Is Nothing Then 'checking for no other folders
                For Each Folder In Folders
                    SW.WriteLine("Folder : " + Folder + vbCrLf + "----------------------" + vbCrLf)
                    Dir = "/" + Folder
                    ftp://ftp.setworkingdirectory(dir/)
                    ftp://ftp.getlisting(folders/, Files)

If Not Files Is Nothing Then 'checking for no files

                        For Each FileName In Files
                            SW.WriteLine(FileName)
                        Next
                    End If
                    SW.WriteLine(vbCrLf)

            End If
            'Close ftp connection
            ftp://ftp.close/()
            'Close writer
            SW.Close()

Catch ex As Exception 'Catch errors, make up your own error as you can see.

Dts.Events.FireError(911, "", "Errrrrrrrrrrrrrrrrrror, something bad happened!!!", "", -1)
Dts.TaskResult = ScriptResults.Failure 'reprot failure

End Try

Dts.TaskResult = ScriptResults.Success 'report success
End Sub
End Class

SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Complete Downloadable List

2011-07-06T09:05:00.000-04:00

Pinal Dave has a huge list of qestions for the DBA interview questions.
http://blog.sqlauthority.com/2011/07/01/sql-server-interview-questions-and-answers-frequently-asked-questions-complete-downloadable-list-day-0-of-31/

Failure is not my option.. Control SSIS flow success or failure options..

2011-07-05T14:42:00.000-04:00

How to control the success and failure of the package and execute it successfully? Sometimes the requirement is to continue the execution even if an error has raised, might be a package that has nondependent tasks.

There are 3 ways to do that…

1- Setting up ‘MaximumErrorCount’ property of the container as ‘0’ instead of the default ‘1’.
2- Setting up ‘ForceExecutionResult’ property to ‘success’ for the task properties instead of the default ‘None’.

3- In tasks sequence, we can set ‘On Completion’ precedence constraint instead of ‘Success’.

Hope that helps.

SSIS to Sybase OLEDB connection error [IM002]. [Native Code: 30136]

2011-06-30T13:43:00.000-04:00

Hi, I was getting that nasty error message, although you could test the OLEDB connection from the Sybase “data source configuration” and gets a success notification!!
[IM002]
[Native Code: 30136]
[ASEOLEDB]Data source not found and no default driver specified

I also made sure that the OLEDB connection are there and configured well under 32 BIT and 64BIT, and the Sybase “data source configuration” gives a success message when I test them.

So I thought the error is relevant to SSIS and it’s permuissions, not the Sybase, I started investigating the service account that SSIS is running under and if it got access to the C:\Sybase\DataAccess64\OLEDB\DataSources

As Sybase ASE 15.5 was installed on my root drive C:\ and I’m using win & 64 BIT.
I'd the same setup and configuration working on a vista 64 bit, but found other posts online about the driver and it is working well on windows 7.
The full error message is:

TITLE: Microsoft Visual Studio
------------------------------

Error at LookupMedFrequency [Connection manager "CareCast"]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E19.
An OLE DB record is available. Source: "ASEOLEDB" Hresult: 0x80040E19 Description: "[IM002]
[Native Code: 30136]
[ASEOLEDB]Data source not found and no default driver specified".

Error at Data Flow Task [SSISERVisits [5801]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "CareCast" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

------------------------------
ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap)
I also couldn’t find much about the error online, all what I found was couple of posts here :

http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/ff5e2933-c29b-49b8-868c-cb4dca19b87d

and here:

http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/48695cc9-67e7-4ccb-8b5b-f64b7834ddc6

I even opened a new forum post:

http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/1798b64e-e02a-4234-ac09-5cba38eaae29

Solution:
I found out that the 3 connections that I created was created only under “DataAccess64\OLEDB\DataSources\” and wasn’t created under the “DataAccess\OLEDB\DataSources\” So I just copied the 3 files and the problem solved!!!

Hope that helps someone one day ...

SSIS....Articles that I refer to....

2011-06-26T20:07:00.001-04:00

Package Configurations
http://msdn.microsoft.com/en-us/library/cc895212.aspx

Pass Variables from child to parent back
http://vsteamsystemcentral.com/cs21/blogs/steve_fibich/archive/2007/09/29/passing-a-value-back-and-forth-from-a-parent-package-to-a-child-package-in-ssis.aspx

FTP file and Folders list into a SQL Table

2011-06-24T10:05:00.002-04:00

Today I came across a requirement to get all the files and folders list from a FTP server, The only way to get that will be through a script task that will loop through the files and get it into a SQL table.
Here's the question.
and here's my proposed solution....

I’ll be using a free test server ftp.secureftp-test.com User/Pass: test

We'll need to create a table to hold our results:

CREATE TABLE [dbo].[FTPList](
    [ListID] [int] IDENTITY(1,1) NOT NULL,
    [DateTime] [datetime] NOT NULL ,
    [Folder] [varchar](100) NOT NULL,
    [FileName] [varchar](100) NOT NULL)

Now let's start....

•    Drag a “Script Task”.

• 2-edit the code as shown here:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Data.SqlClient
Imports System.Collections.Generic
Imports System.Text
Imports System.Net
Imports System.IO

<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
    Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

    Enum ScriptResults
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    End Enum

    Public Sub Main()

        'Create the connection to the ftp server
        Dim cm As ConnectionManager = Dts.Connections.Add("FTP")
        Dim Folders As String()
        Dim Files As String()
        Dim FileName As String
        Dim Folder As String
        Dim Dir As String

        'Set the properties like username & password
        cm.Properties("ServerName").SetValue(cm, "ftp.secureftp-test.com") 'Serevr Name
        cm.Properties("ServerUserName").SetValue(cm, "test") 'user name
        cm.Properties("ServerPassword").SetValue(cm, "test") 'password

        Dim ftp As FtpClientConnection = New FtpClientConnection(cm.AcquireConnection(Nothing))

        'Connects to the ftp server, to the root
        Dir = "/" 'Root directory
        ftp.Connect() 'Connect
        ftp.SetWorkingDirectory(Dir) 'Set working directory
        ftp.GetListing(Folders, Files) 'list fodlers and files

        Dim SQLStr As String    'SQL string to hold the root query
        Dim SQLStr1 As String   'SQL string to hold rest of the folders query
        Dim ConnString As String

        'SQL server connection to "Localhost" and my Database called "Admin"
        ConnString = "Data Source=localhost;Initial Catalog=Admin;Integrated Security=SSPI;"

        Dim SQLConn As New SqlConnection()
        Dim SQLCmd As New SqlCommand()
        SQLConn.ConnectionString = ConnString
        SQLConn.Open() 'open connection
        SQLCmd.Connection = SQLConn
        'write root file list
        If Not Files Is Nothing Then
            For Each FileName In Files
                'my table called "FTPList"
                SQLStr = "INSERT into FTPList(DateTime,Folder,FileName) VALUES ('" + Now() + "','" + Dir.Trim + "','" + FileName.Trim + "')"
                SQLCmd.CommandText = SQLStr
                SQLCmd.ExecuteNonQuery()
            Next
        End If
        'write other folders and their files list
        If Not Folders Is Nothing Then
            For Each Folder In Folders
                Dir = "/" + Folder
                ftp.SetWorkingDirectory(Dir)
                ftp.GetListing(Folders, Files)

                If Not Files Is Nothing Then
                    For Each FileName In Files
                        SQLStr1 = "INSERT into FTPList(DateTime,Folder,FileName) VALUES ('" + Now() + "','" + Dir.Trim + "','" + FileName.Trim + "')"
                        SQLCmd.CommandText = SQLStr1
                        SQLCmd.ExecuteNonQuery()
                    Next
                End If

            Next
        End If
        ftp.Close()
        SQLConn.Close()
        Dts.TaskResult = ScriptResults.Success
    End Sub
End Class

And the results will be at the FTPList table as shown:

That's it...Enjoy.......

Download the package from http://www.box.net/shared/77tqeitp99czj6feefxv

How to enable GOD mode in win7 and Vista!

2011-06-23T13:32:00.000-04:00

Today's post is short and sweet, I just couldn't resist to share this cool option !!Never heard about this trick before, so cool, gets you a better broad [control panel] over you PC!!

check it here http://www.bleepingcomputer.com/tutorials/tutorial158.html

Ever wanted shapes and check boxes in SSRS?

2011-06-16T00:03:00.001-04:00

I do some webdesigning in my own side business, and was installing some fonts, then this crazy idea came to my mind about using the default fonts that comes installed in windows to produce shapes in SSRS.

The various fonts are called " Webdings, Wingdings, Wingdings2 and Wingdings3 "

Basically you'll use IIF or SWITCH to set your desired shape, you'll need to use capital letters, small letter or numbers.

The list of the shapes and the corespondent key could be found here http://www.purplehell.com/riddletools/wingdingschart.htm

P.S. Use IE to view that page, firefox doesn't display that font correctly!!

Examples:

Hope that helps someone.....

SSIS Error and Message Reference

2011-06-12T19:49:00.002-04:00

This might be helpful to someone while troubleshooting your SSIS packages.
http://msdn.microsoft.com/en-us/library/ms345164.aspx

Dynamically Iterate over a list of tables or views and export to flat files.

2011-06-11T22:42:00.000-04:00

I recently came across an interesting Question In EE and was puzzled about how to achieve that using SSIS out of the box tasks, which was impossible as SSIS can;t use on the fly columns in the source or the destination.

SO definitely only could be done using a script task, but that wasn't really my favorite part, I'm the worst when it comes to that.

Anyway because I had some past experience as developing ASP pages using vb.net, so quickly came to my mind to build aDataGrid using a SQL query, then export the grid to a text file, which worked fine, The I came across the DataReader which is a neater and easier way.

So here I'm going to demonstrate how to Iterate over a list of views which has different structure to flat file using Script Task.

I'll be using the famous "AdventureWorksDW2008R2" DB for SQL 2008 and "AdventureWorks" for SQL 2005 which could be downloaded for free here, The demo is being done on a windows Vista 64BIT machine, with SQL and VS 2008 installed. but there shouldn't be any version different if you're using SQL 2005.

Alright enough talking....

For SQL server 2008 Users......

1-The first step is to identify what are the views that we be exporting.

Views.jpg (29 KB) (File Type Details)

Views

2-Check the columns count for each of them, just to illustrate the different in structure.

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
SELECT A.name AS ViewName
  , count(B.TABLE_NAME) AS ColCount
FROM
 sys.objects AS A
 LEFT OUTER JOIN
  INFORMATION_SCHEMA.COLUMNS AS B
  ON A.name = B.TABLE_NAME
GROUP BY
 A.name
  , A.type
HAVING
 (A.type = 'v')
ORDER BY
 ColCount DESC

Toggle Highlighting Open in New Window Select All

3-I'll create 4 Variables, I love to always to the proper naming convention to tell the data type of the variable.

Variables.jpg (25 KB) (File Type Details)

Variables

A-Variable "ObjAllViews" will hold back the result set which has all the views' Names.
B-Variable "StrDelimiter" will hold the delimiter that you want to use between columns, I love to use the Vertical Line "|" because most of our data has commas in it, you can get it by pressing (SHIFT + \ )
C-Variable "StrViewName" will hold each individual view.
D-Variable "StrFilePath" will hold the desired path to save the files.

4-Create a connection to your server, in my case it's 'localhost' as I've my testing SQL server on the same machine, and I named the connection 'Localhost', take note of naming the connection because it's mentioned later in the script task.

oh...so now you get the idea? we will use the "Execute SQL Task" to get us the list of the views that exists in that table into "ObjAllViews" variable, then we will use "Foreach Loop Container" to shred resultset from "ObjAllViews" variable to each individual view name into "StrViewName" variable .

Then inside the "Foreach Loop Container" we will place a "Script Task" that will do the trick for us.

Now let's go over each tasks and how it's configured....

1-The control flow and the connection

CF.jpg (103 KB) (File Type Details)

ControlFlow and connection

2-Execute SQL Task

EST1.jpg (117 KB) (File Type Details)

EST1

EST2.jpg (81 KB) (File Type Details)

EST2

3-Foreach Loop Container

FEL1.jpg (112 KB) (File Type Details)

FEL1

FEL2.jpg (97 KB) (File Type Details)

FEL2

4-Script Task , Thanks for SIVA for helping me with the counter code.

ST1.jpg (89 KB) (File Type Details)

5- add This code

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Data.OleDb
Imports System.Data.SqlClient
Imports System.IO

<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
    Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    Enum ScriptResults
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    End Enum
    Public Sub Main()

        'Variable collection to hold the variables
        Dim VarCol As Variables = Nothing

        'Lock the 3 variables for read
        Dts.VariableDispenser.LockForRead("User::StrViewName")
        Dts.VariableDispenser.LockForRead("User::StrDelimiter")
        Dts.VariableDispenser.LockForRead("User::StrFilePath")


        'Fille the Variable collection
        Dts.VariableDispenser.GetVariables(VarCol)

        'Getting the variables' values, so we can use it later
        Dim ViewName As String = VarCol("User::StrViewName").Value.ToString()
        Dim delimiter As String = VarCol("User::StrDelimiter").Value.ToString()
        Dim FilePath As String = VarCol("User::StrFilePath").Value.ToString()


        'Construct the file name, example output: D:\vTargetMail.TXT
        Dim FName As String = FilePath & ViewName & ".TXT"

        'Build our Query
        Dim Query As String = "SELECT * FROM " & ViewName


        'Using StreamWriter
        Dim writer As StreamWriter = Nothing

        'Use our OLEDB COnnection
        Dim connection As OleDbConnection = New OleDbConnection(Dts.Connections("Localhost").ConnectionString)
        Dim command As OleDbCommand = Nothing
        Dim reader As OleDbDataReader = Nothing

        Try
            'Checking for the file, delete if exist(you can append or rename with [File.Move(FName, Today() & "_" & FName)]
            If File.Exists(FName) Then
                File.Delete(FName)
            End If
            'Open the OLEDB connection
            connection.Open()

            'Run the query
            command = New OleDbCommand(Query, connection)
            reader = command.ExecuteReader()

            If reader.HasRows Then

                'Stream Writer using the FNAME that we declared erlier
                writer = New System.IO.StreamWriter(FName)
                While reader.Read()

                    'Counter to get the columns number
                    Dim counter As Integer = 0
                    Dim fieldCount As Integer = reader.FieldCount - 1

                    While counter <= fieldCount
                        If counter <> fieldCount Then

                            writer.Write(reader(counter).ToString() & delimiter)
                        Else
                            writer.WriteLine(reader(counter).ToString())
                        End If
                        counter += 1
                    End While
                End While
            End If
        Catch ex As Exception
            Dts.Events.FireError(1, "", "Something Wrong happened!!!", "", 0)
        Finally
            connection.Close()
            writer.Close()
        End Try
        Dts.TaskResult = ScriptResults.Success
    End Sub
End Class

Toggle Highlighting Open in New Window Select All

And The results .........

Files.jpg (17 KB) (File Type Details)

Files

For SQL 2005 users...

We need to change the script task, also the SQL query too as follow:

1- change the SQL query to

SELECT  (sys.schemas.name + '.' + sys.objects.name) AS ViewName
FROM    sys.objects 
INNER JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id
Where type ='v'

Toggle Highlighting Open in New Window Select All

2- and the script task as shown here

script2005.jpg (86 KB) (File Type Details)

scripttask2005

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Data.OleDb
Imports System.Data.SqlClient
Imports System.IO

Public Class ScriptMain

    Public Sub Main()

        'Variable collection to hold the variables
        Dim VarCol As Variables = Nothing

        'Lock the 3 variables for read
        Dts.VariableDispenser.LockForRead("User::StrViewName")
        Dts.VariableDispenser.LockForRead("User::StrDelimiter")
        Dts.VariableDispenser.LockForRead("User::StrFilePath")

        'Fille the Variable collection
        Dts.VariableDispenser.GetVariables(VarCol)

        'Getting the variables' values, so we can use it later
        Dim ViewName As String = VarCol("User::StrViewName").Value.ToString()
        Dim delimiter As String = VarCol("User::StrDelimiter").Value.ToString()
        Dim FilePath As String = VarCol("User::StrFilePath").Value.ToString()

        'Construct the file name, example output: D:\vTargetMail.TXT
        Dim FName As String = FilePath & ViewName & ".TXT"

        'Using StreamWriter
        Dim writer As StreamWriter = Nothing

        'Try
        'Checking for the file, delete if exist(you can append or rename with [File.Move(FName, Today() & "_" & FName)]
        If File.Exists(FName) Then
            File.Delete(FName)
        End If

        'Open the OLEDB connection
        Dim connection As New OleDbConnection("Provider=SQLOLEDB;Data Source=Localhost;Initial Catalog=AdventureWorks;Integrated Security=SSPI;")
        connection.Open()
        Dim Query As String = "SELECT * FROM " & ViewName
      
        Dim command As OleDbCommand = New OleDbCommand(Query, connection)

        Dim reader As OleDbDataReader = command.ExecuteReader()

        If reader.HasRows Then

            'Stream Writer using the FNAME that we declared erlier
            writer = New System.IO.StreamWriter(FName)
            While reader.Read()

                'Counter to get the columns number
                Dim counter As Integer = 0
                Dim fieldCount As Integer = reader.FieldCount - 1

                While counter <= fieldCount
                    If counter <> fieldCount Then

                        writer.Write(reader(counter).ToString() & delimiter)
                    Else
                        writer.WriteLine(reader(counter).ToString())
                    End If
                    counter += 1
                End While
            End While
            reader.Close()
        End If

        connection.Close()
        writer.Close()
        'Catch ex As Exception
        '    Dts.Events.FireError(1, "Nasty", "Something Wrong happened!!!", "", 0)
        '    Throw ex
        'Finally

        'End Try
        Dts.TaskResult = Dts.Results.Success
    End Sub
End Class

Toggle Highlighting Open in New Window Select All

Solution assumptions:

1-You're using SQL 2008 or SQL 2005.
2-Server name is "Localhost" hosted locally, otherwise, please change that.
3-You have write access to your D:\ Drive.
4-For SQL 2008 you have installed "AdventureWorksDW2008R2" DB and "AdventureWorks" for SQL 2005.

That's it...I hope my article helps someone to be more dynamic and flexible in SSIS and over come some of the Data Flow limitations.

Also I'm open to discussions and comments if you have a better way to do it.

The final package for SQL 2008 can be FOUND HERE and for SQL 2005 FROM HERE

dbForge SQL Complete Review....

2011-06-07T11:56:00.000-04:00

Introduction

This is a review of a great utility that I evaluated as a possible solution for replacing the Intellisense in SQL server management studio named dbForge SQL Complete which could be found at http://www.devart.com/dbforge/sql/sqlcomplete/.

I was surfing the SQL Server newsgroups for help with some T-SQL issues and I saw a post with a link to that utility. I went to their website and downloaded a free evaluation for 30 days.

I could not believe how simple it was to use and how thoroughly it solved my code writing problems. I am still a fan and hooked and it's one of the tools that I need to install on each machine that I’m working on, wherever I go!

The Environment

The evaluation was done on various operating systems and various SQL server versions, windows vista 32 BIT with SQL server 2008, windows 7 enterprise 64Bit with SQL server 2008R2 and windows 7 enterprise 64Bit with SQL server 2005.

Download

The best thing ever that companies could offer a free trial of their product for testing and it's for 30 days, which divert did, totally free download without even giving your e-mail address and the other bogus stuff, the makes it easier to evaluate and work around with the tool.

They also offer free support even during your evaluation period, but I didn't get the chance to use it or call them as the product works smoothly on 32BIT and 64BIT, windows Vista and seven enterprises.

The dbForge SQL Complete has two editions - Express and Standard.

Express is a free SQL Server Management Studio add-in that provides basic functionality to complete and format SQL code.

Standard is a fully-featured tool that provides powerful SQL Intellisense and formatting of T-SQL code.

You can download it from http://www.devart.com/dbforge/sql/sqlcomplete/download.html

Version

The latest version as we speak is 2.50.92 (30-May-2011). For a list of changes see the revision history.

Installation

Ready to work out of the box, quickly installed in a Next-Next-Finish manner, it just requires Microsoft SQL Server Management Studio 2005 or 2008 installed

Cost

Ok, so that is what it costs, but what is its value? What will it bring to your business that you do not already have? Consider this:

· A tool for creating faster code completes as you write.

· The ability to when you type names of user-defined and built-in functions and procedures, dbForge SQL Complete will display their parameter information in a hint.

· A utility to produce clear and well-formatted code without worrying about all those indents, whitespaces, etc. Just keep typing your SQL and see it automatically transformed based on your preferences and formatting rules.

My Thoughts

Who don't have access to 4 or 5 machines which he develops, test and work on all the time, speaking about me, I'm using 2 machines at work and 1 laptop and 1 PC at home on my spare time, most of the time you need more Intellisense from SSMS Intellisense.

Honestly I’ve been to other tools but more expensive and really gives me the same functionality of this one.

To be fair, some of this functionality already exists in other tools you're using, such as SSMS. But Consider this:

· Speed! It's fast. The code behind this tool was recently rewritten for the latest version and it is as fast as lightning.

· Ease of use! Fluid motion, completing as you expect, as you type.

· Customization! With a wide selection of formatting options you can set any formatting style you need.

Yes, it is a great tool, but is it right for you? It may or may not be, depending on your development environment. Consider these scenarios:

· If you're not a developer or a code writer! Then you do not need it.

· If you don't use SQL server at all! Then you do not need it.

· If your databases and codes remain relatively static, then you do not need it.

· If your IT department don't have a budget to spend $50!

· If your development team codes furiously in a RAD environment then, as far as I'm concerned, you can't live without it.

Final Thoughts

Positively I’m with this utility. It’s a great product and cheap and everybody needs it! You’ll know what I’m talking about after you try it!

From Their Website

Context-based code completion

Click to enlarge

SQL Complete thoroughly defines the context to predict what you want to type next. It displays only relative keywords and names of database objects. Just type several symbols and see the required word in the suggestion list. It takes only to press Enter and go on with your query.

Parameter info for functions

Click to enlarge

When you are typing names of user-defined and built-in functions and procedures, SQL Complete displays their parameter information in a hint.

Customizable SQL formatting

Click to enlarge

Produce clear and well-formatted code without worrying about all those indents, whitespaces, wrappings, line breaks, etc. Just keep typing your SQL and see it automatically transformed based on your preferences and formatting rules.

With a wide selection of formatting options you can set any formatting style you need.

Quick object info

Click to enlarge

To get essential information on a database object you don't have to look it up any more - just hover the mouse cursor over the identifier, and it's here. For tables, the popup window with information will contain:

- column names with data types
- additional column properties (e.g., not null and identity attributes)
- primary, unique and foreign keys
- user information from the extended properties.

For other objects it shows object type and user description if any.

Smart filtering to suggest relative keywords and objects

Click to enlarge

SQL Complete filters the suggestion list based on various criteria:

- first typed symbols,
- a whitespace,
- camel case,
- a square bracket.

You can be sure, whatever you type, the suggestions will be just what you wanted.

Context-based sorting of suggestions in the list

Click to enlarge

SQL Complete saves you even a second by sorting names of database objects by type, and displaying most relevant suggestions in the top of the list; others are moved to the bottom.

Determining a current database or schema

Click to enlarge

SQL Complete displays a list of database objects from the current database or schema even if their names are not specified in a query. The names of the current database or schema are determined based on connection properties of the current SQL document. Besides, SQL Complete finds USE statements in the code and changes the current database for subsequent SQL statements.

Expanding a list of columns

Click to enlarge

You can replace the * asterisk symbol with the list of columns from the required table or view on pressing the Tab key.

Expanding INSERT statements

Click to enlarge

You can expand INSERT statements to the full template on pressing the Tab key.

Except the template, the type of column data will be generated in the comments along with the NOT NULL and ROWGUID properties. For datetime, a template for inserting time will be created. Also DEFAULT clause is generated if a default value is created for a table.

Suggesting conditions for JOIN statements

Click to enlarge

Get the fastest way of typing JOIN statements. SQL Complete can suggest a full statement based on foreign keys, or it can prompt conditions based on column names.

Automatic alias generation

Click to enlarge

SQL Complete automatically generates an alias for each table object (table, view, stored procedure, function, synonym) in the FROM list of your SQL document.

Suggestions of T-SQL statements in procedures and functions

Click to enlarge

SQL Complete supports:DECLARE statements for local variables, cursor variables, and table definitions;

cursors (declared with the help of DECLARE CURSOR statements) in OPEN, CLOSE, DEALLOCATE statements;

context for such control-of-flow as BEGIN...END, GOTO, IF...ELSE, RETURN, BREAK, CONTINUE, WHILE, WAITFOR, TRY...CATCH, CASE, RAISERROR.

Semi-transparent view of the suggestion box

Click to enlarge

When your code is overlapped by the suggestion box, do not close the suggestion box. Hold Ctrl to make the suggestion box switch to semi-transparent view.

Disclaimer: I am not employed by them. I am just a happy customer who use one or more features offered by this utility to make my life easier. It is quite possible that there are other competitive products in the market that offer similar features, so please do your own research and download the trial version first.

Run a VBscript using SQL Server.

2011-06-01T09:06:00.000-04:00

Today I came across a question in EE of how to run a VBscript using SQL server.
Well, it could be done using the SQL server agent or using SSIS, but the easiest way would be the SQL agent.

Here how:

1-Right click on "SQL Server Agent" and select "New Job"
2-Name the job according to your needs.
3-From the "Steps" , select New.
4-Name it to your needs and select the "Type" as "Operating System CmdExec" as shown in the screenshot.
4-Set the schedule as desired and that's it!!