i used following Expression in Integer values expression shap
(DT_WSTR, 4) YEAR( @[User::StartDate] ) +
Right("0"+ (DT_WSTR, 2) MONTH( @[User::StartDate] ) ,2) +
Right("0"+ (DT_WSTR, 2) DAY( @[User::StartDate] ) ,2)
It convert the date into integer like 20120229
{ Comments on this entry are closed }
Currently i have to make query dynamic, i have to pass date variable to query to execute for specific date range, and this date range could be very time by time, Previously I tried for script component or expression of variable to build query but failed, but then I found command option of oledb command. I placed query there with “?” sign at places where my parameter will replace the date value . Consider following
When I passed query there Parameter button on wizard activate. I set there variable to parameter .
This approach is work fine for me, but if you have places are more then 3 to 4 , query parsing failed. so you have to write query in such a way that minimum number of places parameter are used.
{ Comments on this entry are closed }
During my assignment I have to generate Integer value from datetime filed for Date Dimension . I found excellent id this way.
CAST(CONVERT(varchar(8),StartDATE,112) AS int) DateDateKey,
The whole Query is as follow.
SELECT
CAST(CONVERT(varchar(8),BEGDATE,112) AS int) DateDateKey,
,Name
,Address
,Joindate
,EndDate
FROM dbo.from Student
its works for me.
{ Comments on this entry are closed }
Create Parameter on package level with string datatype with following Name, set default value with respect to your machine configuration I set according to mine
VServerName =”pc-aliraza” VSQLUserName=”aliraza” VSQLDbName =”Nwind” VSQLPassword =”123”
If integrated securtity with database is false or you connect with windows authentication following is the expression you have to set expression at connection string property.
"Data Source=" + @[User::VServerName] + ";Initial Catalog=" + @[User:: VSQLDbName] + ";Provider=SQLNCLI10.1;Integrated Security=SSPI;"
If you want to connect with database with Sql server authentication you have to use following expression string at expression of connection string at connection in SSIS.
"Data Source="+ @[User::VServerName] +";User ID="+ @[User::VSQLUserName] +"Password= "+ @[User::VSQLPassword] +" ;Initial Catalog=" + @[User::VSQLDbName] + ";Provider=SQLNCLI10.1;Persist Security Info=True;"
{ Comments on this entry are closed }
Delete Part of ETL Process
If any row is deleted at source table, but destination table rows exits. So I did to make a inner join with source table with destination table I used right join in this case, so I got all rows from destination which I placed at right side of query. The result set I got with null at left side form source table. I filter the result set and got all the Keys which have null at source table. And call delete at destination on filter keys.
My common table Expression for Delete will be as follow
with DeleteRows as ( SELECT sourceEmp.empid, DestEmployee.SourceEmpId FROM TSQLFundamentals2008.HR.Employees sourceEmp right join TSQLFundamentals2008DW.HR.DimEmployees DestEmployee on sourceEmp.empid = DestEmployee.SourceEmpId ) --select SourceEmpId from DeleteRows where empid is null delete from TSQLFundamentals2008DW.HR.DimEmployees where TSQLFundamentals2008DW.HR.DimEmployees.SourceEmpId in ( select SourceEmpId from DeleteRows where empid is null)
{ Comments on this entry are closed }
For update part of ETL Process I have to write another Common table expression to get all records where are changed at source to run update query on destination table
My update part of basic ETL using TSQL as
with ChangedRows as ( SELECT sourceEmp.empid, sourceEmp.lastname, sourceEmp.firstname, sourceEmp.title, sourceEmp.titleofcourtesy, sourceEmp.birthdate, sourceEmp.hiredate, sourceEmp.address, sourceEmp.city, sourceEmp.region, sourceEmp.postalcode, sourceEmp.country, sourceEmp.phone, sourceEmp.mgrid,DestEmployee.SourceEmpId FROM TSQLFundamentals2008.HR.Employees sourceEmp inner join TSQLFundamentals2008DW.HR.DimEmployees DestEmployee on sourceEmp.empid = DestEmployee.SourceEmpId where sourceEmp.lastname <> DestEmployee.lastname or sourceEmp.firstname <> DestEmployee.firstname or sourceEmp.title <> DestEmployee.title or sourceEmp.titleofcourtesy <>DestEmployee.titleofcourtesy or sourceEmp.birthdate<>DestEmployee.birthdate or sourceEmp.hiredate <> DestEmployee.hiredate or sourceEmp.address <> DestEmployee.address or sourceEmp.city <> DestEmployee.city or sourceEmp.region <> DestEmployee.region or sourceEmp.postalcode<>DestEmployee.postalcode or sourceEmp.country <>DestEmployee.country or sourceEmp.phone<>DestEmployee.phone or sourceEmp.mgrid <> DestEmployee.mgrid ) update TSQLFundamentals2008DW.HR.DimEmployees set lastname = ChangedRows.lastname , firstname = ChangedRows.firstname , title = ChangedRows.title , titleofcourtesy =ChangedRows.titleofcourtesy , birthdate=ChangedRows.birthdate , hiredate = ChangedRows.hiredate , address = ChangedRows.address , city = ChangedRows.city , region = ChangedRows.region , postalcode=ChangedRows.postalcode , country =ChangedRows.country , phone=ChangedRows.phone , mgrid = ChangedRows.mgrid from ChangedRows where TSQLFundamentals2008DW.HR.DimEmployees.SourceEmpId=ChangedRows.empid
{ Comments on this entry are closed }
There will be change possible of server name at connection strings, file paths when deploying SSIS packages in production and same issue appears when ssis package will go from development environment to QA server for testing. So what is workaround. SSIS configuration wizard allow us to generate configuration settings for connection string and properties of other objects. And this allows us to update these settings at run time at any place, dev, QA or Production.
Benfites
Let see how we can do this
Open the package for which you want to generation configuration
Check the enable button.
Click on enable button. To start Wizard
From the above configuration type dialogue box, Specify the configuration type and then set the property types relevant to the configuration type.
Configuration type source can be
Specify the configuration file name and then say next then you will get the following dialogue box
Select the object and properties you want in configuration file.
Press next and then press finish button.
You can open the created file with extension “dtsConfig” in notepad, notepad++ , visual studio or any text editor supports xml to update the required filed.
Configurations are included in when you create package deployment utility for Installing packages.
{ Comments on this entry are closed }
There are hundreds of free SSIS tools available, which provide additional functionality. Codeplex listed them as http://ssisctc.codeplex.com/. These divided into following sections
{ Comments on this entry are closed }
Today I face the following Error, when I transfer data from excel file to sql server server through SSIS package.
Problem is that in excel data is stored in Unicode. And transfer data to field of sql server which is define as varchar.
Solution 1. Change the sql data type to nvarchar instead varchar. Unicode sql table will be define as
CREATE TABLE [dbo].[Product](
[ProductName] [nvarchar](50) NULL,
[ProductDetail] [nvarchar](50) NULL
) ON [PRIMARY]
Now try again. Data will successfully migrate. But this not good solution.
Solution 2
Work around to this problem is use data Conversion task between the excel source and destination sql server As
So to get around this problem we have to also use a Data Conversion task.
{ Comments on this entry are closed }