Tag: ssis

Make Kingswaysoft truncate input data that is too long

I have an SSIS project that I’m using to automate pulling CRM data into a SQL Server Database using Kingswaysoft. These SSIS packages are autogenerated, so my solution to this issue needs to be compatible with that. The description field on Contact in CRM is a nvarchar(2000), but this CRM org still has old data, […]

How to fetch value from another row within the same data pipleline?

I have data loaded and various transformations on the data complete, the problem is there is a parent/child relationship managed in the data – best explained by an example each row has (column names are made up) row_key parent_row_key row_name parent_row_name some rows have row_key == parent_row_key (their own parent) some rows relate to another […]

How to execute a stored procedure which has its object name stored in a variable

I have a table which contains a column of stored procedures associated with each tuple. I have an Execute SQL Task which selects the procedure for the desired tuple and stores it in a variable. I then connect that to another Execute SQL Task which uses the statement EXEC ? and map the variable as […]

How execute an exe file with dynamic parameters within a loop

I have a database table with 20 columns and 40 rows. In the third column is an identifier that is unique. I have a .exe written in VB.NET that connects to a datasource and downloads a dataset depending on the identifier. So this VB.NET programm needs an input parameter. Now I want SSIS to loop […]

Automate SQL Script with Multiple Statement Types

I have a SQL script that works exactly as I want it to when I step through it line by line. At first, this was a viable option, but the script has gotten larger over time and stepping through it now sucks. I’ve tried to run the script as part of an SSIS package in […]

Batch script using dtexec does not succeed after recent windows updates

My PC caught up with about 2 months worth of updates last Friday. Now I cannot launch SSIS with batch scripts. Tried adding the Microsoft.sqlserver.manageddts.dll to the C:\Windows\System32 Directory using the gacutil.exe. It is showing as the 11.0.5058.0 version. Readout of batch below. Started: 3:43:52 PM Info: 2017-05-19 15:43:52.75 Code: 0x40019316 Source: Package Format Migration […]

Parameterize and Execute SSIS DSTX for Data Import

I’d like to transfer a whole bunch of data between 2 SQL Server databases using SSIS. How can I parameterize the output of the SQL Server “Import Data” wizard and execute it to run on other environments? In SQL Server, I right-clicked the destination database and clicked “Import Data” and went through the wizard and […]

Powershell returns negative exit code while script results are correct

I have made the following PowerShell script: Set-Location D:\folder1\folder2\folder3\folder4; Get-ChildItem | Rename-Item -NewName {$_.BaseName.insert(19,’loadtime’) + (Get-Date -Format HHmm) + $_.Extension}; The goal is to rename a file by adding loadtime at position 19 and a time stamp. While the results of running the scripts the following error message is returned: Rename-Item : The input to […]

can not access sql server developer sp1 ssis from ssms

I am installing Sql server 2016 Sp1 developer on Azure VM Windows 2012 R2 datacenter. The database engine and analysis service (SSAS) are working proerpty however integration service (SSIS) is inaccessible even the service is running. The services are running: but I could not login from SSMS (run as Administrator). The message error is “The […]

Abnormal data validation in XML

I will be receiving some confidential employee data in XML with separate XSD. It will be injected into our SQL Server (2016) using SSIS. I was asked to edit the XSD schema as I want (for the data formats, i.e. change string to int, etc.). I wish I had some data validation, like if I […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.