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

Hello Experts,
I am stuck in a package which was running fine before, but hangs now.  I have about 15 tasks to run in the package.  Task 1 thru 4 runs just fine.  They are just copy the Flat file to local directory, Backup and Delete it etc.  The 5th one is insert the data into the database table where it hangs.

FYI, after 1 thru 4, if I try to execute the 5th task independently, it runs without any problem.  But fails if I run the whole package.

I added an Event Handler, but it does not show me any errors.

Any idea what else could be going on?   Please try to help.

Thank you very much in advance.
0
OWASP: Forgery and Phishing
LVL 12
OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

I have query performance issues when using date parameters in a stored procedure.  The proc takes 15 minutes with hard-coded date values but well over an hour with parameters. I was calling it from SSIS and had trouble passing the variables as datetime2. So I was passing as varchar and handling the conversion in the proc.  Then I took SSIS out of it as i tried to debug. I've been searching for quite some time and I can't get anything to work. This is basically where I've left off.  (SQL2016)

EXEC dbo.GetData @ObsStartDt = '1/1/2017', @ObsEndDt = '12/31/2017'



CREATE PROCEDURE [dbo].[GetData] @ObsStartDt varchar(10) = NULL, @ObsEndDt varchar(10) = NULL
AS

BEGIN TRY  
SET NOCOUNT ON;


declare @StartDT datetime2  
declare @EndDt datetime2

set @StartDT = cast(@ObsStartDt as datetime2)
set @EndDt = cast(@ObsEndDt as datetime2)

select  <stuff)

from HealthPlanDM.MED_CLAIMS_FACT a
join HealthPlanDM.MEMBER_DEMO_DIM b
on a.MEM_DEMO_KEY = b.MEM_DEMO_KEY
join HealthPlanDM.PRODUCT_DIM c
on a.PROD_KEY = c.PROD_KEY      
join HealthPlanDM.DIAGNOSIS_DIM d
on a.DIAG_KEY = d.DIAG_KEY
join HealthPlanDM.PROCEDURE_DIM e
on a.PROC_KEY = e.PROC_KEY
join HealthPlanDM.SERVICING_PROV_DIM f
on a.SERV_PROV_KEY = f.SERV_PROV_KEY
join      HealthPlanDM.POS_DIM g
on a.POS_KEY = g.POS_KEY
join      HealthPlanDM.DRG_DIM h
on a.DRG_KEY = h.DRG_KEY
join HealthPlanDM.TOS_DIMBASE i
on a.TOS_KEY = i.TOS_KEY
join devwork.ACG_PATIENT_EXTRACT1 P                  
on b.MEM_ID = P.patient_id  
left …
0
How to fetch value from a variable in the Process Task in SSIS?

In the Control Flow : - I have  

1 ) Execute SQL Task - > In which i have a select query which fetches 3 row of records. Each mapped to 3 variables.
Variable is mapped to pick the folder path value which is configured in the database.

2) Execute Process Task -> Property ->
                                       Executable ->  I have mapped the physical folder path to it. ( Hard Coded ).

Objective : To get the value of Folder Path from SQL Database and assigned to the variable.

How to map the value to the variable dynamically in  Execute Process Task : SSIS Package?
0
Inside the Forloop each container; In the DataFlow Task, we have Excel Source file. Excel Source file is currently mapped to the source folder path.

We are making the changes to pick the folder path from SQL Table Configuration.  

We have a table name FileConfiguration which has Inbound file path, Archive file path.

How to pick the value from SQL Table and assign to the variables which can be used for passing it to the Excel Source in the SSIS package?
0
In SSIS, How to export an excel file by pointing to the specific tab # 3?

Source file has 3 tabs  (or) 3 sheets.  I need to export specifically sheet # 3 or tab # 3.
Sheet # 3 has few passages in the beginning and then comes table of records.

At present, I manually clean up the sheet # 3 by deleting the passages and having only the table of records.
I load the  Excel by pointing only the sheet # 3 to the SQL Table.
0
I am getting this message when I try and run my SSIS Package in a Command Window

Package runs perfectly fine in debug

I have tried setting the package to 32 bit
And I have downloaded and installed the Access ole dll

Executing this in COmmand Prompt
"C:\Program Files\Microsoft SQL Server\140\DTS\Binn\DTEXEC.exe" /File "C:\EW\SSIS\MyFolder\Package.dtsx"

...blah blah blah...
Error: 2018-12-20 14:34:44.94
   Code: 0xC0209303
   Source: Package Connection manager "Excel Connection Manager 3"
   Description: The requested OLE DB provider Microsoft.Jet.OLEDB.4.0 is not registered. If the 64-bit driver is not installed, run the package in 32-bit mode. Error code: 0x00000000.
An OLE DB record is available.  Source: "Microsoft OLE DB Service Components"  Hresult: 0x80040154  Description: "Class not registered".
End Error
Error: 2018-12-20 14:34:44.94
   Code: 0xC020801C
   Source: Send Data to Everyware Get All Events [2]
   Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "Excel Connection Manager 3" failed with error code 0xC0209303.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.
End Error
0
I have an SSIS project that I created in Visual Studio.  It includes two packages.  One package exports data from SQL tables into an Excel spreadsheet.  The other iimports data from an Excel spreadsheet into a SQL table.  When I run it in Visual Studio on my development machine, both packages work properly.  But when I deploy it to another server as an Integration Services Catalog SSISDB package, the export from SQL to Excel works, but the import from Excel to SQL fails with the following message:

"SSIS Error Code. DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager 'SQLSERVER' failed with error code 0xC0202009."

Then a second error pops up:

"An OLE DB record is available. Source: 'Microsoft SQL Server Native Client 11.0'. Hresult: 0x80004005. Description: 'Named Pipes Provider. Could not open a connection to SQL Server [53]."

Both packages use the same connection manager (called SQLSERVER).  I made sure that the Run64BitRuntime setting is set to False and DelayValidation is set to True.

Again, the export from SQL to Excel runs without error.  It is the import from Excel to SQL that is throwing the errors.

T
0
Hi,
I am using the following to populate an SSIS variable
SELECT ? = CAST(MIN_ACTIVE_ROWVERSION() AS BINARY(8))

Open in new window


When I run this in BIDS it returns a non specific error.
When I run the code in SQL 2005 management studio (minus ? =) it returns the value as expected.

Anyone any ideas on why this is not working?

Any help would be appreciated.

Thanks
0
i am trying below kind of query which should give count less than 30 minutes of difference of time. not able to execute below. How to modify to make it work

select count(order_nbr) from order INNER JOIN timings ON order.order_id=timings.order_id WHERE LEN(order_nbr) = 11 AND order_cd=02  AND order.time_ins- timings.time_upd <30 minutes
please advise
0
I hope someone can provide me some guidenace...

We have a lot of InfoPath forms. We're going to PowerApps. My teammate has been tasked with finding out if we could convert the InfoPath forms into PowerApps without rebuilding all the forms from scratch . He said he's read something about "auto converters" but he hasn't found any.

I don't know anything about InfoPath or PowerApps. My teammate  has hit a wall in his research. I hope someone can give me some input.
0
Become a CompTIA Certified Healthcare IT Tech
LVL 12
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Hi EE,

I am getting an error connecting to integration services 'Access is denied' when a particular package is run. I have checked the following:

The users are part of the Windows Admin group and have sysadmin credentials on the SQL Server.

Running SQL server SSIS 2008 or v10.

Any assistance is welcome.

Thank you.
0
Hi EE,

We have recently upgraded our SSIS Server from 2008 to 2014, SSIS services v10 has been kept for some of our older packages. All the SSIS packages on our server are running except for 1, error message below:

Executed as user: BUCR\svc_0070. Microsoft (R) SQL Server Execute Package Utility  Version 12.0.5000.0 for 64-bit  Copyright (C) Microsoft Corporation. All rights reserved.    Started:  2:28:10 PM  Error: 2018-11-27 14:29:21.73     Code: 0xC0202009     Source: Cleanse Aurion employee data for import to Procura view_Aurion [154]     Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E09.  End Error  Error: 2018-11-27 14:29:21.73     Code: 0xC02020E8     Source: Cleanse Aurion employee data for import to Procura view_Aurion [154]     Description: Opening a rowset for "[dbo].[View_Aurion]" failed. Check that the object exists in the database.  End Error  Error: 2018-11-27 14:29:21.73     Code: 0xC004706B     Source: Cleanse Aurion employee data for import to Procura SSIS.Pipeline     Description: "view_Aurion" failed validation and returned validation status "VS_ISBROKEN".  End Error  Error: 2018-11-27 14:29:21.73     Code: 0xC004700C     Source: Cleanse Aurion employee data for import to Procura SSIS.Pipeline     Description: One or more component failed validation.  End Error  Error: 2018-11-27 14:29:21.73     Code: 0xC0024107     Source: Cleanse Aurion employee data for import to Procura      Description: There …
0
Hi EE,

How do I write the SSIS logging output to a DB on my server, there is someone wrong in the connection string I am using:

I am using SQL server 2008 R2 SSIS.

Any assistance is welcome.

Thank you.
Capture.PNG
0
I need an SSIS Example project (VS Shell 2013) That Uses a 2003 version of Excel file(s)

And 1 by 1 pulls in the file... sends the data to SQL
Then renames the file to another folder
0
I have an SSIS package that is giving me an error in our test environment. It runs fine in DEV, but errors in test. It is a data flow task with an OLE DB source that executes a SQL Servr stored procedure. The code is:
EXEC [uspME-GF-30250-HMS_HD] ?,?,?,?
WITH RESULT SETS(
(	
	ORDERS VARCHAR(1),
	RECORDS VARCHAR(1000)
 )
)

Open in new window

The error is:
Error: 2018-11-14 09:24:03.48
   Code: 0xC0202009
   Source: DFT_LOAD_MEMBER_EXTRACT_FOR_HMS OLE_SRC_MEMBER_EXTRACT [9]
   Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80040E14  Description: "EXECUTE statement failed because its WITH RESULT SETS clause specified 1 result set(s), but the statement only sent 0 result set(s) at run time.".
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80040E14  Description: "Invalid column name 'CXCX_RECORD_NO'.".
End Error

I thought of removing the WITH RESULT SETS but I can't even save the changes. SSIS gives me the following error:
The metadata could not be determined because the statement 'INSERT INTO #StaticData...' uses a temp table.
The stored procedure is 'relatively' complex, creating and loading several temp tables, some intermediate staging tables, them finally building the data extract result. I've never run across this issue before and am looking for a solution. Checking Google for…
0
Hi,
am Getting an error when importing a file using SSIS
The "Flat File Source.Outputs[Flat File Source Output].Columns[TEST_ACT_TYPE]" failed because truncation occurred, and the truncation row disposition on "Flat File Source.Outputs[Flat File Source Output].Columns[TEST_ACT_TYPE]" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.  
An error occurred while processing file "\\testfolder\\Inbound\test_file_123756.TXT" on data row 1.  
any guidance appreciated
0
Dear Experts,

I am thinking to use SSIS to do the following procedure.

(1) copy many files to Linux machines. then
(2) execute a Linux program (command) to analysis those files. The program will output the result to the local Linux drive
(3) when (2) is done, then pull the result back to Windows machine

For (1) I am thinking of FTP task (by FTP or SFTP)

how about the task of (2) and (3) ?  Could you please help ... Thanks
0
Using SSIS, I am exectuing a stored procedure and which returns one column which contains values serperated by the | delimiter so the datatype is nvarchar(max).

I can successfully seperate it into columns using the FINDSTRING function, but when I exporting the data to excel (2007-2010 version as an xslx), everything is in the text format.
Excel warns that the cell is a 'number stored as text' on numeric fields.

So I require help on how to ensure numeric fields come out as numbers.
For info the template excel file as headers on the first row that are all text.

Useful info:
Excel Connection String is:
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=samplefile.xlsx;Extended Properties="EXCEL 12.0 XML;HDR=YES";

Let me know if further info required, Thanks in advance.
0
Hi all.

We're trying to figure out a way to download emails from an Office 365 email address to a table in our SQL Server 2008 server. Is there a way to do this (ie. SSIS package or other).

We would bring in the from, to, cc, email body

Thank you in advance!
0
CompTIA Security+
LVL 12
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

Where to begin.
I need to Move some data from Teradata to MS SQL ussing SSIS.

Here is the part I am stuck. I order to create the query in Teradata I need to import a file with 20,000 IDs into a Volatile temp table in TeraData then use that table in a join.

CREATE MULTISET VOLATILE TABLE SomeTempTable(
IDs
)
PRIMARY INDEX(IDs)
ON COMMIT PRESERVE ROWS;

--After i create the temp table I need to do this
INSERT INTO SomeTempTable(?)

Open in new window



In SSIS how do i account for the import here because using SQL Assistant i get a explorer window to point to file with IDs.

I am thinking this would have to be done in a script Task. Even using a script task i am not sure how to point to file?

I would like to know how to get this done. If there is another way not involving script task I am all "EARS" as well.

Thank you in advance!!!
0
In Visual Studio, Integration Services, I have a SQL source and an Excel destination.  Is it possible to create an initial step that deletes the contents of the Excel file, except for the column headings.
0
Just trying to find out more information about SQL SSIS which I know nothing about.  Can SQL SSIS be used ingests a Multi-Worksheet EXCEL file and separate each worksheet into a separate output file like text or csv?  We are think about using SQL SSIS as a preprocessor for an application that does not support multi-sheet EXCEL files.  I am aware we can use SQL SSIS to do data transformations but now i want to know if this can file re-engineering tasks like this.  Also, SQL SSIS be configured to generate an XML file?
0
Is it possible to create an SSIS package in Visual Studio with the Server Name and Data Source in the Source object as a parameter, so that you can provide the SQL server name at runtime?
0
I run an SSIS package to create a .txt file on to an FTP server and the delimiter I use is a pipe delimeter:

I have to import this file into a third party too and I get this error message: invalid field count

196      196      Invalid field count: expected 17 fields but found 10      [203822][1][903510001][Accessories][331000][0][Uncertified][5000][1/8/8/0/9/18809622][  Edition Red, White & Blue Crystal Minaudiere]
197      197      Invalid field count: expected 17 fields but found 1      [<BR> Condition: 1]
198      198      Invalid field count: expected 17 fields but found 8      [<][][1][][0][0][37][collection]

I can't use any other delimiter because of the nature of the file. Can you provide any suggestions please
0
I have an ADO.Net Connection that goes to a Teradata source. The query gets created on the fly by expressions and SQL TASK before I pass it to the ADO.Net Source but I cant figure out a way to pass my query to the ADO.Net Source.

The only 2 options I get are "Table and View" and SQLCommand
I tried SQLCOMMAND and placed the variable holding query in the input box but it failed.

OLE Source has the variable option to use a variable  for query. I cant user OLE because I dont have the drivers installed for Teradata. How can i get my query on to this ADO.Net source?
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.