SSIS

SQL Server Integration Services (SSIS) is a component of the Microsoft SQL Server database software that can be used to perform a broad range of data migration tasks. SSIS is a platform for data integration and workflow applications. It features a fast and flexible data warehousing tool used for data extraction, transformation, and loading (ETL). The tool may also be used to automate maintenance of SQL Server databases and updates to multidimensional cube data. SSIS replaced Data Transformation Services, which had been a feature of SQL Server since Version 7.0.

Share tech news, updates, or what's on your mind.

Sign up to Post

how do I join two queries, join them HORIZONTALLY, i.e. extra columns, second columns query 2 to right of first query



--query 1 OUTPUTS
SELECT a.timeStampKey, t.timeStamp,MAX(CASE WHEN a.PIE2_O_ID = 1 THEN value END) AS 'K_DESIGN_SG_A_AVERAGE_A', MAX(CASE WHEN a.PIE2_O_ID = 2 THEN value END) AS 'K_DESIGN_SG_B_AVERAGE_B'FROM   tblOutputs as a INNER JOIN       tblTimeStamp as t ON a.timeStampKey = t.timeStampKey GROUP BY a.timeStampKey, t.timeStamp
--query 2 INPUTS
SELECT a.timeStampKey, t.timeStamp,MAX(CASE WHEN a.PIE2_I_ID = 10104 THEN value END) AS Flow, MAX(CASE WHEN a.PIE2_I_ID = 10006 THEN value END) AS Head FROM   tblInputs as a INNER JOIN       tblTimeStamp as t ON a.timeStampKey = t.timeStampKey GROUP BY a.timeStampKey, t.timeStamp
0
Free Tool: Subnet Calculator
LVL 9
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

I have installed SQL Server 2016 with  all the features, but when I open the management studio I am unable to connect to SSIS, all other services are availble and the SSIS  service is running. I read an article which suggested using Dcomcnfg.exe to add users to the permission for integration services which i have done. still unable to connect though

SQL server connector - Browsing for servers
0
I can not send mail through the SMTP connection manager (send mail task). System.net.webexepction

attched file
www.png
0
Hi,

Previously I was able to make use of an sysadmin account on our client's server to create jobs and execute packages. The client has now changed the rights of this account so it's not a sysadmin anymore and now the jobs are failing. They don't want to give me another sysadmin account. I now need to know how do I go forward in giving this new account permissions to run Sql Server Agent jobs and to execute packages. The account they have created for me is not a windows login but only a sql login.

Regards
Christian
0
Hi ,

Please provide detailed procedure for In-Place Up-gradation from MS SQL Server 2012 to MS SQL Server 2014 include MSBI Components like SSIS Packages, Cubes/OLAP Databases and SSRS Reports.

Please consider this high priority.

Thanks,
Chandra
0
Hello Experts

I have developed a package that works very well in development. I do 2 things
1. I publish it to the SSIS server
2. I call you through a job

In the execution of the job falls for a subject of permissions
This is the error
Error: 2017-08-12 13:53:28.56
   Code: 0xC0016016
   Source: DWH 
   Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Clave no v lida para utilizar en el estado especificado.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.
End Error
err_DTS.txt
0
Hi,

I am looking for SQL Server Interview Questions and Answers links and material, additionaly looking for SQL Server Certification

Thanks
Chandra
0
Greetings,
I have SQL SERVER 2016 Standard.  Included in this I have SSDT 2016, and SSIS to go along with it.

Inside my Package, I have the following:

Command Exec Task:
Command:  C:/Program Files/Putty/PSFTP.exe
Arguments:  XXXXXX@sftp1.XXXX.com -pw XXXXX -be -batch -b "D:\Code\XXXXX\XXXXXX.bat"  <-- Names changed to protect the innocent.
Working Folder:  D:\Data\ZIPFOLDER

Inside SSDT, this works without failure.  When deployed to SQL SERVER, I recieve:
 The process exit code was "1" while the expected was "0".

This happens whether or not there's files to get on the other side.

My steps to rectify:

1. I created a Windows SQL User: DB\SSISWORKUSER
2. I made DB\SSISWORKUSER an administrative-level user on the system.
3. I added DB\SSISWORKUSER on as FULL CONTROL to both C:\PROGRAM FILES\PUTTY and D:\ (the whole drive)
4. I confirmed that DB\SSISWORKUSER has FULL CONTROL of all fines inside the folders specified in step 3.
5. ON SQL SERVER, I gave DB_OWNER privileges on ALL databases, to include SSIS, My Database, MASTER, MSDB, and SSIS (I'll take away some things when I solve this)
6. I created a credential based DB\SSISWORKUSER
7. I created a Proxy User off of DB\SSISWORKUSER's credential and added CmdExec privileges, plus all SSIS privileges to the Proxy User.
8. I changed my deployed SSIS Job to be owned by the new Proxy.
9. I added ftp:\C, ftp:\D, and ftp:\E to the Internet Options Safe list.

I am still having difficulty getting this task …
0
hi,

it seems that MS only release SQL server data tools for VS 2015 so that we can admin and develop even SSIS project within VS 2015, how about VS 2017?

it seems we don't have this option to develop SSIS package inside VS2017, am I right?
0
Hi there,
I am trying to load info from one table directly into a new table (replicated in the same way), so I can keep an archive , since the org table clears out every 30 days.
I setup a simple ETL flow, but it just hung for hours.
So I wrote an insert statement inT-sql and this took some time, but after hours did run.
so I rewrote another insert statement using top 150k entries per load and this seems to run faster.
so I thought I would use a query insert on SSIS and tried this, but the same query still hangs for hours rather than running in seconds as it did on management studios.

I'm not sure what to look for her, can anyone please advise?
thank you,
Elaine.
0
New benefit for Premium Members - Upgrade now!
LVL 9
New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

We are importing (Bulk Insert) of around half million records to the staging table and then to the production table.

More often column data type or size validation fails the bulk insert process from Staging table to the Production table.

I will have to redesign the process by SSIS packages by inserting all the good records from Staging to the Production and load the bad / error out data to the staging table. Those bad or error out data with proper validation message.

Validation message is based on first error out column. If the all the columns in that row are erroring, we can consider with the first column error and reload the data after fixing that data.

Example Error Message: Column PurchaseDate has invalid data

How do I achieve this complete workflow SSIS package. What are the controls, I need to use to start with?

Please :)
0
Hi there,

thank you for reading this ,  i have a light SSIS package . It runs without any errors in the visual studio 2012 in  dev environment.  but once it gets deployed to test environment   using proxy account with full permission access ran the package in SSMS 2012 . it shows error below .  Does anyone have any ideas ?  Thank you .

DataFlowTaskError: there were errors during  validations
DataFlowTaskError: Failed validation and return status "VS_ISBROKEN"
DataFlowTaskError:  one or more component failed validation
DataFlowTaskError: Microsoft SQL server Client 11.0  hresult:0x80004005
Description :Syntax error, permission violation , or other non specific error
0
Hello, In a google search, I come up the knowledge that the MS SSDT, which is the SQL Server Data Tools.  So I downloaded SSDT.
I installed SSDT trying to get to the SSIS, which is the SQL Server Integration Services.
I can't find it.  Where is it?
I even have the SSMS and it is not there.
thanks
0
Hi All,

Could you please advise how to achieve below requirement (using SSIS or any other methods)

Requirement:
I have T-SQL Server query and Oracle SP (SP is located on Oracle DB).

Now I want to update records on Oracle DB table using Oracle SP. Before updating records on Oracle, 1st SP validates data of MS SQL query output (if record is exist in SQL server and Oracle table (or if matches on both), then update oracle DB table else ignore...this is logic of SP)

Can you please tell me how to design SSIS package for above requirement in SQL 2014.
0
Hi EE,

I have got a source table with a column of data type Nvarchar(max) and in my lookup dimension i have got a column with the same data type as Nvarchar(max). When i try to map those columns i get the following error message.
Cannot map the lookup column, 'AdditionalInstructions', because the column data type is a binary large object block (BLOB).

Open in new window


Can you please help.

regards,
0
hi experts

i am reading about Upgrading Data Quality Services:
DQS schema must be upgraded as a separate step

what's the mean DQS schema
0
Hi EE,

I have got a scheduled job which populated the data warehouse. There are a wrapper packages in the job and wrapper package has sub-packages.  until 28/06 everything has been fine but it started failing. when i look into the errors its very confusing.
Sometimes it fails on first sub package but other times it fails on another sub package. i have tried running it from Visual Studio as well and same thing is happening that is it can fail on any sub package but when i run that package again it might succeed or might not. It might succeed on 3rd fourth attempt and keeps giving me different errors on different packages.
The server is SQL Server 2014 SP2. The server is windows serrver 2012 R2 and i have applied all the updates too.

The errors i get on different times are below

[Update [2]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "LegacyDW Staging" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.

Open in new window


[WebBooking Staging [357]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "Login timeout expired".
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "Unable to complete login process due to delay in prelogin response".
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "TCP Provider: Timeout error [258]. ".

Open in new window


Test connection failed because of an error in initializing provider. Login timeout expired
Unable to complete login process due to delay in prelogin response
TCP Provider: Timeout error [258]. 

Open in new window


Truncate table: Error: Failed to acquire connection "connectionname" Connection may not be configured correctly or you may not have the right permissions on this connection

Open in new window


Unable to locate reference column "column name"

Open in new window


regards
0
Hi Experts,
I have one stored procedure to update the control table which contains two fields (Table_Name & Timestamp) to know the details of tables updated through SSIS . But now,I have a new requirement to add one more field (ie.Latest Data) which is a date field  to get the max(date ) from each table everyday.These are  for daily sheduled job.After the execution of each job,the stored procedure for update control table is called through "EXECUTE SQL TASK" in SSIS.So now,I want to pass the result of  an sql query (select max(date) from order)  as a parameter through execute sql task .Already a parameter is there to pass the table name to stored procedure.

Thanks in Advance
0
I am not able to see the MSDB Folder in the SSIS Stored Packages. We are using SQL 2014 64-bit. In the Database Folder Databases\System Databases\MSDB\Tables\System Tables, I see the table dbo.sysssispackages but no table contents. In our SQL 2008 32-bit I can view the contents and all the MSDB tables are available to view and the Stored Packages MSDB is visible . In SQL 2014 64-bit, that is not available.

Do I have a corrupted MSDB in 2014? How is the available and yet no editing or adding data to the table? How do I find out if the MSDB needs to be repaired?
0
New feature and membership benefit!
LVL 9
New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

I have SSIS package and it runs every hour. This package import data from csv into database table. From march 2017 this package has been failing. What i see there is a SQL task step which is failing but before it was succeeded. Here is the below code:-- I put the four *** sign where it is failing.


declare @toparse nvarchar(max)
declare @strlength int
declare @separator_location int
declare @instance nchar(10)

declare @instance_url nvarchar(max)

declare @hostname nvarchar(50)
declare @strIn nvarchar(2000)
declare @separator nchar(2) = ' '
declare @inner_separator nchar(1) = '|'

DECLARE @item nvarchar(1000)  -- assume maximun item length 1000 char
DECLARE @len int, @sepPos1 int,  @sepPos2 int

-- truncate table staging_oracle_version
--declare @truncate varchar(200)
--set @truncate = 'truncate table staging_oracle_version'
--exec (@truncate)

-- get oracle instances to load
declare  oracleinstance_csr cursor for
select certname
      , value
from puppetdb_certname_facts
where fact like 'oracle_instance_url'
   and value IS NOT NULL
   and value !=''
  and value != '""'

open oracleinstance_csr

fetch next from oracleinstance_csr into @hostname, @strIn

--select @hostname, @strIn

while @@FETCH_STATUS = 0
begin

  SET @strIn = REPLACE(@strIn, char(10), @separator)  -- Replace all the LF (char(10)) from the string and replace with defined separator [ the code for CR is char(13)]
  Set @sepPos1 = 1
  set @sepPos2 = …
0
Dear All,

While updating he particular table i am getting this issue.
i am using sql server 2102 sp1
the update command is running through ssis package.

Update top (20000) [SQLMPDB].MPLokSewa_PSM_New_01.dbo.Trans_app_Log set INSERTFLG ='M' from [SQLMPDB].MPLokSewa_PSM_New_01.dbo.Trans_app_Log t, [SQLMPDB].MPLokSewa_PSM_New_01.dbo.Transfer_Reg_no R where t.Reg_no=R.Reg_no and (INSERTFLG <>'M' or INSERTFLG is NULL)

i have check with nolock also still i am getting same issue.

Please help suggest me.

Regards
Ganesh Yerme
0
SQL 2010:

With the following Is there a way within a view to create the results below?

BudgetId      AdminFeeId      SupplementNbr      MonthsToFundNbr      FundAmt            
614                              70                           0                                   2                                   8500            The first budget (614) has two entries for admin fee
614                             71                           0                                   4                                   8000            
721                       145                           1                                   6                                   7500            The second budget has one entry for Admin
856                           156                           2                                   4                                   6500            The third budget has one entry for Admin
                                    
I need the results to look like the following:                                    
                                    
BudgetId      AdminFeeId      SupplementNbr      StartRangeNbr      EndRangeNbr      FundAmt      
614                              70                        0                              1                                    2                        8500      
614                              71                        0                              3                                    6                        8000        Start range is 1 plus last row;
                                                                                                                                                                          End Range  =
                                                                                                      …
0
Are there any list of requirements that must be in place for SSIS to show up in the program application list below. I would assume either a database must be present or is it a stand alone product in the list provide that I can point to a specific database to work on.

One of the members said Development licenses. I cannot afford one at the moment is there a trial or a way to get a free temporary license?
I think I just need as simple as I can get. I have weeks before I would start if I do get this opportunity. I think it will be time enough for me to catch up and get back into the swing of things. Reporting is what I do anyway. This is just a tool to clean up the data enhance performance and produce a result set to report off of.
SQL-Server-2016.jpg
0
If we have delimiters  as "." then     why "Will D. Smith" and "Will D Smith" returns similarity  as 0.98 instead of 1?
0
Hi Expert,

One of my user requested me to access on the integration services and i gave him access on MSDB database db_ssisadmin  but he is getting the error. Error is attached. I suggested him to run SSMS as an administrator but didn't work for him.

Please help!
_error.jpg
0

SSIS

SQL Server Integration Services (SSIS) is a component of the Microsoft SQL Server database software that can be used to perform a broad range of data migration tasks. SSIS is a platform for data integration and workflow applications. It features a fast and flexible data warehousing tool used for data extraction, transformation, and loading (ETL). The tool may also be used to automate maintenance of SQL Server databases and updates to multidimensional cube data. SSIS replaced Data Transformation Services, which had been a feature of SQL Server since Version 7.0.