To save the spatial Data within a SSIS package, you must use a script task.

image

Define all desired input colums to use them inside the script task.
Set a reference to Microsoft.SqlServer.Types inside the script task.


SNAGHTMLcaedf78

In the following example the ShapeFile Data and the included dbf-data is stored in the table NUTS_BN_03M_2006 through a OleDbConnection:

   1: string _ConnectionString = "Provider=SQLNCLI10;Server=.;Database=NUTS;Trusted_Connection=yes;";
<!--CRLF-->
   2: string _CommandString = "INSERT INTO [NUTS].[dbo].[NUTS_BN_03M_2006] ([ContentLength],[NumberOfParts]"
<!--CRLF-->
   3:                         + " , [NumberOfPoints],[Points],[RecordNumber],[ShapeType],[XMax]"
<!--CRLF-->
   4:                         + " ,[XMin],[YMax],[YMin],[OBJECTID],[NUTS_BN_ID],[STAT_LEVL_]"
<!--CRLF-->
   5:                         + " ,[EU_FLAG],[EFTA_FLAG],[CC_FLAG],[OTHR_CNTR_],[COAS_FLAG],[LEN],[Shape_Leng])"
<!--CRLF-->
   6:                         + " VALUES (?, ?, ?, geometry::STGeomFromText(?,4326), ?, ?, ?, ?, ?"
<!--CRLF-->
   7:                         + " ,? ,? ,? ,? ,? ,? ,? "
<!--CRLF-->
   8:                         + " ,? ,? ,? ,?)";
<!--CRLF-->
   9:  
<!--CRLF-->
  10: OleDbConnection _Connection = new OleDbConnection(_ConnectionString);
<!--CRLF-->
  11: OleDbCommand _Command = new OleDbCommand(_CommandString, _Connection);
<!--CRLF-->
  12:  
<!--CRLF-->
  13: if (!Row.ContentLength_IsNull)
<!--CRLF-->
  14: {
<!--CRLF-->
  15:     _Command.Parameters.Add("@ContentLength", OleDbType.Integer).Value = Row.ContentLength;
<!--CRLF-->
  16: }
<!--CRLF-->
  17: else
<!--CRLF-->
  18: {
<!--CRLF-->
  19:     _Command.Parameters.Add("@ContentLength", OleDbType.Integer).Value = DBNull.Value;
<!--CRLF-->
  20: }
<!--CRLF-->
  21:  
<!--CRLF-->
  22: if (!Row.NumberOfParts_IsNull)
<!--CRLF-->
  23: {
<!--CRLF-->
  24:     _Command.Parameters.Add("@NumberOfParts", OleDbType.Integer).Value = Row.NumberOfParts;
<!--CRLF-->
  25: }
<!--CRLF-->
  26: else
<!--CRLF-->
  27: {
<!--CRLF-->
  28:     _Command.Parameters.Add("@NumberOfParts", OleDbType.Integer).Value = DBNull.Value;
<!--CRLF-->
  29: }
<!--CRLF-->
  30:  
<!--CRLF-->
  31: if (!Row.NumberOfPoints_IsNull)
<!--CRLF-->
  32: {
<!--CRLF-->
  33:     _Command.Parameters.Add("@NumberOfPoints", OleDbType.Integer).Value = Row.NumberOfPoints;
<!--CRLF-->
  34: }
<!--CRLF-->
  35: else
<!--CRLF-->
  36: {
<!--CRLF-->
  37:     _Command.Parameters.Add("@NumberOfPoints", OleDbType.Integer).Value = DBNull.Value;
<!--CRLF-->
  38: }
<!--CRLF-->
  39:  
<!--CRLF-->
  40: if (!Row.Points_IsNull)
<!--CRLF-->
  41: {
<!--CRLF-->
  42:     byte[] blobData = Row.Points.GetBlobData(0, (int)Row.Points.Length);
<!--CRLF-->
  43:     SqlBytes SqlBlobData = new SqlBytes(blobData);
<!--CRLF-->
  44:     SqlString sqls = SqlGeometry.STGeomFromWKB(SqlBlobData, 4326).STAsText().ToSqlString();
<!--CRLF-->
  45:     _Command.Parameters.Add("@Point", OleDbType.VarWChar).Value = sqls.ToString();
<!--CRLF-->
  46: }
<!--CRLF-->
  47: else
<!--CRLF-->
  48: {
<!--CRLF-->
  49:     _Command.Parameters.Add("@Point", OleDbType.VarWChar).Value = DBNull.Value;
<!--CRLF-->
  50: }
<!--CRLF-->
  51:  
<!--CRLF-->
  52: if (!Row.RecordNumber_IsNull)
<!--CRLF-->
  53: {
<!--CRLF-->
  54:     _Command.Parameters.Add("@RecordNumber", OleDbType.Integer).Value = Row.RecordNumber;
<!--CRLF-->
  55: }
<!--CRLF-->
  56: else
<!--CRLF-->
  57: {
<!--CRLF-->
  58:     _Command.Parameters.Add("@RecordNumber", OleDbType.Integer).Value = DBNull.Value;
<!--CRLF-->
  59: }
<!--CRLF-->
  60:  
<!--CRLF-->
  61: if (!Row.ShapeType_IsNull)
<!--CRLF-->
  62: {
<!--CRLF-->
  63:     _Command.Parameters.Add("@ShapeType", OleDbType.Integer).Value = Row.ShapeType;
<!--CRLF-->
  64: }
<!--CRLF-->
  65: else
<!--CRLF-->
  66: {
<!--CRLF-->
  67:     _Command.Parameters.Add("@ShapeType", OleDbType.Integer).Value = DBNull.Value;
<!--CRLF-->
  68: }
<!--CRLF-->
  69:  
<!--CRLF-->
  70: if (!Row.XMax_IsNull)
<!--CRLF-->
  71: {
<!--CRLF-->
  72:     _Command.Parameters.Add("@XMax", OleDbType.Double).Value = Row.XMax;
<!--CRLF-->
  73: }
<!--CRLF-->
  74: else
<!--CRLF-->
  75: {
<!--CRLF-->
  76:     _Command.Parameters.Add("@XMax", OleDbType.Double).Value = DBNull.Value;
<!--CRLF-->
  77: }
<!--CRLF-->
  78:  
<!--CRLF-->
  79: if (!Row.XMin_IsNull)
<!--CRLF-->
  80: {
<!--CRLF-->
  81:     _Command.Parameters.Add("@XMin", OleDbType.Double).Value = Row.XMin;
<!--CRLF-->
  82: }
<!--CRLF-->
  83: else
<!--CRLF-->
  84: {
<!--CRLF-->
  85:     _Command.Parameters.Add("@XMin", OleDbType.Double).Value = DBNull.Value;
<!--CRLF-->
  86: }
<!--CRLF-->
  87:  
<!--CRLF-->
  88: if (!Row.YMax_IsNull)
<!--CRLF-->
  89: {
<!--CRLF-->
  90:     _Command.Parameters.Add("@YMax", OleDbType.Double).Value = Row.YMax;
<!--CRLF-->
  91: }
<!--CRLF-->
  92: else
<!--CRLF-->
  93: {
<!--CRLF-->
  94:     _Command.Parameters.Add("@YMax", OleDbType.Double).Value = DBNull.Value;
<!--CRLF-->
  95: }
<!--CRLF-->
  96:  
<!--CRLF-->
  97: if (!Row.YMin_IsNull)
<!--CRLF-->
  98: {
<!--CRLF-->
  99:     _Command.Parameters.Add("@YMin", OleDbType.Double).Value = Row.YMin;
<!--CRLF-->
 100: }
<!--CRLF-->
 101: else
<!--CRLF-->
 102: {
<!--CRLF-->
 103:     _Command.Parameters.Add("@YMin", OleDbType.Double).Value = DBNull.Value;
<!--CRLF-->
 104: }
<!--CRLF-->
 105:  
<!--CRLF-->
 106: if (!Row.OBJECTID_IsNull)
<!--CRLF-->
 107: {
<!--CRLF-->
 108:     _Command.Parameters.Add("@OBJECTID", OleDbType.VarWChar).Value = Row.OBJECTID;
<!--CRLF-->
 109: }
<!--CRLF-->
 110: else
<!--CRLF-->
 111: {
<!--CRLF-->
 112:     _Command.Parameters.Add("@OBJECTID", OleDbType.VarWChar).Value = DBNull.Value;
<!--CRLF-->
 113: }
<!--CRLF-->
 114:  
<!--CRLF-->
 115: if (!Row.NUTSBNID_IsNull)
<!--CRLF-->
 116: {
<!--CRLF-->
 117:     _Command.Parameters.Add("@NUTS_BN_ID", OleDbType.VarWChar).Value = Row.NUTSBNID;
<!--CRLF-->
 118: }
<!--CRLF-->
 119: else
<!--CRLF-->
 120: {
<!--CRLF-->
 121:     _Command.Parameters.Add("@NUTS_BN_ID", OleDbType.VarWChar).Value = DBNull.Value;
<!--CRLF-->
 122: }
<!--CRLF-->
 123:  
<!--CRLF-->
 124: if (!Row.STATLEVL_IsNull)
<!--CRLF-->
 125: {
<!--CRLF-->
 126:     _Command.Parameters.Add("@STAT_LEVL_", OleDbType.VarWChar).Value = Row.STATLEVL;
<!--CRLF-->
 127: }
<!--CRLF-->
 128: else
<!--CRLF-->
 129: {
<!--CRLF-->
 130:     _Command.Parameters.Add("@STAT_LEVL_", OleDbType.VarWChar).Value = DBNull.Value;
<!--CRLF-->
 131: }
<!--CRLF-->
 132:  
<!--CRLF-->
 133: if (!Row.EUFLAG_IsNull)
<!--CRLF-->
 134: {
<!--CRLF-->
 135:     _Command.Parameters.Add("@EU_FLAG", OleDbType.VarWChar).Value = Row.EUFLAG;
<!--CRLF-->
 136: }
<!--CRLF-->
 137: else
<!--CRLF-->
 138: {
<!--CRLF-->
 139:     _Command.Parameters.Add("@EU_FLAG", OleDbType.VarWChar).Value = DBNull.Value;
<!--CRLF-->
 140: }
<!--CRLF-->
 141:  
<!--CRLF-->
 142: if (!Row.EFTAFLAG_IsNull)
<!--CRLF-->
 143: {
<!--CRLF-->
 144:     _Command.Parameters.Add("@EFTA_FLAG", OleDbType.VarWChar).Value = Row.EFTAFLAG;
<!--CRLF-->
 145: }
<!--CRLF-->
 146: else
<!--CRLF-->
 147: {
<!--CRLF-->
 148:     _Command.Parameters.Add("@EFTA_FLAG", OleDbType.VarWChar).Value = DBNull.Value;
<!--CRLF-->
 149: }
<!--CRLF-->
 150:  
<!--CRLF-->
 151: if (!Row.CCFLAG_IsNull)
<!--CRLF-->
 152: {
<!--CRLF-->
 153:     _Command.Parameters.Add("@CC_FLAG", OleDbType.VarWChar).Value = Row.CCFLAG;
<!--CRLF-->
 154: }
<!--CRLF-->
 155: else
<!--CRLF-->
 156: {
<!--CRLF-->
 157:     _Command.Parameters.Add("@CC_FLAG", OleDbType.VarWChar).Value = DBNull.Value;
<!--CRLF-->
 158: }
<!--CRLF-->
 159:  
<!--CRLF-->
 160: if (!Row.OTHRCNTR_IsNull)
<!--CRLF-->
 161: {
<!--CRLF-->
 162:     _Command.Parameters.Add("@OTHR_CNTR_", OleDbType.VarWChar).Value = Row.OTHRCNTR;
<!--CRLF-->
 163: }
<!--CRLF-->
 164: else
<!--CRLF-->
 165: {
<!--CRLF-->
 166:     _Command.Parameters.Add("@OTHR_CNTR_", OleDbType.VarWChar).Value = DBNull.Value;
<!--CRLF-->
 167: }
<!--CRLF-->
 168:  
<!--CRLF-->
 169: if (!Row.COASFLAG_IsNull)
<!--CRLF-->
 170: {
<!--CRLF-->
 171:     _Command.Parameters.Add("@COAS_FLAG", OleDbType.VarWChar).Value = Row.COASFLAG;
<!--CRLF-->
 172: }
<!--CRLF-->
 173: else
<!--CRLF-->
 174: {
<!--CRLF-->
 175:     _Command.Parameters.Add("@COAS_FLAG", OleDbType.VarWChar).Value = DBNull.Value;
<!--CRLF-->
 176: }       
<!--CRLF-->
 177:  
<!--CRLF-->
 178: if (!Row.LEN_IsNull)
<!--CRLF-->
 179: {
<!--CRLF-->
 180:     _Command.Parameters.Add("@LEN", OleDbType.VarWChar).Value = Row.LEN;
<!--CRLF-->
 181: }
<!--CRLF-->
 182: else
<!--CRLF-->
 183: {
<!--CRLF-->
 184:     _Command.Parameters.Add("@LEN", OleDbType.VarWChar).Value = DBNull.Value;
<!--CRLF-->
 185: }
<!--CRLF-->
 186:  
<!--CRLF-->
 187: if (!Row.ShapeLeng_IsNull)
<!--CRLF-->
 188: {
<!--CRLF-->
 189:     _Command.Parameters.Add("@Shape_Leng", OleDbType.VarWChar).Value = Row.ShapeLeng;
<!--CRLF-->
 190: }
<!--CRLF-->
 191: else
<!--CRLF-->
 192: {
<!--CRLF-->
 193:     _Command.Parameters.Add("@Shape_Leng", OleDbType.VarWChar).Value = DBNull.Value;
<!--CRLF-->
 194: }
<!--CRLF-->
 195:  
<!--CRLF-->
 196: _Connection.Open();
<!--CRLF-->
 197: _Command.ExecuteNonQuery();
<!--CRLF-->
 198: _Connection.Close();
<!--CRLF-->
 199: _Connection.Dispose();
<!--CRLF-->
 200: _Command.Dispose();
<!--CRLF-->

Last edited Jun 13, 2011 at 9:19 PM by Tillmann, version 1

Comments

No comments yet.