SSISSponsored by jamf

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 working on a SSIS package, which was converted from DTS package.  In Script Task, I have some codes, where I am trying to check the existence of the network file.  For some reason, I am getting the runtime errors below.  Please let me know what is the error and how to fix it.

Thank you very much in advance.


Dim obj As Object
If (Not obj.FileExists(strNetworkFile)) Then
     Dts.Variables("TaskResult_Failure").Value = True
     Exit Sub
End If

Errors
---------------
Implicit conversation from 'Object' to 'Boolean'.
Late bound resolution; runtime errors could occur.
Operands of type Object used for operator 'Not'; runtime errors could occur.
0
Introducing Cloud Class® training courses
LVL 12
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Experts,

I'll be loading hundreds of XML files into the SQL Server and I am wondering what is the best solution out there. I have attached a sample XML file. Is SSIS a better solution to automate this process or are there any third party tools we can use to complete this project?

Regards.
HE_24201_OK.xml
0
Hello Experts,
I am trying to open a DTS package in Visual Studio 2015.  The goal is to convert it to SSIS package.   I was able to open the DTS package with error in Integration Services Script Task, where I have VB codes.  When tried to edit it, I received "This task does not have a custom editor. Use the properties window to edit properties of this task"
 message.  Any idea how to edit it?  Please try to help.  Thank you in advance.

Thank you!
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 a DTS package in SSIS and trying to make use of the existing codes defined under ActiveX Script Task.  When copied and pasted in Integration Services Script Task in SSIS package, I received some errors as below.  I am trying to find the place where they are declared as I don't see these variables defined in the program.  Please see if you can help.  Thank you in advance.

Error      1      Expression is a value and therefore cannot be the target of an assignment.      
Error      2      Name 'StepScript_TaskDontExecute' is not declared.      
Error      3      'SourceFileExists' is not a member of 'String'
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
Free Tool: SSL Checker
LVL 12
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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
How do you change SSIS 2014 package variables. I can see the variables but they are grayed out.  
This SSIS Project was recently migrated from 2008 to 2014 by our migration team.
For instance, I would like to change variable DispatchExcellenceServer from DISPEXCDEVMIG to DISPEXCDEV.
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
The application executes the SQL stored procedure to run the package and package executes as proxy account. We can schedule same package to run at same time with different variables or source files as input and when scheduled to run at same time, one package completes the execution and other package fails with below error. If there is some time gap then both packages succeeds.Can someone suggest how to fix the issue?

Started:  10:13:06 AM
Error: 2018-05-21 10:13:06.78
   Code: 0x00000001
   Source: Script Task
   Description: Exception has been thrown by the target of an invocation.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started:  10:13:06 AM
Finished: 10:13:06 AM
Elapsed:  0.795 seconds
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
Cloud Class® Course: CompTIA Healthcare IT Tech
LVL 12
Cloud Class® Course: CompTIA 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.

I have a 2012 SSIS package that will take the results from a query and using a flat file connection save the data to a csv file to a local drive on the server. This works but I would like to upload this csv file to a web page so a user can get access to it easily.
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
We have a scheduled job which executes a SSIS package daily and it has been working fine. But recently for one customer, it fails with error "The binary code for the script is not found" and again works fine without any fix for the same data source file. The same issue is happening for different data source files and it is not consistent.

Below are the log details when the package fails. We have DelayValidation set to True for the data flow task. Can someone help me on how to fix this issue?

Details from log file:

04/20/2018 2:00:08 PM: About to execute the package
04/20/2018 2:00:08 PM: Execute result: Failure
04/20/2018 2:00:08 PM: Executing the package resulted in 4 error(s)
04/20/2018 2:00:08 PM: Error: The binary code for the script is not found. Please open the script in the designer by clicking Edit Script button and make sure it builds successfully.
04/20/2018 2:00:08 PM: Microsoft.SqlServer.Dts.Runtime.DtsError
04/20/2018 2:00:08 PM: Error: "Script Source" failed validation and returned validation status "VS_ISBROKEN".

04/20/2018 2:00:08 PM: Microsoft.SqlServer.Dts.Runtime.DtsError
04/20/2018 2:00:08 PM: Error: One or more component failed validation.

04/20/2018 2:00:08 PM: Microsoft.SqlServer.Dts.Runtime.DtsError
04/20/2018 2:00:08 PM: Error: There were errors during task validation.

04/20/2018 2:00:08 PM: Microsoft.SqlServer.Dts.Runtime.DtsError
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
Using SSIS 2008 R2
Output to Excel 2010

I am using SSIS to export a SQL table to an Excel template. This all works fine (aside from a Date issue which is the subject of another question), but for some reason the exported cells are all formatted with a blue colour.
I've checked that there is no conditional formatting in place on the Excel worksheet. There are no macros either.
I can't find any settings anywhere within SSIS that would cause it to do this; it's not a huge issue as it is cosmetic (the output data is correct), but I would prefer it to not change the cell colour! However, if I have the workbook open when SSIS is exporting the data it does not change the colour of the cells; of course this takes far too long to process.

Cell formatting
0
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

SSISSponsored by jamf

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.