when you run Ssis package through BIDS or SSDT, package variables does get populated from environment variablabe. When it was running uder sql job agent, it failed to read from Env. Variables.
Solution:
1. Restart SQL SERVER AGENT for that instance and Intergration Service Engine
2. Restart SSMS
Try the following steps
Open the Developer Command Prompt for VS212 as Administrator
execute the command cd "C:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\PrivateAssemblies"
execute the command gacutil /if Microsoft.SqlServer.Dts.Design.dll
restart Visual Studio
Sometimes when it comes to truncate all tables to get a clean base, you need to drop all foreign keys instead of disabling
This link explains it perfectly
code below:
Tips: when it comes to recreate , sometimes you may have conflict error, that’s because table being referenced to has less data.
The simple way is to put WITH NOCHECK after ALTER TABLE XXXX
truncate table dbo.foreignkeyDropCreate
DECLARE @drop NVARCHAR(MAX) = N'',
@create NVARCHAR(MAX) = N'';
-- drop is easy, just build a simple concatenated list from sys.foreign_keys:
SELECT @drop += N'
ALTER TABLE ' + QUOTENAME(cs.name) + '.' + QUOTENAME(ct.name)
+ ' DROP CONSTRAINT ' + QUOTENAME(fk.name) + ';'
FROM sys.foreign_keys AS fk
INNER JOIN sys.tables AS ct
ON fk.parent_object_id = ct.[object_id]
INNER JOIN sys.schemas AS cs
ON ct.[schema_id] = cs.[schema_id];
INSERT foreignkeyDropCreate(drop_script) SELECT @drop;
-- create is a little more complex. We need to generate the list of
-- columns on both sides of the constraint, even though in most cases
-- there is only one column.
SELECT @create += N'
ALTER TABLE '
+ QUOTENAME(cs.name) + '.' + QUOTENAME(ct.name)
+ ' ADD CONSTRAINT ' + QUOTENAME(fk.name)
+ ' FOREIGN KEY (' + STUFF((SELECT ',' + QUOTENAME(c.name)
-- get all the columns in the constraint table
FROM sys.columns AS c
INNER JOIN sys.foreign_key_columns AS fkc
ON fkc.parent_column_id = c.column_id
AND fkc.parent_object_id = c.[object_id]
WHERE fkc.constraint_object_id = fk.[object_id]
ORDER BY fkc.constraint_column_id
FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'')
+ ') REFERENCES ' + QUOTENAME(rs.name) + '.' + QUOTENAME(rt.name)
+ '(' + STUFF((SELECT ',' + QUOTENAME(c.name)
-- get all the referenced columns
FROM sys.columns AS c
INNER JOIN sys.foreign_key_columns AS fkc
ON fkc.referenced_column_id = c.column_id
AND fkc.referenced_object_id = c.[object_id]
WHERE fkc.constraint_object_id = fk.[object_id]
ORDER BY fkc.constraint_column_id
FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'') + ');'
FROM sys.foreign_keys AS fk
INNER JOIN sys.tables AS rt -- referenced table
ON fk.referenced_object_id = rt.[object_id]
INNER JOIN sys.schemas AS rs
ON rt.[schema_id] = rs.[schema_id]
INNER JOIN sys.tables AS ct -- constraint table
ON fk.parent_object_id = ct.[object_id]
INNER JOIN sys.schemas AS cs
ON ct.[schema_id] = cs.[schema_id]
WHERE rt.is_ms_shipped = 0 AND ct.is_ms_shipped = 0;
UPDATE foreignkeyDropCreate SET create_script = @create;
PRINT @drop;
PRINT @create;
Step 1: Download TFS provider
Step 2: Close SSMS for TFS provider installation
BIDS Helper contains the feature and this is quite useful in terms of SSIS deployment
https://bidshelper.codeplex.com/wikipage?title=Fix%20Relative%20Paths
http://www.artisconsulting.com/blogs/greggalloway/2008/7/13/relative-paths-in-ssis
When a SSIS package has been deployed to target SSIS server, it was running fine under SSIS 2012 designer. When running designer, this package will be running under the windows credentials . When I create a sql job to run the package, it always gave me an error and this error message was always complaining about one specific SP syntax. After spending 2 days troubleshooting, I realize that there was nothing wrong with the SP, it was the Sql agent account which is the sql server service account that does not have permission on some folder in a remote server. After I’ve put this Service account in the admin group of that Remote server, the sql job runs without an issue.
Lessoned learned : 1. If ssis package contains folder access, always make sure which account this pkg will be run under. Does this account have access to the folder specified?
when I was working with SSDT-BI VS2012, I was often disappointed the GUI. Sometimes, I need to move object around or resize but the object will simply fly off to the remote area… blog from andy shed some useful hints
http://sqlblog.com/blogs/andy_leonard/archive/2012/03/20/ssis-2012-moving-and-resizing.aspx
also some workaround
1. to realign task to the top
Create an empty task (doesnt matter) in the top left corner - then zoom out of ure project .. ctrl+click the new task, and the next one down - then go to format - remove - vertical spacing and it will lock everything back to the top
2. To move object around, ctrl+direction Arrow, it’s slow but working
3. to resize an object, alt+direction Arrow
4. Use format –> auto Layout –> diagram will bring everything back but your object size and annotation will be totally screwed up.This is usually the last resort, strongly no recommended!!
Link:
http://www.25hoursaday.com/weblog/2010/03/29/TheNoSQLDebateAutomaticVsManualTransmission.aspx
The NoSQL Debate: Automatic vs. Manual Transmission
The debate on the pros and cons of non-relational databases which are typically described as “NoSQL databases” has recently been heating up. The anti-NoSQL backlash is in full swing from the rebuttal to one of my recent posts of mine I saw mentioned in Dennis Forbes’s write-up The Impact of SSDs on Database Performance and the Performance Paradox of Data Explodification (aka Fighting the NoSQL mindset) and similar thoughts expressed in typical rant-y style by Ted Dziuba in his post I Can't Wait for NoSQL to Die.
This will probably be my last post on the topic for a while given that the discussion has now veered into religious debate territory similar to vi vs. emacs OR functional vs. object oriented programming. With that said…
It would be easy to write rebuttals of what Dziuba and Forbes have written but from what I can tell people are now talking past each other and are now defending entrenched positions. So instead I’ll leave this topic with an analogy. SQL databases are like automatic transmission and NoSQL databases are like manual transmission. Once you switch to NoSQL, you become responsible for a lot of work that the system takes care of automatically in a relational database system. Similar to what happens when you pick manual over automatic transmission. Secondly, NoSQL allows you to eke more performance out of the system by eliminating a lot of integrity checks done by relational databases from the database tier. Again, this is similar to how you can get more performance out of your car by driving a manual transmission versus an automatic transmission vehicle.
However the most notable similarity is that just like most of us can’t really take advantage of the benefits of a manual transmission vehicle because the majority of our driving is sitting in traffic on the way to and from work, there is a similar harsh reality in that most sites aren’t at Google or Facebook’s scale and thus have no need for a Bigtable or Cassandra. As I mentioned in my previous post, I believe a lot of problems people have with relational databases at web scale can be addressed by taking a hard look at adding in-memory caching solutions like memcached to their infrastructure before deciding the throw out their relational database systems.
step 1: create the .NET project and build it
Step 2: Sign and rebuild
Step 3: Deploy to the GAC global assembly Cache use GACUTIL located at c:\windows\system32
Step 4: when to deploy to Target SSIS machine, try drag and drop the DLL into c:\windows\assembly or if it's .Net 4 in C:\Windows\Microsoft.NET\assembly\****\
Reference article: http://microsoft-ssis.blogspot.ca/2011/05/referencing-custom-assembly-inside.html#comment-form
Sometimes in SSIS, you may encounter this error when try to execute a ssis package
Solution:
End all dtsDebugHost.exe under Processes
Name might be different if OS is windows2012
Detailed explanation here:
http://microsoft-ssis.blogspot.ca/2013/12/the-process-cannot-acces-file.html
