[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

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

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
C++ 11 Fundamentals
LVL 12
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

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
Hi,

I have a DTS job that uploads some CSV data into a SQL table.

I wanted to schedual the DTS using an SQL job.

I have setup the job in SQL but when it is executed through the SQL job it fails (see error message below).

If I run the DTS directly it works ok.

I think it is something to do with the user permissions but I don't know why it fails as SQL user I have setup (web_admin) has SA rights.

Any ideas?

DTS fail
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
I have a situation where i need to reproduce a complex SSIS package with varying degrees of differences but for the most part 60% of it is the same across all permutations.

The problem being is each one needs to be recreated from scratch. If any copy and paste is done then it results in METADATA errors when i test in the DEV environment but even more so when i deploy to production.

This quickly turns into a nightmare.

Has any one developed any clever ways to resolve the SSIS METADATA errors that doesn't require either creating each package from scratch  or opening, building, saving, and closing each and every task?

Thank you.
0
Hi,
I have the following VB Script Task in an SSIS package.
It takes a set of variables  (ScriptPath, uName, Pwd, etc) and builds a shell command .
The package is not executing successfully.
I want to somehow be able to output the final text that is presented to the Shell so I can examine it, and possibly try to run it outside of the package.
Any guidance in how I can output this text, possibly a log file? Or output to the screen when running the package on Debug mode in visual studio?
Thanks

' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic 2008.
' The ScriptMain is the entry point class of the script.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

<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
	

	' The execution engine calls this method when the task executes.
	' To access the object model, use the Dts property. Connections, variables, events,
	' and logging features are available as members of the Dts property as shown in the following examples.
	'
	' To reference a variable, call 

Open in new window

0
I have an SSIS 2017 package that reads an Excel file. I have tried the xls and xlxs versions. The project first checks for file existence before importing. Upgrading to SSDT 2017(VS) from SSDT  2015 got me passed a script version error message. Now I am stuck here. What must I do to get passed this? The project runs just fine in SSDT 2017. It is when it is run a job or package in SQL that it fails. I have installed 64 bit everything. Why is it complaiNIng about 32 bit OLEDB?
SSIS Excel import error
0
I just deployed a job in SQL 2016 just like I have deployed dozens of other jobs. They all run SSIS projects. I received this bizarre message when trying to run the job. The SSIS package runs just fine and I even deployed it a second time. What must I do to correct this? Job Failure
0
Hi , I would like to load data from 3 different SQL Command (3 different queries) in 3 different excel files in one folder with each excel file with Date time feature in One package .
I tried this solution however getting issue as It wont open mapping window for 2nd Excel destination. However 1 excel file is loaded successfully., Your help is appreciated

Error is:

Excel destination: Opening a rowset failed. Checked that objects exists in Database.
0
Become a Certified Penetration Testing Engineer
LVL 12
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

How to resolve culture problem of decimal and thousand separator  of number of excel using c#
1
Hi,

I'm using SSIS 2008.

I have 2 ssis packages.
QUESTION:
1. can packageA call packageB ?   How (please show example code)?
2. Will packageA task that executes packageB wait until packageB is completed before packageA goes on to process a subsequent task?

Thanks.
0
When this stored proc executes, it doesn't seem to be able to evaluate whether the variable @SQLAgentJobType contains the text 'Incremental' or 'Manual'.  Right now the value is set in the SSIS environment as 'Manual', but it executed the steps for 'Incremental'. If I reverse the order of the IF -blocks, it will execute the steps for 'Manual' first if the value is set to 'Incremental'.  As an aside, before anyone complains about the start and end dates as being nvarchar(20)-- don't. They are set this way because they are concatenated to another set of SSIS environment variables for an openquery statement needed elsewhere in the SSIS package.  Please review and advise.

ALTER PROCEDURE [etl].[ResetProjectDaterangeVariables]
	-- Add the parameters for the stored procedure here
( @SQLAgentJobType varchar(20),
  @FolderName nvarchar(128),
  @EnvironmentName nvarchar(128),
  @startDate nvarchar(20),
  @endDate nvarchar(20)
)
AS
BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.

	SET NOCOUNT ON;

	 DECLARE
	 -- Note: The string dates are created in order to deal with cobbling together an Informix SQL statement as part of a 
	 --       concatenated text string built by variables used in source dataflows

	 @tempstartDate nvarchar(20)
	,@tempendDate nvarchar(20)
	-- leave room for a leading space
	,@fixedEndTime nvarchar(9)
	,@fixedStartTime nvarchar(9)
	,@var sql_variant;

   -- DEBUG [uncomment]
   --  declare 

Open in new window

0
Hi, I'm using SSIS 2008 and SQL SERVER 2008R2.

I have a table that has several months of data.fep_ssis_upload.JPG
How do I make SSIS prompt the user for a string such as 'JUN 2018' to pass to a stored procedure query or VIEW that gets the data?

Currently, my SSIS calls the table that has data.  Please see the attached 2 printscreens.
Need to get just 1 month of data from this
0
Hi,

I am in the process of migrating a SQL server 2005 to a SQL server 2012.     On the 2005 server,  SSIS packages are used to upload CSV data into various databases.  These SSIS packages are created in Visual studio 2005.

My questions are around the versions of software I require on the new server:

1.  Will the new SQL 2012 server require an install of visual studio? if so, which version?  It would only be used for SSIS development - would an express version be suitable?
2. Are there any migrating tools to help?
0
Can someone point me to a few good links that illustrate how to use SSIS to read an Excel xls file and then insert that data into a SQL table? First I want to check for the existence of the xls file and if it is there proceed with the import. I have started by creating a script task but I cannot see where the connection from that task is made to the FileExists variable I defined so I can determine whether or not to proceed. I have both the script task and the OLE DB Destination task started but I do not get the second arrow indicating failure. I have been scouring the internet for example but have not found any that provide a solution.
0
Help with C# code to delete first row of all Worksheets in an Excel Workbook using SSIS script task.
There are about 17 worksheets, the script needs to loop through and delete the first row.

 Loop-through-Excel-sheets-and-delete.sql
test.xlsx
0
Hello,

I am getting below error while connecting Oracle DB from Visual Studio 2015 - SSIS. Can you help me to fix below error?
MS SQL Version: MS SQL 2016 Ent Edition
Oracle client version: Oracle 11g 64 bit (Oracle Database 11g Release 2 Client (11.2.0.1.0) for Microsoft Windows (x64) )
ODAC version: ODAC 11.2 Release 5 and Oracle Developer Tools for Visual Studio (11.2.0.3.20) (32 bit)

I have also tried with 64-bit ODAC 12.2c Release 1 (12.2.0.1.1) for Windows x64, but still same error. There are No multiple 32-bit client libraries (SQAORA32.dll) on the same machine.

I am able ro connect oracle DB using SQL plus utility but geeting error in SSIS.
0
Hi,

I'm using SQL SERVER 2008R2 and SSIS 2008.  How do I export a SQL SERVER view to Excel .xls format?  I've done it a long time ago.

In the DTS task, I've set the fields to DATA TYPE=UNICODE STRING [DT_WSTR].

Problem I'm running into is the next task  EXCEL DESTINATION: when I click MAPPINGS, the right side of the screen just says F1  ssis_export_to_excel.JPG
0
Python 3 Fundamentals
LVL 12
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

I have a SSIS Data Flow Task that read Data from OData services, I'm able to connect to it successfully via OData Connection Manager. But when I run the task within SSDT, I'm getting following error

[OData Source [2]] Error: The OData Source was unable to process the data. The remote server returned an error: (401) Unauthorized.


[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on OData Source returned error code 0xC02090F5.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages posted before this with more information about the failure.

I'm using basic authentication to connect and I can view data in the preview section of OData source editor. I don't know why I'm getting the error, the user has permission to extract the data.
0
I have created SSIS package in VS 2012 and SQL server 2012, it is working properly V.S. on the local machine
but  I am facing One issue when I am executing my package using a batch file on Server  
I got one error as
Error: 2018-08-30 07:37:43.57
Code: 0x00000001
Source: Script Task
Description: Exception has been thrown by the target of an invocation.
End Error

Can you please  provide a solution how to fix this issue
0
I am currently using a fore each file enumerator within SSIS to pick up the names of files and pass then to a script for processing. There are 2 parts to the process. I use a variable to hold the file name and another variable to hold the file path. For debugging I have set message boxes to display firstly the file name and another to display the full path to the file. The process is not picking up the file name so the first message box is empty. The second only displays the path to the folder where the file should be.

Any one any ideas on this?

Thanks
0
Hi,
I’ve made some changes to some SQL Server objects, tables,views, stored procedures, functions etc in my test environment which I now want to move to the production environment.
I want to make backups of the existing objects in production, eg Production_Table_1 as Production_Table_1_backUp.
I’m looking for the easiest way to make a copy of the table in its existing database, and the copy to include all associated indexes,constraints, data etc…
I looked at Transfer SQL Server Object Tasks in SSIS but it doesn’t seem to allow altering the object name.
I’m now looking at using  the “Script Table as” option in Management studio but it doesn’t seem to copy indexes.
Any guidance appreciated.
0
Is it possible to attach a file with the email notification of an SSIS job being completed? I have several SSIS job that run in the morning and produce csv files. For one of the jobs the client not only want email notification of completion but wants the csv file attached to the email. Is this possible? I already have the email notification set up but how do you attach the file?
0
I was  creating another SSIS  project by copying to another project. The flat file source changed and the target SQL remained the same. I am getting this message"

[Flat File Source [2]] Error: Data conversion failed. The data conversion for column ""MyField"" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

and upon looking at the data I cannot see anything wrong. How do I put in an error trap that will give more detailed information?
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.