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

HI EE,

I have an SSIS package that has a single step to run a PowerShell script.

When I execute the SSIS package via visual studio no issues. But when I run the SSIS package via a job in the SQL instance, and it completes successfully (no errors), no file is outputted as per the script.

Any assistance is welcome, see the attached screenshot for the SQL job configuration.

I am thinking it could be a permissions issue, but I checked the SQL Agent service account and it has permissions to the folder the PowerShell script is writing too.  

Thank you.
Capture.PNG
0
Keep up with what's happening at Experts Exchange!
LVL 12
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Hi,

I have an SSIS package in which the tasks are:

1. TASK 1: log to a file that the package started.
2. TASK 2: email task
3. TASK 3: EXECUTE SQL TASK
4. TASK 4: email a report

Problem is if the email server gateway is down, then TASK 2 fails.  However, I want TASK 3 to run regardless and I can do this by creating a Precedent Constraint arrow from TASK 1 to TASK 3.    Problem is how would I be notified that the email server is down so that when the email is working again, that I'd know when I can run TASK 4 (a report)?

Thanks.
0
I have bunch of files that I need to copy,but issue is that the file name is same but extensions are something like this .txt0,.txt1,.txt2 etc. So how can I create a SSIS package that could loop through these files and copy these files?
0
In my quest to create a poor man's address correction routine I created about 25 stored procedures similar to the one below. The only thing that changes are the strings used in the search. I have wrapped all these sp's into a VS program plus an SSIS job. What I just discovered is that the only changes that are sticking are the ones in the last sp that is called. If I run them individually the changes stick. I have a COMMIT TRAN at the end of each one.  What must I change to get the changes to stick as each sp is called? It must have something to do with all these sps called inside one session.


CREATE PROCEDURE [dbo].[apd_UpdateAddressesCASESB02]

AS

BEGIN

      SET NOCOUNT ON

DECLARE @TranCount int
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int

SET @TranCount = @@TRANCOUNT

IF @TranCount = 0
      BEGIN TRAN

BEGIN TRY

UPDATE mt
 SET [MOD_ESRI_LOCATION] = chg99.[MOD_ESRI_LOCATION]
 FROM [dbo].[cases] mt
 CROSS APPLY (
     SELECT CASE WHEN PATINDEX('%BLVD%', mt.[MOD_ESRI_LOCATION]) >= 4 THEN SUBSTRING(mt.[MOD_ESRI_LOCATION],1,PATINDEX('%BLVD%',mt.[MOD_ESRI_LOCATION])+1)
         ELSE mt.[MOD_ESRI_LOCATION] END  AS [MOD_ESRI_LOCATION]
 ) AS chg01
 CROSS APPLY (
     SELECT  CASE WHEN PATINDEX('%BSMT%', chg01.[MOD_ESRI_LOCATION]) >= 4 THEN SUBSTRING(chg01.[MOD_ESRI_LOCATION],1,PATINDEX('%BSMT%',chg01.[MOD_ESRI_LOCATION])-1)
         ELSE chg01.[MOD_ESRI_LOCATION] END AS [MOD_ESRI_LOCATION]
 ) AS chg02
 CROSS APPLY (
     SELECT CASE WHEN …
0
Hi EE,

Just finished setting up connectors to Oracle from SQL Data Engine standpoint now I need to get it working on SSIS.

Troubleshooting so far:

  • Installed the 32 bit and 64 bit Oracle client drivers on the windows server
  • Created a linked server on the SQL Server database engine instance works fine when I apply the whole EZCONNECT string. Doesn't work if just reference the label of the string in TNSNAMES.ORG file.
  • Installed the latest Microsoft Connectors V4.0 on the SQL server 2016 box.
  • Set ORACLE_HOME as a system environment variable.
  • TNS_ADMIN entry is now an environment variable.

Attached is the error I am getting

Any assistance is welcome.

Thank you.
SSIS-Connecton-Error.PNG
0
Hello Experts,
I am trying to rewrite an existing DTS package to SSIS package.  The very first step is (ActiveX Script Task Properties), where I have vb scripts, which picks up a text file and writes the data in a SQL table.  

1.  I am using VS 2008 to create the SSIS package.  The question is, what is the option in Toolbox, I need to select to do the job?   I need to select in SSIS tools
2.  Also, I have many steps, where I used Execute SQL Task Properties for coding the SQL Statements.  In SSIS, what is the option I need to select.

Please try to help.  Thank you very much in advance.
0
I had this question after viewing SSIS error VS_NEEDSNEWMETADATA.


have just run my SSIS package through SQL Agent and recieved the following error.

"component "Schedules01" (426)" failed validation and returned validation status "VS_NEEDSNEWMETADATA".  End Error  Error: 2009-05-24 04:16:10.30     Code: 0xC004700C     Source: AllSchedules DTS.Pipeline     Description: One or more component failed validation.  End Error  Error: 2009-05-24 04:16:10.32     Code: 0xC0024107     Source: AllSchedules      Description: There were errors during task validation.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  04:15:12  Finished: 04:16:10  Elapsed:  58.828 seconds.  The package execution failed.  The step failed.

I tried below solution :

deleted the data source and recreated it.
refresh the mapping  
recreated the whole task

I am not sure what it all means. Can anybody help?
0
I have a csv flat file full of customer names and addresses. One of the goals of this import process is to clean up addresses. In SQL I have added an additional field called MOD_ADDRESS. This is an exact copy of the ADDRESS field. It will be this field I will be correcting via other scripts. Is possible to populate both the ADDRESS and MOD_ADDRESS fields during the initial import task from the one input column? Since I can map the input column called ADDRESS to only one SQL column, how can I also populate the MOD_ADDRESS column at the same time?
0
hi,

is it possible to connection Oracle OLAP , Oracle application server, Oracle BI answer and BI server to MS SQL ?

we are thinking about what if we just migrate Oracle DB to MS SQL DB and left the rest untouched.
0
I have two very similar queries pulling this column value out of a table. These queries are embedded into two separate SSIS csv flat file export packages.
[Zone Location]
(42.628714605774775,-73.763909085255904)
(42.636101731163215,-73.781746725049999)
(42.666456988124622,-73.769417826333267)
(42.696827302737731,-73.848687575844366)
(42.644585953193598,-73.75781304403651)

All fields in both tables are defined as varchar(max). The first export package works perfectly every time. The second one is giving me fits because when it goes from the SQL query to the csv flat file, it insists on braking this one field into two and after that all the subsequent fields are out of alignment. I have been staring at both packages and everything is set up the same except the query. How can I force SSIS to recognize this value as text and accept the comma as part of the field value?
0
Get your problem seen by more experts
LVL 12
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

I am just wondering of the pros and cons in .Net of the following scenario...
We have an ssis project that...
Picks up CSV files from an ftp folder
And through the data flow loads them into SQL table

We also have a similar process that takes a CSV file and reads the data line by line into a standard procedure with each column [assed in as the parameter....

Personally I like a .Net console project where I read the CSV into a datatable
And then pass in the entire table to SQL Server in one shot.
In SQL Server there is a User Defined Table that matches the CSV format
Personally I like that because there is one connection opened and then closed
There are generally less than 1000 rows of data

What are the pro's / con's/ or preferred method?

    Private Sub SendToSQL(ByVal dt As DataTable)
        Try
            Using objConn1 As SqlConnection = New SqlConnection(System.Configuration.ConfigurationManager.AppSettings("strConn1"))
                Using oCom1 As SqlCommand = New SqlCommand
                    objConn1.Open()
                    oCom1.Connection = objConn1
                    oCom1.CommandTimeout = 0
                    oCom1.CommandText = "InsertVendorDispositionFileData"
                    oCom1.CommandType = CommandType.StoredProcedure
                    oCom1.Parameters.Add(New SqlParameter("@Table", SqlDbType.Structured)).Value = dt
                    oCom1.ExecuteNonQuery()
                End Using
            End Using
        Catch

Open in new window

0
Hi,
I'm currently running SQL Server 2005 and I've a script task in SSIS that has been running for years without issue however recently it failed and when I try to run the SSIS dtsx script task myself on Visual studio it throws an error saying that "Error: 0x7 at Script Task: Error 30451: Name 'MYNAME' is not declared."

So when I opened the script and go to the end I can see the item they are saying that's not declared has a blue underline on it.
However nothing has changed in the script so im not sure why its saying this now.

Also, the "PrecomileSciptintoBinarycode" I set this to false - other wise I get an error stating
"TITLE: Package Validation Error
------------------------------
Package Validation Error
------------------------------
ADDITIONAL INFORMATION:
Error at Script Task: The task is configured to pre-compile the script, but binary code is not found. Please visit the IDE in Script Task Editor by clicking Design Script button to cause binary code to be generated.
 (Microsoft.DataTransformationServices.VsIntegration)
------------------------------


Again I'm not sure why this is popping up all of a sudden. I tried to change the precompile to false , save and close the package then reopen and changed it back and still this doesn't work. I've tried just changing the precompile to false and its giving me the error above about not declaring.

Can someone help me please?
Thank you,
0
Two questions regarding creating an SSIS from SQL export/Import wizard. I am creating an export package that will extract data out of SQL and write it to a csv flat file.
1) When you use the Query option to select the data, after you have deployed the project is there any way to see that query again?
2) I see an option on the last screen about data will be appended to the csv file. Is there a way to have it not append and create a new file each time?
0
I have an SSIS project will consist of these three major components:

1) Import data from an input csv file into a SQL table
2) Run a series of SQL scripts that will correct and filter the imported data
3) Export this corrected data out to an output csv file

The end result will be a project that will need to be scheduled to run once a day. What are the best tools to be used to set this up? Can I deploy the two SSIS portions to run under SQL Server agent? How do I deploy these two projects to SQL so they can be scheduled? This entire process is being created on a test server and will be moved to production after testing. What would be the best way to design this so a minimum number of changes will be required after the move? This is being done with SQL Server 2014.
0
Hello,
I'm working on my first SSIS Project. I've included an audit process for every package.  When a package first starts the following Execute SQL Task is executed which adds a record to the auditing table RPSLoad
BEGIN TRY
BEGIN TRANSACTION
INSERT INTO [dbo].[RPSLoad] (
ServerExecutionID
,[ParentExecutionGUID]
,[PackageStart]
--,[PackageEnd]
--,[SourceRowCount]
--,[InsertRowCount]
--,[UpdateRowCount]
--,[LogicalDeleteRowCount]
--,[PhysicalDeleteRowCount]
--,[ErrorRowCount]
--,[IgnoredRowCount]
--,[PackageSucceed]
,[PackageName]
,[PackageExecutionGUID])
VALUES (?,?,?,?,?)
COMMIT
END TRY
BEGIN CATCH
DECLARE @ErrNum INT, @ErrMess VARCHAR(MAX), @ErrState INT, @ProcName VARCHAR(75)
SELECT @ErrNum = ERROR_NUMBER();
SELECT @ErrMess = ERROR_MESSAGE();
SELECT @ErrState = ERROR_STATE();
SELECT @ProcName = OBJECT_NAME(@@PROCID);


IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION
END
END CATCH

Open in new window


If the Package succeeds, then another Execute SQL Task is executed that updates the previously added record
BEGIN TRY
BEGIN TRANSACTION
INSERT INTO [dbo].[RPSLoad] (
ServerExecutionID
,[ParentExecutionGUID]
,[PackageStart]
--,[PackageEnd]
--,[SourceRowCount]
--,[InsertRowCount]
--,[UpdateRowCount]
--,[LogicalDeleteRowCount]
--,[PhysicalDeleteRowCount]
--,[ErrorRowCount]
--,[IgnoredRowCount]
--,[PackageSucceed]
,[PackageName]
,[PackageExecutionGUID])
VALUES (?,?,?,?,?)
COMMIT
END TRY
BEGIN CATCH
DECLARE @ErrNum INT, @ErrMess VARCHAR(MAX), @ErrState INT, @ProcName VARCHAR(75)
SELECT @ErrNum = ERROR_NUMBER();
SELECT @ErrMess = ERROR_MESSAGE();
SELECT @ErrState = ERROR_STATE();
SELECT @ProcName = OBJECT_NAME(@@PROCID);


IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION
END
END CATCH

Open in new window


Everything works fine in the Microsoft Visual Studio environment. However after it is deployed and executed via SSMS, I get the following type of error

"SQL Update Record on Load Audit - Failure:Error:
Executing the query "BEGIN TRY
BEGIN TRANSACTION
UPDATE a
set a.[Packag..." failed with the following error: "No value
given for one or more required parameters.". Possible
failure reasons: Problems with the query, "ResultSet"
property not set correctly, parameters not set correctly, or
connection not established correctly.
SQL Update Record on Load Audit
- Failure
InvLocCost Import:Error: One or more component

The record is added to the audit table.  The error occurrs when the record is being updated.  It's almost like it is not seeing the variables..  However I have them mapped properly in the Execute SQL Task. I've attached a screen shot of the mapping.  

Thanks
Mapping.jpg
0
We recently migrated from VS2008 to VS2015.
How would you recode the attached small script from a VS2008 SSIS Script Task so it would run under VS2015.
VS2008ScriptMain.vb
ScriptInfo.xlsx
0
Hi all,
I have following file Access-Logins-2018_05_20_Batch1.csv.gz and I need to know how do I unzip this zip file by using  powershell ? or can I achieve the same if I use SQL ssis package to unzip the file? After unzip this file going to load in sql server database
0
Hi All

I have Sql sever 2012. In production I am seeing slowness in database.
   How do I find slow running queries.
    How do we check the CPU usage.
    How do I see any deadlock.

Do we have queries to check this?

Any other metrics to check?

Also we have few SSIS packages. They load lot of new data from the files. The DB size is 1.5 TB.

Thank you!!
0
a Job is continously failing, it is transcational log backup

atabase Task Execute SQL Task     Description: Executing the query "BACKUP LOG [xxx] TO  DISK = N'F:\MSSQL11.MSSQLSE..." failed with the following error: "BACKUP detected corruption in the database log. Check the errorlog for more information.  BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.  End Error  Warning: 2018-05-12 11:00:19.53     Code: 0x80019002     Source: User DB Transaction log backup      Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.  End Warning  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  11:00:00 AM  Finished: 11:00:19 AM  Elapsed:  19.125 seconds.  The package execution failed.  The step failed.

any idea, what needs to be done


USE [msdb]
GO

/****** Object:  Job [IBM DBA.User DB Transaction log backup]    Script Date: 12/05/2018 8:07:19 PM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [Database Maintenance]    Script Date: 12/05/2018 8:07:19 PM ******/
IF NOT EXISTS (SELECT …
0
Cloud Class® Course: C++ 11 Fundamentals
LVL 12
Cloud Class® Course: C++ 11 Fundamentals

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

I am using SSIS 2008 R2.
I have created a Data Flow Task which exports the contents of an SQL table to an Excel template. The data exports with no issues (other than a cell colour issue which is another question!).
However, one of the columns is a date (which I know often causes problems!); the SQL column has a datatype of DATE.
For the Excel file the client wants the date in US format mm/dd/yyyy; the server is set to English(UK) and cannot be changed.
So, I have selected the column in the Excel template and set it to a custom format of mm/dd/yyyy. The problem is that when I open the file after the export has run, the dates show in the database format of yyyy-mm-dd. But, if you go into one of the cells and double-click it will change to the mm/dd/yyyy format.
Also, if you copy the column to notepad and then copy back, it shows the mm/dd/yyyy format.

The question is, how can I get it to show correctly in the first place? I've tried setting the localeID in the Advanced Editor to English(US) in the Excel Connection for the Data Flow Task and have also Refreshed the metadata, but none of this seems to make any difference.
0
I have a xml source task. When I run the package I get an error

The component "XML Source 1" (2861) was unable to read the XML data. Could not find file:

The component "XML Source 1" (2861) was unable to read the XML data. Could not find file 'C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\01_TwoWayFeed.xml'

Why?
0
working on an ETL project where we need to tie information from the AD server to the ERP system to get SSO logins.  I need to relate the Employee ID found in the ERP system to an Employee ID in the AD. I was trying to do this from SQL running in a SSIS package.

The problem is that the Employee ID in the AD is stored in the description attribute... i know... couldn't have been easy and stored in the EmployeeID or EmployeeNumber attribute right?  I can connect to the AD  and read the other attributes, but I get the "Could not convert the data value due to reasons other than sign mismatch or overflow" when trying to read the description attribute.  After some research, I understand it is a common problem because LDAP reports that value as a multi-value.  I found a supposed workaround on the Experts Exchange site, but that doesn't appear to work as it is also accessing via an OpenQuery (ADSI, ...... LDAP://.....  query.  

Just wondering if there is a work-around with SQL?  or if it is possible to pull a list of the description and mail fields via  .NET?  if .NET, could you point me to some examples?  While I do understand some C#/VB, I'm not an expert in that regard, especially when accessing AD.

Thanks
0
Hi,
I am connecting  my sql db(us-cdbr-iron-east-05.cleardb.net) via dbeaver no issues.

When i try to connect it via ssis i have issues giving error

even when i try to ping us-cdbr-iron-east-05.cleardb.net it is not pinging.
0
Hi,

I'm using SSIS 2008 and SQL SERVER 2008R2.

I want to get the current datestamp instead of  @[System::ContainerStartTime]   but I don't know what it's called.

In SSIS, I have a SEND MAIL TASK, then in  EXPRESSIONS, I have set MessageSource to :

        (DT_STR, 100, 1252)  @[System::ContainerStartTime] + " -- " + "Package completed -- SUCCESS"
0
I have created an SSIS package that import text and excel file from a central network location into tables on an SQL Server 2016 .  It execute flawlessly using the sql Data tools 2015. I then deployed the the project to the sql server.  The deployment went fine.  However, when I try to execute the import package I get the following error.  I've also attached the error report. I've checked the files and they are not open.  The Sql Server has been given the permissions to the file folder in question.  Just to cross my t's and dot my i, what types of permission does the sql server need in order to import files from a network location

RPS Import:Error: SSIS Error Code
DTS_E_OLEDBERROR. An OLE DB error has occurred.
Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft Access
Database Engine" Hresult: 0x80004005 Description: "The
Microsoft Access database engine cannot open or write to
the file '\\Bfs\departments\CORPORATE\DQM\ONLINE
RPS TEST DATA\Online BCM Tool Files\6. Change
Report Type\Change Report Type.xlsx'. It is already
opened exclusively by another user, or you need
permission to view and write its data.".
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.

Top Experts In
SSIS
<
Monthly
>