This sort of approach does actually work very well. You haven’t given any idea of what your data/metadata is like, but the concepts can be explained with a simple example. Consider the following data: We will make a simple report based on this, grouped by the grp column: Sort the groups by total val, from … Read more
By default, everything will run in 64 bit on the servers. To change this behaviour, you need to indicate that the 32bit version of dtexec should be used. For the 2012 SSISDB, we have two easy ways of invoking our packages: SQL Agent and the catalog.start_execution method. catalog.start_execution For single serving package runs, you can … Read more
EDIT: Casting to a float/int no longer works in recent versions of SQL Server. Use the following instead: select datediff(day, ‘1899-12-30T00:00:00′, my_date_field) from mytable Note the string date should be in an unambiguous date format so that it isn’t affected by your server’s regional settings. In older versions of SQL Server, you can convert from … Read more
Here is one possible option. Following example might give you an idea of how you can achieve emailing a result set using Send Email task. This example shows how to loop through the query result set to form the message body that will then be emailed using Send Email task. If you don’t want to … Read more
Whilst awaiting clarification on how you intend to use it, I’d suggest looking at the Export Column Transformation. Similar need on this question Using SSIS to extract a XML representation of table data to a file I banged out a quick example that illustrates how to do export varbinary data. The following query concatenates some … Read more
Being a dumb ‘merican, I think the lower case thorn character is 0xFE while upper case is 0xDE. This will become important soon. I created an SSIS package with a Flat File Connection Manager. I pointed it at a comma delimited file that looked like col 1,col 2,col 3 This allowed me to get the … Read more
To disable foreign key constraints: DECLARE @sql nvarchar(max) = N”; ;WITH x AS ( SELECT DISTINCT obj = QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + ‘.’ + QUOTENAME(OBJECT_NAME(parent_object_id)) FROM sys.foreign_keys ) SELECT @sql += N’ALTER TABLE ‘ + obj + N’ NOCHECK CONSTRAINT ALL; ‘ FROM x; EXEC sys.sp_executesql @sql; To re-enable: DECLARE @sql nvarchar(max) = N”; ;WITH x AS … Read more
This has been documented well in MSDN , running through the steps similar to as @houseofsql mentioned Step1: Exclude column names in first row in excel connection, use sql command as data access mode Step2: Alias column names in output column as matching your destination, Select * from [Sheet1$A2:I] will select from second row Finally … Read more
SQL server provide assemblies to manipulate packages programmatically. To do a reverse engineering (deserialize a dtsx package), You have to do this by looping over packages and read them programmatically, just follow this detailed link Reading DTS and SSIS packages programmatically There is another way (harder way and not recommended) to achieve this , by … Read more
Option A The ConnectionString property for an Excel Connection Manager is not where I go to manipulate the current file, which is contrast to an ordinary Flat File Connection Manager. Instead, put an expression on the Excel Connection Manager’s ExcelFilePath property. In theory, there should be no difference between ConnectionString and ExcelFilePath except that you … Read more