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

image_thumb[1]

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


SNAGHTMLcaedf78_thumb[3]

In the following example the ShapeFile Data and the included dbf-data is stored in the table NUTS_BN_03M_2006 through a OleDbConnection. You can find the complete package in the download area.

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

Last edited Jun 13, 2011 at 8:25 PM by Tillmann, version 3

Comments

No comments yet.