What is ISDATE(Expression) ?
Returns 1 if the expression is a valid date, time, or datetime value; otherwise, 0.
Expression can be any character string , less than 4000 characters but it should not be Date datatype
Msg 8116, Level 16, State 1, Line 3
Argument data type date is invalid for argument 1 of isdate function.
How to create and map excel destination dynamically
In the earlier post we learned how to map excel destination directly in the SSIS in this post we lean to create excel sheets dynamically in a single excel file
we modify the package in the above post slightly to achieve our task here
In this post, you will learn
In this post we will use a OLEDB source and a Excel destination , for the OLEDB source we will use the Adventure works database [Production].[ProductListPriceHistory] table
This table has a history of price changes for each product, so our goal is to create a history Excel file for each product id
Create a variable type object to hold the distinct productID’s
Drag and drop a Data flow task add a oledb source with SQL query as data access mode and modify the query to get the distinct product ids and
Use a record set destination to load all the distinct product ids into the Object variable
drag and drop a For each loop enumerator and configure it as shown below
create a variable with string type and evaluate the variable as expression and open the expression editor modify the sheet create syntax to create the exact sheet name for the excel file, in our case we will use this ‘product_’+ productid
go back to the for loop drag and drop excel with connection type as excel and source input will be variable and select the sheet creation variable that we just created
Go back into the data flow task and edit the expression of excel connection manager and select the connection property as excel file path if you choose connection string then you have to use the full length connection string like this or go for the excel file as shown in the below picture
“Provider=Microsoft.Jet.OLEDB.4.0;Data Source=”+@excelfilename+”;Extended Properties=/”Excel 8.0;HDR=YES/”;”
you will get error like this
TITLE: Microsoft Visual Studio
——————————
Error at Exceldestination [Connection manager “Excel Connection Manager”]: The connection string format is not valid. It must consist of one or more components of the form X=Y, separated by semicolons. This error occurs when a connection string with zero components is set on database connection manager.
Error at Exceldestination: The result of the expression “@[User::Excelfilename]” on property “ConnectionString” cannot be written to the property. The expression was evaluated, but cannot be set on the property.
Error at Create and load Excel [Excel Destination [57]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E37.
Error at Create and load Excel [Excel Destination [57]]: Opening a rowset for “productid_0” failed. Check that the object exists in the database.
——————————
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC02020E8 (Microsoft.SqlServer.DTSPipelineWrap)
——————————
BUTTONS:
OK
——————————
TITLE: Microsoft Visual Studio
——————————
Error at Create and load Excel [Excel Destination [57]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E37.
Error at Create and load Excel [Excel Destination [57]]: Opening a rowset for “productid_0” failed. Check that the object exists in the database.
——————————
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC02020E8 (Microsoft.SqlServer.DTSPipelineWrap)
——————————
BUTTONS:
OK
——————————
to fix this error create the excel file and the sheet during the design time
after clearing the errors, you can now go ahead and delete the excel file we created during the design time
If you have any problems please use the comments section
Thank You
How to remove blank rows or empty rows in SSIS Data Flow task from a Flat file source or Excel Source, Raw source
Here I have a flat file source which has some blank values
Simple and easy trick
Hi , Using a simple trick you can easily eliminate duplicate rows when using Flat file or Excel source or Raw source or any where in the Data Flow task
Simple and easy trick you can using any where in the Data Flow task
Thank you
SQLJUNKIESHARE
How to get time in AM PM format in SQL Server
How to get time in 12 hour format in SQL Server
SELECT CONVERT(VARCHAR,GETDATE(),100) as ‘Default Date format’
SELECT RIGHT(‘0’+LTRIM(RIGHT(CONVERT(varchar,getDate(),100),8)),7) as ’12 Hour Time in AMPM’
SELECT REPLACE(REPLACE(RIGHT(‘0’+LTRIM(RIGHT(CONVERT(varchar,getdate(),100),7)),7),’AM’,’ AM’),’PM’,’ PM’)
as ’12 Hour Time in AM PM’
Please click the below thumbnails to start the presentaion
Please download the above presentation on Lookup Transformation
Above presentation is briefly explained in the below video
In this post we will go through two scalar functions
Len() is one of the string functions
Datalength is one of the Data type functions
so what does Len() do , it returns(int) the number of characters of the specified string expression, excluding trailing blanks.
excluding trailing blanks is an important note to remember when using Len() function
— It can also be used in select, where clause
Declare
@string varchar(100)
set @string =‘sqljunkieshare’
select LEN(@string)
select * from dept
where LEN(dept_id)> 5
Datalength() is data type function (size of the data)
It returns the number of bytes used to represent any expression.
Now you have a clear idea of what does LEN() and DATALENTH() scalar functions do
SQL Server provides the following set operators. Set operators combine results from two or more queries into a single result set
1) Intersect and Except
2) Union
Today in this post we will learn Intersect and Except set operators
Returns distinct values by comparing the results of two queries.
EXCEPT returns any distinct values from the left query that are not also found on the right query.
INTERSECT returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operand.
The basic rules for combining the result sets of two queries that use EXCEPT or INTERSECT are the following:
Lets take a simple example and you will better understand the set operators
create table #newproducts(ProductID int, name varchar(20), size varchar(20),price varchar(20))
insert into #newproducts(ProductID,name,size,price)
values(1,’productx’,’small’,20),
(2,’producty’,’small’,40),
(3,’productz’,’large’,50),
(1,’productxy’,’small’,60)
create table #oldproducts(ProductID int, name varchar(20), size varchar(20),price varchar(20))
insert into #oldproducts(ProductID,name,size,price)
values(4,’productq’,’small’,20),
(5,’productk’,’small’,40),
(6,’productz’,’large’,50),
(1,’productxy’,’small’,60),
(1,’productzy’,’small’,80)
select * from #newproducts
select * from #oldproducts
;with oldonly as
(
select * from #oldproducts
except
select * from #newproducts
),
newonly as (select * from #newproducts
except
select * from #oldproducts)
select o.ProductID,o.name as oldname, n.name as newname
from oldonly o inner join newonly n on o.ProductID = n.ProductID
where o.name <> n.name
Returns the partition number into which a set of partitioning column values would be mapped for any specified partition function.
Syntax
[ database_name. ] $PARTITION.partition_function_name(expression)
It returns a Partition number of column value when mapped to a partition function
this will be useful if you want find the rows in a particular partition
so lets explain this with a simple example
create partition function
now lets create a partitioned table
insert some sample data
now lets see some examples on $partition function
Sqljunkieshare 