Tag: ssis

Bulk inserts fail while switching partitions

I have a partitioned table. parttiion key is SiteID. I load data from different sites concurrently. I first truncate a partition (tried both partition truncation and partition switching) and then load data to that partition. This is doneby SSIS. Sometimes while loading data,another session truncates a different partition belonging to the same table. The partition […]

SQL Agent job intermittently fails to execute SSIS package

I need some suggestions for troubleshooting this issue. I have the following setup: Application T-SQL I have SQL code in an application that executes a SQL Agent Job. (exec @rtn = msdb.dbo.sp_start_job ‘Job Name’) If the SQL Agent Job returns a 0, I report a successful launch on the UI. If the SQL Agent Job […]

SSIS Package and DataSources tab's password

I have a SSIS Job with one step that executes a package. On the datasources tab I have specified the password in a SQL Connection string because I can’t connect using integrated security (I am using SQL Login). So I went to DataSources, added Password=XXXXXX and then pressed ENTER and clicked OK. The job worked […]

How to clean up SSISDB history for a specific job only

I am using SQL Server 2012. My ssisdb has a job that runs every minute. Standard retention cleanup would not effectively trim that job because it can cause growth in far less time than a standard daily job. Therefore I need to run a different maintenance cleanup on that job vs other jobs. I am […]

SSIS script task validation error upon

I am getting the following error in SSIS Script task (SSIS 2015) Adding Data to var: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. Did some online research and made sure to do the following based […]

SSIS Package and Foreign Keys

I have an SSIS Package that is importing a CSV file into a table called “OPEX_SPEND”. I also have a table called OPEX_Accounts with the following columns: opex_id (primary key) opex_name gl_account The “OPEX_Spend” has the following columns: OPEX_SpendID (primary key) opex_id (foreign key to OPEX_Accounts table) Date ItemText Amount I’ve created a data flow […]

SSIS Excel column name reading from file

I have a source file, has columns names with length as more than 100. Column name are same upto 80 characters and remaining 20 characters will be different, please find below example. ABC_XXXX_MNOAP : XYZABC_PAGELOADER_CLICKS_MANOPD_YXBDBAGD : VIEWS ABC_XXXX_MNOAP : XYZABC_PAGELOADER_CLICKS_MANOPD_YXBDBAGD : CLICKS & THROGHS While i am loading this file in to ssis, full column […]

Send additional information when control flow item fails?

My very simple SSIS package has three control flow items: two of them do work, and the third sends an email when any of the two fail. As it stands, if any of the items fail, I get a generic email saying that the package failed. Is it possible to send the “Send error email” […]

Linking sysssislog executionid to SSIS Agent Job history

I have an SSIS package that is configured to log to SQL Server. With the package configured, a System Table is created under: MyDatabase > System Tables > dbo.sysssislog This table has a schema that matches the identically named table held in msdb. Within this table, each package execution has a unique executionid, which is […]

Converting Cognos “running count” to SSIS

I’m in the process of converting some reports in Cognos to SSIS when I came across running-count. To my understanding it’s equivalent in MSSQL would be ROW_NUMBER() or at it least produces a similar result. Now I have 2 separate running-count statements running-count(distinct([row1]) for [row2], [row3]) running-count(1 for [row1], [row2], [row3]) and I’m not sure […]

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