SSIS 2012 Script Task "Could not load file or assembly"

I ran across a bug with SQL2012 install and SSIS script tasks. I was working on a simple ftp check file exists and for some reason after adding the System.Net.dll reference the task would result in a failure every time. Hit the we and came across a workaround that actually work. Please check out link 

After finding the article, I eventually figured out I could get all the components installed by running some of the installers on the SQL2012 DVD

• redist/VSTA/designtime/VSTA_DT30.msi 
• redist/VSTA/runtime/x64/VSTA_RT30.msi
• redist/VSTA/runtime/x86/VSTA_RT30.msi

AWS RedShift/S3 Data Warehouse Solution

Well over the last week I have been asked to setup a Amazon Web Service Redshift datawarehouse solution. At first I was a little taken back since I didn't know much about it. But over the past couple of days I have been reading over the infrastructure and design principals. At the end of the day after jumping through a few hoops it comes down to a traditional start schema design in PostgreSql backend. Below I will outline some key resources to help get started with the project.

• Creating an account with amazon, most of you already have one you just need to sign into the AWS Console. 
• You need to setup a drop point in S3. This allows you to drop bulk operation files into the online store for batch operations over to RedShift.
• You need to setup a RedShift cluster. Size and scale are key hear to cost. Make sure to start small and scale as your data set grows.
• http://docs.aws.amazon.com/redshift/latest/gsg/getting-started-launch-cluster.html
• You need to setup external ip access. By default you will not be able to connect via endpoint, jdbc, odbc connections
• http://docs.aws.amazon.com/redshift/latest/gsg/getting-started-authorize-access.html
• You need to setup local dev connectivity. I used Sql Workbench/J via jdbc drivers. 
• I found issues with 32/64 bit drivers and making sure the ip was trusted for connectivity
• http://docs.aws.amazon.com/redshift/latest/gsg/getting-started-connect-to-the-cluster.html
• Now that you are setup and connected you can start with creating a schema. Database, users, tables and stored procedures. One thing to keep in mind with developing your schema is you are working with a limited list of PostgreSql data types. 
• How to get mass amounts of data into your new cloud based hosted solution. This is were it gets a little tricky. Typically, a quick ETL tool like SSIS or TalenD would do the trick. But these tools send an insert as "Insert into". Ideally RedShift is looking for a "Insert". The difference is 10 rows per sec vs 100K per sec. So Amazon recommends S3 to RedShift "COPY" command. I would associate this command like a tsql bulk copy command in MSSQL. 
• Programmatic solution to loading data into S3
• Found the AWS SDK and they have examples of how to do this. The key is to make sure the permissions are setup correctly and the region is bound correctly in the app config.
• Using Data Pipeline to automate data from your drop point S3 to RedShift
• more to come
• Staging Updates and Deletes
• more to come

Please check back as I develop the solution I will provide additional updates. 

invoke-expression for sqlcmd from powershell

Having some issues running a sqlcmd via powershell on a sql instance with custom ports. Try the following command.

Does not work:
sqlcmd -S USHXXDBWD002\I0007DS01,4070 -E -Q "select getdate()"

Does work:
$query = "select getdate()"
$cmd = "SQLCMD -S `"USHGNDBWD002\I0007DS01,4070`" -h-1 -Q `"$query`""
write-host "command is: $cmd"
invoke-expression $cmd

So, when calling SQLCMD directly, quotes were not necessary around the ; however, when invoking SQLCMD from PowerShell using Invoke-Expresion, the multi-part identifier needed quotes.